Bug #92631 importing dump from mysqldump --all-databases breaks SYS schema due to routines

• Submitted: 2 Oct 2018 8:07
• Modified: 2 Oct 2018 9:26
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Verified
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S3 (Non-critical)
• Version: 5.7.23
• OS: Any
• CPU Architecture: Any

[2 Oct 2018 8:07] Shane Bester

Description:
Affects 5.7.   8.0 works as expected.
Here's the sequence of events:
It works fine in new system :

E:\mysql-5.7.23-winx64\bin>mysql -uroot -e"select * from sys.memory_global_total;"
+-----------------+
| total_allocated |
+-----------------+
| 131.20 MiB      |
+-----------------+

We take a dumpfile:

E:\mysql-5.7.23-winx64\bin>mysqldump --all-databases --routines -uroot > t.sql

Import the dumpfile:

E:\mysql-5.7.23-winx64\bin>mysql -uroot < t.sql

Broken, because mysql.proc was recreated empty:

E:\mysql-5.7.23-winx64\bin>mysql -uroot -e"select * from sys.memory_global_total;"
ERROR 1356 (HY000) at line 1: View 'sys.memory_global_total' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

E:\mysql-5.7.23-winx64\bin>

How to repeat:
mysql -uroot -e"select * from sys.memory_global_total;"
mysqldump --all-databases --routines -uroot > t.sql
mysql -uroot < t.sql
mysql -uroot -e"select * from sys.memory_global_total;"

Suggested fix:
Workaround: run mysql_upgrade --force on the restored system to recreate SYS schema.

Dump the mysql.proc table directly and import it afterwards.

[2 Oct 2018 8:13] MySQL Verification Team

Documented workaround:
https://dev.mysql.com/doc/refman/5.7/en/sys-schema-usage.html

Neither mysqldump nor mysqlpump dump the sys schema by default. To generate a dump file, name the sys schema explicitly on the command line using either of these commands:

mysqldump --databases --routines sys > sys_dump.sql
mysqlpump sys > sys_dump.sql

To reinstall the schema from the dump file, use this command:

mysql < sys_dump.sql

[2 Oct 2018 9:26] MySQL Verification Team

Thank you for the bug report.

miguel@luz:~/dbs $ 5.7/bin/mysql -uroot --socket=/tmp/mysql57.sock -e "SHOW VARIABLES LIKE '%VERSION%'"
+-------------------------+----------------------------------------+
| Variable_name           | Value                                  |
+-------------------------+----------------------------------------+
| innodb_version          | 5.7.25                                 |
| protocol_version        | 10                                     |
| slave_type_conversions  |                                        |
| tls_version             | TLSv1,TLSv1.1                          |
| version                 | 5.7.25                                 |
| version_comment         | Source distribution BUILD: 2018-SET-27 |
| version_compile_machine | x86_64                                 |
| version_compile_os      | Linux                                  |
+-------------------------+----------------------------------------+
miguel@luz:~/dbs $ 5.7/bin/mysqldump -uroot --socket=/tmp/mysql57.sock --all-databases --routines -uroot > t.sql
miguel@luz:~/dbs $ 5.7/bin/mysql -uroot --socket=/tmp/mysql57.sock < t.sql
miguel@luz:~/dbs $ 5.7/bin/mysql -uroot --socket=/tmp/mysql57.sock -e "select * from sys.memory_global_total;"
ERROR 1356 (HY000) at line 1: View 'sys.memory_global_total' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
miguel@luz:~/dbs $