Description:
When using MySQL Shell 8.0, the checkForServerUpgrade utility doesn't report any errors for procedures with identifiers beginning with $$ when checking compatibility between MySQL 5.7.44 and 8.0.36 (or 8.0.41). However, when running the same check using MySQL Shell 8.4.4, errors are reported for these identifiers.
The use of the $ sign in identifiers was deprecated in MySQL 8.0.32.
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-32.html#mysqld-8-0-32-deprecation...
In theory, this should be treated as a warning when upgrading to any 8.0.x version. However, the utility is unexpectedly reporting these as errors.
Conversely, when checking for an upgrade from 8.0.36 (or 8.0.41) to 8.4, we would expect to see errors for identifiers beginning with $$. Surprisingly, neither errors nor warnings are reported in this case.
How to repeat:
[root@Testbox ~]# mysql80 -u root -p********** -S /tmp/mysql_sandbox5744.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql>
mysql> create table $$test1(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table $test2(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| $$test1 |
| $test2 |
+----------------+
2 rows in set (0.00 sec)
mysql> delimiter //
mysql> create procedure test123() begin declare $$samp int; end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> show create procedure test123\G
*************************** 1. row ***************************
Procedure: test123
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `test123`()
begin declare $$samp int; end
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
mysql> \q
Bye
[root@Testbox ~]#
## Now run upgrade checks using mysqlshell 8.4 and we see error in routineSyntax check with status OK
[root@Testbox ~]# cd -
/root/mysql-shell-8.4.4-linux-glibc2.17-x86-64bit/bin
[root@Testbox bin]#
[root@Testbox bin]# ./mysqlsh --version
./mysqlsh Ver 8.4.4 for Linux on x86_64 - for MySQL 8.4.4 (MySQL Community Server (GPL))
[root@Testbox bin]#
[root@Testbox bin]# ./mysqlsh -- util checkForServerUpgrade root@localhost:5744 --target-version=8.0.36 --output-format=JSON --config-path=/root/sandboxes/msb_5_7_44/my.sandbox.cnf
{
"serverAddress": "localhost:5744",
"serverVersion": "5.7.44 - MySQL Community Server (GPL)",
"targetVersion": "8.0.36",
"errorCount": 1,
"warningCount": 41,
"noticeCount": 2,
"summary": "1 errors were found. Please correct these issues before upgrading to avoid compatibility issues.",
"checksPerformed": [
{
"id": "oldTemporal",
"title": "Usage of old temporal type",
"status": "OK",
"detectedProblems": [],
"solutions": []
},
{
"id": "routineSyntax",
"title": "MySQL syntax check for routine-like objects",
"status": "OK",
"description": "The following objects did not pass a syntax check with the latest MySQL grammar. A common reason is that they reference names that conflict with new reserved keywords. You must update these routine definitions and `quote` any such references before upgrading.",
"documentationLink": "https://dev.mysql.com/doc/refman/en/keywords.html",
"detectedProblems": [
{
"level": "Error",
"dbObject": "test.test123",
"description": "at line 1,14: unexpected token '$$samp int; end$$'",
"dbObjectType": "Routine"
}
],
"solutions": []
},
.
.
[root@Testbox bin]#
[root@Testbox bin]# cd
[root@Testbox ~]#
## When we use mysqlshell 8.0 and run upgrade checks, we see neither error or warning from routinesSyntaxCheck
[root@Testbox ~]# mysqlsh8037 --version
/root/mysql-shell-8.0.37-linux-glibc2.12-x86-64bit/bin/mysqlsh Ver 8.0.37 for Linux on x86_64 - for MySQL 8.0.37 (MySQL Community Server (GPL))
[root@Testbox ~]#
[root@Testbox ~]# mysqlsh8037 -- util checkForServerUpgrade root@localhost:5744 --target-version=8.0.36 --output-format=JSON --config-path=/root/sandboxes/msb_5_7_44/my.sandbox.cnf
{
"serverAddress": "localhost:5744",
"serverVersion": "5.7.44 - MySQL Community Server (GPL)",
"targetVersion": "8.0.36",
"errorCount": 0,
"warningCount": 30,
"noticeCount": 2,
"summary": "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.",
"checksPerformed": [
{
"id": "oldTemporalCheck",
"title": "Usage of old temporal type",
"status": "OK",
"detectedProblems": []
},
{
"id": "routinesSyntaxCheck",
"title": "MySQL 8.0 syntax check for routine-like objects",
"status": "OK",
"detectedProblems": []
},
.
.
[root@Testbox ~]#
## Now, lets create same procedure in 8.0 version and run checks with target version as 8.4.4 and we see no error or warning related to the procedure test123 even its not allowed to be created syntactically in 8.4
[root@Testbox ~]# mysql80 -u root -p********** -S /tmp/mysql_sandbox8036.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show create procedure test123\G
*************************** 1. row ***************************
Procedure: test123
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `test123`()
begin declare $$samp int; end
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
mysql> \q
Bye
[root@Testbox ~]#
[root@Testbox ~]# /root/mysql-shell-8.4.4-linux-glibc2.17-x86-64bit/bin/mysqlsh -- util checkForServerUpgrade root@localhost:8036 --target-version=8.4.4 --output-format=JSON --config-path=/root/sandboxes/msb_8_0_36/my.sandbox.cnf
{
"serverAddress": "localhost:8036",
"serverVersion": "8.0.36 - MySQL Community Server - GPL",
"targetVersion": "8.4.4",
"errorCount": 0,
"warningCount": 30,
"noticeCount": 4,
"summary": "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.",
"checksPerformed": [
{
"id": "removedSysVars",
"title": "Removed system variables",
"status": "OK",
"detectedProblems": [],
"solutions": []
},
{
"id": "sysVarsNewDefaults",
"title": "System variables with new default values",
"status": "OK",
"description": "Warning: Following system variables that are not defined in your configuration file will have new default values. Please review if you rely on their current values and if so define them before performing upgrade.",
"documentationLink": "https://dev.mysql.com/blog-archive/new-defaults-in-mysql-8-0/",
.
.
"solutions": []
},
{
"id": "checkTableCommand",
"title": "Issues reported by 'check table x for upgrade' command",
"status": "OK",
"detectedProblems": [],
"solutions": []
},
{
"id": "foreignKeyReferences",
"title": "Checks for foreign keys not referencing a full unique index",
"status": "OK",
"detectedProblems": [],
"solutions": [
"Convert non unique key to unique key if values do not have any duplicates. In case of foreign keys involving partial columns of key, create composite unique key containing all the referencing columns if values do not have any duplicates.",
"Remove foreign keys referring to non unique key/partial columns of key.",
"In case of multi level references which involves more than two tables change foreign key reference."
]
},
{
"id": "authMethodUsage",
"title": "Check for deprecated or invalid user authentication methods.",
"status": "OK",
"description": "Some users are using authentication methods that may be deprecated or removed, please review the details below.",
.
.
"solutions": []
},
{
"id": "pluginUsage",
"title": "Check for deprecated or removed plugin usage.",
"status": "OK",
"detectedProblems": [],
"solutions": []
},
{
"id": "deprecatedDefaultAuth",
"title": "Check for deprecated or invalid default authentication methods in system variables.",
"status": "OK",
"detectedProblems": [],
"solutions": []
},
{
"id": "deprecatedRouterAuthMethod",
"title": "Check for deprecated or invalid authentication methods in use by MySQL Router internal accounts.",
"status": "OK",
"detectedProblems": [],
"solutions": []
},
{
"id": "columnDefinition",
"title": "Checks for errors in column definitions",
"status": "OK",
"detectedProblems": [],
"solutions": []
},
{
"id": "sysvarAllowedValues",
"title": "Check for allowed values in System Variables.",
"status": "OK",
"detectedProblems": [],
"solutions": []
},
{
"id": "invalidPrivileges",
"title": "Checks for user privileges that will be removed",
"status": "OK",
"description": "Verifies for users containing grants to be removed as part of the upgrade process.",
"detectedProblems": [
.
.
"solutions": [
"If the privileges are not being used, no action is required, otherwise, ensure they stop being used before the upgrade as they will be lost."
]
},
{
"id": "partitionsWithPrefixKeys",
"title": "Checks for partitions by key using columns with prefix key indexes",
"status": "OK",
"detectedProblems": [],
"solutions": []
}
],
"manualChecks": []
}
[root@Testbox ~]#
Suggested fix:
Report error when upgrading from 8.0 to 8.4 for identifiers with $ at beginning inside routines as its not valid syntactically.
===
[root@Testbox ~]# mysql80 -u root -p********** -S /tmp/mysql_sandbox8403.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.4.3 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> delimiter //
mysql> create procedure test123() begin declare $$samp int; end//
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 '$$samp int; end' at line 1
mysql> \q
Bye
[root@Testbox ~]#
===
For same routines, while upgrading to 8.0 from 5.7, report warning instead of error as its deprecated but not block the upgrade.
===
[root@Testbox ~]# dbdeployer --sandbox-binary=/root/dbdeployer admin upgrade msb_5_7_44 msb_8_0_36
stop /root/sandboxes/msb_5_7_44
stop /root/sandboxes/msb_8_0_36
Data directory msb_5_7_44/data moved to msb_8_0_36/data
............ sandbox server started
The data directory from msb_8_0_36/data is preserved in msb_8_0_36/data-msb_8_0_36
The data directory from msb_5_7_44/data is now used in msb_8_0_36/data
msb_5_7_44 is not operational and can be deleted
[root@Testbox ~]#
===
