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
– VolkerK, Commented Oct 21, 2015 at 8: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
– VolkerK, Commented Oct 21, 2015 at 8:33
see bugs.php.net/bug.php?id=70384 and github.com/php/php-src/commit/… — VolkerK
– VolkerK, Commented Oct 21, 2015 at 8: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.
– Quang Ng., Commented Oct 22, 2015 at 3:01

Robert Moskal · Accepted Answer · 2017-10-20 15:31:05Z

14

It's a Bug reported to PHP Developers #70384

The developer [email protected] 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, 2017 at 15:31

Robert Moskal

22.7k8 gold badges69 silver badges91 bronze badges

answered Dec 15, 2015 at 17:27

LeonanCarvalho

1,8893 gold badges22 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Quang Ng. Over a year ago

Thanks for your answer. Do you know when will the next version release ?

Cafe Coder Over a year ago

Casting worked for me, but the issue doesn't seem to be resolved in PHP 5.6.19

LeonanCarvalho Over a year ago

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

Robert Moskal Over a year ago

This is ok, but the values come back quoted. How to get rid of them?

Wojciech · Accepted Answer · 2016-04-26 17:45:11Z

2

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

answered Apr 26, 2016 at 17:45

Wojciech

291 bronze badge

Comments

Robert Moskal · Accepted Answer · 2017-10-20 15:33:47Z

1

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

answered Oct 20, 2017 at 15:33

Robert Moskal

22.7k8 gold badges69 silver badges91 bronze badges

Comments

Huy Phan · Accepted Answer · 2019-10-29 07:16:47Z

0

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

answered Oct 29, 2019 at 7:16

Huy Phan

812 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related