Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24038

Assertion `next_insert_id >= auto_inc_interval_for_cur_row.minimum()' failed in handler::update_auto_increment

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5(EOL), (16)
      10.0(EOL), 10.1(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4
    • 10.5, 10.6, 10.11
    • Data Manipulation - Insert
    • None

    Description

      --source include/have_innodb.inc
      		 
      create table t1 (
      		 
        id int not null auto_increment  primary key,
      		 
        s timestamp(6) not null
      		 
      ) engine=innodb auto_increment=2147483648;
      		 
       
      		 
      insert ignore into t1 (s) values ('2000-10-01'), ('1975-08-12') on duplicate key update s = '1';

      Fails with InnoDB/MyIsam min, not on non-debug build.

      Version: '10.5.7-MariaDB-debug-log' 
      mariadbd: /10.5/sql/handler.cc:3524: int handler::update_auto_increment(): Assertion `next_insert_id >= auto_inc_interval_for_cur_row.minimum()' failed.
      		 
      201027 15:55:41 [ERROR] mysqld got signal 6 ;
      		 
      Server version: 10.5.7-MariaDB-debug-log
      		 
       
      		 
      linux/raise.c:51(__GI_raise)[0x7f6083c6b18b]
      		 
      stdlib/abort.c:81(__GI_abort)[0x7f6083c4a859]
      		 
      sql/handler.cc:3526(handler::update_auto_increment())[0x5629f93bc3cb]
      		 
      handler/ha_innodb.cc:7535(ha_innobase::write_row(unsigned char const*))[0x5629f88bca19]
      		 
      sql/handler.cc:7146(handler::ha_write_row(unsigned char const*))[0x5629f7fcb110]
      		 
      sql/sql_insert.cc:1784(write_record(THD*, TABLE*, st_copy_info*, select_result*))[0x5629f7fc5b53]
      		 
      sql/sql_insert.cc:1099(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool, select_result*))[0x5629f808d7bc]
      		 
      sql/sql_parse.cc:4550(mysql_execute_command(THD*))[0x5629f80a59e0]
      		 
      sql/sql_parse.cc:8010(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5629f807c35c]
      		 
      sql/sql_parse.cc:1874(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5629f8078c85]
      		 
      sql/sql_parse.cc:1352(do_command(THD*))[0x5629f84b7f80]
      		 
      sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x5629f84b78e4]
      		 
      sql/sql_connect.cc:1314(handle_one_connection)[0x5629f91bf6e5]
      		 
      nptl/pthread_create.c:478(start_thread)[0x7f6084173609]
      		 
      x86_64/clone.S:97(__GI___clone)[0x7f6083d47293]
      		 
       
      		 
      Query (0x62b0000932a8): insert ignore into t1 (s) values ('2000-10-01'), ('1975-08-12') on duplicate key update s = '1'

      with myisam:

      5.5 821a6939087d411f97256d

      		 
      #3  <signal handler called>
      		 
      #4  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
      		 
      #5  0x00007f1addfd4535 in __GI_abort () at abort.c:79
      		 
      #6  0x00007f1addfd440f in __assert_fail_base (fmt=0x7f1ade136ee0 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x557681a355d0 "next_insert_id >= auto_inc_interval_for_cur_row.minimum()", file=0x557681a34b96 "/5.5/sql/handler.cc", line=2565, function=<optimized out>) at assert.c:92
      		 
      #7  0x00007f1addfe2102 in __GI___assert_fail (assertion=0x557681a355d0 "next_insert_id >= auto_inc_interval_for_cur_row.minimum()", file=0x557681a34b96 "/5.5/sql/handler.cc", line=2565, function=0x557681a36560 <handler::update_auto_increment()::__PRETTY_FUNCTION__> "int handler::update_auto_increment()") at assert.c:101
      		 
      #8  0x0000557681448c78 in handler::update_auto_increment (this=0x7f1ad759f878) at /5.5/sql/handler.cc:2565
      		 
      #9  0x000055768169dfa8 in ha_myisam::write_row (this=0x7f1ad759f878, buf=0x7f1ad798fe78 "\377") at /5.5/storage/myisam/ha_myisam.cc:849
      		 
      #10 0x000055768144df8d in handler::ha_write_row (this=0x7f1ad759f878, buf=0x7f1ad798fe78 "\377") at /5.5/sql/handler.cc:5205
      		 
      #11 0x0000557681277f01 in write_record (thd=0x7f1adb5f1c60, table=0x7f1ad7999460, info=0x7f1addf5c510) at /5.5/sql/sql_insert.cc:1596
      		 
      #12 0x00005576812767a3 in mysql_insert (thd=0x7f1adb5f1c60, table_list=0x7f1ad79871e0, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_UPDATE, ignore=true) at /5.5/sql/sql_insert.cc:986
      		 
      #13 0x000055768129528d in mysql_execute_command (thd=0x7f1adb5f1c60) at /5.5/sql/sql_parse.cc:2960
      		 
      #14 0x000055768129d38f in mysql_parse (thd=0x7f1adb5f1c60, rawbuf=0x7f1ad7987078 "insert ignore into t1 (s) values ('2000-10-01'), ('1975-08-12') on duplicate key update s ='1'", length=94, parser_state=0x7f1addf5ce50) at /5.5/sql/sql_parse.cc:5928
      		 
      #15 0x0000557681290cb2 in dispatch_command (command=COM_QUERY, thd=0x7f1adb5f1c60, packet=0x7f1adc139f61 "insert ignore into t1 (s) values ('2000-10-01'), ('1975-08-12') on duplicate key update s ='1'", packet_length=94) at /5.5/sql/sql_parse.cc:1067
      		 
      #16 0x000055768128fec7 in do_command (thd=0x7f1adb5f1c60) at /5.5/sql/sql_parse.cc:793
      		 
      #17 0x0000557681394b2b in do_handle_one_connection (thd_arg=0x7f1adb5f1c60) at /5.5/sql/sql_connect.cc:1268
      		 
      #18 0x00005576813948a4 in handle_one_connection (arg=0x7f1adb5f1c60) at /5.5/sql/sql_connect.cc:1184
      		 
      #19 0x0000557681715171 in pfs_spawn_thread (arg=0x7f1adb61c3c0) at /5.5/storage/perfschema/pfs.cc:1015
      		 
      #20 0x00007f1ade9befa3 in start_thread (arg=<optimized out>) at pthread_create.c:486
      		 
      #21 0x00007f1ade0ab4cf in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27770 Assertion `next_insert_id >= auto_inc_interval_for_cur_row.minimum()' failed in handler::update_auto_increment

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24613 Assertion `next_insert_id >= auto_inc_interval_for_cur_row.minimum()' failed in handler::update_auto_increment from sql/structs.h:410

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            Please note that it's not at all an unrealistic issue. On whatever legacy reason, the crazy value of AUTO_INCREMENT is quite easy to get by an honest mistake, e.g. by a carelessly written application.
            Slightly modified original test case

            10.3 b527bfe8

            		 
            create table t1 (
            		 
              id int not null auto_increment  primary key,
            		 
              s timestamp(6) not null
            		 
            );
            		 
            insert into t1 values (1,now()),(2,now());
            		 
            update ignore t1 set id = now();
            		 
            show create table t1;
            		 
            insert ignore into t1 (s) values ('2000-10-01'), ('1975-08-12') on duplicate key update s = '1';

            leads to the same result

            10.3

            		 
            show create table t1;
            		 
            Table	Create Table
            		 
            t1	CREATE TABLE `t1` (
            		 
              `id` int(11) NOT NULL AUTO_INCREMENT,
            		 
              `s` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
            		 
              PRIMARY KEY (`id`)
            		 
            ) ENGINE=MyISAM AUTO_INCREMENT=2147483648 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
            		 
            insert ignore into t1 (s) values ('2000-10-01'), ('1975-08-12') on duplicate key update s = '1';
            		 
            mysqltest: At line 10: query 'insert ignore into t1 (s) values ('2000-10-01'), ('1975-08-12') on duplicate key update s = '1'' failed: 2013: Lost connection to MySQL server during query

            elenst Elena Stepanova added a comment - Please note that it's not at all an unrealistic issue. On whatever legacy reason, the crazy value of AUTO_INCREMENT is quite easy to get by an honest mistake, e.g. by a carelessly written application. Slightly modified original test case 10.3 b527bfe8 create table t1 ( id int not null auto_increment primary key , s timestamp (6) not null ); insert into t1 values (1,now()),(2,now()); update ignore t1 set id = now(); show create table t1; insert ignore into t1 (s) values ( '2000-10-01' ), ( '1975-08-12' ) on duplicate key update s = '1' ; leads to the same result 10.3 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int (11) NOT NULL AUTO_INCREMENT, `s` timestamp (6) NOT NULL DEFAULT current_timestamp (6) ON UPDATE current_timestamp (6), PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2147483648 DEFAULT CHARSET=latin1 COLLATE =latin1_swedish_ci insert ignore into t1 (s) values ( '2000-10-01' ), ( '1975-08-12' ) on duplicate key update s = '1' ; mysqltest: At line 10: query 'insert ignore into t1 (s) values (' 2000-10-01 '), (' 1975-08-12 ') on duplicate key update s = ' 1 '' failed: 2013: Lost connection to MySQL server during query
            elenst Elena Stepanova added a comment -

            Finally the test case without ODKU (all previous ones here and in the linked issues are ODKU); but otherwise it appears to be still the same – auto-increment overflow and ignored duplicate key.

            --source include/have_innodb.inc
            		 
             
            		 
            CREATE TABLE t1 (f BIGINT) ENGINE=InnoDB;
            		 
            INSERT INTO t1 VALUES (0),(2147483648),(0),(0);
            		 
             
            		 
            CREATE TABLE t2 (a INT DEFAULT 0 UNIQUE, id INT AUTO_INCREMENT UNIQUE) ENGINE=InnoDB;
            		 
            INSERT IGNORE INTO t2 (id) SELECT f FROM t1;
            		 
             
            		 
            # Cleanup
            		 
            DROP TABLE t1, t2;

            elenst Elena Stepanova added a comment - Finally the test case without ODKU (all previous ones here and in the linked issues are ODKU); but otherwise it appears to be still the same – auto-increment overflow and ignored duplicate key. --source include/have_innodb.inc   CREATE TABLE t1 (f BIGINT ) ENGINE=InnoDB; INSERT INTO t1 VALUES (0),(2147483648),(0),(0);   CREATE TABLE t2 (a INT DEFAULT 0 UNIQUE , id INT AUTO_INCREMENT UNIQUE ) ENGINE=InnoDB; INSERT IGNORE INTO t2 (id) SELECT f FROM t1;   # Cleanup DROP TABLE t1, t2;
            alice Alice Sherepa added a comment -

            slight variation - if the table has partitions:

            mariadbd: /11.4/sql/handler.cc:4091: int handler::update_auto_increment(): Assertion `next_insert_id >= auto_inc_interval_for_cur_row.minimum()' failed.
            		 
            240129 17:27:35 [ERROR] mysqld got signal 6 ;
            		 
             
            		 
            Server version: 11.4.0-MariaDB-debug-log source revision: d039346a7acac7c72f264377a8cd6b0273c548df
            		 
             
            		 
            /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f5e93375fd6]
            		 
            sql/handler.cc:4093(handler::update_auto_increment())[0x5583fb23c323]
            		 
            sql/ha_partition.cc:4636(ha_partition::write_row(unsigned char const*))[0x5583fb9ab780]
            		 
            sql/handler.cc:7872(handler::ha_write_row(unsigned char const*))[0x5583fb25b3fc]
            		 
            sql/sql_insert.cc:1887(write_record(THD*, TABLE*, st_copy_info*, select_result*))[0x5583fa7e2cbf]
            		 
            sql/sql_insert.cc:1154(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool, select_result*))[0x5583fa7dce86]
            		 
            sql/sql_parse.cc:4431(mysql_execute_command(THD*, bool))[0x5583fa8adb6f]
            		 
            sql/sql_parse.cc:7798(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x5583fa8c56dd]
            		 
            sql/sql_parse.cc:1895(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x5583fa89cc8b]
            		 
            sql/sql_parse.cc:1406(do_command(THD*, bool))[0x5583fa8999d0]
            		 
            sql/sql_connect.cc:1417(do_handle_one_connection(CONNECT*, bool))[0x5583fad7ea1d]
            		 
            sql/sql_connect.cc:1321(handle_one_connection)[0x5583fad7e37a]
            		 
            perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5583fb9f73da]
            		 
            nptl/pthread_create.c:478(start_thread)[0x7f5e93890609]

            alice Alice Sherepa added a comment - slight variation - if the table has partitions: mariadbd: /11.4/sql/handler.cc:4091: int handler::update_auto_increment(): Assertion `next_insert_id >= auto_inc_interval_for_cur_row.minimum()' failed. 240129 17:27:35 [ERROR] mysqld got signal 6 ;   Server version: 11.4.0-MariaDB-debug-log source revision: d039346a7acac7c72f264377a8cd6b0273c548df   /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f5e93375fd6] sql/handler.cc:4093(handler::update_auto_increment())[0x5583fb23c323] sql/ha_partition.cc:4636(ha_partition::write_row(unsigned char const*))[0x5583fb9ab780] sql/handler.cc:7872(handler::ha_write_row(unsigned char const*))[0x5583fb25b3fc] sql/sql_insert.cc:1887(write_record(THD*, TABLE*, st_copy_info*, select_result*))[0x5583fa7e2cbf] sql/sql_insert.cc:1154(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool, select_result*))[0x5583fa7dce86] sql/sql_parse.cc:4431(mysql_execute_command(THD*, bool))[0x5583fa8adb6f] sql/sql_parse.cc:7798(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x5583fa8c56dd] sql/sql_parse.cc:1895(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x5583fa89cc8b] sql/sql_parse.cc:1406(do_command(THD*, bool))[0x5583fa8999d0] sql/sql_connect.cc:1417(do_handle_one_connection(CONNECT*, bool))[0x5583fad7ea1d] sql/sql_connect.cc:1321(handle_one_connection)[0x5583fad7e37a] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5583fb9f73da] nptl/pthread_create.c:478(start_thread)[0x7f5e93890609]
            Roel Roel Van de Paar added a comment - - edited

            Please also test with:

            SET SESSION default_tmp_storage_engine=MEMORY, sql_mode=no_auto_value_on_zero;
            		 
            CREATE TEMPORARY TABLE t (a INT AUTO_INCREMENT KEY);
            		 
            INSERT INTO t VALUES (2147483647),(-2147483647),(0);  # Will not crash when both values are changed to 2147483646 (MAX_INT-1)
            		 
            SET sql_mode=traditional;
            		 
            INSERT INTO t VALUES (1);
            		 
            INSERT IGNORE INTO t SELECT a FROM t AS t2 ON DUPLICATE KEY UPDATE a=t.a;


            next_insert_id >= auto_inc_interval_for_cur_row.minimum()|SIGABRT|handler::update_auto_increment|ha_heap::write_row|handler::ha_write_row|write_record

            Roel Roel Van de Paar added a comment - - edited Please also test with: SET SESSION default_tmp_storage_engine=MEMORY, sql_mode=no_auto_value_on_zero; CREATE TEMPORARY TABLE t (a INT AUTO_INCREMENT KEY ); INSERT INTO t VALUES (2147483647),(-2147483647),(0); # Will not crash when both values are changed to 2147483646 (MAX_INT-1) SET sql_mode=traditional; INSERT INTO t VALUES (1); INSERT IGNORE INTO t SELECT a FROM t AS t2 ON DUPLICATE KEY UPDATE a=t.a; next_insert_id >= auto_inc_interval_for_cur_row.minimum()|SIGABRT|handler::update_auto_increment|ha_heap::write_row|handler::ha_write_row|write_record
            Roel Roel Van de Paar added a comment -

            Please also test with:

            SET sql_mode='';
            		 
            CREATE TABLE t (a INT AUTO_INCREMENT,KEY(a)) ENGINE=MyISAM;
            		 
            INSERT INTO t VALUES (0),(3000000000);
            		 
            UPDATE t SET a=0;
            		 
            INSERT IGNORE INTO t SELECT a FROM t AS t2 ON DUPLICATE KEY UPDATE a=t.a;


            next_insert_id >= auto_inc_interval_for_cur_row.minimum()|SIGABRT|handler::update_auto_increment|ha_myisam::write_row|handler::ha_write_row|write_record

            There is also:

            INSTALL PLUGIN ARCHIVE SONAME 'ha_archive.so';
            		 
            SET SESSION insert_id=3000000000;
            		 
            CREATE TABLE t (c INT AUTO_INCREMENT KEY) ENGINE=Archive;
            		 
            INSERT INTO t VALUES (0),(1);


            next_insert_id >= auto_inc_interval_for_cur_row.minimum()|SIGABRT|handler::update_auto_increment|ha_archive::write_row|handler::ha_write_row|write_record

            Here, the assert does not fire for other storage engines and instead we see (using InnoDB for example):

            CS 11.2.6 12a91b57e27b979819924cf89614e6e51f24b37b (Debug)

            		 
            11.2.6-dbg>INSERT INTO t VALUES (0),(1);
            		 
            ERROR 167 (22003): Out of range value for column 'c' at row 1

            Roel Roel Van de Paar added a comment - Please also test with: SET sql_mode= '' ; CREATE TABLE t (a INT AUTO_INCREMENT, KEY (a)) ENGINE=MyISAM; INSERT INTO t VALUES (0),(3000000000); UPDATE t SET a=0; INSERT IGNORE INTO t SELECT a FROM t AS t2 ON DUPLICATE KEY UPDATE a=t.a; next_insert_id >= auto_inc_interval_for_cur_row.minimum()|SIGABRT|handler::update_auto_increment|ha_myisam::write_row|handler::ha_write_row|write_record There is also: INSTALL PLUGIN ARCHIVE SONAME 'ha_archive.so' ; SET SESSION insert_id=3000000000; CREATE TABLE t (c INT AUTO_INCREMENT KEY ) ENGINE=Archive; INSERT INTO t VALUES (0),(1); next_insert_id >= auto_inc_interval_for_cur_row.minimum()|SIGABRT|handler::update_auto_increment|ha_archive::write_row|handler::ha_write_row|write_record Here, the assert does not fire for other storage engines and instead we see (using InnoDB for example): CS 11.2.6 12a91b57e27b979819924cf89614e6e51f24b37b (Debug) 11.2.6-dbg>INSERT INTO t VALUES (0),(1); ERROR 167 (22003): Out of range value for column 'c' at row 1

            People

              sanja Oleksandr Byelkin
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.