[MDEV-22542] mysql_upgrade fails on 10.4.13 in docker container Created: 2020-05-13  Updated: 2021-01-11  Resolved: 2020-05-15

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.4.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Chingis Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-24566 mysql_upgrade failed with "('mariadb.... Closed

 Description   

I run mysql_upgrade test for my mariadb docker image and it started to fail with the latest update. Works fine for 10.1, 10.2, 10.3 and worked fine for previous updates of 10.4

mysql_upgrade --upgrade-system-tables --verbose -uroot -ppassword -hmariadb
 
Looking for 'mysql' as: mysql
 
Looking for 'mysqlcheck' as: mysqlcheck
 
The --upgrade-system-tables option was used, user tables won't be touched.
 
Phase 1/7: Checking and upgrading mysql database
 
Processing databases
 
mysql
 
mysql.column_stats                                 OK
 
mysql.columns_priv                                 OK
 
mysql.db                                           OK
 
mysql.event                                        OK
 
mysql.func                                         OK
 
mysql.global_priv                                  OK
 
mysql.gtid_slave_pos                               OK
 
mysql.help_category                                OK
 
mysql.help_keyword                                 OK
 
mysql.help_relation                                OK
 
mysql.help_topic                                   OK
 
mysql.index_stats                                  OK
 
mysql.innodb_index_stats                           OK
 
mysql.innodb_table_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.transaction_registry                         OK
 
Phase 2/7: Installing used storage engines... Skipped
 
Phase 3/7: Fixing views... Skipped
 
Phase 4/7: Running 'mysql_fix_privilege_tables'
 
ERROR 1449 (HY000) at line 7: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 16: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 59: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 68: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 86: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 172: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 181: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 183: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 194: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 199: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 202: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 326: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 329: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 397: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 402: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 404: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 407: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 411: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 416: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 432: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1449 (HY000) at line 440: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
 
ERROR 1062 (23000) at line 620: Duplicate entry 'localhost-mysql-mariadb.sys-global_priv' for key 'PRIMARY'
 
FATAL ERROR: Upgrade failed



 Comments   
Comment by Elena Stepanova [ 2020-05-13 ]

Hi chingis,

The docker image at the moment has 10.4.12.
Could you please describe the steps that were performed before mysql_upgrade?

Thanks.

Comment by Chingis [ 2020-05-13 ]

Here's the build https://circleci.com/gh/wodby/mariadb/1025, it's 10.4.13. mysql_upgrade was run right after the server has started

Comment by Elena Stepanova [ 2020-05-13 ]

chingis,

You have this logic in your init_mariadb:

		"${mysql[@]}" <<-EOSQL
 
			-- What's done in this file shouldn't be replicated
 
			--  or products like mysql-fabric won't work
 
			SET @@SESSION.SQL_LOG_BIN=0;
 
 
 
			DELETE FROM mysql.user WHERE user NOT IN ('mysql.sys', 'mysqlxsys', 'root') OR host NOT IN ('localhost') ;
 
			SET PASSWORD FOR 'root'@'localhost'=PASSWORD('${MYSQL_ROOT_PASSWORD}') ;
 
			GRANT ALL ON *.* TO 'root'@'localhost' WITH GRANT OPTION ;
 
			${rootCreate}
 
			DROP DATABASE IF EXISTS test ;
 
			FLUSH PRIVILEGES ;
 
		EOSQL

So, it removes freshly created mariadb.sys account, the new owner of mysql.user. Hence the problem. At least, that's my guess for now. Can you fix it and try again?

Comment by Chingis [ 2020-05-15 ]

Thank you! Adding mariadb.sys user to the exclusion list resolved my issue. That was the bootstrap code I use from the official mariadb docker image, also created a PR for them https://github.com/docker-library/mariadb/pull/306

Comment by Elena Stepanova [ 2020-05-15 ]

Thanks.
Meanwhile, for those who already have a damaged container from dockerhub or one based on it, this should bring it back to normal:

insert into mysql.global_priv values ('localhost','mariadb.sys','{"access":0,"plugin":"mysql_native_password","authentication_string":"","account_locked":true,"password_last_changed":0}');
 
flush privileges;

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