MySQL Bugs: #49694: Crash executing LOAD DATA LOCAL INFILE

Bug #49694	Crash executing LOAD DATA LOCAL INFILE
Submitted:	14 Dec 2009 20:39	Modified:	2 Aug 2010 16:48
Reporter:	Charles Bailey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.11, 5.2.15	OS:	MacOS (10.5.8)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any

Description:
Executing LOAD DATA LOCAL INFILE as a query under workbench against a 5.4.1-beta-log server over TCP causes the application to crash after partial processing (crash leaves table with 218512 rows, whereas successful completion from command line client linked to locally built libmysqlclient yields 219196 rows).

From traceback, it's not entirely clear whether the bug lies in the application itself or in the bundled client library; filed here to start the process.

How to repeat:
1. Create database
2. Execute query:  LOAD DATA LOCAL INFILE "/home/baileyc/hacking/enclist.csv" INTO TABLE enclist FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'  LINES TERMINATED BY "\r\n"  IGNORE 1 LINES (pat_id, episode_id, enc_id);

CrashReporter traceback

Attachment: 49694_abbrev_traceback.txt (text/plain), 11.34 KiB.

Thank you for the problem report. Please, send the results of:

show create table enclist\G

and .csv file to load to get this crash.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Sorry for the delay; too quick submitting the bug and missed the update.

Table structure:
CREATE TABLE `enclist` (
  `pat_id` int(11) NOT NULL,
  `episode_id` int(11) NOT NULL,
  `enc_id` double NOT NULL,
  PRIMARY KEY (`pat_id`,`episode_id`,`enc_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Rather than take up space, CSV file can be generated by the following bit of Perl:
for (my $i = 0; $i < $ARGV[0]; $i++) {
  printf ("%.08d,%d,%f\r\n", 100 + $i, $i % 3 + 1, 60000 + $i/100);
}

for WB 5.2.11 r4842, 100 lines of CSV is sufficient to crash the application.

I am experiencing the same problem; if I can provide any additional input, please let me know.

Verified just as described using CREATE TABLE and Perl script provided.

Crash information collcted by the OS

Attachment: bug49694.txt (text/plain), 39.08 KiB.

This bug isn't just in MySQL Workbench, this affects libmysqlclient period. I am seeing the same thing with the 5.1.35 C client library. Also on Mac OS X.

The bug #51583 is marked as duplicate of this one.

Same problem with 5.2.21 OSS RC Rev 5918.  Platform:  Windows XP
Script statement:  LOAD DATA LOCAL INFILE 'C:/HD-P.dat' INTO TABLE application 
  FIELDS TERMINATED BY '|' 
  LINES TERMINATED BY '\r\n';

Workbench crashes without loading any data into the table.

Set bug #54492 as duplicate

So does "LOAD DATA LOCAL INFILE" ever work? Is there a work-around?

Bug #54556 has been marked as duplicate of this one

Marked bug #54632 as a duplicate

[10:41]  <stkachenko> Thread 13 Crashed:
[10:41]  <stkachenko> 0   libmysqlclient_r.16.0.0.dylib  0x010d4211 my_register_filename + 233
[10:42]  <stkachenko> 1   libmysqlclient_r.16.0.0.dylib  0x010d42ac my_open + 83
[10:42]  <stkachenko> 2   libmysqlclient_r.16.0.0.dylib  0x010cd38a default_local_infile_init + 145
[10:42]  <stkachenko> 3   libmysqlclient_r.16.0.0.dylib  0x010d2431 handle_local_infile + 213
[10:42]  <stkachenko> 4   libmysqlclient_r.16.0.0.dylib  0x010f8627 cli_read_query_result + 292
[10:42]  <stkachenko> 5   libmysqlclient_r.16.0.0.dylib  0x010f64b0 mysql_real_query + 52
[10:42]  <stkachenko> 6   mysqlcppconn.dylib             0x0052d1f2 sql::mysql::NativeAPI::MySQL_NativeConnectionWrapper::query(sql::SQLString const&) + 104
[10:42]  <stkachenko> 7   mysqlcppconn.dylib             0x00525fcc sql::mysql::MySQL_Statement::do_query(char const*, unsigned long) + 140
[10:42]  <stkachenko> 8   mysqlcppconn.dylib             0x005262b2 sql::mysql::MySQL_Statement::execute(sql::SQLString const&) + 56

I'm not able to reproduce this behaviour with Connector/C or libmysql.
Can you please provide some more information about server version, connector version and if possible a small test program? I also like to know if the server crashed too.

Thanks!

Workbench 5.2.24 crashed for me even without data.
Just put any file name for LOAD DATA query and observe it crashing.
It seems that the server version does not matter.
Trying debugging libmysql.dll, which is listed as module where the actual crash happened.

The key moment when trying to reproduce this bug is that query must be run in another thread (not main thread). On all 3 platforms (windows,linux,osx) trying to do so, reliably leads to crash somewhere in Connector/C.

Self sufficient project to reproduce this behavior can be accessed here: https://inside.mysql.com/mywiki/images/c/c8/Cppconn_test.zip
It contains only windows build but should be easily ported.

Tried with following versions:
MySQL Server v5.1.39
MySQL Connector/C v5.1.45.
MySQL Connector/C++ corresponds to rev819 in the baseline branch.

And no, server didn't crash, only client.

Sorry guys, but this isn't really helpful.

You set the bug category to Connector/C / ( or libmysql), so I expect that you will be able to deliver a reproducable testcase which clearly shows the problem is in Connector/C and not in the program or in Connector/C++ itself with a simple testcase. 
A crash in libmysql doesn't indicate that there is in error in libmysql, very often the underlying application is responsible for a possible crash.

Bug #54904 was marked as a duplicate of this one.

Hi,

the thread which executes the LOAD DATA statement doesn't call mysql_thread_init and mysql_thread_end (see http://dev.mysql.com/doc/refman/5.0/en/mysql-thread-init.html) and crashes therefore. -> Not a C/C bug.

Hi,

this is rather a workbench/application/usage bug or a C/C++ feature request. 

The crash is caused by the wrong usage of the C API. The example will crash with C/C and with libmysql. The correct usage of the C API is well documented in the MySQL manual. Threaded applications using C/C++ must not share connections - or any other SQL objects - between threads. In the given example the LOAD DATA must not be run in its own thread.

C/C++ is thread-agnostic: it does not know anything about threads. It does the mysql_library_ini() and mysql_init() C API calls for you, which in turn wrap down to mysql_thread_init(). But that is about it.

However, when starting a new thread it is the duty of the application to call mysql_thread_init(). When the thread exists, it has to call mysql_thread_end(). See also, http://dev.mysql.com/doc/refman/5.1/en/c-thread-functions.html .

C/C++ cannot call those functions for you implicitly. Applications must invoke them. 

Trouble is, the C/C++ API does not export those functions. You cannot call them. If you want those functions in the C/C++ API, you will have to file a feature request for C/C++.

What can you do? Don't use threads ;-). Ok... Users of released Connector/C++ versions have pretty much no other option.

Workbench is using a fork of C/C++ which gets synchronized with the C/C++ repository from time to time. Workbench can add Connection::threadInit(), Connection::threadEnd() calls to its C/C++ fork. This will allow WB to call the necessary C API functions upon the start and the end of the thread.

There may be other ways - for the Workbench fork - to call the C API. You may be able to ignore the libmysql/C-API indirection level and call the C API directly. However, no promise on that. You may have some success using thread private connections. Again, no promise on that.

Ulf

Bug #55221 was marked as a duplicate of this one.

Patch solving the problem on the c/c++ end has been pushed as rev#842

it allows to call mysql_thread_init and mysql_thread_end using Driver interface(threadInit and threadEnd methods respectively).
Driver object is accessible through Connection via getDriver.

yeah, already pushed in rev#844. sorry, forgot to add them

Bug #55363 was marked as a duplicate of this one.

Marked bug #53661 as duplicate

fix confirmed in repository

An entry has been added to the 5.2.26 changelog:

MySQL Workbench crashed when executing LOAD DATA LOCAL INFILE as a query in the SQL Editor.

Bug #55695 has been marked as duplicate of this one

All, I ran into the same issue(LOAD DATA INFILE) with MySQL Workbench 5.2.25 CE (Revision 6303) on Windows XP SP 3 box. Ran the same line from my command line and it worked fine. So it is an issue with the MySQL Workbench and not the calls to C/C++ functions.