Bug #51840 infinite hang with 100% cpu usage with load data local and import errors
Submitted: 8 Mar 2010 19:13 Modified: 21 Mar 2014 22:01
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:4.0.24,4.1.25,5.0.90,5.1.44,5.5.2-m2 OS:Any
Assigned to: CPU Architecture:Any
Tags: hang, load data local
Triage: Triaged: D2 (Serious)

[8 Mar 2010 19:13] Shane Bester
Description:
cpu flatlines at 100% when using load data local syntax into a childless merge table.  local keyword is needed for this.

How to repeat:
drop table if exists `t1`;
create table `t1`(`a` int)engine=mrg_myisam;
load data local infile 'data.bin' into table `t1`
fields optionally enclosed by '' escaped by ''
lines terminated by '' (`a`);
[8 Mar 2010 19:14] Shane Bester
data.bin

Attachment: data.bin (application/octet-stream, text), 43.34 KiB.

[8 Mar 2010 19:33] Miguel Solorzano
Thank you for the bug report.
[8 Mar 2010 19:43] Shane Bester
not a merge problem. happens with myisam too and strict mode:

set sql_mode='strict_all_tables';
drop table if exists t16;
CREATE TABLE `t16` (`a16`int NOT NULL) ENGINE=MyISAM;
load data local infile 'h:/data.bin' replace into table `t16`
fields terminated by 't' 
lines  terminated by '';
[8 Mar 2010 19:45] Shane Bester
it's looping here:

if (error)
{
  if (read_file_from_client)
   while (!read_info.next_line()) <----
;
[15 Mar 2010 18:05] Sinisa Milivojevic
Shane,

I am the guilty party here ...

That loop solved the problem of LOCAL never returning the error number and message to the client side.

Can you tell me what error number is it that triggers this loop ???
[17 Mar 2010 18:08] Sinisa Milivojevic
Shane,

As you have been so kind to debug it, can you make one more effort and let me know the value of the `error` variable that provokes the loop ???
[22 Mar 2010 17:31] Sinisa Milivojevic
Shane,

Please, send me the info ...
[23 Mar 2010 14:22] Shane Bester
The value of 'error' is 1.
thd->stmt_da->m_message value is "Incorrect integer value: 'MZ' for column 'a16' at row 1"
thd->stmt_da->m_status is DA_ERROR since due to strict mode the binary file cannot be properly loaded into the table.
[22 Dec 2012 12:46] Shane Bester
bug #67394 is a duplicate of this.
[11 Dec 2013 13:17] Stefan Laun
Hello,

we have experienced the same issue. Infinite running LOAD DATA INFILE command. Only killing mysql had "resolved" the problem. Is there a patch available or anything else that we can do to avoid the problem in the future?

regards,
stefan
[20 Jan 2014 17:53] Shane Bester
Still affects 5.6.17.  Call stack of hang is:

mysqld_debug!READ_INFO::next_line+0x17 [.sql\sql_load.cc @ 1713]
mysqld_debug!mysql_load+0x1586 [.sql\sql_load.cc @ 544]
mysqld_debug!mysql_execute_command+0x37f9 [.sql\sql_parse.cc @ 3693]
mysqld_debug!mysql_parse+0x4c2 [.sql\sql_parse.cc @ 6235]
mysqld_debug!dispatch_command+0xc88 [.sql\sql_parse.cc @ 1337]
mysqld_debug!do_command+0x371 [.sql\sql_parse.cc @ 1036]
mysqld_debug!do_handle_one_connection+0x1c4 [.sql\sql_connect.cc @ 982]
mysqld_debug!handle_one_connection+0x42 [.sql\sql_connect.cc @ 899]
mysqld_debug!pfs_spawn_thread+0x155 [.storage\perfschema\pfs.cc @ 1860]
mysqld_debug!pthread_start+0x56 [.mysys\my_winthread.c @ 62]
mysqld_debug!_callthreadstartex+0x25 
mysqld_debug!_threadstartex+0xb4
[21 Mar 2014 22:01] Paul Dubois
Noted in 5.5.38, 5.6.18, 5.7.5 changelogs.

LOAD DATA LOCAL INFILE could use all CPU if import errors occurred
when there were no line delimiters.
[31 May 2014 13:49] Laurynas Biveinis
$ bzr log -r 4612
------------------------------------------------------------
revno: 4612
committer: Praveenkumar Hulakund <praveenkumar.hulakund@oracle.com>
branch nick: mysql-5.5
timestamp: Wed 2014-03-19 12:30:30 +0530
message:
  Bug#11759519 - INFINITE HANG WITH 100% CPU USAGE WITH LOAD DATA
                 LOCAL AND IMPORT ERRORS
  
  Description:
  -----------
  This bug happens due to the fact that current algorithm is designed
  that in the case of LOCAL load of data, in case of the error, the
  remaining part of the file is read in order to return the proper
  error message to the client side.
  
  But, the problem with current implementation is that data stream
  for the client side is cleared only in the case where line delimiters
  exist, which is not a case with, for example fixed width
  fields.
  
  Fix:
  ----
  Ported patch provided by Sinisa Milivojevic n bug report for this
  issue to 5.5+ versions.
  
  As part of this patch code is changed to clear the data stream
  by calling new member function "READ_INFO::skip_data_till_eof".