Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4.10
-
None
-
Linux i686
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
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Labels | need_feedback |
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
| Labels | need_feedback |
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.
| Labels | need_feedback |
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.
| Labels | need_feedback |
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.-
| Priority | Blocker [ 1 ] | Major [ 3 ] |
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.
| Fix Version/s | 10.4 [ 22408 ] | |
| Assignee | Sergei Golubchik [ serg ] |
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.
| Priority | Major [ 3 ] | Critical [ 2 ] |
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.
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.
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!
| 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) |
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: {noformat} 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! {noformat} {noformat} MariaDB [mysql]> select * from global_priv; Empty set (0.002 sec) {noformat} |
| Summary | mysql_upgrade creating empty global_priv table when updating from 10.3 | mysql_upgrade creating empty global_priv table |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.4.13 [ 24223 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 101718 ] | MariaDB v4 [ 157063 ] |
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
Thanks.