Bug #114815 Prepared statement in stored procedure - error on util.checkForServerUpgrade
Submitted: 29 Apr 12:15 Modified: 11 Dec 19:15
Reporter: Przemyslaw Malkowski Email Updates:
Status: Closed Impact on me:
None 
Category:Shell Upgrade Checker Severity:S3 (Non-critical)
Version:8.0.36 OS:Any
Assigned to: CPU Architecture:Any
Tags: upgrade

[29 Apr 12:15] Przemyslaw Malkowski
Description:
For some reason, the presence of a prepared statement triggers an error "unexpected token" when checking with Upgrade Checker Utility.

The very same SP can be created in MySQL 8.0 without any warnings, so I don't see any reason for upgrade check critical event!

How to repeat:
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `code` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO t1 values (1,44), (2,30), (3, 50);

delimiter //
CREATE PROCEDURE sptest2 (IN c1 CHAR(3))
       BEGIN
         PREPARE STMT1 FROM "SELECT COUNT(*) FROM test.t1 WHERE code = ?";
         SET @a = c1;
         EXECUTE STMT1 USING @a;
       END//
delimiter ;
call sptest2(44); -- to confirm the SP works as expected

 MySQL  localhost:5754 ssl  JS > util.checkForServerUpgrade('root:msandbox@localhost:5754',{"outputFormat":"JSON"})
{
    "serverAddress": "localhost:5754",
    "serverVersion": "5.7.44 - MySQL Community Server (GPL)",
    "targetVersion": "8.0.36",
    "errorCount": 1,
    "warningCount": 27,
    "noticeCount": 2,
    "summary": "1 errors were found. Please correct these issues before upgrading to avoid compatibility issues.",
    "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",
            "description": "The following objects did not pass a syntax check with the latest MySQL 8.0 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.sptest2",
                    "description": "at line 2,28: unexpected token '\"SELECT COUNT(*) FROM test.t1 WHERE code = ?\"'"
                }
            ]
        },
(...)

Suggested fix:
The upgrade checker util needs to be corrected to not print this false positive.
[7 May 7:30] MySQL Verification Team
Thanks for the report.
[11 Dec 19:15] Marek Mlynarski
Posted by developer:
 
Fixed in new syntax check in 9.2.0