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