Page MenuHomePhabricator
Paste P9782

(An Untitled Masterwork)
ActivePublic

Authored by Marostegui on Fri, Nov 29, 8:34 AM.
```
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 remove partitioning;
Query OK, 0 rows affected, 4 warnings (0.23 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, 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 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 `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, 11 warnings (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 11
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 |
| Note | 1061 | Duplicate key name 'rev_timestamp' |
| 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. |
+---------+------+-----------------------------------------------------------------------+
11 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)
```
It works if run in different transactions?

Event Timeline

Marostegui created this paste.Fri, Nov 29, 8:34 AM