diff options
| -rw-r--r-- | mysql-test/suite/sys_vars/r/insert_id_func.result | 25 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/t/insert_id_func.test | 20 | ||||
| -rw-r--r-- | sql/sys_vars.cc | 18 |
3 files changed, 57 insertions, 6 deletions
diff --git a/mysql-test/suite/sys_vars/r/insert_id_func.result b/mysql-test/suite/sys_vars/r/insert_id_func.result index 6a5746e130d..e4486cd5acf 100644 --- a/mysql-test/suite/sys_vars/r/insert_id_func.result +++ b/mysql-test/suite/sys_vars/r/insert_id_func.result @@ -69,3 +69,28 @@ id name drop table t1; disconnect test_con1; disconnect test_con2; +connection default; +CREATE TABLE t1(id int primary key auto_increment); +SET SESSION insert_id=123; +SET SESSION insert_id=0; +INSERT INTO t1 VALUES (); +SET SESSION insert_id=123; +SET SESSION insert_id=default; +INSERT INTO t1 VALUES (); +SET SESSION insert_id=123; +SET SESSION insert_id=-1; +Warnings: +Warning 1292 Truncated incorrect insert_id value: '-1' +INSERT INTO t1 VALUES (); +SET SESSION insert_id=123; +SET SESSION insert_id=-10; +Warnings: +Warning 1292 Truncated incorrect insert_id value: '-10' +INSERT INTO t1 VALUES (); +SELECT * FROM t1; +id +1 +2 +3 +4 +DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/t/insert_id_func.test b/mysql-test/suite/sys_vars/t/insert_id_func.test index a710afbb0cc..feb510c091b 100644 --- a/mysql-test/suite/sys_vars/t/insert_id_func.test +++ b/mysql-test/suite/sys_vars/t/insert_id_func.test @@ -103,3 +103,23 @@ drop table t1; disconnect test_con1; disconnect test_con2; + +# MDEV-22711 Assertion `nr != 0' failed in handler::update_auto_increment. +# +connection default; +CREATE TABLE t1(id int primary key auto_increment); +SET SESSION insert_id=123; +SET SESSION insert_id=0; +INSERT INTO t1 VALUES (); +SET SESSION insert_id=123; +SET SESSION insert_id=default; +INSERT INTO t1 VALUES (); +SET SESSION insert_id=123; +SET SESSION insert_id=-1; +INSERT INTO t1 VALUES (); +SET SESSION insert_id=123; +SET SESSION insert_id=-10; +INSERT INTO t1 VALUES (); +SELECT * FROM t1; +DROP TABLE t1; + diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index f231f49a667..bd4b2fbb062 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -4064,12 +4064,16 @@ static Sys_var_session_special Sys_identity( */ static bool update_insert_id(THD *thd, set_var *var) { - if (!var->value) - { - my_error(ER_NO_DEFAULT, MYF(0), var->var->name.str); - return true; - } - thd->force_one_auto_inc_interval(var->save_result.ulonglong_value); + /* + If we set the insert_id to the DEFAULT or 0 + it means we 'reset' it so it's value doesn't + affect the INSERT. + */ + if (!var->value || + var->save_result.ulonglong_value == 0) + thd->auto_inc_intervals_forced.empty(); + else + thd->force_one_auto_inc_interval(var->save_result.ulonglong_value); return false; } @@ -4077,6 +4081,8 @@ static ulonglong read_insert_id(THD *thd) { return thd->auto_inc_intervals_forced.minimum(); } + + static Sys_var_session_special Sys_insert_id( "insert_id", "The value to be used by the following INSERT " "or ALTER TABLE statement when inserting an AUTO_INCREMENT value", |