MDEV-28943 Online alter fails under LOCK TABLE with ER_ALTER_OPERATION_NOT_SUPPORTED_REASON

if ALTER TABLE ... LOCK=xxx is executed under LOCK TABLES,
ignore the LOCK clause, because ALTER should not downgrade
already taken EXCLUSIVE table lock to SHARED or NONE.

This commit preserves the existing behavior (LOCK was de facto ignored),
but makes it explicit.

3 files changed, 68 insertions, 6 deletions

diff --git a/mysql-test/main/alter_table_lock.result b/mysql-test/main/alter_table_lock.result
index 620fca23315..3f6e0eba53f 100644
--- a/mysql-test/main/alter_table_lock.result
+++ b/mysql-test/main/alter_table_lock.result
@@ -14,4 +14,31 @@ ALTER TABLE t1 CHANGE COLUMN IF EXISTS b c INT;
 SET SESSION max_session_mem_used = @max_session_mem_used_save;
 UNLOCK TABLES;
 DROP TABLE t1;
+#
 # End of 10.5 tests
+#
+#
+# MDEV-28943 Online alter fails under LOCK TABLE with ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+#
+create table t1 (f int) engine=innodb;
+insert t1 values (1);
+lock table t1 write;
+connect con1, localhost, root;
+select count(*) as 'must be 0' from t1;
+connection default;
+alter table t1 force, algorithm=copy, lock=none;
+alter table t1 force, algorithm=inplace, lock=none;
+alter table t1 force, algorithm=copy, lock=shared;
+alter table t1 force, algorithm=inplace, lock=shared;
+alter table t1 force, algorithm=copy, lock=exclusive;
+alter table t1 force, algorithm=inplace, lock=exclusive;
+delete from t1;
+unlock tables;
+connection con1;
+must be 0
+0
+connection default;
+drop table t1;
+#
+# End of 10.10 tests
+#
diff --git a/mysql-test/main/alter_table_lock.test b/mysql-test/main/alter_table_lock.test
index bd26c1ac7d0..b74f7d48ae6 100644
--- a/mysql-test/main/alter_table_lock.test
+++ b/mysql-test/main/alter_table_lock.test
@@ -1,4 +1,5 @@
 --source include/not_msan.inc
+--source include/have_innodb.inc
 
 --echo #
 --echo # MDEV-23836: Assertion `! is_set() || m_can_overwrite_status' in
@@ -26,4 +27,35 @@ SET SESSION max_session_mem_used = @max_session_mem_used_save;
 UNLOCK TABLES;
 DROP TABLE t1;
 
+--echo #
 --echo # End of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-28943 Online alter fails under LOCK TABLE with ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+--echo #
+
+# test that any lock=xxx works under LOCK TABLES
+# and that it is ignored, does not actually downgrade the lock
+create table t1 (f int) engine=innodb;
+insert t1 values (1);
+lock table t1 write;
+connect con1, localhost, root;
+--send select count(*) as 'must be 0' from t1
+--connection default
+alter table t1 force, algorithm=copy, lock=none;
+alter table t1 force, algorithm=inplace, lock=none;
+alter table t1 force, algorithm=copy, lock=shared;
+alter table t1 force, algorithm=inplace, lock=shared;
+alter table t1 force, algorithm=copy, lock=exclusive;
+alter table t1 force, algorithm=inplace, lock=exclusive;
+delete from t1;
+unlock tables;
+--connection con1
+--reap
+--connection default
+drop table t1;
+
+--echo #
+--echo # End of 10.10 tests
+--echo #
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 32d6d6d858a..4db8ef464b6 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -10033,13 +10033,16 @@ bool mysql_alter_table(THD *thd, const LEX_CSTRING *new_db,
     set of tables from the old table or to open a new TABLE object for
     an extended list and verify that they belong to locked tables.
   */
-  if ((thd->locked_tables_mode == LTM_LOCK_TABLES ||
-       thd->locked_tables_mode == LTM_PRELOCKED_UNDER_LOCK_TABLES) &&
-      (create_info->used_fields & HA_CREATE_USED_UNION) &&
-      (table->s->tmp_table == NO_TMP_TABLE))
+  if (thd->locked_tables_mode == LTM_LOCK_TABLES ||
+       thd->locked_tables_mode == LTM_PRELOCKED_UNDER_LOCK_TABLES)
   {
-    my_error(ER_LOCK_OR_ACTIVE_TRANSACTION, MYF(0));
-    DBUG_RETURN(true);
+    if ((create_info->used_fields & HA_CREATE_USED_UNION) &&
+        (table->s->tmp_table == NO_TMP_TABLE))
+    {
+      my_error(ER_LOCK_OR_ACTIVE_TRANSACTION, MYF(0));
+      DBUG_RETURN(true);
+    }
+    alter_info->requested_lock= Alter_info::ALTER_TABLE_LOCK_DEFAULT;
   }
 
   /* Check that we are not trying to rename to an existing table */