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

Question

I am trying to use the new json data type for mysql 5.7. When I use my own php mysql query, it works fine, but when I use PDO to query data, it shows this error:

Error: "PDOException" exception with the 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; } ?> 

Answer 1

This is an error message for PHP developers. # 70384

The developer [e-mail] just posted:

Fixed correction of this error.

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

For Windows:

http://windows.php.net/snapshots/ Thanks 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.

Thus, the JSON data type will be supported in PHP 5.6.19+. For another version, there is a workaround available above.

This workaround changes the JSON field from the CAST function to CHAR, which is excellent from a PHP perspective: For example :.

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

He worked with me in all cases.

For full compatibility you should use PHP-5.6.19 +

Answer 2

I had the same problem in PHP 5.5 - decided to upgrade to PHP 5.6, but the problem still existed. Casting in CHAR helps, but this is not a good solution.

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

Therefore, I recommend doing the same.

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

I installed the mysqlnd driver on an Ubuntu server using apt-get:

apt-get install php5-mysqlnd

Answer 3

Like a little addition. Moving a column in char seems to be returning a value with double quotes around it. You can get rid of them with cropping:

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