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

DROP INDEX IF EXISTS and ADD INDEX IF NOT EXISTS in the same transaction doesn't work

    XMLWordPrintable

Details

    Description

      MariaDB [test]> show create table revision\G
      *************************** 1. row ***************************
             Table: revision
      Create Table: CREATE TABLE `revision` (
        `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
        `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
        `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
        `rev_comment` varbinary(255) DEFAULT '',
        `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
        `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
        `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
        `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `rev_len` int(8) unsigned DEFAULT NULL,
        `rev_parent_id` int(8) unsigned DEFAULT NULL,
        `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
        `rev_content_model` varbinary(32) DEFAULT NULL,
        `rev_content_format` varbinary(64) DEFAULT NULL,
        PRIMARY KEY (`rev_id`,`rev_user`),
        KEY `rev_timestamp` (`rev_timestamp`,`rev_id`),
        KEY `page_timestamp` (`rev_page`,`rev_timestamp`,`rev_id`),
        KEY `user_timestamp` (`rev_user`,`rev_timestamp`,`rev_id`),
        KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`,`rev_id`),
        KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`,`rev_id`),
        KEY `rev_page_id` (`rev_page`,`rev_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=928449054 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
      /*!50100 PARTITION BY RANGE (rev_user)
      (PARTITION p1 VALUES LESS THAN (1) ENGINE = InnoDB,
       PARTITION p100000 VALUES LESS THAN (100000) ENGINE = InnoDB,
       PARTITION p200000 VALUES LESS THAN (200000) ENGINE = InnoDB,
       PARTITION p300000 VALUES LESS THAN (300000) ENGINE = InnoDB,
       PARTITION p400000 VALUES LESS THAN (400000) ENGINE = InnoDB,
       PARTITION p500000 VALUES LESS THAN (500000) ENGINE = InnoDB,
       PARTITION p700000 VALUES LESS THAN (750000) ENGINE = InnoDB,
       PARTITION p1000000 VALUES LESS THAN (1000000) ENGINE = InnoDB,
       PARTITION p1200000 VALUES LESS THAN (1500000) ENGINE = InnoDB,
       PARTITION p2000000 VALUES LESS THAN (2000000) ENGINE = InnoDB,
       PARTITION p3000000 VALUES LESS THAN (3000000) ENGINE = InnoDB,
       PARTITION p4000000 VALUES LESS THAN (4000000) ENGINE = InnoDB,
       PARTITION p5000000 VALUES LESS THAN (5000000) ENGINE = InnoDB,
       PARTITION p6000000 VALUES LESS THAN (6000000) ENGINE = InnoDB,
       PARTITION p7000000 VALUES LESS THAN (7000000) ENGINE = InnoDB,
       PARTITION p8000000 VALUES LESS THAN (8000000) ENGINE = InnoDB,
       PARTITION p9000000 VALUES LESS THAN (9000000) ENGINE = InnoDB,
       PARTITION p10000000 VALUES LESS THAN (10000000) ENGINE = InnoDB,
       PARTITION p12000000 VALUES LESS THAN (12000000) ENGINE = InnoDB,
       PARTITION p14000000 VALUES LESS THAN (14000000) ENGINE = InnoDB,
       PARTITION p16000000 VALUES LESS THAN (16000000) ENGINE = InnoDB,
       PARTITION p18000000 VALUES LESS THAN (18000000) ENGINE = InnoDB,
       PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
      1 row in set (0.01 sec)
      
      

      Let's remove its partitions and check the table again:

      MariaDB [test]> alter table revision remove partitioning;
      Query OK, 0 rows affected, 4 warnings (0.18 sec)
      Records: 0  Duplicates: 0  Warnings: 4
       
      MariaDB [test]> show warnings;
      +---------+------+-----------------------------------------------------------------------+
      | Level   | Code | Message                                                               |
      +---------+------+-----------------------------------------------------------------------+
      | Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.        |
      | Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8.                                    |
      | Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. |
      | Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                                  |
      +---------+------+-----------------------------------------------------------------------+
      4 rows in set (0.00 sec)
       
      MariaDB [test]> show create table revision\G
      *************************** 1. row ***************************
             Table: revision
      Create Table: CREATE TABLE `revision` (
        `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
        `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
        `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
        `rev_comment` varbinary(255) DEFAULT '',
        `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
        `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
        `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
        `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `rev_len` int(8) unsigned DEFAULT NULL,
        `rev_parent_id` int(8) unsigned DEFAULT NULL,
        `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
        `rev_content_model` varbinary(32) DEFAULT NULL,
        `rev_content_format` varbinary(64) DEFAULT NULL,
        PRIMARY KEY (`rev_id`,`rev_user`),
        KEY `rev_timestamp` (`rev_timestamp`,`rev_id`),
        KEY `page_timestamp` (`rev_page`,`rev_timestamp`,`rev_id`),
        KEY `user_timestamp` (`rev_user`,`rev_timestamp`,`rev_id`),
        KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`,`rev_id`),
        KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`,`rev_id`),
        KEY `rev_page_id` (`rev_page`,`rev_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=928449054 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
      1 row in set (0.00 sec)
      
      

      Now let's change the PK, remove the indexes and create the new ones all in one transaction:

       
      MariaDB [test]> alter table revision drop primary key, add primary key (rev_id),  drop index if exists rev_timestamp, drop index if exists page_timestamp, drop index if exists user_timestamp, drop index if exists usertext_timestamp, drop index if exists page_user_timestamp, drop index if exists rev_page_id, add index if not exists `rev_timestamp` (`rev_timestamp`), add index if not exists `page_timestamp` (`rev_page`,`rev_timestamp`), add index if not exists `user_timestamp` (`rev_user`,`rev_timestamp`), add index if not exists `usertext_timestamp` (`rev_user_text`,`rev_timestamp`), add index if not exists `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`), add index if not exists `rev_page_id` (`rev_page`,`rev_id`);
      Query OK, 0 rows affected, 10 warnings (0.01 sec)
      Records: 0  Duplicates: 0  Warnings: 10
       
      MariaDB [test]> show warnings;
      +---------+------+-----------------------------------------------------------------------+
      | Level   | Code | Message                                                               |
      +---------+------+-----------------------------------------------------------------------+
      | Note    | 1061 | Duplicate key name 'rev_timestamp'                                    |
      | Note    | 1061 | Duplicate key name 'page_timestamp'                                   |
      | Note    | 1061 | Duplicate key name 'user_timestamp'                                   |
      | Note    | 1061 | Duplicate key name 'usertext_timestamp'                               |
      | Note    | 1061 | Duplicate key name 'page_user_timestamp'                              |
      | Note    | 1061 | Duplicate key name 'rev_page_id'                                      |
      | Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.        |
      | Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8.                                    |
      | Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. |
      | Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                                  |
      +---------+------+-----------------------------------------------------------------------+
      10 rows in set (0.00 sec)
      
      

      Those duplicate warnings are strange...

       
      MariaDB [test]> show create table revision\G
      *************************** 1. row ***************************
             Table: revision
      Create Table: CREATE TABLE `revision` (
        `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
        `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
        `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
        `rev_comment` varbinary(255) DEFAULT '',
        `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
        `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
        `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
        `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `rev_len` int(8) unsigned DEFAULT NULL,
        `rev_parent_id` int(8) unsigned DEFAULT NULL,
        `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
        `rev_content_model` varbinary(32) DEFAULT NULL,
        `rev_content_format` varbinary(64) DEFAULT NULL,
        PRIMARY KEY (`rev_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=928449054 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
      1 row in set (0.00 sec)
      

      That's strange, let's run the command again:

      MariaDB [test]> alter table revision drop primary key, add primary key (rev_id),  drop index if exists rev_timestamp, drop index if exists page_timestamp, drop index if exists user_timestamp, drop index if exists usertext_timestamp, drop index if exists page_user_timestamp, drop index if exists rev_page_id, add index if not exists `rev_timestamp` (`rev_timestamp`), add index if not exists `page_timestamp` (`rev_page`,`rev_timestamp`), add index if not exists `user_timestamp` (`rev_user`,`rev_timestamp`), add index if not exists `usertext_timestamp` (`rev_user_text`,`rev_timestamp`), add index if not exists `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`), add index if not exists `rev_page_id` (`rev_page`,`rev_id`);
      Query OK, 0 rows affected, 10 warnings (0.01 sec)
      Records: 0  Duplicates: 0  Warnings: 10
       
      MariaDB [test]> show warnings;
      +---------+------+-----------------------------------------------------------------------+
      | Level   | Code | Message                                                               |
      +---------+------+-----------------------------------------------------------------------+
      | Note    | 1091 | Can't DROP 'rev_timestamp'; check that column/key exists              |
      | Note    | 1091 | Can't DROP 'page_timestamp'; check that column/key exists             |
      | Note    | 1091 | Can't DROP 'user_timestamp'; check that column/key exists             |
      | Note    | 1091 | Can't DROP 'usertext_timestamp'; check that column/key exists         |
      | Note    | 1091 | Can't DROP 'page_user_timestamp'; check that column/key exists        |
      | Note    | 1091 | Can't DROP 'rev_page_id'; check that column/key exists                |
      | Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.        |
      | Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8.                                    |
      | Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. |
      | Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                                  |
      +---------+------+-----------------------------------------------------------------------+
      10 rows in set (0.00 sec)
       
      MariaDB [test]> show create table revision\G
      *************************** 1. row ***************************
             Table: revision
      Create Table: CREATE TABLE `revision` (
        `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
        `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
        `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
        `rev_comment` varbinary(255) DEFAULT '',
        `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
        `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
        `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
        `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `rev_len` int(8) unsigned DEFAULT NULL,
        `rev_parent_id` int(8) unsigned DEFAULT NULL,
        `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
        `rev_content_model` varbinary(32) DEFAULT NULL,
        `rev_content_format` varbinary(64) DEFAULT NULL,
        PRIMARY KEY (`rev_id`),
        KEY `rev_timestamp` (`rev_timestamp`),
        KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
        KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
        KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
        KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
        KEY `rev_page_id` (`rev_page`,`rev_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=928449054 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
      1 row in set (0.00 sec)
      

      Index got created.

      Let's run the commands separately:

      MariaDB [test]> drop table revision;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [test]> CREATE TABLE `revision` (
          ->   `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
          ->   `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
          ->   `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
          ->   `rev_comment` varbinary(255) DEFAULT '',
          ->   `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
          ->   `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
          ->   `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
          ->   `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
          ->   `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
          ->   `rev_len` int(8) unsigned DEFAULT NULL,
          ->   `rev_parent_id` int(8) unsigned DEFAULT NULL,
          ->   `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
          ->   `rev_content_model` varbinary(32) DEFAULT NULL,
          ->   `rev_content_format` varbinary(64) DEFAULT NULL,
          ->   PRIMARY KEY (`rev_id`,`rev_user`),
          ->   KEY `rev_timestamp` (`rev_timestamp`,`rev_id`),
          ->   KEY `page_timestamp` (`rev_page`,`rev_timestamp`,`rev_id`),
          ->   KEY `user_timestamp` (`rev_user`,`rev_timestamp`,`rev_id`),
          ->   KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`,`rev_id`),
          ->   KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`,`rev_id`),
          ->   KEY `rev_page_id` (`rev_page`,`rev_id`)
          -> ) ENGINE=InnoDB AUTO_INCREMENT=928449054 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
          -> /*!50100 PARTITION BY RANGE (rev_user)
          -> (PARTITION p1 VALUES LESS THAN (1) ENGINE = InnoDB,
          ->  PARTITION p100000 VALUES LESS THAN (100000) ENGINE = InnoDB,
          ->  PARTITION p200000 VALUES LESS THAN (200000) ENGINE = InnoDB,
          ->  PARTITION p300000 VALUES LESS THAN (300000) ENGINE = InnoDB,
          ->  PARTITION p400000 VALUES LESS THAN (400000) ENGINE = InnoDB,
          ->  PARTITION p500000 VALUES LESS THAN (500000) ENGINE = InnoDB,
          ->  PARTITION p700000 VALUES LESS THAN (750000) ENGINE = InnoDB,
          ->  PARTITION p1000000 VALUES LESS THAN (1000000) ENGINE = InnoDB,
          ->  PARTITION p1200000 VALUES LESS THAN (1500000) ENGINE = InnoDB,
          ->  PARTITION p2000000 VALUES LESS THAN (2000000) ENGINE = InnoDB,
          ->  PARTITION p3000000 VALUES LESS THAN (3000000) ENGINE = InnoDB,
          ->  PARTITION p4000000 VALUES LESS THAN (4000000) ENGINE = InnoDB,
          ->  PARTITION p5000000 VALUES LESS THAN (5000000) ENGINE = InnoDB,
          ->  PARTITION p6000000 VALUES LESS THAN (6000000) ENGINE = InnoDB,
          ->  PARTITION p7000000 VALUES LESS THAN (7000000) ENGINE = InnoDB,
          ->  PARTITION p8000000 VALUES LESS THAN (8000000) ENGINE = InnoDB,
          ->  PARTITION p9000000 VALUES LESS THAN (9000000) ENGINE = InnoDB,
          ->  PARTITION p10000000 VALUES LESS THAN (10000000) ENGINE = InnoDB,
          ->  PARTITION p12000000 VALUES LESS THAN (12000000) ENGINE = InnoDB,
          ->  PARTITION p14000000 VALUES LESS THAN (14000000) ENGINE = InnoDB,
          ->  PARTITION p16000000 VALUES LESS THAN (16000000) ENGINE = InnoDB,
          ->  PARTITION p18000000 VALUES LESS THAN (18000000) ENGINE = InnoDB,
          ->  PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
      Query OK, 0 rows affected, 92 warnings (0.33 sec)
       
      MariaDB [test]> show create table revision;
      +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
      +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | revision | CREATE TABLE `revision` (
        `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
        `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
        `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
        `rev_comment` varbinary(255) DEFAULT '',
        `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
        `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
        `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
        `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `rev_len` int(8) unsigned DEFAULT NULL,
        `rev_parent_id` int(8) unsigned DEFAULT NULL,
        `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
        `rev_content_model` varbinary(32) DEFAULT NULL,
        `rev_content_format` varbinary(64) DEFAULT NULL,
        PRIMARY KEY (`rev_id`,`rev_user`),
        KEY `rev_timestamp` (`rev_timestamp`,`rev_id`),
        KEY `page_timestamp` (`rev_page`,`rev_timestamp`,`rev_id`),
        KEY `user_timestamp` (`rev_user`,`rev_timestamp`,`rev_id`),
        KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`,`rev_id`),
        KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`,`rev_id`),
        KEY `rev_page_id` (`rev_page`,`rev_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=928449054 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
      /*!50100 PARTITION BY RANGE (rev_user)
      (PARTITION p1 VALUES LESS THAN (1) ENGINE = InnoDB,
       PARTITION p100000 VALUES LESS THAN (100000) ENGINE = InnoDB,
       PARTITION p200000 VALUES LESS THAN (200000) ENGINE = InnoDB,
       PARTITION p300000 VALUES LESS THAN (300000) ENGINE = InnoDB,
       PARTITION p400000 VALUES LESS THAN (400000) ENGINE = InnoDB,
       PARTITION p500000 VALUES LESS THAN (500000) ENGINE = InnoDB,
       PARTITION p700000 VALUES LESS THAN (750000) ENGINE = InnoDB,
       PARTITION p1000000 VALUES LESS THAN (1000000) ENGINE = InnoDB,
       PARTITION p1200000 VALUES LESS THAN (1500000) ENGINE = InnoDB,
       PARTITION p2000000 VALUES LESS THAN (2000000) ENGINE = InnoDB,
       PARTITION p3000000 VALUES LESS THAN (3000000) ENGINE = InnoDB,
       PARTITION p4000000 VALUES LESS THAN (4000000) ENGINE = InnoDB,
       PARTITION p5000000 VALUES LESS THAN (5000000) ENGINE = InnoDB,
       PARTITION p6000000 VALUES LESS THAN (6000000) ENGINE = InnoDB,
       PARTITION p7000000 VALUES LESS THAN (7000000) ENGINE = InnoDB,
       PARTITION p8000000 VALUES LESS THAN (8000000) ENGINE = InnoDB,
       PARTITION p9000000 VALUES LESS THAN (9000000) ENGINE = InnoDB,
       PARTITION p10000000 VALUES LESS THAN (10000000) ENGINE = InnoDB,
       PARTITION p12000000 VALUES LESS THAN (12000000) ENGINE = InnoDB,
       PARTITION p14000000 VALUES LESS THAN (14000000) ENGINE = InnoDB,
       PARTITION p16000000 VALUES LESS THAN (16000000) ENGINE = InnoDB,
       PARTITION p18000000 VALUES LESS THAN (18000000) ENGINE = InnoDB,
       PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
      +----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.01 sec)
       
      MariaDB [test]> alter table revision remove partitioning;
      Query OK, 0 rows affected, 4 warnings (0.25 sec)
      Records: 0  Duplicates: 0  Warnings: 4
       
      MariaDB [test]> alter table revision drop primary key, add primary key (rev_id),  drop index if exists rev_timestamp, drop index if exists page_timestamp, drop index if exists user_timestamp, drop index if exists usertext_timestamp, drop index if exists page_user_timestamp, drop index if exists rev_page_id;
      Query OK, 0 rows affected, 4 warnings (0.01 sec)
      Records: 0  Duplicates: 0  Warnings: 4
       
      MariaDB [test]> show warnings;
      +---------+------+-----------------------------------------------------------------------+
      | Level   | Code | Message                                                               |
      +---------+------+-----------------------------------------------------------------------+
      | Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.        |
      | Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8.                                    |
      | Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. |
      | Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                                  |
      +---------+------+-----------------------------------------------------------------------+
      4 rows in set (0.00 sec)
       
      MariaDB [test]> alter table revision add index if not exists `rev_timestamp` (`rev_timestamp`), add index if not exists `page_timestamp` (`rev_page`,`rev_timestamp`), add index if not exists `user_timestamp` (`rev_user`,`rev_timestamp`), add index if not exists `usertext_timestamp` (`rev_user_text`,`rev_timestamp`), add index if not exists `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`), add index if not exists `rev_page_id` (`rev_page`,`rev_id`);
      Query OK, 0 rows affected, 4 warnings (0.02 sec)
      Records: 0  Duplicates: 0  Warnings: 4
       
      MariaDB [test]> show warnings;
      +---------+------+-----------------------------------------------------------------------+
      | Level   | Code | Message                                                               |
      +---------+------+-----------------------------------------------------------------------+
      | Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.        |
      | Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8.                                    |
      | Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. |
      | Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                                  |
      +---------+------+-----------------------------------------------------------------------+
      4 rows in set (0.00 sec)
       
      MariaDB [test]> show create table revision;
      +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
      +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | revision | CREATE TABLE `revision` (
        `rev_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
        `rev_page` int(8) unsigned NOT NULL DEFAULT '0',
        `rev_text_id` int(8) unsigned NOT NULL DEFAULT '0',
        `rev_comment` varbinary(255) DEFAULT '',
        `rev_user` int(5) unsigned NOT NULL DEFAULT '0',
        `rev_user_text` varbinary(255) NOT NULL DEFAULT '',
        `rev_timestamp` varbinary(14) NOT NULL DEFAULT '',
        `rev_minor_edit` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `rev_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `rev_len` int(8) unsigned DEFAULT NULL,
        `rev_parent_id` int(8) unsigned DEFAULT NULL,
        `rev_sha1` varbinary(32) NOT NULL DEFAULT '',
        `rev_content_model` varbinary(32) DEFAULT NULL,
        `rev_content_format` varbinary(64) DEFAULT NULL,
        PRIMARY KEY (`rev_id`),
        KEY `rev_timestamp` (`rev_timestamp`),
        KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
        KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
        KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
        KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
        KEY `rev_page_id` (`rev_page`,`rev_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=928449054 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 |
      +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [(none)]> select @@version; select @@tx_isolation;
      +--------------------------+
      | @@version                |
      +--------------------------+
      | 10.1.43-MariaDB-1~xenial |
      +--------------------------+
      1 row in set (0.00 sec)
       
      +-----------------+
      | @@tx_isolation  |
      +-----------------+
      | REPEATABLE-READ |
      +-----------------+
      1 row in set (0.00 sec)
      

      It works if run in different transactions?
      Switching to Barracuda doesn't make any difference

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              marostegui Manuel Arostegui
              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.