Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21244

mysql_upgrade creating empty global_priv table

Details

    Description

      When upgrading from 10.3, running mysql_upgrade on user table with ~15 records empty global_priv table is created, server is unusable, nobody cant connect without skip-grant-tables.

      No errors shown by mysql_upgrade output:

      root /var/lib # /usr/local/mariadb/bin/mysql_upgrade -v -v -s -p
      Enter password: 
      Looking for 'mysql' as: /usr/local/mariadb/bin/mysql
      Looking for 'mysqlcheck' as: /usr/local/mariadb/bin/mysqlcheck
      The --upgrade-system-tables option was used, user tables won't be touched.
      Phase 1/7: Checking and upgrading mysql database
      Running 'mysqlcheck' with connection arguments: --port='3306' --socket='/var/run/mysqld/mysqld.sock' 
      # Connecting to localhost...
      # Disconnecting from localhost...
      Processing databases
      mysql
      mysql.column_stats                                 OK
      mysql.columns_priv                                 OK
      mysql.db                                           OK
      mysql.event                                        OK
      mysql.func                                         OK
      mysql.gtid_slave_pos                               OK
      mysql.help_category                                OK
      mysql.help_keyword                                 OK
      mysql.help_relation                                OK
      mysql.help_topic                                   OK
      mysql.host                                         OK
      mysql.index_stats                                  OK
      mysql.plugin                                       OK
      mysql.proc                                         OK
      mysql.procs_priv                                   OK
      mysql.proxies_priv                                 OK
      mysql.roles_mapping                                OK
      mysql.servers                                      OK
      mysql.table_stats                                  OK
      mysql.tables_priv                                  OK
      mysql.time_zone                                    OK
      mysql.time_zone_leap_second                        OK
      mysql.time_zone_name                               OK
      mysql.time_zone_transition                         OK
      mysql.time_zone_transition_type                    OK
      mysql.user                                         OK
      Phase 2/7: Installing used storage engines... Skipped
      Phase 3/7: Fixing views... Skipped
      Phase 4/7: Running 'mysql_fix_privilege_tables'
      Phase 5/7: Fixing table and database names ... Skipped
      Phase 6/7: Checking and upgrading tables... Skipped
      Phase 7/7: Running 'FLUSH PRIVILEGES'
      OK
      root /var/lib # mysql.server restart
      Shutting down MySQL. SUCCESS! 
      Starting MySQL. SUCCESS! 
      

      MariaDB [mysql]> select * from global_priv;
      Empty set (0.002 sec)
      

      Attachments

        Issue Links

          Activity

            Lulu Lulu added a comment - - edited

            renaming just 1 column doesnt work

            ALTER TABLE `user`
             
            CHANGE `auth_string` `authentification_string` text COLLATE 'utf8_bin' NOT NULL AFTER `plugin`;
            

            see error in above comment: _ERROR 1054 (42S22): Unknown column 'password_last_changed' in 'field list'

            MariaDB [mysql]> select * from global_priv;
            Empty set (0.000 sec)

            still goes with empty set.

            Lulu Lulu added a comment - - edited renaming just 1 column doesnt work ALTER TABLE ` user `   CHANGE `auth_string` `authentification_string` text COLLATE 'utf8_bin' NOT NULL AFTER `plugin`; see error in above comment: _ERROR 1054 (42S22): Unknown column 'password_last_changed' in 'field list' MariaDB [mysql]> select * from global_priv; Empty set (0.000 sec) still goes with empty set.

            I had the same problem on an openSUSE Leap 15.1 => 15.2 update, updating from 10.2.31 to 10.4.12, ending up with an empty global_priv table and no access to the DB..
            See https://bugzilla.suse.com/show_bug.cgi?id=1166781 for details.

            I was able to fix the issue after the fact by

            • starting mysqld with "--skip-grant-tables"
            • ALTER TABLE user CHANGE COLUMN `auth_string` `authentication_string` text;
            • DROP TABLE global_priv;
            • now running mariadb-upgrade

            Afterwards, global_priv was properly populated (AFAICT) and my database is working again.

            seife Stefan Seyfried added a comment - I had the same problem on an openSUSE Leap 15.1 => 15.2 update, updating from 10.2.31 to 10.4.12, ending up with an empty global_priv table and no access to the DB.. See https://bugzilla.suse.com/show_bug.cgi?id=1166781 for details. I was able to fix the issue after the fact by starting mysqld with "--skip-grant-tables" ALTER TABLE user CHANGE COLUMN `auth_string` `authentication_string` text; DROP TABLE global_priv; now running mariadb-upgrade Afterwards, global_priv was properly populated (AFAICT) and my database is working again.

            I can also provide /var/lib/mysql with an affected database that's broken after mariadb-upgrade

            seife Stefan Seyfried added a comment - I can also provide /var/lib/mysql with an affected database that's broken after mariadb-upgrade

            This issue also affects MariaDB 10.4 in SLE-15-SP2 future release. Do you plan to fix it soon? Thanks!

            kstreitova Kristyna Streitova added a comment - This issue also affects MariaDB 10.4 in SLE-15-SP2 future release. Do you plan to fix it soon? Thanks!
            Lulu Lulu added a comment -

            Sucessfully updated affected MariaDB installation to 10.4.13

            спасибо!

            Lulu Lulu added a comment - Sucessfully updated affected MariaDB installation to 10.4.13 спасибо!

            People

              serg Sergei Golubchik
              Lulu Lulu
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.