Bug #96475 ALTER TABLE t IMPORT TABLESPACE blocks SELECT on I_S.tables.
Submitted: 8 Aug 19:39 Modified: 9 Aug 7:06
Reporter: Jean-François Gagné Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.27 OS:Any
Assigned to: CPU Architecture:Any

[8 Aug 19:39] Jean-François Gagné
Description:
Hi,

if doing a SELECT on on I_S.tables while an ALTER TABLE [...] IMPORT TABLESPACE is running, the SELECT blocks until the ALTER finishes.  I would expect the SELECT to not block.  See How to repeat for details.

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

# Now time to show how things block.

./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 <<< "SELECT 'begin alter', now(); ALTER TABLE t2 IMPORT TABLESPACE; SELECT 'end alter', now()"&
./use test_jfg -N <<< "DO sleep(2); SELECT 'begin select', now(); SELECT * FROM INFORMATION_SCHEMA.tables; SELECT 'end select', now()" | grep select
begin alter     2019-08-08 19:36:18
end alter       2019-08-08 19:36:38
begin select    2019-08-08 19:36:20
end select      2019-08-08 19:36:38
[1]+  Done                    ./use test_jfg -N <<< "SELECT 'begin alter', now(); ALTER TABLE t2 IMPORT TABLESPACE; SELECT 'end alter', now()"

# As you can see, the "end select" is only printed after "end alter".  I would expect it to show immediately.
[8 Aug 20:38] Jean-François Gagné
Unable to check if MySQL 8.0.17 is affected as FLUSH TABLE t FOR EXPORT or ALTER TABLE t2 IMPORT TABLESPACE is broken in 8.0.17: Bug#96477.
[9 Aug 7:06] Umesh Shastry
Hello Jean-François,

Thank you for the report and test case.

regards,
Umesh
[9 Aug 7:07] Umesh Shastry
Test results - 5.7.27

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