Bug #83259 All routines in the sys schema are deleted by restoring
Submitted: 5 Oct 2016 5:10 Modified: 6 Oct 2016 0:41
Reporter: keita nishizono Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.7.xx, 5.7.15 OS:Any
Assigned to: CPU Architecture:Any

[5 Oct 2016 5:10] keita nishizono
Description:
After fixing Bug #76735 and #77926, mysqldump and mysqlpump by default skip 
the sys schema and rows in mysql.proc where db = 'sys' when using --all-databases option.
But because of these fixes, all routines in the sys schema are deleted by restoring.

How to repeat:
mysql> select count(*) from information_schema.ROUTINES where ROUTINE_SCHEMA='sys';
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.00 sec)

$ mysqldump [...] --all-databases > dump.sql

$ mysql [...] < dump.sql

mysql> select count(*) from information_schema.ROUTINES where ROUTINE_SCHEMA='sys';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select * from sys.host_summary;
ERROR 1356 (HY000): View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Suggested fix:
(1) get the fixes back to the original.
or
(2) add description about it to the manual.
or
(3) add some options to mysqldump that we can dump all data including the sys schema when using --all-databases option.
[5 Oct 2016 6:02] Umesh Shastry
Hello Nishizono-San,

Thank you for the report.
Imho this is documented in the manual - "mysqldump does not dump the INFORMATION_SCHEMA, performance_schema, or (as of MySQL 5.7.8) sys schema by default. To dump any of these, name them explicitly on the command line. You can also name them with the --databases option.." in http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

Also, for mysqlpump - http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html

Thanks,
Umesh
[5 Oct 2016 7:05] keita nishizono
Hello Umesh,
thanks for your comment.

>Imho this is documented in the manual - "mysqldump does not dump the INFORMATION_SCHEMA, performance_schema, or (as of MySQL 5.7.8) sys schema by default. To dump any of these, name them explicitly on the command line. You can also name them with the --databases option.." in
>http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

Yes, I know that.
It does not matter that mysqldump does not includes the sys schema, 
but the problem is that mysqldump would affect the existing sys schema.

For example, to set up replication, it is normal to dump all databases from the master with mysqldump and import it to the slave.
But at that time, all sys's routines is deleted from the slave without notice.
It is not desirable, so I think you shold add more details to the manual.
[5 Oct 2016 8:19] tsubasa tanaka
I can reproduce this issue.

`mysqldump --routines` does not dump sys-schema's routines, because Fix of 20902791 (https://github.com/mysql/mysql-server/commit/ded3155def2ba3356017c958c49ff58c2cae1830)

1. The dump from original server(mysqldump --all-databases --routines) has *NOT* information about sys-schema's view and sys-schema's routines.
2. New server has views and routines by mysqld --initialized, but
3. Restoring dump drops mysql.proc and doesn't have "CREATE ROUTINE" statement for sys-schema's routine.
4. As result, new server has views but doesn't have routines in sys-schema.

Workaround:
$ mysqldump --all-databases --routines --host=original_server > dump1.sql
$ mysqldump --databases sys --routines --host=original_server > dump2.sql
$ mysql --host=new_server < dump1.sql
$ mysql --host=new_server < dump2.sql
[5 Oct 2016 8:30] Umesh Shastry
Thank you for the feedback!

Thanks,
Umesh
[6 Oct 2016 0:41] keita nishizono
Thanks Tanaka-San.

mysql_upgrade is also another workaround:

$ mysqldump --all-databases --routines --host=original_server > dump1.sql
$ mysql --host=new_server < dump1.sql
$ mysql_upgrade --host=new_server
...
The sys schema is already up to date (version 1.5.1).
Found 0 sys functions, but expected 22. Re-installing the sys schema.
Upgrading the sys schema.
Checking databases.
sys.sys_config                                     OK
Upgrade process completed successfully.
...