MDEV-30984 Online ALTER table is denied with non-informative error messages

2 files changed, 54 insertions, 15 deletions

diff --git a/mysql-test/main/alter_table_online.result b/mysql-test/main/alter_table_online.result
index 8b726ccb591..f113c902a3d 100644
--- a/mysql-test/main/alter_table_online.result
+++ b/mysql-test/main/alter_table_online.result
@@ -118,6 +118,8 @@ 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:
+Note	1846	Tables with CASCADE/SET NULL foreign keys are incompatible with LOCK=NONE, ALGORITHM=COPY
 alter table t2 add d int, algorithm=inplace;
 drop table t2, t1;
 #
@@ -134,6 +136,8 @@ 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
 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;
 #
@@ -203,4 +207,6 @@ 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/sql/sql_table.cc b/sql/sql_table.cc
index 99570008ddb..9d19d4f6311 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -9916,6 +9916,53 @@ bool online_alter_check_autoinc(const THD *thd, const Alter_info *alter_info,
   return online;
 }
 
+bool online_alter_is_supported(THD *thd, const Alter_info *alter_info,
+                               const TABLE *table)
+{
+  const char *reason= NULL;
+  List<FOREIGN_KEY_INFO> fk_list;
+
+  if (table->s->tmp_table)
+  {
+    reason= "Temporary tables are";
+    goto unsupported;
+  }
+
+  if (table->versioned(VERS_TRX_ID))
+  {
+    reason= "Transaction-versioned tables are";
+    goto unsupported;
+  }
+
+  table->file->get_foreign_key_list(thd, &fk_list);
+  for (auto &fk: fk_list)
+  {
+    if (fk_modifies_child(fk.delete_method)
+        || fk_modifies_child(fk.update_method))
+    {
+      reason= "Tables with CASCADE/SET NULL foreign keys are";
+      goto unsupported;
+    }
+  }
+
+  if (!online_alter_check_autoinc(thd, alter_info, table))
+  {
+    reason= "Adding AUTOINC to an existing column for a table without a "
+            "primary key is";
+    goto unsupported;
+  }
+
+  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);
+  return false;
+}
+
 
 /**
   Alter table
@@ -10139,21 +10186,7 @@ bool mysql_alter_table(THD *thd, const LEX_CSTRING *new_db,
   table= table_list->table;
   bool is_reg_table= table->s->tmp_table == NO_TMP_TABLE;
   
-  online= online && !table->s->tmp_table && !table->versioned(VERS_TRX_ID);
-
-  List<FOREIGN_KEY_INFO> fk_list;
-  table->file->get_foreign_key_list(thd, &fk_list);
-  for (auto &fk: fk_list)
-  {
-    if (fk_modifies_child(fk.delete_method)
-        || fk_modifies_child(fk.update_method))
-    {
-      online= false;
-      break;
-    }
-  }
-
-  online= online && online_alter_check_autoinc(thd, alter_info, table);
+  online= online && online_alter_is_supported(thd, alter_info, table);
   
 #ifdef WITH_WSREP
   if (WSREP(thd) &&