Bug #116204 MySQL Shell gives unexpected token for collate specified in functions
Submitted: 23 Sep 2024 14:41 Modified: 25 Oct 2024 7:08
Reporter: Yunus UYANIK Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Document Store: MySQL Shell Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: mysql shell

[23 Sep 2024 14:41] Yunus UYANIK 
Description:
I encountered an unexpected token error while using the mysqlsh utility to check for server upgrade issues. The utility flagged a stored function due to the use of a _latin1 string literal in the IN clause

How to repeat:
Steps to Reproduce:
Create Test Schema and Table:

mysql> CREATE DATABASE IF NOT EXISTS test;
mysql> USE test;
mysql> CREATE TABLE t1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    a CHAR(1),
    b VARCHAR(50)
);

mysql> INSERT INTO t1 (a, b) VALUES ('A', 'test1'), ('B', 'test2');

Create the Function:

mysql> DELIMITER //
mysql> CREATE FUNCTION test.function_test1(val1 VARCHAR(50))
    RETURNS INT
    DETERMINISTIC
BEGIN
    DECLARE val_out INT;
    SELECT id INTO val_out
    FROM test.t1
    WHERE a IN (_latin1'A') AND b = val1;
    RETURN val_out;
END//
mysql> DELIMITER ;

Run the MySQL Shell Upgrade Check:

$ mysqlsh -- util check-for-server-upgrade --user=root --host=localhost --port=3306 --target-version=8.0.38
Output from the Command:
...
issues for upgrade to MySQL 8.0.15...

1) Usage of old temporal type
   No issues found

2) MySQL 8.0 syntax check for routine-like objects
   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.
More information:
   https://dev.mysql.com/doc/refman/en/keywords.html

test.function_test1 - at line 4,61: unexpected token ''A''

$ mysqlsh --version
mysqlsh   Ver 8.0.38 for Linux on x86_64 - for MySQL 8.0.38 (MySQL Community Server (GPL))
[24 Oct 2024 9:42] MySQL Verification Team 
Hi,

I'm having troubles reproducing this from 8.0.15 to 8.0.40 

What version of mysqlsh are you trying this with?
[25 Oct 2024 7:08] Yunus UYANIK 
Hi,
I'm not sure what step you are having trouble with, but the version information was in my previous message also, let me share that again.

$ mysqlsh --version
mysqlsh   Ver 8.0.38 for Linux on x86_64 - for MySQL 8.0.38 (MySQL Community Server (GPL))
[25 Oct 2024 19:15] MySQL Verification Team 
Hi,

Yes, you mentioned you are testing 8.0.38 shell. There is no point for me testing .38 shell as I cannot "fix" .38 and .40 is out so I am testing 8.0.40 shell.

The question is what version of the database are you checking for update. What version of MySQL server you connect to and create "CREATE FUNCTION test.function_test1 ..." to then check with shell if ugprade is possible.

Thanks