Description:
Hi,
I have a table that has ROW_FORMAT=Compact, but it is not reported in the SHOW CREATE TABLE. When I CREATE TABLE LIKE this "Compact" table, it is created as ROW_FORMAT=Dynamic (maybe because different values of innodb_default_row_format, I cannot tell what this was when the original table was created). I would expect CREATE TABLE LIKE to honour the ROW_FORMAT of the original table, not use the default.
Also, this is problematic when I follow the steps in [1] for working with EXCHANGE PARTITION (see how to reproduce).
Many thanks for looking into that,
JFG
How to repeat:
# Using dbdeployer, create a test instance:
dbdeployer deploy single mysql_5.7.26
-- Create a database and a table in row format compact.
-- (I do not know if this is the way my production table was created in ROW_FORMAT=Compact,
-- but what I can see in production is that ROW_FORMAT=Compact does not show in SHOW CREATE TABLE.)
mysql [localhost:5726] {msandbox} ((none)) > CREATE DATABASE test_jfg;
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5726] {msandbox} ((none)) > SET GLOBAL innodb_default_row_format=compact;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5726] {msandbox} ((none)) > CREATE TABLE test_jfg.t1 (
-> id bigint unsigned NOT NULL AUTO_INCREMENT,
-> created_at datetime NOT NULL,
-> str1 varchar(50) DEFAULT NULL,
-> PRIMARY KEY (id,created_at)
-> ) PARTITION BY RANGE ( MONTH(`created_at`))
-> (PARTITION p1 VALUES LESS THAN (2),
-> PARTITION p2 VALUES LESS THAN (3));
Query OK, 0 rows affected (0.03 sec)
mysql [localhost:5726] {msandbox} ((none)) > SHOW CREATE TABLE test_jfg.t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`created_at` datetime NOT NULL,
`str1` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( MONTH(`created_at`))
(PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB) */
1 row in set (0.00 sec)
-- Now use CREATE TABLE LIKE with a different value for innodb_default_row_format.
-- (I cannot tell for sure that the reason for different ROW_FORMAT in production is because
-- of using different values for innodb_default_row_format at the time of creation of the table,
-- but the outcome is the same as below.)
mysql [localhost:5726] {msandbox} ((none)) > SET GLOBAL innodb_default_row_format=dynamic;
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:5726] {msandbox} ((none)) > CREATE TABLE test_jfg.t2 LIKE test_jfg.t1;
Query OK, 0 rows affected (0.02 sec)
mysql [localhost:5726] {msandbox} ((none)) > SHOW CREATE TABLE test_jfg.t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`created_at` datetime NOT NULL,
`str1` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( MONTH(`created_at`))
(PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB) */
1 row in set (0.00 sec)
-- Let's look in I_S, I would expect the ROW_FORMAT of both tables to be the same
-- (after all, I ran CREATE TABLE LIKE), but it is not the case.
mysql [localhost:5726] {msandbox} ((none)) > SELECT TABLE_SCHEMA, TABLE_NAME, ROW_FORMAT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_jfg";
+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | ROW_FORMAT |
+--------------+------------+------------+
| test_jfg | t1 | Compact |
| test_jfg | t2 | Dynamic |
+--------------+------------+------------+
2 rows in set (0.00 sec)
-- And this causes EXCHANGE PARTITION to fail.
mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t1 EXCHANGE PARTITION p2 WITH TABLE test_jfg.t2;
ERROR 1731 (HY000): Non matching attribute 'ROW_FORMAT' between partition and table
-- Obviously, an easy work-around is to ALTER the table to ROW_FORMAT=Compact, but this should not be needed.
mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t2 ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t1 EXCHANGE PARTITION p2 WITH TABLE test_jfg.t2;
Query OK, 0 rows affected (0.02 sec)
Suggested fix:
CREATE TABLE LIKE should honour the original ROW_FORMAT of the table, not use the default specified by innodb_default_row_format.
Description: Hi, I have a table that has ROW_FORMAT=Compact, but it is not reported in the SHOW CREATE TABLE. When I CREATE TABLE LIKE this "Compact" table, it is created as ROW_FORMAT=Dynamic (maybe because different values of innodb_default_row_format, I cannot tell what this was when the original table was created). I would expect CREATE TABLE LIKE to honour the ROW_FORMAT of the original table, not use the default. Also, this is problematic when I follow the steps in [1] for working with EXCHANGE PARTITION (see how to reproduce). Many thanks for looking into that, JFG How to repeat: # Using dbdeployer, create a test instance: dbdeployer deploy single mysql_5.7.26 -- Create a database and a table in row format compact. -- (I do not know if this is the way my production table was created in ROW_FORMAT=Compact, -- but what I can see in production is that ROW_FORMAT=Compact does not show in SHOW CREATE TABLE.) mysql [localhost:5726] {msandbox} ((none)) > CREATE DATABASE test_jfg; Query OK, 1 row affected (0.00 sec) mysql [localhost:5726] {msandbox} ((none)) > SET GLOBAL innodb_default_row_format=compact; Query OK, 0 rows affected (0.00 sec) mysql [localhost:5726] {msandbox} ((none)) > CREATE TABLE test_jfg.t1 ( -> id bigint unsigned NOT NULL AUTO_INCREMENT, -> created_at datetime NOT NULL, -> str1 varchar(50) DEFAULT NULL, -> PRIMARY KEY (id,created_at) -> ) PARTITION BY RANGE ( MONTH(`created_at`)) -> (PARTITION p1 VALUES LESS THAN (2), -> PARTITION p2 VALUES LESS THAN (3)); Query OK, 0 rows affected (0.03 sec) mysql [localhost:5726] {msandbox} ((none)) > SHOW CREATE TABLE test_jfg.t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `created_at` datetime NOT NULL, `str1` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`,`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( MONTH(`created_at`)) (PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB) */ 1 row in set (0.00 sec) -- Now use CREATE TABLE LIKE with a different value for innodb_default_row_format. -- (I cannot tell for sure that the reason for different ROW_FORMAT in production is because -- of using different values for innodb_default_row_format at the time of creation of the table, -- but the outcome is the same as below.) mysql [localhost:5726] {msandbox} ((none)) > SET GLOBAL innodb_default_row_format=dynamic; Query OK, 0 rows affected (0.01 sec) mysql [localhost:5726] {msandbox} ((none)) > CREATE TABLE test_jfg.t2 LIKE test_jfg.t1; Query OK, 0 rows affected (0.02 sec) mysql [localhost:5726] {msandbox} ((none)) > SHOW CREATE TABLE test_jfg.t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `created_at` datetime NOT NULL, `str1` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`,`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( MONTH(`created_at`)) (PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB) */ 1 row in set (0.00 sec) -- Let's look in I_S, I would expect the ROW_FORMAT of both tables to be the same -- (after all, I ran CREATE TABLE LIKE), but it is not the case. mysql [localhost:5726] {msandbox} ((none)) > SELECT TABLE_SCHEMA, TABLE_NAME, ROW_FORMAT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_jfg"; +--------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | ROW_FORMAT | +--------------+------------+------------+ | test_jfg | t1 | Compact | | test_jfg | t2 | Dynamic | +--------------+------------+------------+ 2 rows in set (0.00 sec) -- And this causes EXCHANGE PARTITION to fail. mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t2 REMOVE PARTITIONING; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t1 EXCHANGE PARTITION p2 WITH TABLE test_jfg.t2; ERROR 1731 (HY000): Non matching attribute 'ROW_FORMAT' between partition and table -- Obviously, an easy work-around is to ALTER the table to ROW_FORMAT=Compact, but this should not be needed. mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t2 ROW_FORMAT=Compact; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t1 EXCHANGE PARTITION p2 WITH TABLE test_jfg.t2; Query OK, 0 rows affected (0.02 sec) Suggested fix: CREATE TABLE LIKE should honour the original ROW_FORMAT of the table, not use the default specified by innodb_default_row_format.