The ALTER TABLE…IMPORT TABLESPACE operation that was originally introduced in MySQL 4.1 in a mostly useless form was improved in MySQL 5.6 so that it could actually import data files from other InnoDB installations. The import performs some adjustments to the data files. The DB_TRX_ID will be reset to refer to the transaction that is performing the import operation, and the DB_ROLL_PTR will be reset to 0.

Until the IMPORT TABLESPACE transaction has been consumed by the purge_sys->view, other transactions whose read view was created before the IMPORT TABLESPACE would attempt to dereference the DB_ROLL_PTR, because the DB_TRX_ID should not exist in their read view, and they should attempt to fetch an older version.

If the most significant bit of the DB_ROLL_PTR value were set, then the old transactions would consider the records as a ‘fresh insert’ of a later transaction, and correctly determine that the rows do not exist. (Note: with the fix of MDEV-14407 in MariaDB 10.3, the old transactions should see the full contents of the table, because in the spirit of MDEV-12288 IMPORT would reset DB_TRX_ID=0 to avoid unnecessary lookups by MVCC.)

--source include/have_innodb.inc

SET @save_per_table= @@GLOBAL.innodb_file_per_table;

SET GLOBAL innodb_file_per_table= 1;

let MYSQLD_DATADIR =`SELECT @@datadir`;

CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;

INSERT INTO t1 VALUES(0);

FLUSH TABLES t1 WITH READ LOCK;

perl;

do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";

ib_backup_tablespace("test", "t1");

EOF

UNLOCK TABLES;

ALTER TABLE t1 DISCARD TABLESPACE;

START TRANSACTION WITH CONSISTENT SNAPSHOT;

connect (con1,localhost,root,,);

perl;

do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";

ib_restore_tablespace("test", "t1");

EOF

ALTER TABLE t1 IMPORT TABLESPACE;

disconnect con1;

connection default;

SELECT * FROM t1;

COMMIT;

SELECT * FROM t1;

DROP TABLE t1;

SET GLOBAL innodb_file_per_table= @save_per_table;

The first SELECT would crash with an assertion failure due to trying to dereference the invalid DB_ROLL_PTR=0.