MySQL Bugs: #107243: Com_stmt_reprepare not decrement by using float in prepared statement

Bug #107243	Com_stmt_reprepare not decrement by using float in prepared statement
Submitted:	9 May 2022 9:31	Modified:	9 May 2022 16:23
Reporter:	Morten Urban	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.0.29	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Prepared statement wont close when using 'float' values. It can be seen by looking at the 'Com_stmt_reprepare' variable.

Queries will crash when it's value reaches the value from max_prepared_stmt_count (default: 16382)

This error wont occur on version 9.0.21

How to repeat:
mysql> use sys
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW GLOBAL STATUS LIKE'com_stmt%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Com_stmt_execute | 13807 |
| Com_stmt_close | 13771 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 15106 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_stmt_reprepare | 1297 |
+-------------------------+-------+
7 rows in set (0.00 sec)

mysql> prepare stmt2 from 'select * from version where sys_version > ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SHOW GLOBAL STATUS LIKE'com_stmt%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Com_stmt_execute | 13807 |
| Com_stmt_close | 13771 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 15107 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_stmt_reprepare | 1297 |
+-------------------------+-------+
7 rows in set (0.00 sec)

mysql> set @a = 1.5;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE'com_stmt%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Com_stmt_execute | 13807 |
| Com_stmt_close | 13771 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 15107 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_stmt_reprepare | 1297 |
+-------------------------+-------+
7 rows in set (0.00 sec)

mysql> execute stmt2 using @a;
+-------------+---------------+
| sys_version | mysql_version |
+-------------+---------------+
| 2.1.1 | 8.0.26 |
+-------------+---------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE'com_stmt%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Com_stmt_execute | 13808 |
| Com_stmt_close | 13771 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 15108 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_stmt_reprepare | 1298 |
+-------------------------+-------+
7 rows in set (0.00 sec)

mysql> deallocate prepare stmt2;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE'com_stmt%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Com_stmt_execute | 13808 |
| Com_stmt_close | 13772 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 15108 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_stmt_reprepare | 1298 |
+-------------------------+-------+
7 rows in set (0.01 sec)

*This error wont occur on version 8.0.21

Hi Mr. Urban,

Thank you for your bug report.

We do not understand what do you mean by "Prepared statement wont close when using 'float' values" ???

Especially, what is "close" in respect to the prepared statements.

This forum requires that each bug is accompanied by a fully repeated test case that will prove the behaviour that you are reporting. Each test case should consist of the series of the SQL statements that always lead to the behaviour that you describe.

We are waiting on your test cases.

The bug is reproducible by executing the mysql commands from the 'how to repeat' section. It has to be repeated 16383 times. 

The value 16382 is defined by the variable: max_prepared_stmt_count (default: 16382)

By reaching the 16383 prepared statement this error will happen:

Error 1461: Can't create more than max_prepared_stmt_count statements (current value: 16382

The crucial part is to use a float value as prepared variable like 'set @a = 1.5;'

To track the progress till the error is reached this command can be executed: 'SHOW GLOBAL STATUS LIKE'com_stmt%';'

The variable 'Com_stmt_reprepare' is the field I understand as not closed prepared statements. When it reaches the 'max_prepared_stmt_count' errors will happen and i have to restart the mysql server.

The Com_stmt_reprepare variable will never decrement, even after the prepared statement is deallocated.

HI,

We still do not see what is a bug here ......

After running:

execute stmt2 using @a;

the number has increased.

Hence, not a bug.

And yes, that stat will never decrease, which is expected behaviour.

Hi,

This is not a bug, because you can simply increase the variable "max_prepared_stmt_count".