Bug #43054 Assertion `!table->auto_increment_field_not_null' failed when redefining trigger
Submitted: 20 Feb 2009 11:28 Modified: 12 Nov 2009 18:40
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S1 (Critical)
Version:6.0-bzr,5.4 OS:Any
Assigned to: Magne Mæhre CPU Architecture:Any
Tags: regression

[20 Feb 2009 11:28] Philip Stoev 
Description:
Bug #38821 is back with a vengance. When trying to create triggers between CREATE TABLE statements, mysqld asserted as follows:

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

/lib64/libc.so.6(__assert_fail+0xe9) [0x315a42bec9]
/build/bzr/mysql-6.0/sql/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, enum_open_table_action*, unsigned int)+0x1059) [0x72d27a]
/build/bzr/mysql-6.0/sql/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x42f) [0x72dd32]
/build/bzr/mysql-6.0/sql/mysqld(open_and_lock_tables_derived(THD*, TABLE_LIST*, bool, unsigned int)+0x64) [0x72e47c]
/build/bzr/mysql-6.0/sql/mysqld(open_n_lock_single_table(THD*, TABLE_LIST*, thr_lock_type, unsigned int)+0x76) [0x72e7dc]
/build/bzr/mysql-6.0/sql/mysqld(mysql_create_or_drop_trigger(THD*, TABLE_LIST*, bool)+0x451) [0x8bf9d1]
/build/bzr/mysql-6.0/sql/mysqld(mysql_execute_command(THD*)+0x7fe6) [0x6dc0ca]
/build/bzr/mysql-6.0/sql/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x273) [0x6dcc1d]
/build/bzr/mysql-6.0/sql/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xa54) [0x6dd808]
/build/bzr/mysql-6.0/sql/mysqld(do_command(THD*)+0x22b) [0x6ded31]
/build/bzr/mysql-6.0/sql/mysqld(handle_one_connection+0x11a) [0x6cccb1]

How to repeat:
--disable_abort_on_error

CREATE TABLE B (
        pk INTEGER AUTO_INCREMENT,
        int_key INTEGER NOT NULL,
        PRIMARY KEY (pk),
        KEY (int_key)
);

INSERT IGNORE INTO B VALUES ('9', '9');

CREATE TABLE IF NOT EXISTS t1 ( `pk` INTEGER NOT NULL AUTO_INCREMENT , `int` INTEGER , PRIMARY KEY ( `pk` ) ) SELECT `pk` , `int_key` FROM B ;

DELIMITER |;
CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW BEGIN INSERT INTO t1 ( `int` ) VALUES ( 4 ) , ( 8 ) , ( 2 ) ; END ; |
DELIMITER ;|

CREATE TABLE IF NOT EXISTS t1 ( `pk` INTEGER NOT NULL AUTO_INCREMENT , `int` INTEGER , PRIMARY KEY ( `pk` ) ) SELECT `pk` , `int_key` FROM B ;

DELIMITER |;
CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW BEGIN UPDATE A SET `pk` = 1 WHERE `pk` = 0 ; END ;|
[20 Feb 2009 12:37] Sveta Smirnova 
Thank you for the report.

Verified as described. Version 5.1 is not affected.
[6 May 2009 21:13] 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/73539

2774 Magne Mahre	2009-05-06
      Bug #43054 Assertion `!table->auto_increment_field_not_null' failed when redefining trigger
      
      The 'table->auto_increment_field_not_null' flag is only valid within
      processing of a single row, and should be set to FALSE before
      navigating to the next row, or exiting the operation.
      
      This bug was caused by an SQL error occuring while executing a trigger
      after the flag had been set, so the normal resetting was bypassed.
      The table object was then returned to the table share's cache in
      a dirty condition.   When the table object was reused, an assert
      caught that the flag was set.
      
      This patch explicitly resets the flag if there is an error in 
      store_values, and before the table object is returned to the cache.
[12 May 2009 21:12] 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/73873

2775 Magne Mahre	2009-05-12
      Bug #43054 Assertion `!table->auto_increment_field_not_null' failed when 
                 redefining trigger
            
      The 'table->auto_increment_field_not_null' flag is only valid within
      processing of a single row, and should be set to FALSE before
      navigating to the next row, or exiting the operation.
            
      This bug was caused by an SQL error occuring while executing a trigger
      after the flag had been set, so the normal resetting was bypassed.
      The table object was then returned to the table share's cache in
      a dirty condition.   When the table object was reused, an assert
      caught that the flag was set.
            
      This patch explicitly resets the flag on error and after write_record.
[14 May 2009 21:11] Guilhem Bichot 
my plan is to review only after Evgeny has approved.
[28 May 2009 19:15] 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/75190

2792 Magne Mahre	2009-05-28
      Bug #43054 Assertion `!table->auto_increment_field_not_null' failed when 
                 redefining trigger
                  
      The 'table->auto_increment_field_not_null' flag is only valid within
      processing of a single row, and should be set to FALSE before
      navigating to the next row, or exiting the operation.
                  
      This bug was caused by an SQL error occuring while executing a trigger
      after the flag had been set, so the normal resetting was bypassed.
      The table object was then returned to the table share's cache in
      a dirty condition.   When the table object was reused, an assert
      caught that the flag was set.
                  
      This patch explicitly clears the flag on error/abort.
[28 May 2009 20:51] 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/75199

2792 Magne Mahre	2009-05-28
      Bug #43054 Assertion `!table->auto_increment_field_not_null' failed when 
                 redefining trigger
                        
      The 'table->auto_increment_field_not_null' flag is only valid within
      processing of a single row, and should be set to FALSE before
      navigating to the next row, or exiting the operation.
                        
      This bug was caused by an SQL error occuring while executing a trigger
      after the flag had been set, so the normal resetting was bypassed.
      The table object was then returned to the table share's cache in
      a dirty condition.   When the table object was reused, an assert
      caught that the flag was set.
                        
      This patch explicitly clears the flag on error/abort.
[28 May 2009 21:30] 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/75200

2790 Magne Mahre	2009-05-28
      Bug #43054 Assertion `!table->auto_increment_field_not_null' failed when                  
                 redefining trigger
      
      The 'table->auto_increment_field_not_null' flag is only valid within
      processing of a single row, and should be set to FALSE before
      navigating to the next row, or exiting the operation.
      
      This bug was caused by an SQL error occuring while executing a trigger
      after the flag had been set, so the normal resetting was bypassed.
      The table object was then returned to the table share's cache in
      a dirty condition.   When the table object was reused, an assert
      caught that the flag was set.
      
      This patch explicitly clears the flag on error/abort.
[9 Jul 2009 8:58] Alexander Nozdrin 
Pushed into azalea-bugfixing.
[9 Jul 2009 9:10] Alexander Nozdrin 
Merged into azalea (5.4.4-alpha).
[17 Jul 2009 3:13] Paul DuBois 
Noted in 5.4.4 changelog.

Redefining a trigger could cause an assertion failure.
[12 Aug 2009 22:42] Paul DuBois 
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 1:58] Paul DuBois 
Ignore previous comment about 5.4.2.
[15 Oct 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/86975

2927 Magne Mahre	2009-10-15
      Bug #43054 Assertion `!table->auto_increment_field_not_null' failed when            
                 redefining trigger
            
      The 'table->auto_increment_field_not_null' flag is only valid within
      processing of a single row, and should be set to FALSE before
      navigating to the next row, or exiting the operation.
            
      This bug was caused by an SQL error occuring while executing a trigger
      after the flag had been set, so the normal resetting was bypassed.
      The table object was then returned to the table share's cache in
      a dirty condition.   When the table object was reused, an assert
      caught that the flag was set.
            
      This patch explicitly clears the flag on error/abort.
      
      
      Backported from mysql-6.0-codebase  revid: 2617.52.1
[15 Oct 2009 12:53] Magne Mæhre 
Backported to 5.5.0
[15 Oct 2009 19:30] Paul DuBois 
Noted in 5.5.0 changelog.
[3 Nov 2009 7:16] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@sun.com-20091023064702-2f8jdmny61bdl94u) (merge vers: 6.0.14-alpha) (pib:13)
[3 Nov 2009 17:54] Paul DuBois 
Noted in 6.0.14 changelog.
[12 Nov 2009 8:17] Bugs System 
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)