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

Output result in show create table for mysql_json type should be longtext

    XMLWordPrintable

Details

    Description

      MariaDB [mysql_data_json]> CREATE TABLE testjson (t mysql_json NOT NULL);
      ERROR 4161 (HY000): Unknown data type: 'mysql_json'
       
      MariaDB [mysql_data_json]> install soname 'type_mysql_json';
      Query OK, 0 rows affected (0.015 sec)
       
      MariaDB [mysql_data_json]> CREATE TABLE testjson (t mysql_json NOT NULL);
      Query OK, 0 rows affected (0.031 sec)
       
      MariaDB [mysql_data_json]> show create table testjson;
      +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table    | Create Table                                                                                                                                                             |
      +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | testjson | CREATE TABLE `testjson` (
        `t` json /* MySQL 5.7 */ CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
      +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      Using above query we will get an syntax error

      CREATE TABLE `t2` (
          ->   `j` json CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`j`))
          -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`j`)...' at line 2
      

      However, if instead of json, longtext is specified, than the result is good.

      So, JSON datatype field shouldn't have any character set and collation, except of default utf8mb4|utf8mb4_bin.

      Attachments

        Issue Links

          Activity

            People

              anel Anel Husakovic
              anel Anel Husakovic
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.