MySQL Bugs: #110824: checkForServerUpgrade doesn't catch "intersect" as reserved word

Bug #110824	checkForServerUpgrade doesn't catch "intersect" as reserved word
Submitted:	26 Apr 2023 22:18	Modified:	14 Aug 2023 15:33
Reporter:	Soumya K	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell Upgrade Checker	Severity:	S2 (Serious)
Version:	8.0.31, 8.0.33	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
checkForServerUpgrade doesn't catch "intersect" as reserved word. That became reserved word with 8.0.31.

As expected, a create table using "intersect" as column name fails:

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.31-23 |
+-----------+
1 row in set (0.00 sec)

mysql> create table intersect_test  (intersect varchar(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'intersect varchar(10))' at line 1

How to repeat:

1). Install mysql-shell 8.0.33

ii  mysql-shell:amd64                      8.0.33-1ubuntu18.04                             amd64        MySQL Shell (part of MySQL Server) 8.0

2). Create a table in 5.7.29 database or any version before 8.0.31

mysql> create table intersect_test  (intersect varchar(10));
Query OK, 0 rows affected (0.05 sec)

3). Run upgradechecker

mysqlsh -- util checkForServerUpgrade --user=dbuser --socket=/path/to/socket --configPath=/path/to/my.cnf --target-version=8.0.31 

The MySQL server at /path/to/socket,
version 5.7.29-32-log - Source distribution, will now be checked for
compatibility issues for upgrade to MySQL 8.0.31...

1) Usage of old temporal type
  No issues found

2) MySQL 8.0 syntax check for routine-like objects
  No issues found

3) Usage of db objects with names conflicting with new reserved keywords
  No issues found

<SNIP>

Suggested fix:
Update checkForServerUpgrade to catch the new reserved word "intersect"

Hello Soumya,

Thank you for the report and feedback.

regards,
Umesh

Found out today that Upgradechecker reporting "full" as reserved when documentation doesn't record it as one https://dev.mysql.com/doc/mysqld-version-reference/en/keywords-8-0.html To repeat:

1). Create table with column name "full"

mysql> create table full_test (full varchar(10));
Query OK, 0 rows affected (0.05 sec)

2). Install mysql-shell 8.0.33

ii  mysql-shell:amd64                      8.0.33-1ubuntu18.04                             amd64        MySQL Shell (part of MySQL Server) 8.0

3). Run checkForServerUpgrade using:

mysqlsh -- util checkForServerUpgrade --user=<user> --socket=<socket> --password=<password> --configPath=/path/to/mycnf --target-version=8.0.31 

Output looks like:

The MySQL server at /path/to/sock, version
5.7.29-32-log - Source distribution, will now be checked for compatibility
issues for upgrade to MySQL 8.0.31...

1) Usage of old temporal type
  No issues found

2) MySQL 8.0 syntax check for routine-like objects
  No issues found

3) 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

  db_name.full_test.full - Column name

<SNIP>

Posted by developer:
 
Added the following note to the MySQL Shell 8.2.0 release notes:
	
The utility, checkForServerUpgrade did not recognize intersect as a reserved word. 
intersect was reserved in MySQL Server 8.0.31.

Posted by developer:
 
Updated existing bug to include 8.0.35 version:

Added the following note to the MySQL Shell 8.0.25 and 8.2.0 release notes:
	
The utility, checkForServerUpgrade did not recognize intersect as a reserved word. 
intersect was reserved in MySQL Server 8.0.31.