Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13633

JSON_ARRAY() - bad output with some UTF8 characters

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.8
    • 10.2.9
    • Character Sets, JSON
    • None
    • 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

          Activity

            elenst Elena Stepanova added a comment - - 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)
            

            elenst Elena Stepanova added a comment - - 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)

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

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

            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.)

            rjasdf Rick James (Inactive) added a comment - 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.)

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

            holyfoot Alexey Botchkov added a comment - I think it's fixed, but it's good if Elena doublechecks it.

            People

              holyfoot Alexey Botchkov
              chaky Michal Hucik
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.