summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/suite/gcol/r/main_alter_table.result2
-rw-r--r--mysql-test/suite/gcol/t/main_alter_table.test2
-rw-r--r--mysql-test/suite/innodb/include/show_i_s_tables.inc1
-rw-r--r--mysql-test/suite/innodb/include/show_i_s_tablespaces.inc1
-rw-r--r--mysql-test/suite/innodb_gis/r/point_basic.result117
-rw-r--r--mysql-test/suite/innodb_gis/t/point_basic.test113
-rw-r--r--storage/innobase/row/row0mysql.cc8
7 files changed, 184 insertions, 60 deletions
diff --git a/mysql-test/suite/gcol/r/main_alter_table.result b/mysql-test/suite/gcol/r/main_alter_table.result
index 864169ee16c..77c0a382922 100644
--- a/mysql-test/suite/gcol/r/main_alter_table.result
+++ b/mysql-test/suite/gcol/r/main_alter_table.result
@@ -38,7 +38,7 @@ INFORMATION_SCHEMA.INNODB_SYS_TABLES AS T JOIN
INFORMATION_SCHEMA.INNODB_SYS_INDEXES AS I JOIN
INFORMATION_SCHEMA.INNODB_SYS_FIELDS AS F
ON I.INDEX_ID = F.INDEX_ID AND I.TABLE_ID = T.TABLE_ID
-WHERE T.NAME LIKE 'test/%';
+WHERE T.NAME LIKE 'test/t%';
TABLE_NAME INDEX_NAME IS_PRIMARY_KEY FIELD_NAME FIELD_POS
test/t0 a yes a 0
test/t1 a no a 0
diff --git a/mysql-test/suite/gcol/t/main_alter_table.test b/mysql-test/suite/gcol/t/main_alter_table.test
index bbd87350cb1..2ce768a9f4c 100644
--- a/mysql-test/suite/gcol/t/main_alter_table.test
+++ b/mysql-test/suite/gcol/t/main_alter_table.test
@@ -41,7 +41,7 @@ SELECT T.NAME AS TABLE_NAME, I.NAME AS INDEX_NAME,
INFORMATION_SCHEMA.INNODB_SYS_INDEXES AS I JOIN
INFORMATION_SCHEMA.INNODB_SYS_FIELDS AS F
ON I.INDEX_ID = F.INDEX_ID AND I.TABLE_ID = T.TABLE_ID
- WHERE T.NAME LIKE 'test/%';
+ WHERE T.NAME LIKE 'test/t%';
DROP TABLE t0;
DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/include/show_i_s_tables.inc b/mysql-test/suite/innodb/include/show_i_s_tables.inc
index 5fe34c370c8..8e4a362de95 100644
--- a/mysql-test/suite/innodb/include/show_i_s_tables.inc
+++ b/mysql-test/suite/innodb/include/show_i_s_tables.inc
@@ -15,5 +15,6 @@ SELECT t.name 'Table Name',
WHERE t.name not like 'SYS_%'
AND t.name NOT LIKE 'mysql/%'
AND t.name NOT LIKE 'sys/%'
+ AND t.name NOT LIKE '%/#sql-ib%'
ORDER BY t.name;
--enable_query_log
diff --git a/mysql-test/suite/innodb/include/show_i_s_tablespaces.inc b/mysql-test/suite/innodb/include/show_i_s_tablespaces.inc
index 32c5e88d9b6..6d0c5c63712 100644
--- a/mysql-test/suite/innodb/include/show_i_s_tablespaces.inc
+++ b/mysql-test/suite/innodb/include/show_i_s_tablespaces.inc
@@ -13,5 +13,6 @@ SELECT s.name 'Space_Name',
information_schema.innodb_sys_datafiles d
WHERE s.space = d.space
AND s.name NOT LIKE 'mysql/%'
+ AND s.name NOT LIKE '%/#sql-ib%'
ORDER BY s.space;
--enable_query_log
diff --git a/mysql-test/suite/innodb_gis/r/point_basic.result b/mysql-test/suite/innodb_gis/r/point_basic.result
index eb22fa92741..633caebbce7 100644
--- a/mysql-test/suite/innodb_gis/r/point_basic.result
+++ b/mysql-test/suite/innodb_gis/r/point_basic.result
@@ -61,12 +61,20 @@ POINT(0 1) POINT(10 11)
POINT(1 1) POINT(10 12)
POINT(1 0) POINT(10 13)
POINT(0 0) POINT(10 14)
-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');
name mtype prtype len
p 14 1535 12
g 14 1535 12
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');
name mtype prtype len
p 14 1535 12
g 14 1535 12
@@ -86,7 +94,11 @@ ST_AsText(p) ST_AsText(p1)
ALTER TABLE t1 DROP COLUMN p2;
# 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');
name mtype prtype len
p 14 1535 12
g 14 1535 12
@@ -98,7 +110,11 @@ Expect 4
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');
name mtype prtype len
p 14 1535 12
g 14 1535 12
@@ -123,7 +139,11 @@ POINT(10 20)
# Add spatial keys on the table
ALTER TABLE t1 ADD SPATIAL(p), ADD SPATIAL(p1);
ERROR 42000: All parts of a SPATIAL index must be NOT NULL
-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');
name mtype prtype len
p 14 1535 12
g 14 1535 12
@@ -149,7 +169,11 @@ POINT(0 0) POINT(10 20)
# Drop spatial keys on the table
ALTER TABLE t1 DROP KEY p, DROP KEY p1;
ERROR 42000: Can't DROP INDEX `p1`; check that it exists
-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');
name mtype prtype len
p 14 1535 12
g 14 1535 12
@@ -174,11 +198,19 @@ POINT(1 0) POINT(10 20)
POINT(0 0) POINT(10 20)
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');
name mtype prtype len
g 14 1535 12
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');
name mtype prtype len
g 14 1535 12
p 14 1279 12
@@ -209,7 +241,11 @@ ST_AsText(p) ST_AsText(p1)
POINT(1 0) POINT(10 21)
POINT(0 0) POINT(10 22)
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');
name mtype prtype len
g 14 1535 12
p1 14 1279 12
@@ -223,7 +259,11 @@ POINT(1 0) POINT(5 5)
POINT(0 0) POINT(5 5)
ALTER TABLE t1 ADD SPATIAL(p1), ADD SPATIAL(pp), ALGORITHM = COPY;
ERROR 42000: All parts of a SPATIAL index must be NOT NULL
-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');
name mtype prtype len
g 14 1535 12
p1 14 1279 12
@@ -332,7 +372,11 @@ SELECT table_name, column_name, data_type, column_type FROM INFORMATION_SCHEMA.C
table_name column_name data_type column_type
gis_point p point point
gis_point g point 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 LIKE 'test/g%' AND c.name IN ('p','g');
name mtype prtype len
p 14 1279 12
g 14 1279 12
@@ -381,7 +425,11 @@ POINT(200 200)
CHECK TABLE gis_point;
Table Op Msg_type Msg_text
test.gis_point check status OK
-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');
name mtype prtype len
p 14 1279 12
g 14 1279 12
@@ -398,7 +446,11 @@ ml MULTILINESTRING NOT NULL,
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';
name mtype prtype len
geom 14 1535 12
l 14 1535 12
@@ -416,14 +468,18 @@ p POINT NOT NULL,
g GEOMETRY NOT NULL
) ENGINE=InnoDB;
INSERT INTO t1 VALUES(ST_PointFromText('POINT(10 10)'),ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' AND name='t1';
-name mtype prtype len
-p 14 1535 12
-CREATE TABLE t2 AS SELECT * FROM t1;
-DROP TABLE t1;
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p' OR name = 'g' AND name='t2';
-name mtype prtype len
-DROP table t2;
+CREATE TABLE t2 ENGINE=InnoDB AS SELECT * FROM t1;
+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');
+name name mtype prtype len
+test/t1 p 14 1535 12
+test/t1 g 14 1535 12
+test/t2 p 14 1535 12
+test/t2 g 14 1535 12
+DROP TABLE t1,t2;
#
# Test when POINT is used in spatial index
#
@@ -453,7 +509,11 @@ INSERT INTO gis_point VALUES
(ST_PointFromText('POINT(26.25 57)'), ST_PointFromText('POINT(1 2)')),
(ST_PointFromText('POINT(32.1234 64.2468)'), ST_PointFromText('POINT(-1 -1)'));
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';
+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');
name mtype prtype len
p1 14 1535 12
p2 14 1535 12
@@ -1349,12 +1409,13 @@ INSERT INTO gis_point VALUES(0, ST_PointFromText('POINT(1 1)'));
INSERT INTO gis_point VALUES(1, ST_PointFromText('POINT(2 2)'));
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';
-name mtype prtype len
-p 14 1279 12
-SELECT name, mtype, prtype, len FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE name = 'p';
-name mtype prtype len
-p 14 1279 12
+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';
+name name mtype prtype len
+test/gis_point p 14 1279 12
SELECT i, ST_AsText(p) FROM gis_point;
i ST_AsText(p)
0 POINT(1 1)
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;
diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc
index e4d178f1528..fecb6203f2b 100644
--- a/storage/innobase/row/row0mysql.cc
+++ b/storage/innobase/row/row0mysql.cc
@@ -3504,7 +3504,13 @@ row_drop_table_for_mysql(
if (table->n_foreign_key_checks_running > 0) {
defer:
- if (!is_temp_name) {
+ /* Rename #sql2 to #sql-ib if table has open ref count
+ while dropping the table. This scenario can happen
+ when purge thread is waiting for dict_sys.mutex so
+ that it could close the table. But drop table acquires
+ dict_sys.mutex. */
+ if (!is_temp_name
+ || strstr(table->name.m_name, "/#sql2")) {
heap = mem_heap_create(FN_REFLEN);
const char* tmp_name
= dict_mem_create_temporary_tablename(
View Lorry Controller Status