PHP PDO query error on table has json data type (MySQL 5.7.8-rc)

Question

I'm trying new json datatype on mysql 5.7. When I use native php mysql query, it works fine, but when I use PDO to query data, it shows this error:

Error: exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2036 ' in /some_folder/pdo.php:12 Stack trace: #0 /some_folder/pdo.php(12): PDO->query('select * from table_has_json_datatype') #1 {main}

Do you guys know how to solve this problem ?

Thanks.

Update with my simple test code:

<?php

try{
    $db = new PDO('mysql:host=some.host;dbname=somedb;charset=utf8', 'user', 'pwd');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}catch(PDOException  $e){
    echo "Error1: ".$e;
}

try{
    $query = $db->query("select * from table_with_json_type");

}catch(PDOException $e){
    echo "Error2: ".$e;
}
?>

`Error: 2036 (CR_UNSUPPORTED_PARAM_TYPE) ` - so the actual code is most likely irrelevant. — VolkerK, Oct 21 '15 at 08:31
`a)` What does `echo 'CLIENT_VERSION: "', $pdo->getAttribute(PDO::ATTR_CLIENT_VERSION),'"';` print? (where $pdo is your acual pdo instance created be `new PDO...`) `b)` Which version of PHP do you run on exactly what OS (e.g. PHP 5.4.7/Ubuntu 11.04) — VolkerK, Oct 21 '15 at 08:33
see https://bugs.php.net/bug.php?id=70384 and https://github.com/php/php-src/commit/e07e4f4bb3dca81010e4ae956dfd7de5376fdb8c — VolkerK, Oct 21 '15 at 08:58
@VolkerK my PDO-mysql client version is 5.7.5-m15 and Ubuntu 14.04 with php 5.6.14, mysql 5.7.8-rc — Quang Ng., Oct 22 '15 at 03:01

score 14 · Answer 1 · edited Oct 20 '17 at 15:31

14

It's a Bug reported to PHP Developers #70384

The developer andrey@php.net just posted:

The fix for this bug has been committed.

Snapshots of the sources are packaged every three hours; this change will be in the next snapshot. You can grab the snapshot at http://snaps.php.net/.

For Windows:

http://windows.php.net/snapshots/ Thank you for the report, and for helping us make PHP better.

Fixed in PHP-5.6.19, PHP-7.0 and master (PHP-7.1)

Thank You for your report

So, the JSON data type will be supported on PHP 5.6.19+ For other version there is a workaround available above.

This workaround modify the JSON field with CAST feature to a CHAR, which is fine from PHP's perspective: Eg.:

select *, CAST(json_col as CHAR) as json_col from table_with_json_type

It's worked to me in all cases.

To Full compatibility you must use PHP-5.6.19+

edited Oct 20 '17 at 15:31

Robert Moskal

21,737
8
62
86

answered Dec 15 '15 at 17:27

LeonanCarvalho

1,819
3
20
39

Thanks for your answer. Do you know when will the next version release ? – Quang Ng. Dec 21 '15 at 03:53
Casting worked for me, but the issue doesn't seem to be resolved in PHP 5.6.19 – Cafe Coder Mar 18 '16 at 11:36
Tested on PHP 5.6.20 and it's no need use this workaround to make works. The json data comes as jsonstring. Would be nice have some config to return it as a stdClass – LeonanCarvalho Sep 22 '16 at 19:33
Life saver. Thanks! – Robert Moskal Sep 08 '17 at 18:33
This is ok, but the values come back quoted. How to get rid of them? – Robert Moskal Oct 20 '17 at 15:01

score 2 · Answer 2 · answered Apr 26 '16 at 17:45

I had the same problem in PHP 5.5 - decided to update to PHP 5.6, but the problem still existed. Casting to CHAR helps, but it isn't good solution.

My PHP configuration was using libmysqlclient and when I changed it to mysqlnd (MySQL native driver) it solved the problem.

So I recommend to do the same.

You can read about PHP MySQL drivers here: http://php.net/manual/en/mysqlinfo.library.choosing.php

I installed mysqlnd driver in Ubuntu server using apt-get:

apt-get install php5-mysqlnd

score 1 · Answer 3 · answered Oct 20 '17 at 15:33

As a small addendum. Casting the column to a char like that has the effect of returning the value with double quotes around it. You can get rid of them using trim:

select *, TRIM(BOTH '"' FROM CAST(json_col as CHAR)) as json_col from table_with_json_type

score 0 · Answer 4 · answered Oct 29 '19 at 07:16

if you use laravel or lumen, at this to config/database.php

'options' => [PDO::ATTR_EMULATE_PREPARES => true]

else may be try to install myslnd

apt-get install php7.0-mysqlnd

Ref this: https://laracasts.com/discuss/channels/eloquent/json-column-problem-with-mysql57-and-laravel52#reply-191316

PHP PDO query error on table has json data type (MySQL 5.7.8-rc)

4 Answers4

Linked