Bug #119236 The SQL reprepare bug results in 100% CPU load.

Submitted: 27 Oct 2025 4:15 Modified: 23 Apr 13:11
Reporter: alex xing (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:8.0.44 OS:Any
Assigned to: CPU Architecture:Any

[27 Oct 2025 4:15] alex xing
Description:
When prepared statement parameter types mismatch, re-preparation should be triggered.
However, if types still mismatch after re-preparation, it may lead to infinite loops.

Our production service on 8026 and 8027 hit the infinite loop, and the CPU is now maxed out.

How to repeat:
SQL to reproduce the bug:

SET @sw = 'kill query ?';

SET @e = 9;

PREPARE stmt3 FROM @sw ;

EXECUTE stmt3 using @e;

Suggested fix:
For debug mode, almost all versions of 8.0 (>= 8026) will crash, although the location of the core dump varies across versions.
For release mode, versions 8026 and 8027 will enter an infinite loop, ultimately leading to 100% CPU utilization. For versions 8028 and above, the issue has been resolved.

However, after analyzing the code, I believe the bug has not been completely fixed:

In https://github.com/mysql/mysql-server/commit/24fdf31121a1bfdfe66a06bd508b48efe40d1941#diff..., the bug was fortunately bypassed . (Specifically, in sql_prepare.cc, the check was performed using item->data_type() instead of item->result_type(), and during the initial prepare phase, item(item_param)'s data_type was invalid_type.)

Nevertheless, I believe this is not a complete fix. To thoroughly prevent infinite loops, we need to refer to the following patch.
[27 Oct 2025 4:15] alex xing
a simple patch to describe the bugfix

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: check_parameter_types_bugfix.patch (application/octet-stream, text), 907 bytes.

[27 Oct 2025 6:34] alex xing
Reworked the code and added a fallback logic  to ensure only one retry in release mode.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: check_parameter_types_bugfix_v1.patch (application/octet-stream, text), 940 bytes.

[31 Oct 2025 5:29] Chaithra Marsur Gopala Reddy
Hi Alex xing,

Thank you for the test case. Verified as described.
[23 Apr 13:11] Edward Gilmore
Posted by developer:
 
Added the following note to the MySQL Server 8.0.46, 8.4.9, and 9.7.0 release notes:
		
Prepared statements with mismatched parameter types could enter
infinite loops and consume 100% CPU after re-preparation.
Our thanks to Alex Xing for the contribution.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.