[MDEV-13633] JSON_ARRAY() - bad output with some UTF8 characters - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.8
Fix Version/s: 10.2.9
Component/s: Character Sets, JSON
Labels:
None
Environment:
CentOS Linux 7.3

Description

I compared the output from JSON_ARRAY () in mariaDB and json_encode () in php-5.6.31.

I have found that some of the Czech accent characters are not properly converted to number code (connection setting: SET NAMES UTF8):

$row = $this->database->query("SELECT JSON_ARRAY('1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú') AS json_data")->fetch();

$json_test = [

    'mysql' => $row->json_data,

     'php' => json_encode('1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú'),

];

die(var_dump($json_test));

And here is output:

array(2) {

  ["mysql"]=>

  string(90) "["1. \u011B 2. \u0161 3. \u010D 4. \u0159 5. \u017E 6. ? 7. ? 8. ? 9. ? 10. \u016F 11. ?"]"

  ["php"]=>

  string(113) ""1. \u011b 2. \u0161 3. \u010d 4. \u0159 5. \u017e 6. \u00fd 7. \u00e1 8. \u00ed 9. \u00e9 10. \u016f 11. \u00fa""

Attachments

Issue Links

relates to

MDEV-23890 JSON_ARRAY utf8mb4 encoding error

Open

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2017-08-24 22:29 - edited

Thanks for the bug report.

MariaDB 10.2
MariaDB [test]> SELECT JSON_ARRAY('1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú') AS json_data;
+--------------------------------------------------------------------------------------------+
\| json_data \|
+--------------------------------------------------------------------------------------------+
\| ["1. \u011B 2. \u0161 3. \u010D 4. \u0159 5. \u017E 6. � 7. � 8. � 9. � 10. \u016F 11. �"] \|
+--------------------------------------------------------------------------------------------+

MariaDB [test]> show variables like '%char%';
+--------------------------+--------------------------------+
\| Variable_name \| Value \|
+--------------------------+--------------------------------+
\| character_set_client \| utf8 \|
\| character_set_connection \| utf8 \|
\| character_set_database \| latin1 \|
\| character_set_filesystem \| binary \|
\| character_set_results \| utf8 \|
\| character_set_server \| latin1 \|
\| character_set_system \| utf8 \|
\| character_sets_dir \| /data/bld/10.2/share/charsets/ \|
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)

MariaDB [test]> select @@version, @@version_comment;
+----------------------+---------------------+
\| @@version \| @@version_comment \|
+----------------------+---------------------+
\| 10.2.9-MariaDB-debug \| Source distribution \|
+----------------------+---------------------+
1 row in set (0.00 sec)

MySQL 5.7
MySQL [test]> SELECT JSON_ARRAY('1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú') AS json_data;
+-------------------------------------------------------------------------+
\| json_data \|
+-------------------------------------------------------------------------+
\| ["1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú"] \|
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [test]> show variables like '%char%';
+--------------------------+-------------------------------------+
\| Variable_name \| Value \|
+--------------------------+-------------------------------------+
\| character_set_client \| utf8 \|
\| character_set_connection \| utf8 \|
\| character_set_database \| latin1 \|
\| character_set_filesystem \| binary \|
\| character_set_results \| utf8 \|
\| character_set_server \| latin1 \|
\| character_set_system \| utf8 \|
\| character_sets_dir \| /data/bld/mysql-5.7/share/charsets/ \|
+--------------------------+-------------------------------------+
8 rows in set (0.03 sec)

MySQL [test]> select @@version, @@version_comment;
+--------------+---------------------+
\| @@version \| @@version_comment \|
+--------------+---------------------+
\| 5.7.18-debug \| Source distribution \|
+--------------+---------------------+
1 row in set (0.00 sec)

Elena Stepanova added a comment - 2017-08-24 22:29 - edited Thanks for the bug report. MariaDB 10.2 MariaDB [test]> SELECT JSON_ARRAY( '1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú' ) AS json_data; + --------------------------------------------------------------------------------------------+ | json_data | + --------------------------------------------------------------------------------------------+ | [ "1. \u011B 2. \u0161 3. \u010D 4. \u0159 5. \u017E 6. � 7. � 8. � 9. � 10. \u016F 11. �" ] | + --------------------------------------------------------------------------------------------+ MariaDB [test]> show variables like '%char%' ; + --------------------------+--------------------------------+ | Variable_name | Value | + --------------------------+--------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /data/bld/10.2/share/charsets/ | + --------------------------+--------------------------------+ 8 rows in set (0.01 sec) MariaDB [test]> select @@version, @@version_comment; + ----------------------+---------------------+ | @@version | @@version_comment | + ----------------------+---------------------+ | 10.2.9-MariaDB-debug | Source distribution | + ----------------------+---------------------+ 1 row in set (0.00 sec) MySQL 5.7 MySQL [test]> SELECT JSON_ARRAY( '1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú' ) AS json_data; + -------------------------------------------------------------------------+ | json_data | + -------------------------------------------------------------------------+ | [ "1. ě 2. š 3. č 4. ř 5. ž 6. ý 7. á 8. í 9. é 10. ů 11. ú" ] | + -------------------------------------------------------------------------+ 1 row in set (0.00 sec) MySQL [test]> show variables like '%char%' ; + --------------------------+-------------------------------------+ | Variable_name | Value | + --------------------------+-------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /data/bld/mysql-5.7/share/charsets/ | + --------------------------+-------------------------------------+ 8 rows in set (0.03 sec) MySQL [test]> select @@version, @@version_comment; + --------------+---------------------+ | @@version | @@version_comment | + --------------+---------------------+ | 5.7.18-debug | Source distribution | + --------------+---------------------+ 1 row in set (0.00 sec)

Elena Stepanova added a comment - 2017-08-29 15:41

See also complaint at stackoverflow about JSON_OBJECT:
https://stackoverflow.com/questions/45932774/mariadb-json-object-bad-encoding

Elena Stepanova added a comment - 2017-08-29 15:41 See also complaint at stackoverflow about JSON_OBJECT: https://stackoverflow.com/questions/45932774/mariadb-json-object-bad-encoding

Rick James (Inactive) added a comment - 2017-08-29 17:19

You mentioned PHP's json_encode. Is that being used by the client code? Or MariaDB? In any case, use this as a second argument: JSON_UNESCAPED_UNICODE whenever calling json_encode. (This requires PHP 5.4.0.)

Rick James (Inactive) added a comment - 2017-08-29 17:19 You mentioned PHP's json_encode. Is that being used by the client code? Or MariaDB? In any case, use this as a second argument: JSON_UNESCAPED_UNICODE whenever calling json_encode. (This requires PHP 5.4.0.)

Alexey Botchkov added a comment - 2017-09-13 11:44

I think it's fixed, but it's good if Elena doublechecks it.

Alexey Botchkov added a comment - 2017-09-13 11:44 I think it's fixed, but it's good if Elena doublechecks it.

MariaDB Server

JSON_ARRAY() - bad output with some UTF8 characters

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration