[MDEV-21244] mysql_upgrade creating empty global_priv table Created: 2019-12-07  Updated: 2020-12-19  Resolved: 2020-04-27

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.4.10
Fix Version/s: 10.4.13

Type: Bug Priority: Critical
Reporter: Lulu Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: None
Environment:

Linux i686

Attachments: File mdbX.log    
Issue Links:
Relates
relates to MDEV-22630 mysql_upgrade (MariaDB 5.2.X --> Ma... Closed

 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)



 Comments   
Comment by Elena Stepanova [ 2019-12-18 ]

I can't reproduce it locally, and neither our CI tests nor any other customers reported it so far, so it is likely to be somehow specific to your instance.

Is it reproducible for you? If so, could you please

  • check that you don't have a mysql.global_priv table before the upgrade;
  • enable general log before you run mysql_upgrade, and after mysql_upgrade is done, review it to make sure it doesn't contain anything confidential, and then attach it to the issue or upload to ftp.asmonty.org/private.

Thanks.

Comment by Lulu [ 2019-12-18 ]

Thanks for reply, Elena.

No, i didnt had any mysql.global_priv table prior to mysql_upgrade -s -v -v
Issue is reproducible for 2 different instances, while i had 3 other instances upgraded without any issue.

general log attached as mdbX.log

Comment by Elena Stepanova [ 2019-12-29 ]

Okay, so your upgrade seems to run all the correct statements. Since it's just an upgrade, not creation of a new instance, the result entirely depends on the contents of the instance before the upgrade. In particular this statement is important:

IF 'BASE TABLE' = (select table_type from information_schema.tables where table_schema=database() and table_name='user') THEN
 
  CREATE TABLE IF NOT EXISTS global_priv (Host char(60) binary DEFAULT '', User char(80) binary DEFAULT '', Priv JSON NOT NULL DEFAULT '{}' CHECK(JSON_VALID(Priv)), PRIMARY KEY Host (Host,User)) engine=Aria transactional=1 CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges'
 
  SELECT Host, User, JSON_COMPACT(JSON_OBJECT('access',
 
                             1*('Y'=Select_priv)+
 
                             2*('Y'=Insert_priv)+
 
                             4*('Y'=Update_priv)+
 
                             8*('Y'=Delete_priv)+
 
                            16*('Y'=Create_priv)+
 
                            32*('Y'=Drop_priv)+
 
                            64*('Y'=Reload_priv)+
 
                           128*('Y'=Shutdown_priv)+
 
                           256*('Y'=Process_priv)+
 
                           512*('Y'=File_priv)+
 
                          1024*('Y'=Grant_priv)+
 
                          2048*('Y'=References_priv)+
 
                          4096*('Y'=Index_priv)+
 
                          8192*('Y'=Alter_priv)+
 
                         16384*('Y'=Show_db_priv)+
 
                         32768*('Y'=Super_priv)+
 
                         65536*('Y'=Create_tmp_table_priv)+
 
                        131072*('Y'=Lock_tables_priv)+
 
                        262144*('Y'=Execute_priv)+
 
                        524288*('Y'=Repl_slave_priv)+
 
                       1048576*('Y'=Repl_client_priv)+
 
                       2097152*('Y'=Create_view_priv)+
 
                       4194304*('Y'=Show_view_priv)+
 
                       8388608*('Y'=Create_routine_priv)+
 
                      16777216*('Y'=Alter_routine_priv)+
 
                      33554432*('Y'=Create_user_priv)+
 
                      67108864*('Y'=Event_priv)+
 
                     134217728*('Y'=Trigger_priv)+
 
                     268435456*('Y'=Create_tablespace_priv)+
 
                     536870912*('Y'=Delete_history_priv),
 
                    'ssl_type', ssl_type-1,
 
                    'ssl_cipher', ssl_cipher,
 
                    'x509_issuer', x509_issuer,
 
                    'x509_subject', x509_subject,
 
                    'max_questions', max_questions,
 
                    'max_updates', max_updates,
 
                    'max_connections', max_connections,
 
                    'max_user_connections', max_user_connections,
 
                    'max_statement_time', max_statement_time,
 
                    'plugin', if(plugin>'',plugin,if(length(password)=16,'mysql_old_password','mysql_native_password')),
 
                    'authentication_string', if(plugin>'' and authentication_string>'',authentication_string,password),
 
                    'password_last_changed', if(password_expired='Y', 0, UNIX_TIMESTAMP(password_last_changed)),
 
                    'password_lifetime', ifnull(password_lifetime, -1),
 
                    'account_locked', 'Y'=account_locked,
 
                    'default_role', default_role,
 
                    'is_role', 'Y'=is_role)) as Priv
 
  FROM user;

So, I can think of only two reasons why global_priv table can end up existing but empty:

  • it already existed and was empty (you said it is not so);
  • mysql.user table was empty before the upgrade.

If neither is the case, possibly there is a hidden problem related to the configuration or this particular build (you are running a custom build, right?).
For further investigation, I would suggest running the statement above manually on the schema before upgrade. Maybe you'll see what goes wrong right away.
If you don't, please provide your build (cmake) options and configuration files.

Comment by Lulu [ 2019-12-30 ]

Tables in mysql database prior to upgrade:

+---------------------------+
 
| Tables_in_mysql           |
 
+---------------------------+
 
| column_stats              |
 
| columns_priv              |
 
| db                        |
 
| event                     |
 
| func                      |
 
| general_log               |
 
| gtid_slave_pos            |
 
| help_category             |
 
| help_keyword              |
 
| help_relation             |
 
| help_topic                |
 
| host                      |
 
| index_stats               |
 
| plugin                    |
 
| proc                      |
 
| procs_priv                |
 
| proxies_priv              |
 
| roles_mapping             |
 
| servers                   |
 
| slow_log                  |
 
| table_stats               |
 
| tables_priv               |
 
| time_zone                 |
 
| time_zone_leap_second     |
 
| time_zone_name            |
 
| time_zone_transition      |
 
| time_zone_transition_type |
 
| user                      |
 
+---------------------------+
 
28 rows in set (0.001 sec)

The above statement fails because here:
ERROR 1054 (42S22): Unknown column 'authentication_string' in 'field list'

when replaced with auth_string, the following error appears:
_ERROR 1054 (42S22): Unknown column 'password_last_changed' in 'field list'
_

Here are 8 rows in user table. With fields:
_

Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin auth_string password_expired is_role default_role max_statement_time

_

--
 
-- Table structure for table `user`
 
--
 
 
 
DROP TABLE IF EXISTS `user`;
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE `user` (
 
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
 
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Delete_history_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
 
  `ssl_cipher` blob NOT NULL,
 
  `x509_issuer` blob NOT NULL,
 
  `x509_subject` blob NOT NULL,
 
  `max_questions` int(11) unsigned NOT NULL DEFAULT 0,
 
  `max_updates` int(11) unsigned NOT NULL DEFAULT 0,
 
  `max_connections` int(11) unsigned NOT NULL DEFAULT 0,
 
  `max_user_connections` int(11) NOT NULL DEFAULT 0,
 
  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `auth_string` text COLLATE utf8_bin NOT NULL,
 
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `max_statement_time` decimal(12,6) NOT NULL DEFAULT 0.000000,
 
  PRIMARY KEY (`Host`,`User`)
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';
 
/*!40101 SET character_set_client = @saved_cs_client */;

Database been mysql_upgrade'd from very old versions. maybe i havent ran mysql_upgrade on some step before, but when updating from 10.1 to 10.3 mysql_upgrade and mysql_upgrade --force isnt doing any changes anymore.

As for custom build: I ve tried official i686 binary too. Same result.

Comment by Elena Stepanova [ 2020-01-22 ]

Thanks. So, the culprit is indeed auth_string field, if I rename it this way in my table in 10.3, I get the same result (empty global_priv) after upgrade.

The upgrade scripts don't expect it and don't change it, because there seems to be no version where it would be named auth_string, it was added 10 years ago as authentication_string. Can you maybe try to remember where you got that table? Or, can you attach the .frm file of it, maybe we'll figure it out? (It doesn't contain any data, so it shouldn't be confidential).

As my colleague found out, the column with this name existed in MariaDB 5.2 (and probably 5.3), and was changed to authentication_string in MariaDB 5.5. Frankly I don't know how it worked for you till now, but apparently upgrade scripts don't expect such columns still exist. Maybe it needs to be changed.

Meanwhile, as a workaround, you can rename the field in the table before upgrade.
But first, please backup the unchanged files user.frm, user.MYI.-

Comment by Elena Stepanova [ 2020-01-22 ]

serg, I see two problems here.
First, that upgrade scripts don't attempt to rename auth_string to authentication_string. I don't know if it needs to be addressed, as the column existed in such old and long-EOLed versions, but I guess it couldn't hurt.

More importantly, mysql_upgrade seems to ignore the error which must be happening upon the attempt to use the column during CREATE .. SELECT. It still creates global_priv table later, by a plain CREATE, so the installation ends up with the created yet empty table. This part should be addressed I think.

Comment by Sergei Golubchik [ 2020-01-22 ]

First is easy, I'll fix it.

Second is tricky. mysql_fix_privileges.sql generates tons of "column not found" errors, they're normal and should be ignored. And mysql_upgrade ignores them. We cannot have just one "column not found" error special, but ignore all others. This can only be fixed by refactoring the upgrade script to not generate "column not found" errors at all.

Comment by Lulu [ 2020-01-23 ]

As my colleague found out, the column with this name existed in MariaDB 5.2 (and probably 5.3), and was changed to authentication_string in MariaDB 5.5. Frankly I don't know how it worked for you till now

worked fine and without any issues
with whatever-before-5.5 - 5.5.x - 10.0 - 10.1 - 10.2 and 10.3

Yes, both affected instances are existing for ages, maybe even before MariaDB. Maybe i havent ran mysql_upgrade before on 5.x, i dont remember, its been long time ago.

Comment by Lulu [ 2020-01-23 ]

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.

Comment by Stefan Seyfried [ 2020-03-20 ]

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.

Comment by Stefan Seyfried [ 2020-03-20 ]

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

Comment by Kristyna Streitova [ 2020-04-17 ]

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

Comment by Lulu [ 2020-05-14 ]

Sucessfully updated affected MariaDB installation to 10.4.13

спасибо!

Generated at Thu Feb 08 09:05:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.