Bug #46616 Assertion `!table->auto_increment_field_not_null' on view manipulations
Submitted: 8 Aug 2009 10:48 Modified: 18 Sep 2009 19:54
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.1,5.4 OS:Any
Assigned to: Martin Hansson CPU Architecture:Any
Triage: Triaged: D1 (Critical)

[8 Aug 2009 10:48] Philip Stoev
Description:
After a few fairly simple manpulations (DDL, DML) on views and their base tables, mysqld asserted as follows:

mysqld: sql_base.cc:2968: TABLE* open_table(THD*, TABLE_LIST*, MEM_ROOT*, bool*, uint): Assertion `!table->auto_increment_field_not_null' failed.

#6  0x000000315a42bec9 in __assert_fail () from /lib64/libc.so.6
#7  0x00000000006a9606 in open_table (thd=0x7fd8c002d078, table_list=0x16e0b08, mem_root=0x7fd8c772e280, refresh=0x7fd8c772e30f, flags=0) at sql_base.cc:2968
#8  0x00000000006aa13a in open_tables (thd=0x7fd8c002d078, start=0x7fd8c772e380, counter=0x7fd8c772e3b4, flags=0) at sql_base.cc:4572
#9  0x00000000006aaa3c in open_and_lock_tables_derived (thd=0x7fd8c002d078, tables=0x16e0558, derived=true) at sql_base.cc:4978
#10 0x000000000066045b in open_and_lock_tables (thd=0x7fd8c002d078, tables=0x16e0558) at mysql_priv.h:1555
#11 0x0000000000657149 in mysql_execute_command (thd=0x7fd8c002d078) at sql_parse.cc:3177
#12 0x000000000065d3eb in mysql_parse (thd=0x7fd8c002d078, inBuf=0x16e0408 "INSERT  INTO  t1_view_0_A  (`int_key`) SELECT `int_key` FROM t1_base_0_A",
    length=72, found_semicolon=0x7fd8c772fed0) at sql_parse.cc:5931
#13 0x000000000065e215 in dispatch_command (command=COM_QUERY, thd=0x7fd8c002d078,
    packet=0x7fd8c0081609 "INSERT  INTO  t1_view_0_A  (`int_key`) SELECT `int_key` FROM t1_base_0_A", packet_length=72) at sql_parse.cc:1213
#14 0x000000000065f5e4 in do_command (thd=0x7fd8c002d078) at sql_parse.cc:854
#15 0x000000000064bf15 in handle_one_connection (arg=0x7fd8c002d078) at sql_connect.cc:1127
#16 0x000000315b0073da in start_thread () from /lib64/libpthread.so.0
#17 0x000000315a4e627d in clone () from /lib64/libc.so.6

How to repeat:
--disable_abort_on_error

CREATE TABLE `table0_int_autoinc` ( `int_key` int, pk integer auto_increment, `int` int,        key (`int_key` ), primary key (pk));
CREATE TABLE `table1_int_autoinc` ( `int` int, `int_key` int, pk integer auto_increment,        key (`int_key` ), primary key (pk));
CREATE TABLE `table10_int_autoinc` ( pk integer auto_increment, `int_key` int, `int` int,       primary key (pk), key (`int_key` ));
INSERT IGNORE INTO table10_int_autoinc VALUES  (NULL, '1', '1') ,  (NULL, '1', '1') ,  (NULL, '1', '1') ,  (NULL, '1', '1') ,  (NULL, '1', '1') ,  (NULL, '1', '1') ,  (NULL, '1', '1') ,  (NULL, '1', '1') ,  (NULL, '1', '1') ,  (NULL, '1', '1');

CREATE TABLE IF NOT EXISTS t1_base_0_A LIKE test . table0_int_autoinc ;
CREATE TABLE IF NOT EXISTS t1_base_1_A LIKE test . table0_int_autoinc ;

CREATE VIEW t1_view_0_A  AS SELECT `int_key` FROM t1_base_1_A;

CREATE TABLE IF NOT EXISTS t1_base_3_A LIKE test . table10_int_autoinc ;
INSERT INTO t1_base_3_A (`int_key`) SELECT 0 FROM test . table10_int_autoinc ;

CREATE OR REPLACE VIEW t1_view_2_A  AS SELECT   `int_key` FROM t1_base_3_A;

DROP TABLE IF EXISTS t1_base_0_A;
UPDATE  t1_base_3_A  SET `int_key` = 3;

CREATE TABLE IF NOT EXISTS t1_base_0_A LIKE test . table1_int_autoinc ;

CREATE TABLE IF NOT EXISTS t1_base_0_A  AS SELECT   `int_key` FROM t1_view_2_A  AS A;
UPDATE   t1_base_0_A  SET `pk` = 9;
INSERT  INTO  t1_view_0_A  (`int_key`) SELECT `int_key` FROM t1_base_0_A;
[19 Aug 2009 12:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81069

3070 Martin Hansson	2009-08-19
      Bug#46616: Assertion `!table->auto_increment_field_not_null' on view 
      manipulations
      
      The bespoke flag was not properly reset after last call to fill_record.
      Fixed by resetting in caller mysql_update.
     @ mysql-test/r/auto_increment.result
        Bug#46616: Test result.
     @ mysql-test/t/auto_increment.test
        Bug#46616: Test case.
     @ sql/sql_update.cc
        Bug#46616: Fix.
[20 Aug 2009 11:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81162

2789 Martin Hansson	2009-08-20
      Bug#46616: Assertion `!table->auto_increment_field_not_null' on 
      view manipulations
            
      The bespoke flag was not properly reset after last call to 
      fill_record. Fixed by resetting in caller mysql_update.
     @ mysql-test/r/auto_increment.result
        Bug#46616: Test result.
     @ mysql-test/t/auto_increment.test
        Bug#46616: Test case.
     @ sql/sql_update.cc
        Bug#46616: Fix.
[20 Aug 2009 12:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81167

3070 Martin Hansson	2009-08-20 [merge]
      Bug#46616: Merge
     @ mysql-test/r/auto_increment.result
        Bug#46616: Test result.
     @ mysql-test/t/auto_increment.test
        Bug#46616: Test case.
     @ sql/sql_update.cc
        Bug#46616: Fix.
[2 Sep 2009 10:25] Bugs System
Pushed into 5.0.86 (revid:joro@sun.com-20090902102337-n5rw8227wwp5cpx8) (version source revid:joro@sun.com-20090821141203-nqxwu36mqyppltqr) (merge vers: 5.0.86) (pib:11)
[2 Sep 2009 16:42] Bugs System
Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:martin.hansson@sun.com-20090821101303-p41g0xhn9rjr5a2h) (merge vers: 5.1.39) (pib:11)
[14 Sep 2009 16:06] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[18 Sep 2009 19:54] Paul Dubois
Noted in 5.0.86, 5.1.39, 5.4.4 changelogs.

CREATE TABLE ... SELECT could cause assertion failure if a table
already existed with the same name and contained an AUTO_INCREMENT
column.
[1 Oct 2009 5:59] Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[2 Oct 2009 1:29] Paul Dubois
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[5 Oct 2009 10:50] Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)