From adb117cf6901a0e0da7577267be1209359892207 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Tue, 10 Dec 2019 16:18:30 +0200 Subject: MDEV-16678: Fix a problem with duplicate #sql2 table names 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. --- mysql-test/suite/innodb_gis/t/point_basic.test | 113 ++++++++++++++++++------- 1 file changed, 84 insertions(+), 29 deletions(-) (limited to 'mysql-test/suite/innodb_gis/t/point_basic.test') 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; -- cgit v1.2.1