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:
None 
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
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;
[29 Apr 2015 5:23] Umesh Shastry
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] Umesh Shastry
// 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 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.)