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

• Submitted: 27 Oct 4:15
• Modified: 31 Oct 5:29
• Reporter: alex xing (OCA)
• Status: Verified
• Category: MySQL Server: Prepared statements
• Severity: S2 (Serious)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any

[27 Oct 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 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 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 5:29] Chaithra Marsur Gopala Reddy

Hi Alex xing,

Thank you for the test case. Verified as described.