MDEV-27123 : auto_increment_increment and auto_increment_offset reset to 1 in current session after alter table on auto-increment column

Problem was that in ALTER TABLE execution variables were set
to 1 even when wsrep_auto_increment_control is OFF. We should
set them only when wsrep_auto_increment_control is ON.

4 files changed, 39 insertions, 2 deletions

diff --git a/mysql-test/suite/galera/r/MDEV-27123.result b/mysql-test/suite/galera/r/MDEV-27123.result
new file mode 100644
index 00000000000..98377162a35
--- /dev/null
+++ b/mysql-test/suite/galera/r/MDEV-27123.result
@@ -0,0 +1,17 @@
+connection node_2;
+connection node_1;
+show variables like 'auto_increment%';
+Variable_name	Value
+auto_increment_increment	3
+auto_increment_offset	3
+create table t4 (c1 int primary key auto_increment) engine=innodb;
+show variables like 'auto_increment%';
+Variable_name	Value
+auto_increment_increment	3
+auto_increment_offset	3
+alter table t4 add (c2 varchar(50));
+show variables like 'auto_increment%';
+Variable_name	Value
+auto_increment_increment	3
+auto_increment_offset	3
+DROP TABLE t4;
diff --git a/mysql-test/suite/galera/t/MDEV-27123.opt b/mysql-test/suite/galera/t/MDEV-27123.opt
new file mode 100644
index 00000000000..1c4accb816a
--- /dev/null
+++ b/mysql-test/suite/galera/t/MDEV-27123.opt
@@ -0,0 +1,2 @@
+--wsrep_auto_increment_control=OFF --auto_increment_increment=3 --auto_increment_offset=3
+
diff --git a/mysql-test/suite/galera/t/MDEV-27123.test b/mysql-test/suite/galera/t/MDEV-27123.test
new file mode 100644
index 00000000000..c2b682d6ded
--- /dev/null
+++ b/mysql-test/suite/galera/t/MDEV-27123.test
@@ -0,0 +1,8 @@
+--source include/galera_cluster.inc
+
+show variables like 'auto_increment%';
+create table t4 (c1 int primary key auto_increment) engine=innodb;
+show variables like 'auto_increment%';
+alter table t4 add (c2 varchar(50));
+show variables like 'auto_increment%';
+DROP TABLE t4;
diff --git a/sql/sql_alter.cc b/sql/sql_alter.cc
index 6d75c20e868..67c6a081880 100644
--- a/sql/sql_alter.cc
+++ b/sql/sql_alter.cc
@@ -486,8 +486,18 @@ bool Sql_cmd_alter_table::execute(THD *thd)
       DBUG_RETURN(TRUE);
     }
 
-    thd->variables.auto_increment_offset = 1;
-    thd->variables.auto_increment_increment = 1;
+    /*
+      It makes sense to set auto_increment_* to defaults in TOI operations.
+      Must be done before wsrep_TOI_begin() since Query_log_event encapsulating
+      TOI statement and auto inc variables for wsrep replication is constructed
+      there. Variables are reset back in THD::reset_for_next_command() before
+      processing of next command.
+    */
+    if (wsrep_auto_increment_control)
+    {
+      thd->variables.auto_increment_offset = 1;
+      thd->variables.auto_increment_increment = 1;
+    }
   }
 #endif