MDEV-30984 Online ALTER table is denied with non-informative error messagesbb-11.0-oalter

Unify online support checks under a single function, add warning.

3 files changed, 75 insertions, 14 deletions

diff --git a/mysql-test/main/alter_table_online.result b/mysql-test/main/alter_table_online.result
index f113c902a3d..bcf7afea9df 100644
--- a/mysql-test/main/alter_table_online.result
+++ b/mysql-test/main/alter_table_online.result
@@ -4,6 +4,10 @@
 create table t (a int);
 alter ignore table t add primary key (a), algorithm=copy, lock=none;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+show warnings;
+Level	Code	Message
+Note	1846	ALTER IGNORE TABLE is incompatible with LOCK=NONE, ALGORITHM=COPY
+Error	1846	LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
 drop table t;
 #
 # MDEV-28771 Assertion `table->in_use&&tdc->flushed' failed after ALTER
@@ -92,12 +96,20 @@ on update cascade) engine=InnoDB;
 insert into t2 values (1),(2),(3);
 alter table t2 add c int, algorithm=copy, lock=none;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+show warnings;
+Level	Code	Message
+Note	1846	Tables with CASCADE/SET NULL foreign keys are incompatible with LOCK=NONE, ALGORITHM=COPY
+Error	1846	LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
 alter table t2 add c int, algorithm=inplace, lock=none;
 create or replace table t2 (b int, foreign key (b)
 references t1 (a)
 on delete set null) engine=InnoDB;
 alter table t2 add c int, algorithm=copy, lock=none;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+show warnings;
+Level	Code	Message
+Note	1846	Tables with CASCADE/SET NULL foreign keys are incompatible with LOCK=NONE, ALGORITHM=COPY
+Error	1846	LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
 alter table t2 add c int, algorithm=inplace, lock=none;
 create or replace table t2 (b int, foreign key (b)
 references t1 (a)
@@ -117,9 +129,11 @@ b int references t1 (b) on update cascade) engine=InnoDB;
 insert into t2 values (1, 1),(2, 2);
 alter table t2 add c int, algorithm=copy, lock=none;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
-alter table t2 add c int, algorithm=copy;
-Warnings:
+show warnings;
+Level	Code	Message
 Note	1846	Tables with CASCADE/SET NULL foreign keys are incompatible with LOCK=NONE, ALGORITHM=COPY
+Error	1846	LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+alter table t2 add c int, algorithm=copy;
 alter table t2 add d int, algorithm=inplace;
 drop table t2, t1;
 #
@@ -134,10 +148,12 @@ period for system_time (row_start, row_end))
 engine=innodb with system versioning;
 alter table t1 add c int, algorithm=copy, lock=none;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+show warnings;
+Level	Code	Message
+Note	1846	Transaction-versioned tables are incompatible with LOCK=NONE, ALGORITHM=COPY
+Error	1846	LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
 alter table t1 add c int, algorithm=inplace;
 alter table t1 add d int, lock=none;
-Warnings:
-Note	1846	Transaction-versioned tables are incompatible with LOCK=NONE, ALGORITHM=COPY
 set system_versioning_alter_history= default;
 drop table t1;
 #
@@ -147,6 +163,10 @@ drop table t1;
 create table t (a serial, b int) engine=innodb;
 alter table t drop a, modify b serial, algorithm=copy, lock=none;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+show warnings;
+Level	Code	Message
+Note	1846	Adding AUTOINC to an existing column for a table without a primary key is incompatible with LOCK=NONE, ALGORITHM=COPY
+Error	1846	LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
 set statement sql_mode= NO_AUTO_VALUE_ON_ZERO for
 alter table t drop a, modify b serial, algorithm=copy, lock=none;
 create or replace table t (a serial, b int) engine=innodb;
@@ -161,6 +181,10 @@ t	CREATE TABLE `t` (
 # Only unsafe approach is fine because of possible collisions. 
 alter table t modify a int, modify b serial, algorithm=copy, lock=none;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+show warnings;
+Level	Code	Message
+Note	1846	Adding AUTOINC to an existing column for a table without a primary key is incompatible with LOCK=NONE, ALGORITHM=COPY
+Error	1846	LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
 #
 # Check that we treat autoinc columns correctly modify old autoinc is  
 # fine, adding new autoinc for existed column is unsafe.
@@ -184,22 +208,38 @@ t	CREATE TABLE `t` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 alter table t drop b, change c c serial, algorithm=copy, lock=none;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+show warnings;
+Level	Code	Message
+Note	1846	Adding AUTOINC to an existing column for a table without a primary key is incompatible with LOCK=NONE, ALGORITHM=COPY
+Error	1846	LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
 # Check existed unique keys.
 create or replace table t(a int, b int not null, c int not null, d int);
 # No unique in the old table;
 alter table t add unique(b, c), modify d int auto_increment, add key(d),
 algorithm=copy, lock=none;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+show warnings;
+Level	Code	Message
+Note	1846	Adding AUTOINC to an existing column for a table without a primary key is incompatible with LOCK=NONE, ALGORITHM=COPY
+Error	1846	LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
 alter table t add unique(a, b);
 # Unique in the old table has nulls;
 alter table t modify d int auto_increment, add key(d), 
 algorithm=copy, lock=none;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+show warnings;
+Level	Code	Message
+Note	1846	Adding AUTOINC to an existing column for a table without a primary key is incompatible with LOCK=NONE, ALGORITHM=COPY
+Error	1846	LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
 alter table t add unique(b, c);
 # Change unique'scolumn;
 alter table t change b x int, modify d int auto_increment, add key(d),
 algorithm=copy, lock=none;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
+show warnings;
+Level	Code	Message
+Note	1846	Adding AUTOINC to an existing column for a table without a primary key is incompatible with LOCK=NONE, ALGORITHM=COPY
+Error	1846	LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED
 # Finally good.
 alter table t modify d int auto_increment, add key(d), 
 algorithm=copy, lock=none;
@@ -207,6 +247,4 @@ drop table t;
 # MDEV-31172 Server crash or ASAN errors in online_alter_check_autoinc
 create table t (a int, b int, c char(8), key(a,b,c));
 alter table t modify c int auto_increment key, algorithm=copy;
-Warnings:
-Note	1846	Adding AUTOINC to an existing column for a table without a primary key is incompatible with LOCK=NONE, ALGORITHM=COPY
 drop table t;
diff --git a/mysql-test/main/alter_table_online.test b/mysql-test/main/alter_table_online.test
index 7ebe553aabb..70461d1f95a 100644
--- a/mysql-test/main/alter_table_online.test
+++ b/mysql-test/main/alter_table_online.test
@@ -8,6 +8,7 @@
 create table t (a int);
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 alter ignore table t add primary key (a), algorithm=copy, lock=none;
+show warnings;
 drop table t;
 
 --echo #
@@ -78,6 +79,7 @@ insert into t2 values (1),(2),(3);
 
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 alter table t2 add c int, algorithm=copy, lock=none;
+show warnings;
 alter table t2 add c int, algorithm=inplace, lock=none;
 
 create or replace table t2 (b int, foreign key (b)
@@ -86,6 +88,7 @@ create or replace table t2 (b int, foreign key (b)
 
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 alter table t2 add c int, algorithm=copy, lock=none;
+show warnings;
 alter table t2 add c int, algorithm=inplace, lock=none;
 
 create or replace table t2 (b int, foreign key (b)
@@ -111,6 +114,7 @@ insert into t2 values (1, 1),(2, 2);
 
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 alter table t2 add c int, algorithm=copy, lock=none;
+show warnings;
 alter table t2 add c int, algorithm=copy;
 alter table t2 add d int, algorithm=inplace;
 # Cleanup
@@ -129,6 +133,7 @@ create table t1 (id int,
 
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 alter table t1 add c int, algorithm=copy, lock=none;
+show warnings;
 alter table t1 add c int, algorithm=inplace;
 alter table t1 add d int, lock=none;
 
@@ -142,6 +147,7 @@ drop table t1;
 create table t (a serial, b int) engine=innodb;
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 alter table t drop a, modify b serial, algorithm=copy, lock=none;
+show warnings;
 
 set statement sql_mode= NO_AUTO_VALUE_ON_ZERO for
 alter table t drop a, modify b serial, algorithm=copy, lock=none;
@@ -152,6 +158,7 @@ show create table t;
 --echo # Only unsafe approach is fine because of possible collisions. 
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 alter table t modify a int, modify b serial, algorithm=copy, lock=none;
+show warnings;
 
 --echo #
 --echo # Check that we treat autoinc columns correctly modify old autoinc is  
@@ -172,6 +179,7 @@ show create table t;
 
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 alter table t drop b, change c c serial, algorithm=copy, lock=none;
+show warnings;
 
 --echo # Check existed unique keys.
 create or replace table t(a int, b int not null, c int not null, d int);
@@ -180,19 +188,22 @@ create or replace table t(a int, b int not null, c int not null, d int);
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 alter table t add unique(b, c), modify d int auto_increment, add key(d),
               algorithm=copy, lock=none;
+show warnings;
 
 alter table t add unique(a, b);
 --echo # Unique in the old table has nulls;
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 alter table t modify d int auto_increment, add key(d), 
               algorithm=copy, lock=none;
+show warnings;
 
 alter table t add unique(b, c);
 --echo # Change unique'scolumn;
 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 alter table t change b x int, modify d int auto_increment, add key(d),
               algorithm=copy, lock=none;
-              
+show warnings;
+
 --echo # Finally good.
 alter table t modify d int auto_increment, add key(d), 
               algorithm=copy, lock=none;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 9d19d4f6311..22447db42af 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -9922,6 +9922,18 @@ bool online_alter_is_supported(THD *thd, const Alter_info *alter_info,
   const char *reason= NULL;
   List<FOREIGN_KEY_INFO> fk_list;
 
+  if (alter_info->flags & ALTER_DROP_SYSTEM_VERSIONING)
+  {
+    reason= "DROP SYSTEM VERSIONING is";
+    goto unsupported;
+  }
+
+  if (thd->lex->ignore)
+  {
+    reason= "ALTER IGNORE TABLE is";
+    goto unsupported;
+  }
+
   if (table->s->tmp_table)
   {
     reason= "Temporary tables are";
@@ -9955,11 +9967,13 @@ bool online_alter_is_supported(THD *thd, const Alter_info *alter_info,
   return true;
 
 unsupported:
-  push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
-                      ER_ALTER_OPERATION_NOT_SUPPORTED_REASON,
-                      "%s incompatible with "
-                      "LOCK=NONE, ALGORITHM=COPY",
-                      reason);
+  if (alter_info->requested_lock == Alter_info::ALTER_TABLE_LOCK_NONE
+      && alter_info->algorithm(thd) == Alter_info::ALTER_TABLE_ALGORITHM_COPY)
+    push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+                        ER_ALTER_OPERATION_NOT_SUPPORTED_REASON,
+                        "%s incompatible with "
+                        "LOCK=NONE, ALGORITHM=COPY",
+                        reason);
   return false;
 }
 
@@ -10144,9 +10158,7 @@ bool mysql_alter_table(THD *thd, const LEX_CSTRING *new_db,
   table_list->required_type= TABLE_TYPE_NORMAL;
 
   if (alter_info->requested_lock > Alter_info::ALTER_TABLE_LOCK_NONE
-      || alter_info->flags & ALTER_DROP_SYSTEM_VERSIONING
       || thd->lex->sql_command == SQLCOM_OPTIMIZE
-      || ignore
       || alter_info->algorithm(thd) > Alter_info::ALTER_TABLE_ALGORITHM_COPY)
     online= false;