MySQL Bugs: #96477: FLUSH TABLE t FOR EXPORT or ALTER TABLE t2 IMPORT TABLESPACE broken in 8.0.17.

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

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.

Hello Jean-François,

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

regards,
Umesh

Test results - 8.0.17

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

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.

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.

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."

We are observing this error on 8.0.22 when using the MEB Backup too. Can you please check.

I was able to reproduce the problem in 8.0.19 with mysqlbackup and with the
described SQL statements, but not in 8.0.20 nor 8.0.22. I did not try with
other versions.

Can we please have a description, how to repeat the problem in 8.0.22? There
should be some extra steps or differences in the table layout, compared to
the old description.