MySQL Bugs: #83259: All routines in the sys schema are deleted by restoring

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

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.

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

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.

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

Thank you for the feedback!

Thanks,
Umesh

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.
...