Bug #96351 Check failed and failed to detect sql_mode flags if SQL_MODE is set
Submitted: 27 Jul 2019 6:05 Modified: 10 Sep 2019 15:17
Reporter: Ryusuke Kajiyama Email Updates:
Status: Closed Impact on me:
None 
Category:Shell Upgrade Checker Severity:S3 (Non-critical)
Version:8.0.17 OS:Any
Assigned to: CPU Architecture:Any

[27 Jul 2019 6:05] Ryusuke Kajiyama 
Description:
Setting SQL_MODE to ORACLE, Upgrade Checker fails to check #3 utf8mb3 charset and #17 Schema inconsistencies.

If SQL_MODE is MAXDB, same failures and fails to detect 7) Usage of obsolete MAXDB sql_mode flag.

NO_AUTO_CREATE_USER which was removed in 8.0, was not detected in 8) Usage of obsolete sql_mode flags.

Tested on MySQL 5.7.21 on macOS

How to repeat:
Write SQL_MODE in my.cnf of 5.7, then run Upgrade Checker.
[27 Jul 2019 6:23] MySQL Verification Team 
Hello Kajiyama-San,

Thank you for the report and feedback.

regards,
Umesh
[10 Sep 2019 15:17] Margaret Fisher 
Posted by developer:
 
Fixed with WL #13376, included in changelog entry for MySQL Shell 8.0.18 as follows:

MySQL Shell's upgrade checker utility (the util.checkForServerUpgrade() operation) includes the following new and extended checks:        

- The utility now checks for tablespace names containing the string "FTS", which can be incorrectly identified as tablespaces of full-text index tables, preventing upgrade. The issue has been fixed in MySQL 8.0.18, but affects upgrades to earlier MySQL 8.0 releases. 
- The check for database objects with names that conflict with reserved keywords now covers the additional keywords ARRAY, MEMBER, and LATERAL.
- The checks for obsolete sql_mode flags now check the global sql_mode setting.

Running the upgrade checker utility no longer alters the gtid_executed value, meaning that the utility can be used  on Group Replication group members without affecting their synchronization with the group. The upgrade checker also now works correctly with the ANSI_QUOTES SQL mode.