Bug #34660 crash when federated table loses connection during insert ... select
Submitted: 19 Feb 2008 11:49 Modified: 8 Oct 2011 16:07
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.1.56, 5.0.56, 5.5.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution
Triage: Triaged: D1 (Critical) / R3 (Medium) / E3 (Medium)

[19 Feb 2008 11:49] Shane Bester
Description:
while performing a query against a federated table such as:

insert into federated_table select * from local_table where not exists (select 1 from federated_table where a=b)

and the network connection is broken, or the remote federated link is killed, a server crash can occur:

mysqld-debug.exe!do_select
mysqld-debug.exe!JOIN::exec
mysqld-debug.exe!subselect_single_select_engine::exec
mysqld-debug.exe!Item_subselect::exec
mysqld-debug.exe!Item_exists_subselect::val_bool
mysqld-debug.exe!Item_func_not::val_int
mysqld-debug.exe!evaluate_join_record
mysqld-debug.exe!sub_select
mysqld-debug.exe!do_select
mysqld-debug.exe!JOIN::exec
mysqld-debug.exe!mysql_select
mysqld-debug.exe!handle_select
mysqld-debug.exe!mysql_execute_command
mysqld-debug.exe!mysql_parse
mysqld-debug.exe!dispatch_command
mysqld-debug.exe!do_command
mysqld-debug.exe!handle_one_connection
mysqld-debug.exe!pthread_start
mysqld-debug.exe!_threadstart
kernel32.dll!FlsSetValue

in the error logs we have many errors similar to this:

080219 13:40:32 [ERROR] Got error 1430 when reading table './test/t1'
080219 13:40:32 [ERROR] Got error 1430 when reading table './test/t1'
080219 13:40:34 [ERROR] Got error 1430 when reading table './test/t1'

How to repeat:
perform some insert .. select queries and kill the connections rapidly. will upload testcase later.

Suggested fix:
improve error checking
[19 Feb 2008 11:56] Shane Bester
full stack trace from 5.0.56 on windows

Attachment: bug34660_stack_trace.txt (text/plain), 2.60 KiB.

[19 Feb 2008 13:01] Shane Bester
the testcase is simple to setup on localhost:
-----------------------------------------------
use test;
drop table if exists `t1_local`;
drop table if exists `t1_fed`;
create table `t1_local` (`c1` int,key (`c1`)) engine=myisam;
create table `t1_fed` (`c1` int,key (`c1`)) engine=federated connection='mysql://root:12345@127.0.0.1:3306/test/t1_local';
insert into `t1_fed`(`c1`) values (1),(2),(3),(4),(5),(6);

now, run this query over and over:

insert ignore into `t1_fed` (`c1`) select `c1` from  `t1_local` a where not exists (select 1 from `t1_fed` b where a.c1 = b.c1);

in another connection kill the above query over and over.  A crash happens once in a few hundred kills (get the timing right..)
[19 Feb 2008 13:38] Shane Bester
testcase that crashes server. run against 127.0.0.1

Attachment: bug34660.c (text/plain), 7.08 KiB.

[19 Feb 2008 18:55] Shane Bester
verified on linux and windows.
[14 Nov 2008 20:10] Omer Barnir
triage: Updating tag from SR60GA to SR60BETA based on current guidelines (risk) as the SR60GA tag was set before there were criteria for the 6.0 release phases and bugs should not be set to SR60GA when we are still in alpha
[6 Aug 2009 15:21] kentoku shiba
Contribution patch file for fixing this problem.

Attachment: mysql-5.1.37.bug34660.diff (, text), 454 bytes.

[11 Aug 2009 16:29] Lenz Grimmer
Adding the "Contribution" tag. Thanks for the patch, Kentoku-san!
Before we can apply your contribution, we require a small formality from your side - can you please sign and return the Sun Contributor Agreement (SCA) as outlined on our Wiki?

  http://forge.mysql.com/wiki/Contributing_Code
  http://forge.mysql.com/wiki/Sun_Contributor_Agreement

Once the paperwork has been taken care of, we can focus on the technical issues. Thanks in advance for your support!
[11 Aug 2009 16:38] kentoku shiba
I signed SCA already.
[11 Aug 2009 16:52] Lenz Grimmer
My apologies - I should have looked more carefully. I confirm that you have been added to the SCA signatory list: https://sca.dev.java.net/CA_signatories.htm#s

So everything is fine from the paperwork side already :)

Ramil, it would be great if you could take a look into Kentoku's patch and perform a review. Thanks in advance!
[15 Aug 2009 5:14] Ramil Kalimullin
Kentoku, thanks a lot for your patch!

However, I have to make a closer investigation of the problem.
Hope to commit a patch shortly.
[31 Jan 2010 16:22] Steven Hartland
Just had what I believe is this bug crash our main db 3 times in a row.

Slightly different symptoms I believe, in that the user specified didnt have permission to access the target table.

Query which caused the crash was a simple join between a local table and the federated table
[1 Apr 2011 11:44] Shane Bester
just noting that 5.5.10 still crashes using the .c testcase.

Attachment: bug34660_5.5.10_crash_windows.txt (text/plain), 8.80 KiB.

[23 Jun 2011 7:56] Shane Bester
still crashes 5.6.3, 5.5.15, 5.1.57.  killing queries relating to federated tables is common, since federated has a nasty habit of taking up gigabytes of ram since it likes to pull entire tables into a resultset in memory!
[11 Sep 2012 13:01] Shane Bester
"Noted in 5.1.60, 5.5.18, 5.6.4 changelogs."

You should go to dev.mysql.com and download the recent version, which is fixed.