Bug #101630 Consider increasing placeholder limit to be > 64k
Submitted: 16 Nov 2020 15:58 Modified: 17 Nov 2020 4:42
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Prepared statements Severity:S4 (Feature request)
Version:8.0.22 OS:Any
Assigned to: CPU Architecture:Any

[16 Nov 2020 15:58] Simon Mudd
I see this error message in Orchestrator:

Error 1390: Prepared statement contains too many placeholders.

The 64k Limit in init_param_array() here https://github.com/mysql/mysql-server/blob/8.0/sql/sql_prepare.cc#L1489-L1496 seems to be the problem.

We poll our servers frequently and update the state in a backend MySQL database.  The number of servers is quite high so the update frequency using single changes was too high (generates a very large number of commits/sec) so the code was modified years ago to allow bulk updates, using INSERT ON DUPLICATE KEY UPDATE, and providing placeholders for each of the columns in multiple rows.

This hits the 64k limit.

How to repeat:
Generate a prepared IODKU statement with say 50,000 rows and 10 columns to update and see how this is rejected by the MySQL server with the error given above.

Suggested fix:
There are multiple fixes possible on the client side:
* don't use prepared statements but generate the full SQL in the app. Not ideal.
* reduce the size of the prepared statement, e.g. use less rows, so reducing the advantage of the intended bulk insert, and thus slowing down the insert/update rate

Either option is possible but these days I'd expect if I can have max_allowed_packet to be 64M on the server concerned (and other servers use higher values) that provides plenty of size to insert the rows I want to insert.

Perhaps the assumption is that users will be inserting/updating a single row in which case 64k columns would be a large amount, but event 10,000 rows with 10 columns is already over the limit currently provided.

I have bumped into this several times and think in 2020 you should consider increasing the limit, unless there's a significant reason to not do this.

Note: this issue came up due to an upgrade to the app adding more columns to the database making the "previously working" inserts start to fail.  So this can arise when least expected in app code.
[16 Nov 2020 17:14] Simon Mudd
* workarounds in orchestrator code to highlight the issue and suggest a configuration change: https://github.com/openark/orchestrator/pull/1265
[16 Nov 2020 17:37] Simon Mudd
Fix synopsis
[17 Nov 2020 4:42] MySQL Verification Team
Hello Simon,

Thank you for the feature request!