Re: [Maria-developers] c75f774d278: MDEV-31058 ER_KEY_NOT_FOUND upon concurrent CHANGE column autoinc and DML
![](https://secure.gravatar.com/avatar/39b623a1559cf9c69ac3d9d4fb44e7fe.jpg?s=120&d=mm&r=g)
Hi, Nikita, On May 24, Nikita Malyavin wrote:
revision-id: c75f774d278 (mariadb-11.0.1-118-gc75f774d278) parent(s): 319207175bd author: Nikita Malyavin committer: Nikita Malyavin timestamp: 2023-05-15 19:43:49 +0300 message:
MDEV-31058 ER_KEY_NOT_FOUND upon concurrent CHANGE column autoinc and DML
When column is changed to autoinc, ALTER TABLE may update zero/NULL values, if NO_AUTO_VALUE_ON_ZERO mode is not enabled.
Forbid this for LOCK=NONE for the unreliable cases. The cases are described in online_alter_check_autoinc.
just disable online for any ALTER that adds an autoinc attribute to an existing column. See MDEV.
diff --git a/mysql-test/main/alter_table_online.test b/mysql-test/main/alter_table_online.test index b9e147c812d..7ebe553aabb 100644 --- a/mysql-test/main/alter_table_online.test +++ b/mysql-test/main/alter_table_online.test @@ -135,3 +135,71 @@ alter table t1 add d int, lock=none; set system_versioning_alter_history= default; drop table t1;
+--echo # +--echo # MDEV-31058 ER_KEY_NOT_FOUND upon concurrent CHANGE column autoinc +--echo # and DML +--echo # +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; + +set statement sql_mode= NO_AUTO_VALUE_ON_ZERO for +alter table t drop a, modify b serial, algorithm=copy, lock=none;
I don't think it should work, see my comment in the MDEV
+--echo # Finally good. +alter table t modify d int auto_increment, add key(d), + algorithm=copy, lock=none;
no, it's not good either, as far as I understand
+ +drop table t;
lots of unvisible spaces at line ends, please remove them
+ +--echo # 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;
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
![](https://secure.gravatar.com/avatar/be4a8eaa75d904276e4551aedf1f4b9b.jpg?s=120&d=mm&r=g)
Hi,
On Wed, 24 May 2023 at 16:09, Sergei Golubchik
+set statement sql_mode= NO_AUTO_VALUE_ON_ZERO for +alter table t drop a, modify b serial, algorithm=copy, lock=none;
I don't think it should work, see my comment in the MDEV
the existing values will not be changed under NO_AUTO_VALUE_ON_ZERO, this is why it's fine. -- Yours truly, Nikita Malyavin
participants (2)
-
Nikita Malyavin
-
Sergei Golubchik