Bug #45949 Assertion `!tables->table' in open_tables() on ALTER + INSERT DELAYED
Submitted: 4 Jul 2009 19:19 Modified: 7 Mar 2010 12:40
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.4 OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any

[4 Jul 2009 19:19] Philip Stoev
Description:
When executing a random workload containing ALTER + INSERT DELAYED, 5.4 asserted as follows:

mysqld: sql_base.cc:3746: int open_tables(THD*, TABLE_LIST**, uint*, uint): Assertion `!tables->table' failed.

# 22:16:00 #2  0x000000000066a938 in handle_segfault (sig=6) at mysqld.cc:2715
# 22:16:00 #3  <signal handler called>
# 22:16:00 #4  0x000000315a432ed5 in raise () from /lib64/libc.so.6
# 22:16:00 #5  0x000000315a434a43 in abort () from /lib64/libc.so.6
# 22:16:00 #6  0x000000315a42bec9 in __assert_fail () from /lib64/libc.so.6
# 22:16:00 #7  0x00000000006d70b7 in open_tables (thd=0x1e60228, start=0x7f73e3fa9f60, counter=0x7f73e3fa9f94, flags=0) at sql_base.cc:3746
# 22:16:00 #8  0x00000000006d78f4 in open_and_lock_tables_derived (thd=0x1e60228, tables=0x1e62f58, derived=false, flags=0) at sql_base.cc:4227
# 22:16:00 #9  0x00000000006d7c54 in open_n_lock_single_table (thd=0x1e60228, table_l=0x1e62f58, lock_type=TL_WRITE_DELAYED, flags=0) at sql_base.cc:4074
# 22:16:00 #10 0x00000000007250b2 in Delayed_insert::open_and_lock_table (this=0x1e60208) at sql_insert.cc:2304
# 22:16:00 #11 0x00000000007287ab in handle_delayed_insert (arg=0x1e60208) at sql_insert.cc:2491
# 22:16:00 #12 0x000000315b0073da in start_thread () from /lib64/libpthread.so.0
# 22:16:00 #13 0x000000315a4e627d in clone () from /lib64/libc.so.6

The stack trace and the workload are very similar to bug #45067, so this may be just another manifestation of it. 5.1 does not appear to be affected.

How to repeat:
A test case will be uploaded shortly.
[4 Jul 2009 19:21] Philip Stoev
Grammar file for bug 45949

Attachment: bug45949.yy (application/octet-stream, text), 103 bytes.

[4 Jul 2009 19:26] Philip Stoev
To reproduce, please branch the mysql-test-extra-6.0 tree and cd to mysql-test/gentest. Then run:

$ perl runall.pl --basedir=/build/bzr/azalea --grammar=/tmp/bug45949.yy

You may also get bug 45949 and a bogus "Server shutdown in progress" error (to be filed separately), so you may need to run the test case a few times.

A related mysqltest test case is available from bug #45067.
[4 Aug 2009 19:50] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:iggy@mysql.com-20090731204544-7nio1afvg0dmzs7g) (merge vers: 5.4.4-alpha) (pib:11)
[9 Aug 2009 14:37] Jon Olav Hauglid
Findings so far:

1) ALTER TABLE terminates INSERT DELAYED using mysql_notify_thread_having_shared_lock()
This sets the delayed insert thread´s thd.killed == KILL_CONNECTION and fills its diagnostic area with "Server shutdown in progress".

2) delayed_get_table() notices that di->thd.killed has been set and 
copies the error message from the di->thd.stmt_da to the diagnostics area
of the connection thread thd using 
 my_message(di->thd.stmt_da->sql_errno(), di->thd.stmt_da->message(), MYF(0));

3) The client notices the error and mysqltest sends COM_QUIT to close the connection.
[13 Aug 2009 10: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/80751

2856 Jon Olav Hauglid	2009-08-13
      Bug #45949 Assertion `!tables->table' in open_tables() on 
                 ALTER + INSERT DELAYED
      
      The assertion was caused by improperly closing tables when 
      INSERT DELAYED needed to reopen tables. This patch replaces
      the call to close_thread_tables with close_tables_for_reopen
      which fixes the problem.
      
      The only way I was able to trigger the reopen code path and 
      thus the assertion, was if ALTER TABLE killed the delayed
      insert thread and the delayed insert thread was able to enter
      the reopen code path before it noticed that thd->killed had
      been set. Note that in these cases reopen will always fail 
      since open_table() will check thd->killed and return. This patch
      therefore adds two more thd->killed checks to minimize the 
      chance of entering the reopen code path without hope for success.
      
      The patch also changes the killed_state used when aborting INSERT
      DELAYED. It used to be KILL_CONNECTION which is mapped to 
      ER_SERVER_SHUTDOWN. It has been changed to the more appropriate
      KILL_QUERY (=ER_QUERY_INTERRUPTED).
      
      No test case is supplied. This is for two reasons:
      1) Unable to reproduce the error without having the delayed insert
      thread in a killed state which means that reopen is futile and
      was not supposed to be attempted.
      2) Difficulty of using sync points in other threads than 
      the connection thread.
      The patch has been successfully tested with the RQG and the grammar
      supplied in the bug description.
[19 Aug 2009 12:16] 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/81066

2866 Jon Olav Hauglid	2009-08-19
      Bug #45949 Assertion `!tables->table' in open_tables() on 
                 ALTER + INSERT DELAYED
      
      The assertion was caused by improperly closing tables when 
      INSERT DELAYED needed to reopen tables. This patch replaces
      the call to close_thread_tables with close_tables_for_reopen
      which fixes the problem.
      
      The only way I was able to trigger the reopen code path and 
      thus the assertion, was if ALTER TABLE killed the delayed
      insert thread and the delayed insert thread was able to enter
      the reopen code path before it noticed that thd->killed had
      been set. Note that in these cases reopen will always fail 
      since open_table() will check thd->killed and return. This patch
      therefore adds two more thd->killed checks to minimize the 
      chance of entering the reopen code path without hope for success.
      
      The patch also changes the killed_state used when aborting INSERT
      DELAYED. It used to be KILL_CONNECTION which is mapped to 
      ER_SERVER_SHUTDOWN. It has been changed to the more appropriate
      KILL_QUERY (=ER_QUERY_INTERRUPTED).
      
      No test case is supplied. This is for two reasons:
      1) Unable to reproduce the error without having the delayed insert
      thread in a killed state which means that reopen is futile and
      was not supposed to be attempted.
      2) Difficulty of using sync points in other threads than 
      the connection thread.
      The patch has been successfully tested with the RQG and the grammar
      supplied in the bug description.
[19 Aug 2009 13:51] Konstantin Osipov
Approved by email (with some edits of the patch).
[19 Aug 2009 14: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/81086

2870 Jon Olav Hauglid	2009-08-19
      Bug #45949 Assertion `!tables->table' in open_tables() on 
                 ALTER + INSERT DELAYED
      
      The assertion was caused by improperly closing tables when 
      INSERT DELAYED needed to reopen tables. This patch replaces
      the call to close_thread_tables with close_tables_for_reopen
      which fixes the problem.
      
      The only way I was able to trigger the reopen code path and 
      thus the assertion, was if ALTER TABLE killed the delayed
      insert thread and the delayed insert thread was able to enter
      the reopen code path before it noticed that thd->killed had
      been set. Note that in these cases reopen will always fail 
      since open_table() will check thd->killed and return. This patch
      therefore adds two more thd->killed checks to minimize the 
      chance of entering the reopen code path without hope for success.
      
      The patch also changes it so that if the delayed insert is killed
      using KILL_CONNECTION, the error message that is copied to the
      connection thread is ER_QUERY_INTERRUPTED rather than 
      ER_SERVER_SHUTDOWN. This means that if INSERT DELAYED fails, 
      the user will now see "Query execution was interrupted" rather
      than the misleading "Server shutdown in progress".
      
      No test case is supplied. This is for two reasons:
      1) Unable to reproduce the error without having the delayed insert
      thread in a killed state which means that reopen is futile and
      was not supposed to be attempted.
      2) Difficulty of using sync points in other threads than 
      the connection thread.
      The patch has been successfully tested with the RQG and the grammar
      supplied in the bug description.
[19 Aug 2009 15:49] Jon Olav Hauglid
Pushed into mysql-next-bugfixing
[24 Aug 2009 13:53] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090824135126-2rngffvth14a8bpj) (version source revid:jon.hauglid@sun.com-20090819145121-46k2r8gou2493kn1) (merge vers: 5.4.4-alpha) (pib:11)
[26 Aug 2009 2:23] Paul Dubois
Noted when 5.4.4 changelog.

Improperly closing tables when INSERT DELAYED needed to reopen tables
could cause an assertion failure.
[9 Dec 2009 11:52] Jon Olav Hauglid
Pushed to mysql-next-4284 (5.6.0-beta).
[16 Feb 2010 16:46] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:jon.hauglid@sun.com-20091217143610-t3t1lz1jcda1pney) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:56] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:jon.hauglid@sun.com-20091217124307-glwitkbztcmh8ajr) (pib:16)
[17 Feb 2010 0:48] Paul Dubois
Noted in 6.0.14 changelog.

Setting report to Need Merge pending push of Celosia into release tree.
[6 Mar 2010 11:06] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[7 Mar 2010 12:40] Paul Dubois
Noted in 5.5.3 changelog.