Bug #77659 schema mismatch error when importing tablespace after dropping an index
Submitted: 8 Jul 2015 17:26 Modified: 22 Sep 2015 13:54
Reporter: sean liu Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.14-5.623, 5.6.26, 5.6.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: bug fix

[8 Jul 2015 17:26] sean liu
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:
    Drop an index from a table on the source server
    Move the table to the destination server using transportable tablespaces
    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`)
4. mysql> ALTER TABLE altertest DROP INDEX age;
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
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"
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.

Suggested fix:
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.
[28 Jul 2015 9:37] Umesh Shastry
Hello Sean liu,

Thank you for the report and test case.

[4 Aug 2015 22:42] Vincent Meng
Here's a hack to resolve the issue.
[8 Aug 2015 17:12] sean liu
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
[22 Sep 2015 13:54] Daniel Price
Posted by developer:
Fixed as of the upcoming 5.6.28, 5.7.10, 5.8.0 release, and here's the changelog entry:

A schema mismatch error occurred when importing a tablespace that was
altered by DROP INDEX operation on the source server. 

Thank you for the bug report.