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

mysql 5.7 partitions with json type are not upgradeable (MyISAM or InnoDB)

    XMLWordPrintable

Details

    Description

      mysql-5.7 table

      Create Table: CREATE TABLE `jsonptntest` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `data` json DEFAULT NULL,
        `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`,`ts`),
        KEY `ts` (`ts`)
      ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
      /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(ts))
      (PARTITION p_2021 VALUES LESS THAN (1640991600) ENGINE = MyISAM,
       PARTITION p_2022 VALUES LESS THAN (1672527600) ENGINE = MyISAM,
       PARTITION p_2023 VALUES LESS THAN (1704063600) ENGINE = MyISAM,
       PARTITION p_2024 VALUES LESS THAN (1735686000) ENGINE = MyISAM,
       PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
       
       
      Create Table: CREATE TABLE `t1` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `data` json DEFAULT NULL,
        `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`,`ts`),
        KEY `ts` (`ts`)
      ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
      /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(ts))
      (PARTITION p_2021 VALUES LESS THAN (1640991600) ENGINE = InnoDB,
       PARTITION p_2022 VALUES LESS THAN (1672527600) ENGINE = InnoDB,
       PARTITION p_2023 VALUES LESS THAN (1704063600) ENGINE = InnoDB,
       PARTITION p_2024 VALUES LESS THAN (1735686000) ENGINE = InnoDB,
       PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
      

      10.5.26

      MariaDB [test]>  show create table t1\G
      ERROR 4161 (HY000): Unknown data type: 'MYSQL_JSON'
      MariaDB [test]>  show create table jsonptntest\G
      ERROR 4161 (HY000): Unknown data type: 'MYSQL_JSON'
      MariaDB [test]>  INSTALL SONAME 'type_mysql_json';
      Query OK, 0 rows affected (0.012 sec)
       
      MariaDB [test]>  show create table t1\G
      ERROR 1707 (HY000): Table rebuild required. Please do "ALTER TABLE `test.t1` FORCE" or dump/reload to fix it!
      MariaDB [test]>  show create table jsonptntest\G
      ERROR 1707 (HY000): Table rebuild required. Please do "ALTER TABLE `test.jsonptntest` FORCE" or dump/reload to fix it!
      MariaDB [test]>   REPAIR NO_WRITE_TO_BINLOG TABLE t1;
      +---------+--------+----------+-----------------------------------------+
      | Table   | Op     | Msg_type | Msg_text                                |
      +---------+--------+----------+-----------------------------------------+
      | test.t1 | repair | Error    | Table 'test.t1' doesn't exist in engine |
      | test.t1 | repair | status   | Operation failed                        |
      +---------+--------+----------+-----------------------------------------+
      2 rows in set (0.001 sec)
       
      MariaDB [test]>   REPAIR NO_WRITE_TO_BINLOG TABLE jsonptntest;
      +------------------+--------+----------+---------------------------------------------------------------------------------+
      | Table            | Op     | Msg_type | Msg_text                                                                        |
      +------------------+--------+----------+---------------------------------------------------------------------------------+
      | test.jsonptntest | repair | Error    | The mix of handlers in the partitions is not allowed in this version of MariaDB |
      | test.jsonptntest | repair | status   | Operation failed                                                                |
      +------------------+--------+----------+---------------------------------------------------------------------------------+
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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