schema mismatch error when importing tablespace after dropping an index
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.1 |
Invalid
|
Undecided
|
Unassigned | |||
5.5 |
Invalid
|
Undecided
|
Unassigned | |||
5.6 |
Fix Released
|
High
|
Unassigned | |||
5.7 |
Fix Released
|
High
|
Unassigned |
Bug Description
Description:
A schema mismatch error occurs when importing a tablespace from one server to another. The situation occurs when importing a table has been altered by DROP INDEX on the source server.
The high level flow is:
1. Drop an index from a table on the source server
2. Move the table to the destination server using transportable tablespaces
3. Receive a “schema mismatch” error when importing tablespace
How to repeat:
We can simulate the transportable tablespaces method between two servers by transferring a table between two databases (on the same server). We tested this issue by mocking the transportable tablespaces method (http://
Here are the steps to reproduce:
1. mysql> CREATE DATABASE IF NOT EXISTS source_db;
2. mysql> USE source_db;
3. mysql> CREATE TABLE `altertest` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(20) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
4. mysql> ALTER TABLE altertest DROP INDEX age;
5. mysql> CREATE DATABASE IF NOT EXISTS dest_db;
6. mysql> CREATE DATABASE IF NOT EXISTS temp_db;
7. mysql> FLUSH TABLE source_db.altertest FOR EXPORT;
8. Copy the .ibd file and the .cfg files from their directory on the mocked source server to their intended directory on the mocked destination server:
$ cd /path/to/
$ cp .ibd file .cfg file /path/to/
9. mysql> UNLOCK TABLES;
10. mysql> USE temp_db;
11. mysql> SHOW CREATE TABLE source_
copy output
paste output and execute
12. mysql> ALTER TABLE altertest DISCARD TABLESPACE;
13. mysql> ALTER TABLE temp_db.altertest RENAME TO dest_db.altertest;
14. mysql> USE dest_db;
15. mysql> ALTER TABLE altertest IMPORT TABLEPACE;
We will get then receive the error:
ERROR 1808 (HY000):"schema mismatch, column (age) ordering mismatch"
Analysis:
When the dropped index was originally added to the table (ADD INDEX), the MySQL function dict_index_
When we drop this index on the source database (the column is not included in any compound index), only the frm file is modified and thus the ord_part variable will still remain 1.
However, when we create the table on the destination server, ord_part will equal 0 because the create table statement does not include an index on that column. This will result in a schema mismatch error.
Temporary fix:
The only way for us to resolve this issue is to run OPTIMIZE TABLE on the table located on the source server before transferring it to the destination server. This will recreate the table on the source server and thus the ord_part for that column will equal 0. This will prevent a schema mismatch.
However, this is not a viable solution because running OPTIMIZE TABLE after a DROP INDEX alter statement is inefficient and wastes resources. A complete rebuild of a very large MySQL table is resource intensive and negatively impacts our system.
Suggested fix:
A MySQL solution will prevent a schema mismatch error from occurring in this case, either by modifying ord_part when the DROP INDEX command is completed or by changing the ord_part check in IMPORT TABLESPACE.
tags: | added: innodb |
description: | updated |
description: | updated |
description: | updated |
description: | updated |
description: | updated |
description: | updated |
description: | updated |
summary: |
- schema mismatch: import tablespace after alter table drop index + schema mismatch error when importing tablespace after dropping an index |
description: | updated |
description: | updated |
tags: | added: upstream |
Able to reproduce the same with above test case and PS 5.6.24-72.2.
Just want to update one thing in step 8 if you copy files with sudo/root,
8. Copy the .ibd file and the .cfg files from their directory on the mocked source server to their intended directory on the mocked destination server: source_ directory destination_ directory destination_ directory/ altertest. *
$ cd /path/to/
$ cp .ibd file .cfg file /path/to/
$ chown mysql:mysql /path/to/
because when we will copy those files with sudo or root, it will be like,
root@desktop: /var/lib/ mysql/dest_ db# ll /var/lib/ mysql/dest_ db#
total 140
drwx------ 2 mysql mysql 4096 Jul 10 10:40 ./
drwx------ 9 mysql mysql 4096 Jul 10 10:37 ../
-rw-r----- 1 root root 502 Jul 10 10:38 altertest.cfg
-rw-rw---- 1 mysql mysql 8646 Jul 10 10:39 altertest.frm
-rw-r----- 1 root root 114688 Jul 10 10:38 altertest.ibd
-rw-rw---- 1 mysql mysql 65 Jul 10 10:37 db.opt
root@desktop:
so you'll get error like,
mysql> ALTER TABLE altertest IMPORT TABLESPACE;
ERROR 1146 (42S02): Table 'dest_db.altertest' doesn't exist
mysql>
After setting mysql permissions,
mysql> ALTER TABLE altertest IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Column age ordering mismatch.)
mysql>