Bug #77926 mysqldump/mysqlpump includes sys schema stored programs even if skipping schema

• Submitted: 4 Aug 2015 0:53
• Modified: 29 Sep 2015 16:18
• Reporter: Jesper wisborg Krogh
• Status: Closed
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S3 (Non-critical)
• Version: 5.7.8
• OS: Any
• CPU Architecture: Any

[4 Aug 2015 0:53] Jesper wisborg Krogh

Description:
After fixing Bug 20902791 ( http://bugs.mysql.com/bug.php?id=76735 ) mysqldump and mysqlpump by default skips the sys schema. However the stored program definitions are still included if the mysql.proc table is included in the backup.

How to repeat:
Using mysqldump as an example:

shell$ mysqldump --all-databases > dump.sql

Remove the sys schema:

mysql> DROP DATABASE sys;
Query OK, 92 rows affected (0.10 sec)

mysql> SELECT COUNT(*) FROM mysql.proc WHERE db = 'sys';
+----------+                                                         
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Load the dump:

shell$ mysql < dump.sql

Now:

mysql> SELECT COUNT(*) FROM mysql.proc WHERE db = 'sys';
+----------+
| COUNT(*) |
+----------+
|       36 |
+----------+
1 row in set (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

Suggested fix:
Don't include rows in mysql.proc with db = 'sys' when the sys schema is skipped.

[4 Aug 2015 0:55] Jesper wisborg Krogh

Posted by developer:
 
In principle this also applies to mysql.event however the sys schema currently does not install any events.

[29 Sep 2015 16:18] Paul DuBois

Noted in 5.7.9, 5.8.0 changelogs.

mysqldump and mysqlpump output included sys schema stored programs
even when the sys schema was not dumped.