Bug #107160 Upgrade from 5.7 to 8.0 fails with syntax error while parsing an event

• Submitted: 29 Apr 2022 8:19
• Modified: 29 Apr 2022 9:19
• Reporter: Pranay Motupalli
• Status: Verified
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 8.0.29
• OS: Any
• CPU Architecture: Any
• Tags: Contribution, Upgrade failed

[29 Apr 2022 8:19] Pranay Motupalli

Description:
Upgrade from 5.7 to 8.0 fails with syntax error while parsing an event.

How to repeat:
1. Create a MySQL server with 5.7 and run the following

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

mysql> create table t(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values(3);
Query OK, 1 row affected (0.01 sec)

mysql> CREATE EVENT myevent
    ->     ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    ->     DO
    ->       create table h as select * from ((select * from test.t limit 1) t);
Query OK, 0 rows affected (0.01 sec)

mysql> show create event myevent\G
*************************** 1. row ***************************
               Event: myevent
            sql_mode: NO_ENGINE_SUBSTITUTION
           time_zone: UTC
        Create Event: CREATE DEFINER=`root`@`%` EVENT `myevent` ON SCHEDULE AT '2022-04-29 08:41:27' ON COMPLETION NOT PRESERVE ENABLE DO create table h as select * from ((select * from test.t limit 1) t)
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

mysql> exit
Bye

2. Upgrade the server to 8.0 ( latest ) and monitor error logs

2022-04-29T08:11:18.042980Z mysqld_safe mysqld from pid file /home/ec2-user/sandboxes/msb_5_7_38/data/mysql_sandbox5738.pid ended
2022-04-29T08:11:22.782786Z mysqld_safe Logging to '/home/ec2-user/sandboxes/msb_8_0_29/data/msandbox.err'.
2022-04-29T08:11:22.820190Z mysqld_safe Starting mysqld daemon with databases from /home/ec2-user/sandboxes/msb_8_0_29/data
2022-04-29T08:11:23.170577Z 0 [System] [MY-010116] [Server] /home/ec2-user/opt/mysql/8.0.29/bin/mysqld (mysqld 8.0.29) starting as process 818674
2022-04-29T08:11:23.179578Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2022-04-29T08:11:23.179616Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-04-29T08:11:23.860958Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-04-29T08:11:24.634819Z 2 [ERROR] [MY-013235] [Server] Error in parsing Event 'test'.'myevent' during upgrade. 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 ')' at line 1
2022-04-29T08:11:24.910538Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2022-04-29T08:11:24.910790Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-04-29T08:11:26.079915Z 0 [System] [MY-010910] [Server] /home/ec2-user/opt/mysql/8.0.29/bin/mysqld: Shutdown complete (mysqld 8.0.29)  MySQL Community Server - GPL.
2022-04-29T08:11:26.120830Z mysqld_safe mysqld from pid file /home/ec2-user/sandboxes/msb_8_0_29/data/mysql_sandbox8029.pid ended

Issue:
#####

Version: 5.7.38
###############

mysql> select * from ((select * from test.t limit 1) t);
Empty set (0.00 sec)

Version: 8.0.29
################

mysql> select * from ((select * from test.t limit 1) t);  --> Bracket after giving alias to derived table ( same as the one used in 5.7 )
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 ')' at line 1

mysql> select * from (select * from test.t limit 1) t;  -- Removed the bracket pair and its working.
+------+
| id   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

Suggested fix:
Can consider to include these kind of syntax checks in upgrade checker utility

[29 Apr 2022 9:19] MySQL Verification Team

Hello Pranay,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

[4 Mar 3:17] lanzao lanzao

Add derived_table_parens to support derived table with '()'.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-Bug-107160-Upgrade-from-5.7-to-8.0-fails-with-syntax.patch (application/octet-stream, text), 3.36 KiB.