MySQL Bugs: #111849: checkForServerUpgrade fails for INSERT INTO.SELECT FOR UPDATE routines

Bug #111849	checkForServerUpgrade fails for INSERT INTO.SELECT FOR UPDATE routines
Submitted:	21 Jul 2023 20:29	Modified:	3 Dec 2024 15:13
Reporter:	Marc Reilly	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell Upgrade Checker	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	checkForServerUpgrade, mysql shell, routinesSyntaxCheck

Description:
Hi,

When running checkForServerUpgrade for 57-->80 upgrade, checks will fail for routines which contain INSERT INTO .. SELECT FOR UPDATE with " unexpected token 'FOR' "

The same for just SELECT .. FOR UPDATE does not return an error.

Thanks,
Marc

How to repeat:
1. Create two simple procs. One with SELECT FOR UPDATE and another with INSERT INTO… SELECT…FOR UPDATE on a 5.7.43 database. 

create database if not exists test;
use test;
delimiter //
drop procedure if exists test_sel_for_uptd//
CREATE PROCEDURE `test_sel_for_uptd`()
BEGIN
     SELECT
           id,
           val
     FROM
           foo FOR UPDATE;
END//
delimiter ;
 
delimiter //
drop procedure if exists test_insert_into_sel_for_uptd//
CREATE PROCEDURE `test_insert_into_sel_for_uptd`()
BEGIN
     INSERT INTO test
           (id, val)
     SELECT
           id,
           val
     FROM
           foo FOR UPDATE;
END//
delimiter ;

2. Run checkForServerUpgrade which will return an error for "INSERT INTO… SELECT…FOR UPDATE." (test_insert_into_sel_for_uptd) but not SELECT FOR UPDATE (test_sel_for_uptd)

 MySQL  localhost:5743 ssl  JS > util.checkForServerUpgrade()
The MySQL server at localhost:5743, version 5.7.43 - MySQL Community Server
(GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.34...

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.test_insert_into_sel_for_uptd - at line 8,15: unexpected token 'FOR'

.
.
.
Errors:   1
Warnings: 1
Notices:  3

ERROR: 1 errors were found. Please correct these issues before upgrading to avoid compatibility issues.

3. Ignore error and attempt upgrade. It will be successful.
2023-07-21T20:15:52.495410Z 0 [Note] /home/ec2-user/opt/mysql/5.7.43/bin/mysqld: Shutdown complete

2023-07-21T20:15:52.545292Z mysqld_safe mysqld from pid file /home/ec2-user/sandboxes/msb_5_7_43/data/mysql_sandbox5743.pid ended
2023-07-21T20:15:56.918695Z mysqld_safe Logging to '/home/ec2-user/sandboxes/msb_8_0_34/data/msandbox.err'.
2023-07-21T20:15:56.940547Z mysqld_safe Starting mysqld daemon with databases from /home/ec2-user/sandboxes/msb_8_0_34/data
2023-07-21T20:15:57.215645Z 0 [System] [MY-010116] [Server] /home/ec2-user/opt/mysql/8.0.34/bin/mysqld (mysqld 8.0.34) starting as process 32672
2023-07-21T20:15:57.223895Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-07-21T20:15:57.223921Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-07-21T20:15:57.694890Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-07-21T20:15:59.046630Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2023-07-21T20:16:00.085925Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80034' started.
2023-07-21T20:16:05.242842Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80034' completed.
2023-07-21T20:16:05.324279Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-07-21T20:16:05.324310Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-07-21T20:16:05.344022Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 18034, socket: /tmp/mysqlx-18034.sock
2023-07-21T20:16:05.344064Z 0 [System] [MY-010931] [Server] /home/ec2-user/opt/mysql/8.0.34/bin/mysqld: ready for connections. Version: '8.0.34'  socket: '/tmp/mysql_sandbox8034.sock'
  port: 8034  MySQL Community Server - GPL.
2023-07-21T20:16:18.954503Z 11 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Pl

Suggested fix:
Ensure INSERT INTO… SELECT…FOR UPDATE is correctly handled during routine parsing in routinesSyntaxCheck.

Another (simple) example of a syntax that will fail checkForServerUpgrade, but succeeds in MySQL 8:
delimiter //
CREATE PROCEDURE test.test_proc()
BEGIN
create temporary table t3 select '1', '2';
select t3.1, t3.2 from t3;
END//
delimiter ;

# checkForServerUpgrade fails
 MySQL  JS > util.checkForServerUpgrade()
The MySQL server at localhost:5743, version 5.7.43 - MySQL Community Server
(GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.34...

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.test_proc - at line 3,9: unexpected token '.1'
.
.
.
.
Errors:   1
Warnings: 1
Notices:  2

ERROR: 1 errors were found. Please correct these issues before upgrading to avoid compatibility issues.

# Running on 8.0.34 returns no error:
mysql [localhost:8034] {msandbox} (test) > select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.34    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > create temporary table t3 select '1', '2';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql [localhost:8034] {msandbox} (test) > select * from t3;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

Last comment is not a reserved word but can be overcome by adding `quotes` as suggested. INSERT SELECT FOR UPDATE is a bit more tricky.

Hi Mr. Reilly,

Thank you for your bug report.

However, shell output is correct. 8.0 does not support INSERT INTO ..... SELECT .... FOR UPDATE or IN SHARE MODE.

This is the only syntax that is supported:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {   SELECT ... 
      | TABLE table_name 
      | VALUES row_constructor_list
    }
    [ON DUPLICATE KEY UPDATE assignment_list]

Not a bug.

Hi,

Thanks for the update. 
This syntax works on every MySQL release since 5.5 without any error or warning. I tested versions :
- 5.5.62(EOL - demonstration purposes only)
- 5.6.51(EOL - demonstration purposes only)
- 5.7.43
- 8.0.34
- 8.1.0

In **all** these releases INSERT..SELECT FOR UPDATE works without any error or warning in the mysql client. 

So if this is not a bug in the MySQL shell parser, can you confirm if this is a documentation bug or an engine bug? From my tests below, it seems the engine will handle this syntax without error. Shouldn't the engine return an error if the syntax is not supported?

Thanks,
Marc

```
create database if not exists test;
use test;
create table test(id int, val varchar(50)) engine=innodb;
create table foo(id int, val varchar(50)) engine=innodb;
insert into foo values(1,'ssss'),(2,'ssss'),(3,'cdaasxass'),(4,'cdacadc');
select @@version;
INSERT INTO test(id, val) SELECT id, val FROM foo FOR UPDATE;
```

8.1.0
```
mysql [localhost:8100] {msandbox} (test) > select @@version;
+-----------+
| @@version |
+-----------+
| 8.1.0     |
+-----------+
1 row in set (0.00 sec)

mysql [localhost:8100] {msandbox} (test) > INSERT INTO test(id, val) SELECT id, val FROM foo FOR UPDATE;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
```

8.0.34
```
mysql [localhost:8034] {msandbox} (test) > select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.34    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > INSERT INTO test(id, val) SELECT id, val FROM foo FOR UPDATE;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql [localhost:8034] {msandbox} (test) >
```

5.7.43:
```
mysql [localhost:5743] {msandbox} (test) > select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.43    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost:5743] {msandbox} (test) > INSERT INTO test(id, val) SELECT id, val FROM foo FOR UPDATE;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
```

5.6.51 (EOL - demonstration purposes only)
```
mysql [localhost:5651] {msandbox} (test) > select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.51    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost:5651] {msandbox} (test) > INSERT INTO test(id, val) SELECT id, val FROM foo FOR UPDATE;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
```

5.5.62 (EOL - demonstration purposes only)
```
mysql [localhost:5562] {msandbox} (test) > select @@version;
+-----------+
| @@version |
+-----------+
| 5.5.62    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost:5562] {msandbox} (test) > INSERT INTO test(id, val) SELECT id, val FROM foo FOR UPDATE;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
```

=======================================
=======================================
Demonstration of INSERT... SELECT .. FOR UPDATE functionality
======
Example 1:
Session 2 not blocked without for update in session 1:
======
Session 1
```
mysql [localhost:8034] {msandbox} (test) > select @@version,@@transaction_isolation;
+-----------+-------------------------+
| @@version | @@transaction_isolation |
+-----------+-------------------------+
| 8.0.34    | READ-COMMITTED          |
+-----------+-------------------------+
1 row in set (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > begin;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > INSERT INTO test(id, val) SELECT id, val FROM foo where id=1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
```
Session 2:
```
# ID 1 and 2 updated as expected- no FOR UPDATE in session 1
mysql [localhost:8034] {msandbox} (test) > update foo set val=122 WHERE ID=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql [localhost:8034] {msandbox} (test) > update foo set val=122 WHERE ID=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```

======
Example 2:
With select for update in session 1, session 2 is blocked when trying to change locked row:
======
Session 1:
```
mysql [localhost:8034] {msandbox} (test) > select @@version,@@transaction_isolation;
+-----------+-------------------------+
| @@version | @@transaction_isolation |
+-----------+-------------------------+
| 8.0.34    | READ-COMMITTED          |
+-----------+-------------------------+
1 row in set (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > begin;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > INSERT INTO test(id, val) SELECT id, val FROM foo where id=1 FOR UPDATE;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
```
Session 2:
```
# ID 2 succeeds
mysql [localhost:8034] {msandbox} (test) > update foo set id=id+1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql [localhost:8034] {msandbox} (test) >
# ID 1 times out as THE record is locked by session 1 INSERT..SELECT..FOR UPDATE
mysql [localhost:8034] {msandbox} (test) > update foo set val=122 WHERE ID=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```

TYPE IN "Example 2", should be:

======
Example 2:
With select for update in session 1, session 2 is blocked when trying to change locked row:
======
Session 1:
```
mysql [localhost:8034] {msandbox} (test) > select @@version,@@transaction_isolation;
+-----------+-------------------------+
| @@version | @@transaction_isolation |
+-----------+-------------------------+
| 8.0.34    | READ-COMMITTED          |
+-----------+-------------------------+
1 row in set (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > begin;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > INSERT INTO test(id, val) SELECT id, val FROM foo where id=1 FOR UPDATE;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
```
Session 2:
```
# ID 2 succeeds
mysql [localhost:8034] {msandbox} (test) > update foo set val=122 WHERE ID=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# ID 1 times out as THE record is locked by session 1 `FOR UPDATE`
mysql [localhost:8034] {msandbox} (test) > update foo set val=122 WHERE ID=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```

Hi Mr. Reilly,

You are correct.

Although it was not supposed to be supported it is supported.

This is now a verified bug for the MySQL Shell.

Has this issue been solved? I´m facing the same issue with MySQL 8.0.34

Hi,

No, this bug has not been resolved yet.

When it is resolved, the information shall be posted to this page.

So, just stay subscribe to this bug and you will get a mail when this issue is resolved.

Posted by developer:
 
Already fixed in the newest MySQL Shell (9.2.0) - changed syntax parser for routineSyntax check - now it properly accepts the "FOR UPDATE" syntax.

Thank you, Marek,

This bug is fixed now .......

Added the following note to the MySQL Shell 9.2.0 release notes:
  
The util.checkForServerUpgrade check, routineSyntax, is renamed to syntax and now uses the SQL version of the target server for the syntax checks.

See Utility Checks.

Thank you , very much, Edward.