Bug #84689 Improve "sys" upgrade message and provide a way to continue
Submitted: 26 Jan 2017 20:40 Modified: 27 Jan 2017 4:36
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.7.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: 5.7, mysql_upgrade, sys schema

[26 Jan 2017 20:40] Simon Mudd
I was recently bitten when doing a MySQL 5.7 upgrade by this error message when running mysql_upgrade:

Error occurred: A sys schema exists with no sys.version view. If you have a user created sys schema, this must be renamed for the upgrade to succeed.

This is explained partly by: http://mysqlserverteam.com/the-mysql-sys-schema-in-mysql-5-7-7/

How to repeat:
See above.

Suggested fix:
While the suggestion to rename a schema sounds good that's not actually something you can do. there is no RENAME DATABASE old_name TO new_name, so the instructions are confusing if nothing else.

Also if you created a new database and moved the tables (which you can do) then in any replicated setup you would probably break things if the tables to be moved were involved in replication.

It's clear that the "sys" database is now one that Oracle wants to own but I have had a database of the same name on a system I've been managing a long time before mysql-sys came along. Others may do too.

I have actually migrated most of the tables away leaving just a couple of tables which if moved would require code changes and right now that's not convenient. However there is no conflict in the names and they can happily live side by side of the mysql-sys (now called sys views and so on).

What's missing from the current documentation is a way to override the "brake" that is added when doing a mysql upgrade to 5.7 (as otherwise I have to put special exceptions into my 5.7+ upgrade code (to use  --skip-sys-schema   Do not upgrade/install the sys schema.) which is not convenient.

In a similar way to the option which was added some time ago (requested by me for similar but unrelated reasons), using the  --upgrade-system-tables , which changed the default "safe" behaviour, I would like to see something like --enable-sys-schema so that I can do this once, the "mysql sys" schema will get created and later upgrades will work "normally".

Yes, it's good to be clear that this is treading in slightly murky waters which is why it should not be the default behaviour but I think an action like this is needed as otherwise this needs to be fixed by hand which takes more effort.

I fixed this by installing the mysql-sys 5.6 setup which created the appropriate version view but only as I had a script which did this at hand.  The documentation should _recommend_ that "sys" is not used by users for any new databases and an attempt should be made to move "user information" elsewhere.  However, adding the workaround with an appropriate message, and adjusting the documentation to explain the caveats will avoid people trying to figure out how to work around this problem with every upgrade which in the end just adds a maintenance burden which is unneeded.

So please consider adding an option to mysql_upgrade (--enable-sys-schema) on 5.7+ which will allow the creation of the standard sys schema on 5.7+ even if the database already exists and the required version view is missing.

I'd expect the logging to show something like:

Enabling the creating of "MySQL sys views" on a system with a sys database and no version view as requested with --enable-sys-views.

and the existing logging message to indicate something like:

Error occurred: A sys schema exists with no sys.version view. If you have a user created sys schema please see XXXXXX as MySQL sys views may conflict with existing structures in this database.  You may use the option --enable-sys-view to bypass this warning but special care should be taken if proceeding.
[27 Jan 2017 4:36] MySQL Verification Team
Hello Simon,

Thank you for the report and feedback.