php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #80271 prepare json_set throw exception on mysql 8.0.22
Submitted: 2020-10-22 15:55 UTC Modified: 2020-12-20 13:48 UTC
Votes:5
Avg. Score:5.0 ± 0.0
Reproduced:5 of 5 (100.0%)
Same Version:4 (80.0%)
Same OS:4 (80.0%)
From: cxlblm at gmail dot com Assigned: nikic (profile)
Status: Closed Package: PDO MySQL
PHP Version: 7.4.11 OS: docker/ubuntu
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
12 + 20 = ?
Subscribe to this entry?

 
 [2020-10-22 15:55 UTC] 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

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-10-24 19:07 UTC] 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-12-18 14:16 UTC] cxlblm at gmail dot com 
This is a MySQL bug, fixed in 8.0.23
 [2020-12-18 14:19 UTC] nikic@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: nikic
 [2020-12-18 14:19 UTC] nikic@php.net 
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?
 [2020-12-20 13:48 UTC] cxlblm at gmail dot com 
MySQL bug:
https://bugs.mysql.com/bug.php?id=101575
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Fri Apr 26 10:01:31 2024 UTC