C# - Load data infile to mysql - fatal error

Question

I'm trying to load data to a mysql table, when I run the code in the MySql Workbench, it works fine, but when I try to run it in my c# application, I get this error:

Fatal error encountered during command execution.

Here's my code:

mConn.Open(); //Opens the connection
string query = "delete from time; alter table time auto_increment=1; LOAD DATA INFILE 'C://time_u.txt' INTO TABLE `time` CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (@col2, @col3) set date = @col2, hour = @col3;";
WriteDataToDataBase(query); //This guy just executes the command.ExecuteNonQuery();
mConn.Close();

Ps: I have 3 columns, the first one is a primary key (id) which I can't import, it needs to go up by one automatically, the second is the date (varchar) and third is the hours (varchar as well).

UPDATE
I tried to change the LOAD DATA code and I found out that the part that gives the error is this part: (@col2, @col3) set date = @col2, hour = @col3;. When I remove this, the code works (it doesn't work in my case because without this it tries to put data in the Primary key) but it doesn't show that first error.

Thanks...

Side note: delete from time; alter table time auto_increment=1; can be better replaced with truncate table time — Raymond Nijland
– Raymond Nijland, Commented May 22, 2018 at 13:19
@RaymondNijland thanks for your reply, I'll try to use another method to execute. I'll keep you updated if it works. — Lucas Heise
– Lucas Heise, Commented May 22, 2018 at 13:20
i don't believe ExecuteNonQuery() running MySQL client supports multiple statements separated with semicons (;) — Raymond Nijland
– Raymond Nijland, Commented May 22, 2018 at 13:21
@RaymondNijland I tried to execute only the LOAD DATA code but it gave me the same error — Lucas Heise
– Lucas Heise, Commented May 22, 2018 at 13:28
Have you tried to execute your commands step by step? Delete, then alter, then load data ... Next test could be combine delete and alter statements. Still get an error? What Exception is thrown? Stacktrace? — nilsK
– nilsK, Commented May 22, 2018 at 13:48

Bradley Grainger · Accepted Answer · 2018-05-22 15:38:56Z

1

MySql.Data is treating @col2, @col3 as (undefined) query parameters, which is causing this error. One workaround would be to add AllowUserVariables=true to your connection string.

The other workaround would be to avoid the variables and just load directly into the columns you want:

LOAD DATA INFILE 'C://time_u.txt'
INTO TABLE time
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(date, hour);

edited May 22, 2018 at 15:38

answered May 22, 2018 at 15:32

Bradley Grainger

28.3k4 gold badges95 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lucas Heise Over a year ago

Finally got it working using your second workaround. Thank you very much!!

Collectives™ on Stack Overflow

C# - Load data infile to mysql - fatal error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related