MDEV-13847 Allow ALTER TABLE…ADD SPATIAL INDEX…ALGORITHM=INPLACE

MDEV-13851 Always check table options in ALTER TABLE…ALGORITHM=INPLACE

In the merge of MySQL 5.7.9 to MariaDB 10.2.2, some code was included
that prevents ADD SPATIAL INDEX from being executed with ALGORITHM=INPLACE.

Also, the constant ADD_SPATIAL_INDEX was introduced as an alias
to ADD_INDEX. We will remove that alias now, and properly implement
the same ADD SPATIAL INDEX restrictions as MySQL 5.7 does:

1. table-rebuilding operations are not allowed if SPATIAL INDEX survive it
2. ALTER TABLE…ADD SPATIAL INDEX…LOCK=NONE is not allowed

ha_innobase::prepare_inplace_alter_table(): If the ALTER TABLE
requires actions within InnoDB, enforce the table options (MDEV-13851).
In this way, we will keep denying ADD SPATIAL INDEX for tables
that use encryption (MDEV-11974), even if ALGORITHM=INPLACE is used.

6 files changed, 138 insertions, 72 deletions

diff --git a/mysql-test/suite/encryption/r/innodb-spatial-index.result b/mysql-test/suite/encryption/r/innodb-spatial-index.result
index 852be0b9a73..c2a41ac4c2e 100644
--- a/mysql-test/suite/encryption/r/innodb-spatial-index.result
+++ b/mysql-test/suite/encryption/r/innodb-spatial-index.result
@@ -9,10 +9,14 @@ ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 140 "Wrong creat
 DROP TABLE t1;
 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT,
 c VARCHAR(256), coordinate POINT NOT NULL) ENCRYPTED=YES ENGINE=INNODB;
-CREATE SPATIAL INDEX b on t1(coordinate);
-ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 140 "Wrong create options")
-ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate);
+ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), ALGORITHM=COPY;
 ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 140 "Wrong create options")
+ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), FORCE, ALGORITHM=INPLACE;
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTED'
+ALTER TABLE t1 ADD SPATIAL INDEX(coordinate);
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTED'
+CREATE SPATIAL INDEX b on t1(coordinate);
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTED'
 DROP TABLE t1;
 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT,
 c VARCHAR(256), coordinate POINT NOT NULL) ENCRYPTED=DEFAULT ENGINE=INNODB;
diff --git a/mysql-test/suite/encryption/t/innodb-spatial-index.test b/mysql-test/suite/encryption/t/innodb-spatial-index.test
index de78461c765..6b6191c69cb 100644
--- a/mysql-test/suite/encryption/t/innodb-spatial-index.test
+++ b/mysql-test/suite/encryption/t/innodb-spatial-index.test
@@ -31,12 +31,17 @@ DROP TABLE t1;
 #
 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT,
 c VARCHAR(256), coordinate POINT NOT NULL) ENCRYPTED=YES ENGINE=INNODB;
+# FIXME: MDEV-13851 Encrypted table refuses some form of ALGORITHM=COPY,
+# but allows rebuild by FORCE
 --replace_regex /#sql-[0-9a-f_]*`/#sql-temporary`/
 --error ER_CANT_CREATE_TABLE
+ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), ALGORITHM=COPY;
+--error ER_ILLEGAL_HA_CREATE_OPTION
+ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), FORCE, ALGORITHM=INPLACE;
+--error ER_ILLEGAL_HA_CREATE_OPTION
+ALTER TABLE t1 ADD SPATIAL INDEX(coordinate);
+--error ER_ILLEGAL_HA_CREATE_OPTION
 CREATE SPATIAL INDEX b on t1(coordinate);
---replace_regex /#sql-[0-9a-f_]*`/#sql-temporary`/
---error ER_CANT_CREATE_TABLE
-ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate);
 DROP TABLE t1;
 
 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT,
@@ -69,4 +74,4 @@ INSERT INTO t2 values(1, 'secret', ST_GeomFromText('POINT(903994614 180726515)')
 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION > 0;
 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0;
 
-DROP TABLE t1, t2;
\ No newline at end of file
+DROP TABLE t1, t2;
diff --git a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result
index a6279bdb196..5d6e5787d8c 100644
--- a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result
+++ b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result
@@ -47,10 +47,20 @@ VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,
 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
 ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon';
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry';
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1);
 c1	ST_Astext(c2)	ST_Astext(c4)
@@ -140,8 +150,14 @@ c1	ST_Astext(c2)	ST_Astext(c4)
 1	POINT(1000 1000)	POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))
 DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
 ALTER TABLE tab CHANGE COLUMN c2 c22 POINT NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE tab;
 Table	Create Table
 tab	CREATE TABLE `tab` (
@@ -166,8 +182,14 @@ tab	1	idx4	1	c44	A	#	32	NULL		SPATIAL		testing spatial index on Polygon
 tab	1	idx5	1	c5	A	#	32	NULL		SPATIAL		testing spatial index on Geometry
 tab	1	idx6	1	c44	A	#	10	NULL		BTREE		
 ALTER TABLE tab CHANGE COLUMN c22 c2 POINT NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE tab;
 Table	Create Table
 tab	CREATE TABLE `tab` (
@@ -210,7 +232,11 @@ DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
 c1	ST_Astext(c2)	ST_Astext(c4)
 ALTER TABLE tab  DROP PRIMARY KEY;
+affected rows: 4
+info: Records: 4  Duplicates: 0  Warnings: 0
 ALTER TABLE tab ADD PRIMARY KEY(c2) ;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1);
 c1	ST_Astext(c2)	ST_Astext(c4)
@@ -254,7 +280,11 @@ SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) OR
 c1	ST_Astext(c2)	ST_Astext(c4)
 INSERT INTO tab SELECT * FROM tab1;
 ALTER TABLE tab DROP PRIMARY KEY;
+affected rows: 1
+info: Records: 1  Duplicates: 0  Warnings: 0
 ALTER TABLE tab DROP INDEX idx2;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab where c1 = c2;
 INSERT INTO temp_tab SELECT * FROM tab;
@@ -306,8 +336,14 @@ tab	1	idx5	1	c5	A	#	32	NULL		SPATIAL		testing spatial index on Geometry
 tab	1	idx6	1	c4	A	#	10	NULL		BTREE		
 DELETE FROM tab;
 ALTER TABLE tab ADD PRIMARY KEY(c2);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 CREATE SPATIAL INDEX idx2 ON tab(c2 ASC);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE tab;
 Table	Create Table
 tab	CREATE TABLE `tab` (
@@ -339,9 +375,17 @@ ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
 DELETE FROM tab;
 ALTER TABLE tab DROP PRIMARY KEY ;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab DROP KEY  const_1;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab ADD PRIMARY KEY(c5(10));
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c5(10));
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE tab;
 Table	Create Table
 tab	CREATE TABLE `tab` (
@@ -419,13 +463,25 @@ ANALYZE TABLE tab;
 Table	Op	Msg_type	Msg_text
 test.tab	analyze	status	OK
 ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon';
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry';
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab MODIFY COLUMN c2 GEOMETRY NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE;
-ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Do not support online operation on table with GIS index. Try ALGORITHM=COPY
+ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED
 SHOW CREATE TABLE tab;
 Table	Create Table
 tab	CREATE TABLE `tab` (
@@ -459,6 +515,8 @@ DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
 c1	ST_Astext(c2)	ST_Astext(c4)
 ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE tab;
 Table	Create Table
 tab	CREATE TABLE `tab` (
@@ -507,8 +565,14 @@ test.tab	analyze	status	OK
 SET @g1 = ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))');
 SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)');
 ALTER TABLE tab MODIFY COLUMN c2 POINT NOT NULL;
+affected rows: 8
+info: Records: 8  Duplicates: 0  Warnings: 0
 ALTER TABLE tab MODIFY COLUMN c3 LINESTRING NOT NULL;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL;
+affected rows: 8
+info: Records: 8  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE tab;
 Table	Create Table
 tab	CREATE TABLE `tab` (
@@ -585,7 +649,11 @@ DROP TABLE tab;
 CREATE TABLE parent (id POINT, PRIMARY KEY(id)) ENGINE=InnoDB;
 CREATE TABLE child (id GEOMETRY NOT NULL, parent_id POINT NOT NULL) ENGINE=InnoDB;
 ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE parent;
 Table	Create Table
 parent	CREATE TABLE `parent` (
@@ -613,7 +681,11 @@ DROP table child,parent;
 CREATE TABLE parent (id GEOMETRY, PRIMARY KEY(id(10))) ENGINE=InnoDB;
 CREATE TABLE child (id GEOMETRY NOT NULL, parent_id GEOMETRY NOT NULL) ENGINE=InnoDB;
 ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC) ;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 SHOW CREATE TABLE parent;
 Table	Create Table
 parent	CREATE TABLE `parent` (
@@ -641,11 +713,12 @@ create table t1 (c1 int) engine=innodb;
 insert into t1 values(NULL);
 alter table t1 add b geometry, add spatial index(b), algorithm=inplace;
 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
-alter table t1 add b geometry, algorithm=inplace;
-update t1 set b = st_geomfromtext('point(0 0)');
-alter table t1 add spatial index(b), algorithm=inplace;
-ERROR 42000: All parts of a SPATIAL index must be NOT NULL
-delete from t1;
+alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace;
+ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'),
+add spatial index(b), algorithm=inplace;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
 DROP table t1;
 create table t1 (c1 int) engine=innodb;
 insert into t1 values(NULL);
@@ -653,11 +726,8 @@ alter table t1 add b geometry, add spatial index(b), algorithm=copy;
 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
 alter table t1 add b geometry not null, add spatial index(b), algorithm=copy;
 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
-update t1 set b = st_geomfromtext('point(0 0)');
-ERROR 42S22: Unknown column 'b' in 'field list'
-alter table t1 add spatial index(b), algorithm=copy;
-ERROR 42000: Key column 'b' doesn't exist in table
-delete from t1;
+alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'),
+add spatial index(b), algorithm=copy;
 DROP table t1;
 #
 # BUG#20111575 ALTER TABLE...ADD SPATIAL INDEX...LOCK NONE IS REFUSED
diff --git a/mysql-test/suite/innodb_gis/t/alter_spatial_index.test b/mysql-test/suite/innodb_gis/t/alter_spatial_index.test
index 80ecf95bef9..f02c2f9cd1a 100644
--- a/mysql-test/suite/innodb_gis/t/alter_spatial_index.test
+++ b/mysql-test/suite/innodb_gis/t/alter_spatial_index.test
@@ -86,6 +86,7 @@ ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010
 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
 
 
+--enable_info
 ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC);
 
 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC);
@@ -95,7 +96,7 @@ ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on
 ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry';
 
 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE;
-
+--disable_info
 
 # Test the MBRContains
 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
@@ -195,22 +196,26 @@ SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1);
 
 DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
 
+--enable_info
 ALTER TABLE tab CHANGE COLUMN c2 c22 POINT NOT NULL;
 
 ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING NOT NULL;
 
 ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON NOT NULL;
+--disable_info
 
 SHOW CREATE TABLE tab;
 
 --replace_column 7 #
 SHOW INDEX FROM tab;
 
+--enable_info
 ALTER TABLE tab CHANGE COLUMN c22 c2 POINT NOT NULL;
 
 ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL;
 
 ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL;
+--disable_info
 
 SHOW CREATE TABLE tab;
 
@@ -234,9 +239,11 @@ DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
 
 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1);
 
+--enable_info
 ALTER TABLE tab  DROP PRIMARY KEY;
 
 ALTER TABLE tab ADD PRIMARY KEY(c2) ;
+--disable_info
 
 SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
 
@@ -298,9 +305,11 @@ SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) OR
 
 INSERT INTO tab SELECT * FROM tab1;
 
+--enable_info
 ALTER TABLE tab DROP PRIMARY KEY;
 
 ALTER TABLE tab DROP INDEX idx2;
+--disable_info
 
 # Check spatial index on temp tables
 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
@@ -342,11 +351,13 @@ SHOW INDEX FROM tab;
 
 DELETE FROM tab;
 
+--enable_info
 ALTER TABLE tab ADD PRIMARY KEY(c2);
 
 CREATE SPATIAL INDEX idx2 ON tab(c2 ASC);
 
 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2);
+--disable_info
 
 SHOW CREATE TABLE tab;
 
@@ -362,6 +373,7 @@ ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
 
 DELETE FROM tab;
 
+--enable_info
 ALTER TABLE tab DROP PRIMARY KEY ;
 
 ALTER TABLE tab DROP KEY  const_1;
@@ -369,6 +381,7 @@ ALTER TABLE tab DROP KEY  const_1;
 ALTER TABLE tab ADD PRIMARY KEY(c5(10));
 
 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c5(10));
+--disable_info
 
 SHOW CREATE TABLE tab;
 
@@ -445,6 +458,7 @@ ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010
 
 ANALYZE TABLE tab;
 
+--enable_info
 ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC);
 
 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC);
@@ -466,6 +480,7 @@ ALTER TABLE tab MODIFY COLUMN c2 GEOMETRY NOT NULL;
 
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE;
+--disable_info
 
 SHOW CREATE TABLE tab;
 
@@ -483,10 +498,12 @@ DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
 
 SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
 
+--enable_info
 # --error ER_CANT_CREATE_GEOMETRY_OBJECT
 # ALTER TABLE tab MODIFY COLUMN c2 POLYGON NOT NULL;
 
 ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL;
+--disable_info
 
 SHOW CREATE TABLE tab;
 
@@ -520,11 +537,13 @@ SET @g1 = ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020
 SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)');
 
 # When Point type data exist in the column allow DDL operation
+--enable_info
 ALTER TABLE tab MODIFY COLUMN c2 POINT NOT NULL;
 
 ALTER TABLE tab MODIFY COLUMN c3 LINESTRING NOT NULL;
 
 ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL;
+--disable_info
 
 SHOW CREATE TABLE tab;
 
@@ -571,6 +590,7 @@ DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2);
 SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1)
 OR ST_Touches(tab.c3,@g2);
 
+--enable_info
 # --error ER_SPATIAL_MUST_HAVE_GEOM_COL
 --error ER_WRONG_ARGUMENTS
 ALTER TABLE tab MODIFY COLUMN c4 INT NOT NULL;
@@ -578,6 +598,7 @@ ALTER TABLE tab MODIFY COLUMN c4 INT NOT NULL;
 # --error ER_SPATIAL_MUST_HAVE_GEOM_COL
 --error ER_WRONG_ARGUMENTS
 ALTER TABLE tab MODIFY COLUMN c4 BLOB NOT NULL;
+--disable_info
 
 # Test InnoDB to Myisam to InnoDB
 ALTER TABLE tab  ENGINE Myisam;
@@ -615,9 +636,11 @@ CREATE TABLE parent (id POINT, PRIMARY KEY(id)) ENGINE=InnoDB;
 
 CREATE TABLE child (id GEOMETRY NOT NULL, parent_id POINT NOT NULL) ENGINE=InnoDB;
 
+--enable_info
 ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC);
 
 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC);
+--disable_info
 
 SHOW CREATE TABLE parent;
 
@@ -646,9 +669,11 @@ CREATE TABLE parent (id GEOMETRY, PRIMARY KEY(id(10))) ENGINE=InnoDB;
 
 CREATE TABLE child (id GEOMETRY NOT NULL, parent_id GEOMETRY NOT NULL) ENGINE=InnoDB;
 
+--enable_info
 ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC) ;
 
 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC);
+--disable_info
 
 SHOW CREATE TABLE parent;
 
@@ -678,29 +703,13 @@ alter table t1 add b geometry, add spatial index(b), algorithm=inplace;
 # Add spatial index fail, since there's invalid geo data.
 # The case has to be commented because it no longer fails and following cases
 # don't expect the effect of such a statement.
-#--error ER_CANT_CREATE_GEOMETRY_OBJECT
-# alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace;
-
-# Add a geometry column.
-alter table t1 add b geometry, algorithm=inplace;
-
-# Add spatial index fail, since there's a NULL or invalid geo data.
-# The case has to be commented because it no longer fails and following cases
-# don't expect the effect of such a statement.
-#--error ER_CANT_CREATE_GEOMETRY_OBJECT
-#alter table t1 add spatial index(b), algorithm=inplace;
-
-# Update invalide geo data to point(0 0).
-update t1 set b = st_geomfromtext('point(0 0)');
-
-# Add spatial index success.
---error ER_SPATIAL_CANT_HAVE_NULL
-alter table t1 add spatial index(b), algorithm=inplace;
-
-# Delete rows.
-delete from t1;
+--error ER_CANT_CREATE_GEOMETRY_OBJECT
+ alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace;
 
-#cleanup
+--enable_info
+alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'),
+add spatial index(b), algorithm=inplace;
+--disable_info
 DROP table t1;
 
 # Check add spatial index when table already has rows (copy).
@@ -712,32 +721,11 @@ insert into t1 values(NULL);
 alter table t1 add b geometry, add spatial index(b), algorithm=copy;
 
 # Add spatial index fail, since there's a NULL or invalid geo data.
-# --error ER_INVALID_USE_OF_NULL
 --error ER_CANT_CREATE_GEOMETRY_OBJECT
 alter table t1 add b geometry not null, add spatial index(b), algorithm=copy;
 
-# Add a geometry column.
-# --error ER_INVALID_USE_OF_NULL
-# alter table t1 add b geometry not null, algorithm=copy;
-
-# Add spatial index.
-# The case has to be commented because it no longer fails and following cases
-# don't expect the effect of such a statement.
-#--error ER_CANT_CREATE_GEOMETRY_OBJECT
-#alter table t1 add spatial index(b), algorithm=copy;
-
-# Update invalide geo data to point(0 0).
---error ER_BAD_FIELD_ERROR
-update t1 set b = st_geomfromtext('point(0 0)');
-
-# Add spatial index success.
---error ER_KEY_COLUMN_DOES_NOT_EXITS
-alter table t1 add spatial index(b), algorithm=copy;
-
-# Delete rows.
-delete from t1;
-
-#cleanup
+alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'),
+add spatial index(b), algorithm=copy;
 DROP table t1;
 
 --echo #
diff --git a/mysql-test/suite/innodb_zip/r/create_options.result b/mysql-test/suite/innodb_zip/r/create_options.result
index 1c152229b3c..4e38bec08e3 100644
--- a/mysql-test/suite/innodb_zip/r/create_options.result
+++ b/mysql-test/suite/innodb_zip/r/create_options.result
@@ -325,19 +325,17 @@ SET GLOBAL innodb_file_format=Antelope;
 Warnings:
 Warning	131	Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
 ALTER TABLE t1 ADD COLUMN f1 INT;
-Warnings:
+ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT'
+SHOW WARNINGS;
+Level	Code	Message
 Warning	1478	InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
-Warning	1478	InnoDB: ignoring KEY_BLOCK_SIZE=4.
 Warning	1478	InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.
-Warning	1478	InnoDB: assuming ROW_FORMAT=DYNAMIC.
+Error	1478	Table storage engine 'InnoDB' does not support the create option 'ROW_FORMAT'
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `i` int(11) DEFAULT NULL,
-  `f1` int(11) DEFAULT NULL
+  `i` int(11) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
-SHOW WARNINGS;
-Level	Code	Message
 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
 SHOW WARNINGS;
 Level	Code	Message
diff --git a/mysql-test/suite/innodb_zip/t/create_options.test b/mysql-test/suite/innodb_zip/t/create_options.test
index e7303dee8f3..58fd764932c 100644
--- a/mysql-test/suite/innodb_zip/t/create_options.test
+++ b/mysql-test/suite/innodb_zip/t/create_options.test
@@ -249,9 +249,10 @@ SET GLOBAL innodb_file_format=Barracuda;
 DROP TABLE t1;
 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
 SET GLOBAL innodb_file_format=Antelope;
+--error ER_ILLEGAL_HA_CREATE_OPTION
 ALTER TABLE t1 ADD COLUMN f1 INT;
-SHOW CREATE TABLE t1;
 SHOW WARNINGS;
+SHOW CREATE TABLE t1;
 ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0;
 SHOW WARNINGS;
 ALTER TABLE t1 ADD COLUMN f2 INT;