Description:
When we try to execute mysqlshell upgrade checker utility on MySQL instance running 8.0.28 version with target as 8.0.35, it is not generating any warnings related to FULL and INTERSECT keywords.
However, when we execute the upgrade checker utility on MySQL instance running 8.0.12 version with target as 8.0.35, its generating warning for same keyworkds (FULL and INTERSECT).
These keywords (especially INTERSECT) are added in 8.0.31.
"INTERSECT (R); added in 8.0.31 (reserved)"
https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-I
mysqlshell:
https://github.com/mysql/mysql-shell/commit/05614fe9a1ccc1cdd8397f98a65f099806898dc4#diff-...
https://github.com/mysql/mysql-shell/commit/afdf4fb02aae172b891bd43728a7d9df882f44bb#diff-...
How to repeat:
1. create mysql instances running 8.0.12 and 8.0.28.
2. execute below sql commands on both instances to create required tables.
create database sbtest;
use sbtest
create table `ARRAY`(id int);
create table `FULL`(id int);
create table `INTERSECT`(id int);
create table `LATERAL`(id int);
create table `MEMBER`(id int);
8.0.12:-
[root@testbox ~]# /home/mysql80/mysql8012/bin/mysql -u root -S /tmp/mysql8012.sock -ptemp12345 sbtest -e"show tables"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_sbtest |
+------------------+
| ARRAY |
| FULL |
| INTERSECT |
| LATERAL |
| MEMBER |
+------------------+
[root@testbox ~]#
8.0.28:-
[root@testbox ~]# /home/mysql80/mysql8028/bin/mysql -u root -S /tmp/mysql8028.sock -ptemp12345 sbtest -e"show tables"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_sbtest |
+------------------+
| ARRAY |
| FULL |
| INTERSECT |
| LATERAL |
| MEMBER |
+------------------+
[root@testbox ~]#
3. Run mysqlshell (version 8.0.35) upgrade checker utility with target version as 8.0.35 on both instances
8.0.12:- (The output generate warning related to the all reserved keywords and check is working as expected)
[root@testbox ~]# mysqlsh8035 -- util checkForServerUpgrade root@/tmp%2Fmysql8012.sock --target-version=8.0.35 --output-format=TEXT
The MySQL server at /tmp%2Fmysql8012.sock, version 8.0.11 - MySQL Community
Server - GPL, will now be checked for compatibility issues for upgrade to MySQL
8.0.35...
1) Usage of db objects with names conflicting with new reserved keywords
Warning: The following objects have names that conflict with new reserved
keywords. Ensure queries sent by your applications use `quotes` when
referring to them or they will result in errors.
More information:
https://dev.mysql.com/doc/refman/en/keywords.html
sbtest.ARRAY - Table name
sbtest.MEMBER - Table name
sbtest.LATERAL - Table name
sbtest.FULL - Table name
sbtest.INTERSECT - Table name
.
.
Errors: 0
Warnings: 5
Notices: 0
NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
[root@testbox ~]#
8.0.28:- (the output is not showing any warnings for newly added keywords. In fact the check is completely skipped.)
[root@testbox ~]# mysqlsh8035 -- util checkForServerUpgrade root@/tmp%2Fmysql8028.sock --target-version=8.0.35 --output-format=TEXT
The MySQL server at /tmp%2Fmysql8028.sock, version 8.0.28 - MySQL Community
Server - GPL, will now be checked for compatibility issues for upgrade to MySQL
8.0.35...
1) Issues reported by 'check table x for upgrade' command
No issues found
2) Check for deprecated usage of single dollar signs in object names
No issues found
Errors: 0
Warnings: 0
Notices: 0
No known compatibility errors or issues were found.
[root@testbox ~]#
Suggested fix:
As far as I see (didn't test), we need to update below lines of code and include 8.0.31 as well.
namespace {
bool UNUSED_VARIABLE(register_reserved) = Upgrade_check::register_check(
&Sql_upgrade_check::get_reserved_keywords_check,
- Upgrade_check::Target::OBJECT_DEFINITIONS, "8.0.11", "8.0.14", "8.0.17");
+ Upgrade_check::Target::OBJECT_DEFINITIONS, "8.0.11", "8.0.14", "8.0.17", "8.0.31");
}
