MySQL Bugs: #34660: crash when federated table loses connection during insert ... select

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

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

full stack trace from 5.0.56 on windows

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

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..)

testcase that crashes server. run against 127.0.0.1

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

verified on linux and windows.

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

Contribution patch file for fixing this problem.

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

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!

I signed SCA already.

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!

Kentoku, thanks a lot for your patch!

However, I have to make a closer investigation of the problem.
Hope to commit a patch shortly.

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

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.

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!

"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.