Bug #80271 [Opn->Csd]: prepare json_set throw exception on mysql 8.0.22

Edit report at https://bugs.php.net/bug.php?id=80271&edit=1 ID: 80271 Updated by: nikic@php.net Reported by: cxlblm at gmail dot com Summary: prepare json_set throw exception on mysql 8.0.22 -Status: Open +Status: Closed Type: Bug Package: PDO MySQL Operating System: docker/ubuntu PHP Version: 7.4.11 -Assigned To: +Assigned To: nikic Block user comment: N Private report: N New Comment: Okay, in that case I'll close this one... do you happen to have a bug ID on the MySQL side handy, for future reference? Previous Comments: ------------------------------------------------------------------------ [2020-12-18 14:16:53] cxlblm at gmail dot com This is a MySQL bug, fixed in 8.0.23 ------------------------------------------------------------------------ [2020-10-24 19:07:58] mxmsw at hotmail dot com Encountering this issue with 'ATTR_EMULATE_PREPARES' set to true will "fix" this. This is not the recommended way (security vulnerabilities). Reading the mysql change log (https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-22.html) under the 'Optimizer Notes' section notes ---- Important Change: A prepared statement is now prepared only once, when executing PREPARE, rather than once each time it is executed. In addition, a statement inside a stored procedure is also now prepared only once, when the stored procedure is first executed. This change enhances performance of such statements, since it avoids the added cost of repeated preparation and rollback of preparation structures, the latter being the source of several bugs. ------ This has also affected prepared statements not regarding JSON specifically. Downgrading from MYSQL 8.0.22 to 8.0.21 resolves this issue ------------------------------------------------------------------------ [2020-10-22 15:55:06] cxlblm at gmail dot com Description: ------------ $pdo = new \PDO("mysql:host=host.docker.internal;dbname=laravel;charset=utf8mb4", 'root', 'root'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $stmt = $pdo->prepare('update test set setting = json_set(setting, "$.name", ?)'); $r = $stmt->execute(['fefwef']); var_dump($r); Expected result: ---------------- true Actual result: -------------- PHP Fatal error: Uncaught PDOException: SQLSTATE[22032]: <<Unknown error>>: 3141 Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 1. in /opt/project/local/PDO.php:10 Stack trace: #0 /opt/project/local/PDO.php(10): PDOStatement->execute(Array) #1 {main} thrown in /opt/project/local/PDO.php on line 10 ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=80271&edit=1