Bug #38499 flush tables and multitable table update with derived table cause crash

• Submitted: 31 Jul 2008 16:38
• Modified: 17 Oct 2008 18:21
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 5.0.66a,mysql-5.1.26-rc-linux-i686-glibc23, 5.1-bzr
• OS: Any
• Assigned to: Gleb Shchepa
• CPU Architecture: Any

[31 Jul 2008 16:38] Shane Bester

Description:
stored procedure with an update in this form caused a crash.

update t1,(select distinct x from t2 inner join t3 on a=b
inner join t4 on g=h and .... where ....
union select distinct ....)
set f=g where .....

0x00cdb402 in ?? ()                                                         
0x00de5017 in pthread_kill                                                  
0x082d27d3 in write_core                                                    
0x081ca061 in handle_segfault                                               
0x00cdb420 in ?? ()                                                         
0x0000000b in ?? ()                                                         
0x0822fd69 in build_equal_items_for_cond             at sql_select.cc:7495  
0x0822b930 in build_equal_items                      at sql_select.cc:7784  
0x082221e1 in optimize_cond                          at sql_select.cc:8869  
0x08214506 in JOIN::optimize                         at sql_select.cc:821   
0x082d1b60 in st_select_lex_unit::exec               at sql_union.cc:480    
0x082d252d in mysql_derived_filling                  at sql_derived.cc:243  
0x082d2184 in mysql_handle_derived                   at sql_derived.cc:56   
0x08242b6e in mysql_multi_update_prepare             at sql_update.cc:1142  
0x081d6116 in mysql_execute_command                  at sql_parse.cc:2865   
0x082e4d66 in sp_instr_stmt::exec_core               at sp_head.cc:2892     
0x082e4abe in sp_lex_keeper::reset_lex_and_exec_core at sp_head.cc:2721     
0x082e4c33 in sp_instr_stmt::execute                 at sp_head.cc:2835     
0x082e2185 in sp_head::execute                       at sp_head.cc:1245     
0x082e3496 in sp_head::execute_procedure             at sp_head.cc:1970     
0x081d9382 in mysql_execute_command                  at sql_parse.cc:4119   
0x081db911 in mysql_parse                            at sql_parse.cc:5645   
0x081d424e in dispatch_command                       at sql_parse.cc:1137   
0x081d3dd3 in do_command                             at sql_parse.cc:794    
0x081d2301 in handle_one_connection                  at sql_connect.cc:1115 
0x00de0402 in start_thread                                                  
0x001f72ae in clone                                                         

see attached file for core file details.

How to repeat:
very difficult

[31 Jul 2008 20:54] MySQL Verification Team

bug found!  setup a table like this:

drop table if exists `t1`;
create table `t1`(`a` int,unique(`a`))engine=myisam;
replace into `t1` values (1),(2),(3),(4);

now run the following statements in >1 threads

update `t1`, (select 1 from `t1`) `d` set `a` = 0 where 1=0;
flush tables;

crash happens soon.

[31 Jul 2008 21:11] MySQL Verification Team

testcase.

Attachment: bug38499.c (text/plain), 5.80 KiB.

[18 Sep 2008 15:11] Martin Hansson

What happens is that all table are put in the table cache. As they are retrieved we should run setup_tables() on all of them, but for some reason this is not done for nested query tables (st_select_lex_unit::prepared == TRUE for the nested query)

[20 Sep 2008 8: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/54402

2690 Martin Hansson	2008-09-20
      Bug#38499: flush tables and multitable table update with 
      derived table cause crash
      
      When a multi-UPDATE command fails to lock some table, and
      subsequently succeeds, the tables need to be reopened if
      they were altered. But the reopening procedure failed for
      derived tables.
      Fixed by resetting the 'prepared' flag on derived tables
      during the mentioned procedure, which causes them to be 
      reopened.

[20 Sep 2008 9:24] 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/54406

2690 Martin Hansson	2008-09-20
      Bug#38499: flush tables and multitable table update with 
      derived table cause crash
      
      When a multi-UPDATE command fails to lock some table, and
      subsequently succeeds, the tables need to be reopened if
      they were altered. But the reopening procedure failed for
      derived tables.
      Fixed by resetting the 'prepared' flag on derived tables
      during the mentioned procedure, which causes them to be 
      reopened.

[20 Sep 2008 11:44] Martin Hansson

This is a runtime bug, changing lead and reviewer.

[21 Sep 2008 5:52] Dmitry Lenev

Please note that very similar bug #37894 is currently being worked on by Gleb. I believe both these bugs are different facets of the same problem (improper cleanup of statements structures after failed attempt to lock tables in multi-update) and therefore should be fixed by one patch.

[22 Sep 2008 6:42] Martin Hansson

Do you mean Bug#38691?

[7 Oct 2008 18:16] Paul DuBois

Noted in 5.1.29 changelog.

A server crash resulted from execution of an UPDATE that used a
derived table together with FLUSH TABLES. 

Leaving report status unchanged; this is early documentation of an upcoming push into 5.1.29.

[8 Oct 2008 18:09] 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/55798

2690 Gleb Shchepa	2008-10-08
      Bug#38499: flush tables and multitable table update with  
                 derived table cause crash
      
      When a multi-UPDATE command fails to lock some table, and
      subsequently succeeds, the tables need to be reopened if
      they were altered. But the reopening procedure failed for
      derived tables.
      
      Extra cleanup has been added.

[8 Oct 2008 21:08] 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/55831

2703 Gleb Shchepa	2008-10-08
      Bug#38499: flush tables and multitable table update with 
                 derived table cause crash
      
      When a multi-UPDATE command fails to lock some table, and
      subsequently succeeds, the tables need to be reopened if
      they were altered. But the reopening procedure failed for
      derived tables.
      
      Extra cleanup has been added.

[9 Oct 2008 14:50] 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/55947

2703 Gleb Shchepa	2008-10-09
      Bug#38499: flush tables and multitable table update with
                 derived table cause crash
      
      When a multi-UPDATE command fails to lock some table, and
      subsequently succeeds, the tables need to be reopened if
      they were altered. But the reopening procedure failed for
      derived tables.
      
      Extra cleanup has been added.

[9 Oct 2008 15:25] 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/55953

2703 Gleb Shchepa	2008-10-09
      Bug#38499: flush tables and multitable table update with
                 derived table cause crash
      
      When a multi-UPDATE command fails to lock some table, and
      subsequently succeeds, the tables need to be reopened if
      they were altered. But the reopening procedure failed for
      derived tables.
      
      Extra cleanup has been added.

[9 Oct 2008 17:24] Bugs System

Pushed into 5.0.72  (revid:[e-mail]-20081009152431-2b8p1vvat6n95pta) (version source revid:[e-mail]-20081009152431-2b8p1vvat6n95pta) (pib:4)

[9 Oct 2008 17:30] Bugs System

Pushed into 5.1.29  (revid:[e-mail]-20081009152431-2b8p1vvat6n95pta) (version source revid:[e-mail]-20081009155741-c4lu3fjm3c2o1qdp) (pib:4)

[9 Oct 2008 18:18] Paul DuBois

Setting report to NDI pending push into 6.0.x.

[17 Oct 2008 16:44] Bugs System

Pushed into 6.0.8-alpha  (revid:[e-mail]-20081009152431-2b8p1vvat6n95pta) (version source revid:[e-mail]-20081009161303-q1mfoo15sbrs9iqt) (pib:5)

[17 Oct 2008 18:21] Paul DuBois

Noted in 6.0.8 changelog.

[28 Oct 2008 21:04] Bugs System

Pushed into 5.1.29-ndb-6.2.17  (revid:[e-mail]-20081009152431-2b8p1vvat6n95pta) (version source revid:[e-mail]-20081028140209-u4emkk1xphi5tkfb) (pib:5)

[28 Oct 2008 22:22] Bugs System

Pushed into 5.1.29-ndb-6.3.19  (revid:[e-mail]-20081009152431-2b8p1vvat6n95pta) (version source revid:[e-mail]-20081028194045-0353yg8cvd2c7dd1) (pib:5)

[1 Nov 2008 9:48] Bugs System

Pushed into 5.1.29-ndb-6.4.0  (revid:[e-mail]-20081009152431-2b8p1vvat6n95pta) (version source revid:[e-mail]-20081101082305-qx5a1bj0z7i8ueys) (pib:5)

[6 Jul 2009 10: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/77998

2794 Georgi Kodinov	2009-07-06
      Bug #38499 : test marked as a big test : takes approx 275 secs on a P4 2.4
      GHz.

[6 Jul 2009 10:32] 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/78006

3001 Georgi Kodinov	2009-07-06 [merge]
      null-merge the mark of bug #38499 to big_test : it's running faster on 5.1

[7 Jul 2009 7:52] Bugs System

Pushed into 5.0.84 (revid:[e-mail]-20090707074938-ksah1ibn0vs92cem) (version source revid:[e-mail]-20090706101540-4drfye92xoywzyfc) (merge vers: 5.0.84) (pib:11)

[8 Jul 2009 13:30] Bugs System

Pushed into 5.1.37 (revid:[e-mail]-20090708131116-kyz8iotbum8w9yic) (version source revid:[e-mail]-20090706103025-g3axz6f6gal6ijap) (merge vers: 5.1.37) (pib:11)

[9 Jul 2009 7:35] Bugs System

Pushed into 5.0.84 (revid:[e-mail]-20090707074938-ksah1ibn0vs92cem) (version source revid:[e-mail]-20090706101540-4drfye92xoywzyfc) (merge vers: 5.0.84) (pib:11)

[9 Jul 2009 7:37] Bugs System

Pushed into 5.1.37 (revid:[e-mail]-20090708131116-kyz8iotbum8w9yic) (version source revid:[e-mail]-20090706103025-g3axz6f6gal6ijap) (merge vers: 5.1.37) (pib:11)

[10 Jul 2009 11:20] Bugs System

Pushed into 5.4.4-alpha (revid:[e-mail]-20090710111017-bnh2cau84ug1hvei) (version source revid:[e-mail]-20090706104514-4dj1xron15x44x6h) (merge vers: 5.4.4-alpha) (pib:11)

[26 Aug 2009 13:45] Bugs System

Pushed into 5.1.37-ndb-7.0.8 (revid:[e-mail]-20090826132541-yablppc59e3yb54l) (version source revid:[e-mail]-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

[26 Aug 2009 13:46] Bugs System

Pushed into 5.1.37-ndb-6.3.27 (revid:[e-mail]-20090826105955-bkj027t47gfbamnc) (version source revid:[e-mail]-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

[26 Aug 2009 13:48] Bugs System

Pushed into 5.1.37-ndb-6.2.19 (revid:[e-mail]-20090825194404-37rtosk049t9koc4) (version source revid:[e-mail]-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

[27 Aug 2009 16:32] Bugs System

Pushed into 5.1.35-ndb-7.1.0 (revid:[e-mail]-20090827163030-6o3kk6r2oua159hr) (version source revid:[e-mail]-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)