MySQL LOAD DATA INFILE – Resolving Fatal Errors

cMySQL

I use this query for loading sql data from my pc to localhost mysql database.(version 5.0.10 working witj XAMPP)

The query is as below:

cmd = new MySqlCommand("LOAD DATA LOCAL INFILE '" + filename + "' IGNORE INTO TABLE " + tblname + " CHARACTER SET UTF8", conn);

And I tried this also (enclosed tblname) 

cmd = new MySqlCommand("LOAD DATA LOCAL INFILE '" + filename + "' INTO TABLE `" + tblname + "` CHARACTER SET UTF8", conn);

And one line of my sql data (loaded without problem by phpmyadmin) :

INSERT INTO `atelier` VALUES(1,'Chateau Carbonnieux -1','26.12.2013 00:00:00',23,10,0,'4 macarons differents','mamie','15.12.2013 11:09:14','sabrina','18.12.2013 05:29:26');

The variable filename is : "E:\Macdata\20131228\atelier.sql" (with double backslah)

And the tblname is the table name read directly form database is: "atelier".

When the program reaches the line cmd.ExecuteNonQuery() 30 seconds after i get "Error 0 fatal error ecountered attempting to read the resultset."

This data file is loaded without problem with phpmyadmin.

What can be the reason ? MySql version ? Any help ?

As I'am a newbie in mysql, i used this link for creating my query command. Often they send this link for answer. But in this link there is no remarq to enclose the filename with ' otherwise it does not work it gives an error. http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Best Answer

You don't use LOAD DATA INFILE for what you are trying to do.

LOAD DATA INFILE loads a delimited text file (such as CSV) full of raw data, that is sitting on the server, into a table.

What you have is a dump file, which is full of queries -- not raw data.

You have three basic options:

  • use phpmyadmin to apply the file
  • use the mysql command line client to load the file
  • open the file yourself in your code (however you'd open a normal text file for reading), read each line in, and execute each line as a query from your code.

A dump file contains the queries to recreate data structures, so its contents are simply executed as queries. That's what phymyadmin is doing when you "load" it that way.

If the file contains DDL that drops and creates tables, you'll have to do a little more work, because you'll need to buffer what you read from the file, and only execute what you've collected so far each time you find a line ending with the ; delimiter -- or any other delimiter that may be specified in the dump file (a line beginning with DELIMITER changes the expected delimiter for subsequent statements until a subsequent DELIMITER statement is found).

But using the mysql.exe command line client on your local machine would probably be the easiest.

C:\>mysql [options] < "E:\Macdata\20131228\atelier.sql"

With a file structured like this, you can't specify what table it goes into. If the file contains USE statements to change to the appropriate database, that will happen when those statements are encountered. If it doesn't, you'll need to add the name of your schema right before the < in the command line.

Related Question