| Bug #79469 | Getting inappropriate error while transport table from MySQL 5.6 to MySQL 5.7 | ||
|---|---|---|---|
| Submitted: | 1 Dec 2015 5:43 | Modified: | 1 Dec 2015 11:25 |
| Reporter: | Nilnandan Joshi | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.7, 5.7.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | ALTER TABLE, IMPORT TABLESPACE, MySQL 5.7 | ||
[1 Dec 2015 11:25]
MySQL Verification Team
Hello Nilnandan, Thank you for the report. Imho this is reproducible just even with both source/target with 5.7.9. Per manual, when you have diff ROW_FORMAT on source and target instances then this could result in a schema mismatch error. But, I'm verifying it for the clear/meaningful error message. Thanks, Umesh
[1 Dec 2015 11:26]
MySQL Verification Team
// Source Server - create table with ROW_FORMAT=COMPACT
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> show variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t(c1 INT) engine=InnoDB row_format=compact;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`c1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
1 row in set (0.01 sec)
mysql> FLUSH TABLES t FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)
mysql> \! ls -l /export/umesh/server/binaries/mysql-5.7.9/79469/test
total 116
-rw-r----- 1 umshastr common 65 Dec 1 12:06 db.opt
-rw-r----- 1 umshastr common 355 Dec 1 12:08 t.cfg
-rw-r----- 1 umshastr common 8556 Dec 1 12:08 t.frm
-rw-r----- 1 umshastr common 98304 Dec 1 12:08 t.ibd
mysql> \! cp /export/umesh/server/binaries/mysql-5.7.9/79469/test/t.{ibd,cfg} /tmp/
mysql> \! ls -l /tmp/t.{ibd,cfg}
-rw-r----- 1 umshastr common 355 Dec 1 12:09 /tmp/t.cfg
-rw-r----- 1 umshastr common 98304 Dec 1 12:09 /tmp/t.ibd
mysql>
-- target server
-- create table and leave ROW_FORMAT for default
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE t DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)
mysql> \! cp /tmp/t.{ibd,cfg} /export/umesh/server/binaries/mysql-5.7.9/79469/test/
mysql>
mysql> \! ls -l /export/umesh/server/binaries/mysql-5.7.9/79469/test
total 116
-rw-r----- 1 umshastr common 65 Dec 1 12:11 db.opt
-rw-r----- 1 umshastr common 355 Dec 1 12:13 t.cfg
-rw-r----- 1 umshastr common 8556 Dec 1 12:11 t.frm
-rw-r----- 1 umshastr common 98304 Dec 1 12:13 t.ibd
mysql>
mysql> ALTER TABLE t IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x4 and the meta-data file has 0x1)
mysql>
[8 Jan 2016 11:49]
MySQL Verification Team
Bug #79888 marked as duplicate of this
[20 Jan 2016 7:55]
Daniël van Eeden
Related: Bug #80057 View for InnoDB Flags
[27 Jun 2017 5:15]
MySQL Verification Team
Bug#86824 marked as duplicate of this one
Description: While transport tablespace from MySQL 5.6 to MySQL 5.7, I got this error. mysql> ALTER TABLE nil IMPORT TABLESPACE; ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x5 and the meta-data file has 0x1) After checking code, found that there is some mistake. It should print table->flags and not table->n_cols if (m_flags != m_table->flags) { ib_errf(thd, IB_LOG_LEVEL_ERROR, ER_TABLE_SCHEMA_MISMATCH, "Table flags don't match, server table has 0x%lx" " and the meta-data file has 0x%lx", (ulong) m_table->n_cols, (ulong) m_flags); Also, just printing 0x1, doesn't ideally make sense for end-user. Here I think, error message should be improved and decode these flags while printing. like if 0x5 or 0X1 corresponds to ROW_FORMAT_COMPACT then it should say something like ... Table flags don't match, server table has ROW_FORMAT_DYNAMIC and metadata file has ROW_FORMAT_COMPACT ... It gives actual error when we removed .cfg file. mysql> ALTER TABLE nil IMPORT TABLESPACE; ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.) But still It would be great help if constant are replaced with more meaningful values at the first stage. How to repeat: mysql> create database nil; Query OK, 1 row affected (0.02 sec) mysql> use nil; Database changed mysql> create table nil(id int, name varchar(10)); Query OK, 0 rows affected (0.01 sec) mysql> insert into nil values (1, 'nilnandan'),(2, 'niljoshi'),(3, 'njoshi'),(4,'joshi'); Query OK, 4 rows affected (0.10 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from nil; +------+-----------+ | id | name | +------+-----------+ | 1 | nilnandan | | 2 | niljoshi | | 3 | njoshi | | 4 | joshi | +------+-----------+ 4 rows in set (0.02 sec) Then take the backup for nil database and export it. nilnandan.joshi@bm-support01:~$ innobackupex --defaults-file=/home/njoshi/sandboxes/msb_5_6_24/my.sandbox.cnf --user=root --password=msandbox --databases="nil" /home/njoshi/backup/ 151127 01:29:14 innobackupex: Starting the backup operation .... 151127 01:29:16 completed OK! nilnandan.joshi@bm-support01:~$ nilnandan.joshi@bm-support01:~$ innobackupex --apply-log --export backup/2015-11-27_01-29-14 151127 01:32:25 innobackupex: Starting the apply-log operation ... 151127 01:32:28 completed OK! nilnandan.joshi@bm-support01:~$ nilnandan.joshi@bm-support01:~/backup/2015-11-27_01-29-14/nil$ ll total 140 drwx------ 2 nilnandan.joshi percona 4096 Nov 27 01:32 ./ drwx------ 3 nilnandan.joshi percona 4096 Nov 27 01:32 ../ -rw-r----- 1 nilnandan.joshi percona 65 Nov 27 01:29 db.opt -rw-r--r-- 1 nilnandan.joshi percona 421 Nov 27 01:32 nil.cfg -rw-r--r-- 1 nilnandan.joshi percona 16384 Nov 27 01:32 nil.exp -rw-r----- 1 nilnandan.joshi percona 8586 Nov 27 01:29 nil.frm -rw-r----- 1 nilnandan.joshi percona 98304 Nov 27 01:29 nil.ibd nilnandan.joshi@bm-support01:~/backup/2015-11-27_01-29-14/nil$ Now on MySQL 5.7, create nil table, discard tablespace, copy .cfg and .ibd files from backup to the datadir and set proper permissions. nilnandan.joshi@bm-support01:~/backup/2015-11-27_01-29-14/nil$ mysql -uroot -p --socket=/tmp/mysql_sandbox13454.sock Enter password: ver version: 5.7.9-log MySQL Community Server (GPL) ... mysql> create database nil; Query OK, 1 row affected (0.04 sec) mysql> use nil Database changed mysql> create table nil(id int, name varchar(10)); Query OK, 0 rows affected (0.04 sec) mysql> ALTER TABLE nil DISCARD TABLESPACE; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ sudo cp /home/njoshi/backup/2015-11-27_01-29-14/nil/nil.cfg . nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ sudo cp /home/njoshi/backup/2015-11-27_01-29-14/nil/nil.ibd . nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ mysql -uroot -p --socket=/tmp/mysql_sandbox13454.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 17 Server version: 5.7.9-log MySQL Community Server (GPL) ... mysql> use nil Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------+ | Tables_in_nil | +---------------+ | nil | +---------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE nil IMPORT TABLESPACE; ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x5 and the meta-data file has 0x1) Suggested fix: Create table with row_format=compact on MySQL 5.7 and then import tablespace. create table nil(id int, name varchar(10)) row_format=compact;