schema mismatch error when importing tablespace after dropping an index

Bug #1469901 reported by Sean Liu
68
This bug affects 11 people
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://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html) from MySQL version 5.6.14 to MySQL version 5.6.23.

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/source_directory
    $ cp .ibd file .cfg file /path/to/destination_directory

9. mysql> UNLOCK TABLES;

10. mysql> USE temp_db;

11. mysql> SHOW CREATE TABLE source_db.altertest;
    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_add_to_cache() is executed and it sets the variable field->col->ord_part = 1. From the source code, we see that the ord_part for a column is nonzero if the column appears in the ordering fields of an 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.

Sean Liu (shenroong)
tags: added: innodb
description: updated
Sean Liu (shenroong)
description: updated
description: updated
description: updated
Sean Liu (shenroong)
description: updated
description: updated
suanmeiguo (suanmeiguo)
description: updated
Sean Liu (shenroong)
summary: - schema mismatch: import tablespace after alter table drop index
+ schema mismatch error when importing tablespace after dropping an index
description: updated
Layne Hubbard (lhubbard)
description: updated
Revision history for this message
Nilnandan Joshi (nilnandan-joshi) wrote :

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:
    $ cd /path/to/source_directory
    $ cp .ibd file .cfg file /path/to/destination_directory
    $ chown mysql:mysql /path/to/destination_directory/altertest.*

because when we will copy those files with sudo or root, it will be like,

root@desktop:/var/lib/mysql/dest_db# ll
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:/var/lib/mysql/dest_db#

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>

Changed in percona-server:
status: New → Confirmed
Revision history for this message
Nilnandan Joshi (nilnandan-joshi) wrote :

Confirmed with upstream MySQL 5.6.24 and it's already reported.

tags: added: upstream
Revision history for this message
suanmeiguo (suanmeiguo) wrote :
Revision history for this message
Sean Liu (shenroong) wrote :

I modified commit_try_norebuild function in handler0alter.cc .

The basic idea is that when we drop an index (indexes), we will insert the associated column order to a vector. Then we loop through all indexes, if no other indexes use those columns, the ord_part for those columns will become 0. I tested on both MySQL and Percona server (5.6.24). Feel free to test it, if you have any question or concern, let me know.

Here is the script
https://github.com/shenroong/MySQL-Bug-77659/blob/master/handler0alter.cc

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

Upstream bug documented as fixed in 5.6.28, 5.7.10, 5.8.0, closing accordingly.

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-911

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.