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:
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`)
) 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.
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.