Issue

Client got an error when request to MySQL to create a prepare statement

Can't get news id 868122904328848557 from db with error Error 1461: Can't create more than max_prepared_stmt_count statements (current value: 16382)

mysql> show status like "%prepare%";
+---------------------------------------------+--------+
| Variable_name                               | Value  |
+---------------------------------------------+--------+
| Com_prepare_sql                             | 0      |
| Com_stmt_prepare                            | 0      |
| Com_xa_prepare                              | 0      |
| Com_stmt_reprepare                          | 0      |
| Handler_prepare                             | 0      |
| Performance_schema_prepared_statements_lost | 0      |
| Prepared_stmt_count                         | 16382  |
+---------------------------------------------+--------+
7 rows in set (0.01 sec)

Tracing

$ tcpdump -i any -s 65535 dst port 3306 -w cap.pcap

Open file on Wireshark -> Filter by protocol “mysql”

We got some informations

  • Prepare statement sent from 192.168.2.23:64898
  • Query: SELECT id FROM news WHERE source=? AND url LIKE ?

Show processlist filter by 192.168.2.23

mysql> select * from information_schema.processlist where host like '192.168.2.23%';
+-----------+---------+--------------------+--------+---------+------+-------+------+---------+-----------+---------------+
| ID        | USER    | HOST               | DB     | COMMAND | TIME | STATE | INFO | TIME_MS | ROWS_SENT | ROWS_EXAMINED |
+-----------+---------+--------------------+--------+---------+------+-------+------+---------+-----------+---------------+
| 409608130 | test1   | 192.168.2.23:54688 | testdb | Sleep   |    0 |       | NULL |       8 |         0 |             0 |
| 409608627 | test1   | 192.168.2.23:54898 | testdb | Sleep   |    0 |       | NULL |       4 |         0 |             0 |
+-----------+---------+--------------------+--------+---------+------+-------+------+---------+-----------+---------------+
2 rows in set (0.00 sec)

After kill 2 ids above, counter Prepared_stmt_count has been decreased to 0