Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
-
None
-
Linux x86_64
Description
When CREATE TABLE LIKE copies an InnoDB table with explicitly named subpartitions, page-compression options specified on the parent partition are absent from SHOW CREATE TABLE and are not applied to the copy. The source table's physical subpartitions have InnoDB dictionary FLAG 929, while the copied table's subpartitions have FLAG 33.
This was observed on MariaDB 12.3.3 with the following SQL:
CREATE DATABASE options; |
USE options; |
SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'; |
|
|
CREATE TABLE implicit (id INT) ENGINE=InnoDB PAGE_COMPRESSED=0 |
PARTITION BY RANGE(id) SUBPARTITION BY HASH(id) SUBPARTITIONS 2 |
(PARTITION p VALUES LESS THAN MAXVALUE |
PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3);
|
SHOW WARNINGS;
|
|
|
CREATE TABLE explicit (id INT) ENGINE=InnoDB PAGE_COMPRESSED=0 |
PARTITION BY RANGE(id) SUBPARTITION BY HASH(id) |
(PARTITION p VALUES LESS THAN MAXVALUE |
PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=3
|
(SUBPARTITION s0, SUBPARTITION s1));
|
SHOW WARNINGS;
|
|
|
SHOW CREATE TABLE implicit; |
SHOW CREATE TABLE explicit; |
|
|
CREATE TABLE clone LIKE explicit; |
SHOW WARNINGS;
|
|
|
SELECT 'FLAGS', NAME, FLAG |
FROM information_schema.INNODB_SYS_TABLES |
WHERE NAME LIKE 'options/%' |
ORDER BY NAME; |
|
|
SELECT 'ORACLE', COUNT(*)=6 AND COUNT(DISTINCT FLAG)=1 |
FROM information_schema.INNODB_SYS_TABLES |
WHERE NAME LIKE 'options/%'; |
|
|
FLUSH TABLES explicit;
|
SHOW CREATE TABLE explicit; |
SHOW CREATE TABLE implicit retains the parent partition options:
PARTITION BY RANGE (`id`)
|
SUBPARTITION BY HASH (`id`)
|
SUBPARTITIONS 2
|
(PARTITION `p` VALUES LESS THAN MAXVALUE ENGINE = InnoDB `PAGE_COMPRESSED`='1' `PAGE_COMPRESSION_LEVEL`='3')
|
For explicit, the same effective options are omitted even though its physical subpartitions use them:
PARTITION BY RANGE (`id`)
|
SUBPARTITION BY HASH (`id`)
|
(PARTITION `p` VALUES LESS THAN MAXVALUE
|
(SUBPARTITION `s0` ENGINE = InnoDB,
|
SUBPARTITION `s1` ENGINE = InnoDB))
|
The dictionary query returns:
FLAGS options/clone#P#p#SP#s0 33
|
FLAGS options/clone#P#p#SP#s1 33
|
FLAGS options/explicit#P#p#SP#s0 929
|
FLAGS options/explicit#P#p#SP#s1 929
|
FLAGS options/implicit#P#p#SP#psp0 929
|
FLAGS options/implicit#P#p#SP#psp1 929
|
ORACLE 0
|
The generated-subpartition and explicitly named source definitions are equivalent with respect to the parent compression settings, and all four source subpartitions have FLAG 929. CREATE TABLE LIKE should preserve those effective settings, so the copied subpartitions should also have FLAG 929. Instead, the missing persisted options cause the copy to use FLAG 33.
This occurs when CREATE TABLE LIKE copies a subpartitioned InnoDB table whose compression options are specified on a parent partition with explicitly named subpartitions.