MDEV-16678: Fix a problem with duplicate #sql2 table namesbb-10.5-MDEV-16678-rebase

row_drop_table_for_mysql(): If a #sql2 table is open in another
thread (purge) while we attempting to drop it, rename it to #sql-ib
name to hide it from the SQL layer.

Adjust tests accordingly to hide #sql-ib tables, which can
continue to exist until the background DROP TABLE completes.

1 files changed, 84 insertions, 29 deletions

diff --git a/mysql-test/suite/innodb_gis/t/point_basic.test b/mysql-test/suite/innodb_gis/t/point_basic.test
index 4ac90b93d4c..3ab1ab5c902 100644
--- a/mysql-test/suite/innodb_gis/t/point_basic.test
+++ b/mysql-test/suite/innodb_gis/t/point_basic.test
@@ -58,10 +58,18 @@ INSERT INTO t1 VALUES(ST_PointFromText('POINT(0 0)'), ST_PointFromText('POINT(10
 
 SELECT ST_AsText(p), ST_AsText(g) FROM t1;
 
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/t1' AND c.name IN ('p','g');
 
 ALTER TABLE t1 ADD COLUMN p1 POINT, ADD COLUMN p2 POINT, ADD KEY(p);
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1' OR name = 'p2';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
 
 --echo # NOT NULL POINT will use ''
 SELECT count(*) AS `Expect 4` FROM t1 WHERE p1 = '';
@@ -74,14 +82,22 @@ ALTER TABLE t1 DROP COLUMN p2;
 
 --echo # NULLABLE POINT will use NULL
 ALTER TABLE t1 ADD COLUMN p2 POINT, ADD KEY(p2);
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1' OR name = 'p2';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
 
 SELECT count(*) AS `Expect 4` FROM t1 WHERE p2 IS NULL;
 UPDATE t1 SET p2 = ST_PointFromText('POINT(10 20)');
 UPDATE t1 SET p1 = ST_PointFromText('POINT(10 20)');
 
 ALTER TABLE t1 DROP COLUMN p2;
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/t1' AND c.name IN ('p','g','p1','p2');
 
 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p = p1;
 
@@ -93,7 +109,11 @@ SELECT ST_AsText(p1) FROM t1;
 --echo # Add spatial keys on the table
 --error ER_SPATIAL_CANT_HAVE_NULL
 ALTER TABLE t1 ADD SPATIAL(p), ADD SPATIAL(p1);
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
 
 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
@@ -104,7 +124,11 @@ SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('P
 --echo # Drop spatial keys on the table
 --error ER_CANT_DROP_FIELD_OR_KEY
 ALTER TABLE t1 DROP KEY p, DROP KEY p1;
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
 
 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
@@ -115,10 +139,18 @@ SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('P
 TRUNCATE t1;
 
 ALTER TABLE t1 DROP COLUMN p, DROP COLUMN p1;
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
 
 ALTER TABLE t1 ADD COLUMN p POINT, ADD COLUMN p1 POINT;
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'p1';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/t1' AND c.name IN ('p','g','p1');
 
 SHOW CREATE TABLE t1;
 
@@ -134,7 +166,11 @@ SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p1, ST_GeomFromText('
 SELECT ST_AsText(p), ST_AsText(p1) FROM t1 WHERE MBRWithin(p, ST_GeomFromText('POLYGON((-1 0.5, -1 -0.5, 1 -0.5, 1 0.5, -1 0.5))'));
 
 ALTER TABLE t1 DROP COLUMN p1, ADD COLUMN p1 POINT, CHANGE COLUMN p pp POINT AFTER p1;
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'pp' OR name = 'g' OR name = 'p1';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/t1' AND c.name IN ('pp','g','p1');
 
 UPDATE t1 SET p1 = ST_PointFromText('POINT(5 5)');
 
@@ -142,7 +178,11 @@ SELECT ST_AsText(pp), ST_AsText(p1) FROM t1;
 
 --error ER_SPATIAL_CANT_HAVE_NULL
 ALTER TABLE t1 ADD SPATIAL(p1), ADD SPATIAL(pp), ALGORITHM = COPY;
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'pp' OR name = 'g' OR name = 'p1';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/t1' AND c.name IN ('pp','g','p1');
 
 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 30)');
 SELECT ST_AsText(pp), ST_AsText(p1) FROM t1 WHERE p1 = ST_PointFromText('POINT(10 20)');
@@ -206,7 +246,11 @@ SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POIN
 --echo # Check the information schema tables
 SELECT table_name, column_name, data_type, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='gis_point' AND (column_name = 'p' OR column_name = 'g');
 
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name LIKE 'test/g%' AND c.name IN ('p','g');
 
 SELECT length(p) FROM gis_point;
 
@@ -235,11 +279,14 @@ SELECT ST_AsText(g) FROM gis_point WHERE g IS NULL OR g = ST_PointFromText('POIN
 
 CHECK TABLE gis_point;
 
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/gis_point' AND c.name IN ('p','g');
 
 DROP TABLE gis_point;
 
-
 --echo #
 --echo # Check the mtype of other geometry data types should be 15
 --echo #
@@ -252,7 +299,11 @@ CREATE TABLE g (
   mpoly MULTIPOLYGON NOT NULL,
   gc GEOMETRYCOLLECTION NOT NULL
 ) ENGINE=InnoDB;
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' OR name = 'geom' OR name = 'l' OR name = 'poly' OR name = 'mp' OR name = 'ml' OR name = 'mpoly' OR name = 'gc';
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/g';
 
 DROP TABLE g;
 
@@ -268,20 +319,17 @@ CREATE TABLE t1 (
 
 INSERT INTO t1 VALUES(ST_PointFromText('POINT(10 10)'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
 
-# Check the mtype and len of the table
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' AND name='t1';
-
-CREATE TABLE t2 AS SELECT * FROM t1;
-
-# Cleanup
-DROP TABLE t1;
+CREATE TABLE t2 ENGINE=InnoDB AS SELECT * FROM t1;
 
-# Check the mtype and len of the table, should be 14,25
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' AND name='t2';
+# Check the mtype and len of the table
+SELECT t.name, c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name LIKE 'test/t%' AND c.name IN ('p','g');
 
 # Cleanup
-DROP table t2; 
-
+DROP TABLE t1,t2;
 
 --echo #
 --echo # Test when POINT is used in spatial index
@@ -316,7 +364,12 @@ INSERT INTO gis_point VALUES
 
 CREATE TABLE gis_point1 SELECT * FROM gis_point;
 
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p1' OR name = 'p2';
+# Check the mtype and len of the table, should be 14,25
+SELECT c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/gis_point' AND c.name IN ('p1','p2');
 
 SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point1;
 SELECT ST_AsText(p1) FROM gis_point1 WHERE ST_Intersection(@ls1, @ls2) = p1;
@@ -640,9 +693,11 @@ INSERT INTO gis_point VALUES(2, NULL);
 
 ALTER TABLE gis_point ADD COLUMN j INT, ALGORITHM = COPY;
 
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p';
-
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p';
+SELECT t.name, c.name, c.mtype, c.prtype, c.len
+FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS c
+JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+ON c.table_id = t.table_id
+WHERE t.name='test/gis_point' AND c.name='p';
 
 SELECT i, ST_AsText(p) FROM gis_point;