Description:
Hi,
if I interrupt (CTRL-C) an ALTER TABLE [...] IMPORT TABLESPACE, the idb file is corrupted and a following ALTER TABLE [...] IMPORT TABLESPACE fails. I would expect to be able to recover from this. See How to repeat for details.
About my expectation to recover, keep in mind that this ibd file can be massive (in the order of TB). Forcing me to redo a FLUSH TABLE [...] FOR EXPORT or to re-copy the file over the network is a serious hurdle (I set the severity as S3, but there could be arguments for setting this to S2 because data corruption).
Many thanks for looking into that,
JFG
How to repeat:
# Create a sandbox with dbdeployer.
$ dbdeployer deploy single mysql_5.7.27
# 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
# Prepare the import tablespace.
# The sleep 1 is to make sure the FLUSH TABLE completes before starting the cp.
$ ./use test_jfg <<< "FLUSH TABLE t FOR EXPORT; DO SLEEP(100)" & \
sleep 2; \
for s in cfg ibd; do cp data/test_jfg/t{,_}.$s; done; \
kill %1
# Import the tablespace, but interrupting the operation.
$ ./use test_jfg <<< "CREATE TABLE t2 LIKE t; ALTER TABLE t2 DISCARD TABLESPACE"; \
for s in cfg ibd; do cp data/test_jfg/t{_,2}.$s; done; \
./use test_jfg -N <<< "ALTER TABLE t2 IMPORT TABLESPACE"& \
sleep 5; \
kill %1; \
time ./use test_jfg -N <<< "ALTER TABLE t2 IMPORT TABLESPACE"
[1] 2789
ERROR 1813 (HY000) at line 1: Tablespace 'test_jfg/t2' exists.
[1]+ Terminated ./use test_jfg -N <<< "ALTER TABLE t2 IMPORT TABLESPACE"
real 0m15.012s
user 0m0.004s
sys 0m0.000s
# So in above, I was not able to resume the "ALTER TABLE t2 IMPORT TABLESPACE". And note the time taken for things to fail, this is a little weird...
# But maybe I did things wrong, let's try to do things in another way.
# Cleanup, not that this actually deletes the ibd files.
$ ./use <<< "DROP TABLE test_jfg.t2"
# Try resuming the import in another way.
$ ./use test_jfg <<< "CREATE TABLE t2 LIKE t; ALTER TABLE t2 DISCARD TABLESPACE"; \
for s in cfg ibd; do cp data/test_jfg/t{_,2}.$s; done; \
./use test_jfg -N <<< "ALTER TABLE t2 IMPORT TABLESPACE"& \
sleep 5; \
kill %1; \
for s in cfg ibd; do mv data/test_jfg/t{2,3}.$s; done; \
./use test_jfg <<< "DROP TABLE t2; CREATE TABLE t2 LIKE t; ALTER TABLE t2 DISCARD TABLESPACE"; \
for s in cfg ibd; do mv data/test_jfg/t{3,2}.$s; done; \
time ./use test_jfg -N <<< "ALTER TABLE t2 IMPORT TABLESPACE"
[1] 2838
[1]+ Terminated ./use test_jfg -N <<< "ALTER TABLE t2 IMPORT TABLESPACE"
ERROR 1815 (HY000) at line 1: Internal error: Cannot reset LSNs in table `test_jfg`.`t2` : Data structure corruption
# So even if I re-create the table, the ibd file is unusable after an interrupted ALTER TABLE [...] IMPORT TABLESPACE. I would expect a way to resume this operation.
# Note that the behaviour is a little different when CRTL-C in the MySQL client.
$ ./use test_jfg <<< "CREATE TABLE t2 LIKE t; ALTER TABLE t2 DISCARD TABLESPACE"; \
for s in cfg ibd; do cp data/test_jfg/t{_,2}.$s; done;
$ ./use test_jfg
mysql [localhost:5727] {msandbox} (test_jfg) > ALTER TABLE t2 IMPORT TABLESPACE;
^C^C -- query aborted
ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table `test_jfg`.`t2` : Operation interrupted
mysql [localhost:5727] {msandbox} (test_jfg) > ALTER TABLE t2 IMPORT TABLESPACE;
ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table `test_jfg`.`t2` : Data structure corruption
mysql [localhost:5727] {msandbox} (test_jfg) > DROP TABLE t2;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5727] {msandbox} (test_jfg) > CREATE TABLE t2 LIKE t;
ERROR 1813 (HY000): Tablespace '`test_jfg`.`t2`' exists.
# When CTRL-C an ALTER TABLE [...] IMPORT TABLESPACE in the mysql client, we get an error, and a following DROP TABLE does not delete the t2.{cfg,ibd}.
Description: Hi, if I interrupt (CTRL-C) an ALTER TABLE [...] IMPORT TABLESPACE, the idb file is corrupted and a following ALTER TABLE [...] IMPORT TABLESPACE fails. I would expect to be able to recover from this. See How to repeat for details. About my expectation to recover, keep in mind that this ibd file can be massive (in the order of TB). Forcing me to redo a FLUSH TABLE [...] FOR EXPORT or to re-copy the file over the network is a serious hurdle (I set the severity as S3, but there could be arguments for setting this to S2 because data corruption). Many thanks for looking into that, JFG How to repeat: # Create a sandbox with dbdeployer. $ dbdeployer deploy single mysql_5.7.27 # 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 # Prepare the import tablespace. # The sleep 1 is to make sure the FLUSH TABLE completes before starting the cp. $ ./use test_jfg <<< "FLUSH TABLE t FOR EXPORT; DO SLEEP(100)" & \ sleep 2; \ for s in cfg ibd; do cp data/test_jfg/t{,_}.$s; done; \ kill %1 # Import the tablespace, but interrupting the operation. $ ./use test_jfg <<< "CREATE TABLE t2 LIKE t; ALTER TABLE t2 DISCARD TABLESPACE"; \ for s in cfg ibd; do cp data/test_jfg/t{_,2}.$s; done; \ ./use test_jfg -N <<< "ALTER TABLE t2 IMPORT TABLESPACE"& \ sleep 5; \ kill %1; \ time ./use test_jfg -N <<< "ALTER TABLE t2 IMPORT TABLESPACE" [1] 2789 ERROR 1813 (HY000) at line 1: Tablespace 'test_jfg/t2' exists. [1]+ Terminated ./use test_jfg -N <<< "ALTER TABLE t2 IMPORT TABLESPACE" real 0m15.012s user 0m0.004s sys 0m0.000s # So in above, I was not able to resume the "ALTER TABLE t2 IMPORT TABLESPACE". And note the time taken for things to fail, this is a little weird... # But maybe I did things wrong, let's try to do things in another way. # Cleanup, not that this actually deletes the ibd files. $ ./use <<< "DROP TABLE test_jfg.t2" # Try resuming the import in another way. $ ./use test_jfg <<< "CREATE TABLE t2 LIKE t; ALTER TABLE t2 DISCARD TABLESPACE"; \ for s in cfg ibd; do cp data/test_jfg/t{_,2}.$s; done; \ ./use test_jfg -N <<< "ALTER TABLE t2 IMPORT TABLESPACE"& \ sleep 5; \ kill %1; \ for s in cfg ibd; do mv data/test_jfg/t{2,3}.$s; done; \ ./use test_jfg <<< "DROP TABLE t2; CREATE TABLE t2 LIKE t; ALTER TABLE t2 DISCARD TABLESPACE"; \ for s in cfg ibd; do mv data/test_jfg/t{3,2}.$s; done; \ time ./use test_jfg -N <<< "ALTER TABLE t2 IMPORT TABLESPACE" [1] 2838 [1]+ Terminated ./use test_jfg -N <<< "ALTER TABLE t2 IMPORT TABLESPACE" ERROR 1815 (HY000) at line 1: Internal error: Cannot reset LSNs in table `test_jfg`.`t2` : Data structure corruption # So even if I re-create the table, the ibd file is unusable after an interrupted ALTER TABLE [...] IMPORT TABLESPACE. I would expect a way to resume this operation. # Note that the behaviour is a little different when CRTL-C in the MySQL client. $ ./use test_jfg <<< "CREATE TABLE t2 LIKE t; ALTER TABLE t2 DISCARD TABLESPACE"; \ for s in cfg ibd; do cp data/test_jfg/t{_,2}.$s; done; $ ./use test_jfg mysql [localhost:5727] {msandbox} (test_jfg) > ALTER TABLE t2 IMPORT TABLESPACE; ^C^C -- query aborted ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table `test_jfg`.`t2` : Operation interrupted mysql [localhost:5727] {msandbox} (test_jfg) > ALTER TABLE t2 IMPORT TABLESPACE; ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table `test_jfg`.`t2` : Data structure corruption mysql [localhost:5727] {msandbox} (test_jfg) > DROP TABLE t2; Query OK, 0 rows affected (0.00 sec) mysql [localhost:5727] {msandbox} (test_jfg) > CREATE TABLE t2 LIKE t; ERROR 1813 (HY000): Tablespace '`test_jfg`.`t2`' exists. # When CTRL-C an ALTER TABLE [...] IMPORT TABLESPACE in the mysql client, we get an error, and a following DROP TABLE does not delete the t2.{cfg,ibd}.