[MDEV-21176] DROP INDEX IF EXISTS and ADD INDEX IF NOT EXISTS in the same transaction doesn't work Created: 2019-11-29  Updated: 2019-11-29  Resolved: 2019-11-29

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.1.43
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Manuel Arostegui Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: 10.1, alter
Environment:

Ubuntu

Issue Links:
Duplicate
duplicates MDEV-8351 alter table - failing to ADD KEY IF ... Confirmed

 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

 Comments   
Comment by Alice Sherepa [ 2019-11-29 ]

Thanks for the report!
There is a bug with using the same names of indexes in one query and IF NOT EXISTS.
I close the report, if you'd like to follow the progress, please watch MDEV-8351.

Comment by Manuel Arostegui [ 2019-11-29 ]

Thank you - I will follow that one.

Generated at Thu Feb 08 09:05:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.