Bug #76877 | Cannot import tables containing prefix indexes | ||
---|---|---|---|
Submitted: | 29 Apr 2015 2:49 | Modified: | 8 May 2015 17:45 |
Reporter: | Davi Arnaut (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
Version: | 5.6,5.7, 5.6.25, 5.7.8 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | innodb, Prefix index, transportable tablespaces |
[29 Apr 2015 2:49]
Davi Arnaut
[29 Apr 2015 5:23]
MySQL Verification Team
Hello Davi, Thank you for the report and test case. Confirmed this 5.6.25 and 5.7.8 builds. Thanks, Umesh
[29 Apr 2015 5:25]
MySQL Verification Team
// 5.7.8/5.6.25 CURRENT_TEST: main.bug76877 mysqltest: At line 18: query 'ALTER TABLE t1 IMPORT TABLESPACE' failed: 1808: Schema mismatch (Index PRIMARY field c1 prefix len 0 doesn't match meta-data file value 16)
[1 May 2015 18:10]
Davi Arnaut
Another variation of this bug is the possibility of importing tables where the index order has been changed: mysql> create table t1 (c1 int, c2 int, c3 int, primary key(c1,c2)) engine=innodb; Query OK, 0 rows affected (0.05 sec) mysql> insert into t1 values (1,2,3); Query OK, 1 row affected (0.01 sec) mysql> flush tables t1 for export; Query OK, 0 rows affected (0.00 sec) mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> drop table t1; Query OK, 0 rows affected (0.03 sec) mysql> create table t1 (c1 int, c2 int, c3 int, primary key(c2,c1)) engine=innodb; Query OK, 0 rows affected (0.04 sec) mysql> alter table t1 discard tablespace; Query OK, 0 rows affected (0.02 sec) mysql> alter table t1 import tablespace; Query OK, 0 rows affected (0.11 sec) mysql> select * from t1; +----+----+------+ | c1 | c2 | c3 | +----+----+------+ | 2 | 1 | 3 | +----+----+------+ 1 row in set (0.00 sec) mysql>
[8 May 2015 17:44]
Daniel Price
Posted by developer: commit ff00b0f8efe778ef47c160cd7fae7d89885c2b13 Author: Debarun Banerjee <debarun.banerjee@oracle.com> Date: Wed May 6 12:37:56 2015 +0530 BUG#20977779 CANNOT IMPORT TABLES CONTAINING PREFIX INDEXES Problem : --------- While importing table, we are getting the index columns from cfg by name and hence if a column is repeated we get the first reference always. If the first and second reference has different prefix length, the validation fails. Solution : ---------- Check all index column in dictionary and cfg one to one. The fix expects cfg and dictionary to match exactly.
[8 May 2015 17:45]
Daniel Price
Posted by developer: Fixed as of the upcoming 5.6.26, 5.7.8, 5.8.0 releases, and here's the changelog entry: An "ALTER TABLE ... IMPORT TABLESPACE" operation on a table with prefix index failed with a schema mismatch error. Thank you for the bug report.
[13 Jul 2018 6:35]
wen ren
8.0.11 MySQL Community Server - GPL have the same issue. create table lookup ( email varchar(256) not null, primary key (email(255)), ) engine=innodb; mysql> alter table email_user_lookup import tablespace; ERROR 1808 (HY000): Schema mismatch (Column email max prefix mismatch.)