[MDEV-27888] Errors upgrading from Mariadb 10.2.15 to 10.6.7 Created: 2022-02-18  Updated: 2022-04-04  Resolved: 2022-04-04

Status: Closed
Project: MariaDB Server
Component/s: Upgrades
Affects Version/s: 10.6.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Ritwik C Pandav Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None
Environment:

RHEL 3.10.0-1160.25.1.el7.x86_64


 Description   

Was performing an upgrade from Mariadb 10.2.15 to 10.6.7, by first completely unistalling and then installing the upgraded version:

yum remove MariaDB-server MariaDB-client
yum clean all
yum install MariaDB-server MariaDB-client

Proceeded to start mariadb service:

systemctl start mariadb

Run upgrade:

sudo mysql_upgrade -uroot -p

Got the following errors:

sys.x$waits_by_host_by_latency OK
sys.x$waits_by_user_by_latency OK
sys.x$waits_global_by_latency OK
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1449 (HY000) at line 32: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 41: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 84: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 93: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 111: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 197: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 206: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 208: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 219: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 224: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 227: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 353: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 356: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 425: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 430: 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 435: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 439: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 444: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 465: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 473: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1 (HY000) at line 553: Can't create/write to file '/tmp/#sql-temptable-4c02-12-195.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 567: Can't create/write to file '/tmp/#sql-temptable-4c02-12-197.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 568: Can't create/write to file '/tmp/#sql-temptable-4c02-12-198.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 570: Can't create/write to file '/tmp/#sql-temptable-4c02-12-199.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 629: Can't create/write to file '/tmp/#sql-temptable-4c02-12-19a.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 757: Can't create/write to file '/tmp/#sql-temptable-4c02-12-19c.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 787: Can't create/write to file '/tmp/#sql-temptable-4c02-12-1a1.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 2287: Can't create/write to file '/tmp/#sql-temptable-4c02-12-1b0.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 2306: Can't create/write to file '/tmp/#sql-temptable-4c02-12-1b1.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 2451: Can't create/write to file '/tmp/#sql-temptable-4c02-12-1b7.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 2476: Can't create/write to file '/tmp/#sql-temptable-4c02-12-1b8.MAI' (Errcode: 13 "Permission denied")

Reading from other similar issues, attempted to fix the definer does not exist by running the following commands:

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

GRANT ALL PRIVILEGES ON . TO 'mariadb.sys'@'localhost' WITH GRANT OPTION;

flush privileges;

Re-running mysql upgrade, see the permission denied errors only:

sys.x$wait_classes_global_by_avg_latency OK
sys.x$wait_classes_global_by_latency OK
sys.x$waits_by_host_by_latency OK
sys.x$waits_by_user_by_latency OK
sys.x$waits_global_by_latency OK
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1 (HY000) at line 553: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-100.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 567: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-102.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 568: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-103.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 570: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-104.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 629: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-105.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 757: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-107.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 787: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-10c.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 2287: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-11b.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 2306: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-11c.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 2451: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-122.MAI' (Errcode: 13 "Permission denied")
ERROR 1 (HY000) at line 2476: Can't create/write to file '/tmp/#sql-temptable-4dfb-a-123.MAI' (Errcode: 13 "Permission denied")
FATAL ERROR: Upgrade failed

Here are the privileges for /tmp:

[root@xsdclxmdndb001 hscadmin]$ namei -om /tmp
f: /tmp
dr-xr-xr-x root root /
drwxrwxrwt root root tmp

 Comments   
Comment by Ritwik C Pandav [ 2022-02-21 ]

Added config to read another temp directory in /etc/my.cnf

[mysqld]
tmpdir = /logsdb/

Where

[root@xsdclxmdndb001 logsdb]$ namei -om /logsdb/
f: /logsdb/
dr-xr-xr-x root root /
drwxr-xr-x mysql mysql logsdb

This seems to work

Comment by Ritwik C Pandav [ 2022-02-21 ]

Noticed the following errors in the mysql upgrade script:

[root@xsdclxmdndb001 my.cnf.d]$ sudo mysql_upgrade -uroot -phsc321
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
mysql.user
Error : SELECT command denied to user 'mariadb.sys'@'localhost' for table 'global_priv'
Error : View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
error : Corrupt
sys.host_summary OK
sys.host_summary_by_file_io OK
sys.host_summary_by_file_io_type OK

Any idea why this happens? What's the repercussion of this error and how to avoid it?

Comment by Sergei Golubchik [ 2022-02-22 ]

First, try to select from mysql.user. If it works — the view is fine.

If not:
Repercussion: if you have tools that use mysql.user they might not work.
Better not use those tools anyway, as mysql.user is an incomplete representation of the true account data mysql.global_priv, so you'll miss information if you'll use tools that rely on mysql.user.
But anyway, you can try to drop the view and re-run mariadb-upgrade, it could recreate the view with the correct definition.

Comment by Elena Stepanova [ 2022-03-05 ]

FLUSH PRIVILEGES or server restart should do the trick, I think.

The mystery here is why the permission denied error occurred. The sticky bit alone doesn't cause it, not even on RHEL 7 (I checked).
Also, if the directory weren't writable, the server probably wouldn't even start, InnoDB tries to write something into the tmpdir on startup.
The presence of temporary files with the same names but wrong ownership could have done it, but such a coincidence seems to be very unlikely.
So, I'm out of ideas.

But assuming there was a valid reason for permission denied errors, the rest seems clear enough.

  • Upon the very first mysql_upgrade attempt after 10.2=>10.6, only permission denied errors would occur (this run is not shown in the bug description, the description contains an output of a re-run). At this point, mysql.user is still a table and no mariadb.sys user exists; mysql_upgrade attempts to create mariadb.sys user first, fails to do so because it cannot create a temporary table, but proceeds far enough to create mysql.user view and sysschema views, and eventually aborts. So, it ends up with a bunch of views with a non-existing definer.
  • Upon further attempts, it can't read the views (hence the errors about mysql.user and complaints about non-existing definer), and still can't create temporary tables, hence permission denied errors.
  • After the tmpdir is pointed to something writable and the server is restarted, the next mysql_upgrade succeeds at creating mariadb.sys; but it does so by inserting into the table, without flushing privileges, so authentication remains unaware of the existence of mariadb.sys. FLUSH PRIVILEGES is executed at the very end of mysql_upgrade, but it aborts before that, so it never happens. Thus, this and consequent mysql_upgrade runs still show the error for mysql.user and complain about "non-existing" mariadb.sys. To get rid of the errors, either FLUSH PRIVILEGES should be run explicitly, or the server should be restarted, and then mysql_upgrade should be performed again, to make sure everything is executed to the end.

Maybe it's possible to reshuffle mysql_upgrade actions a bit or make it less tolerant to intermediate errors (so that it didn't create views after failing to create the definer, for example), but I'm not sure it's worth the risk of causing some other problems.

It would be still good to understand the cause of the "access denied" errors. twix1312, did you ever figure out why they were happening?

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