Bug #102344 Prepared Statements with GROUP_CONCAT fail with a high group_concat_max_len
Submitted: 22 Jan 2021 11:42 Modified: 14 Apr 2021 15:43
Reporter: Roman Pertl Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:8.0.22, 8.0.23 OS:Linux (Docker Container mysql/8.0.22)
Assigned to: CPU Architecture:Any
Tags: regression

[22 Jan 2021 11:42] Roman Pertl 
Description:
prepared statements fail to execute on 8.0.22 and 8.0.23 with a very high group_concat_max_len setting (~ 10000000000). the execution of the prepared statements fails with the following error 

ERROR 1615 (HY000): Prepared statement needs to be re-prepared

although the error message suggest that the statement should be re-prepared, this leads to the same error

this is probably related to the changes done on prepared statements released in  8.0.22.

How to repeat:
mysql> CREATE TABLE `tbl_group_concat_max_len` (
     `o_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
      PRIMARY KEY (`o_id`) )
      ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> set group_concat_max_len = 1024; 
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'group_concat_max_len'; 
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 1024  |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> 

mysql> PREPARE `stmt` FROM "SELECT GROUP_CONCAT(o_id) FROM tbl_group_concat_max_len;"; 
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt; 
+--------------------+
| GROUP_CONCAT(o_id) |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

mysql> 

mysql> set group_concat_max_len = 10000000000; 
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE `stmt` FROM "SELECT GROUP_CONCAT(o_id) FROM tbl_group_concat_max_len;"; 
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt; 
ERROR 1615 (HY000): Prepared statement needs to be re-prepared
[22 Jan 2021 11:46] Roman Pertl 
update Synopsis to include which type of query fails
[22 Jan 2021 11:57] MySQL Verification Team 
Hello Roman Pertl,

Thank you for the report and test case.

regards,
Umesh
[22 Jan 2021 12:38] Roy Lyseng 
Workaround: Use group_concat_max_len less or equal to 4294967295
[14 Apr 2021 15:43] Paul DuBois 
Posted by developer:
 
Fixed in 8.0.25.

For large values of the group_concat_max_len system variable,
prepared statements that used the GROUP_CONCAT() function could be
unnecessarily re-prepared across executions.
[24 Apr 2021 16:15] Paul DuBois 
Posted by developer:
 
Fixed in 8.0.26, not 8.0.25.