Bug #96477 FLUSH TABLE t FOR EXPORT or ALTER TABLE t2 IMPORT TABLESPACE broken in 8.0.17.
Submitted: 8 Aug 2019 20:37 Modified: 23 Dec 2019 20:11
Reporter: Jean-François Gagné Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:8.0.17 OS:Any
Assigned to: CPU Architecture:Any

[8 Aug 2019 20:37] Jean-François Gagné
Description:
Hi,

trying to reproduce Bug#96475, I discovered that FLUSH TABLE [...] FOR EXPORT or ALTER TABLE [...] IMPORT TABLESPACE is broken.  Details in How to repeat.

Many thanks for looking into that,

JFG

How to repeat:
# Create a sandbox with dbdeployer.
$ dbdeployer deploy single mysql_8.0.17

# Create a schema and a table.
# I am using many CHAR fields fo generate a big dataset quickly.
$ ./use <<< "CREATE DATABASE test_jfg"
$ ./use test_jfg <<< "
  CREATE TABLE test_jfg.t(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
  ALTER TABLE test_jfg.t ADD COLUMN v VARCHAR(255);"
$ for i in $(seq 9); do
  ./use test_jfg <<< "ALTER TABLE test_jfg.t ADD COLUMN c$i CHAR(255) DEFAULT ''"
done

# Generate ~2 GB of data in that table.
$ seq -f "INSERT INTO t(v) VALUES ('%f');" 1 1000000 | sed -e '1~1000s/^/COMMIT;BEGIN;/' | ./use test_jfg

# Contrarily to Bug#96475, I will run all next steps manually to show things are broken.

-- In the mysql client, run FLUSH TABLE [...] FOR EXPORT.
mysql [localhost:8017] {msandbox} (test_jfg) > FLUSH TABLE t FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)

# In bash, copy the files.
$ for s in cfg ibd; do cp data/test_jfg/t{,_}.$s; done

-- In the . mysql client, UNLOCK TABLE and prepare import.
mysql [localhost:8017] {msandbox} (test_jfg) > UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8017] {msandbox} (test_jfg) > CREATE TABLE t2 LIKE t; ALTER TABLE t2 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.01 sec)

# In bash, copy the files.
$ for s in cfg ibd; do cp data/test_jfg/t{_,2}.$s; done

-- In the mysql client, import the table.
mysql [localhost:8017] {msandbox} (test_jfg) > ALTER TABLE t2 IMPORT TABLESPACE;
ERROR 1817 (HY000): Index corrupt: Clustered index validation failed, due to data file corruption.

-- In above, we can see that things fail, but I would expect them to succeed.
[16 Aug 2019 9:29] Umesh Shastry
Hello Jean-François,

Thank you for the report and test case.
Verified as described.

regards,
Umesh
[16 Aug 2019 9:30] Umesh Shastry
Test results - 8.0.17

Attachment: 96477_8.0.17.results (application/octet-stream, text), 4.70 KiB.

[16 Aug 2019 9:31] Umesh Shastry
Test results - 5.7.27 - No issues observed like 8.0.17 with exact steps

Attachment: 96477_5.7.27.results (application/octet-stream, text), 3.59 KiB.

[23 Dec 2019 20:11] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.20 release, and here's the changelog entry:

An index corruption error was reported when attempting to import a table
containing a column that was added to the table using the INSTANT
algorithm. The error was due to missing metadata associated with the
instantly added column.
[24 Dec 2019 13:33] Daniel Price
Posted by developer:
 
Changelog entry revised as follows:

"An index corruption error was reported when attempting to import a
tablespace containing a table column that was added using
ALGORITHM=INSTANT. The error was due to missing metadata associated with
the instantly added column."