| 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 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]
sean sean
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.)
Description: It is not possible to import a tablespace if the table contains a prefix index. How to repeat: let $MYSQLD_TMPDIR = `SELECT @@tmpdir`; let $MYSQLD_DATADIR = `SELECT @@datadir`; SET GLOBAL innodb_file_per_table = 1; CREATE TABLE t1 (c1 VARCHAR(128), PRIMARY KEY (c1(16))) ENGINE=InnoDB; INSERT INTO t1 VALUES ('foo'); FLUSH TABLES t1 FOR EXPORT; --copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_TMPDIR/t1.cfg --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_TMPDIR/t1.ibd UNLOCK TABLES; ALTER TABLE t1 DISCARD TABLESPACE; --copy_file $MYSQLD_TMPDIR/t1.cfg $MYSQLD_DATADIR/test/t1.cfg --copy_file $MYSQLD_TMPDIR/t1.ibd $MYSQLD_DATADIR/test/t1.ibd ALTER TABLE t1 IMPORT TABLESPACE;