[MDEV-21176] DROP INDEX IF EXISTS and ADD INDEX IF NOT EXISTS in the same transaction doesn't work - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 10.1.43
Fix Version/s: N/A
Component/s: Data Definition - Alter Table
Labels:
- 10.1
- alter
Environment:
Ubuntu

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

duplicates

MDEV-8351 alter table - failing to ADD KEY IF NOT EXISTS when existing dropping the same index name earlier in the alter table

Confirmed

Activity

People

Assignee:: Unassigned

Reporter:: Manuel Arostegui

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2019-11-29 09:15

Updated:: 2019-11-29 13:40

Resolved:: 2019-11-29 13:38

Git Integration

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