-1

I have a console to load the sample.txt file into Table in database but it's failed when the sample.txt file size is more the 4000kb. How to incerase the limit? I think the default limit is 4096kb.

When I run the SQL query in MYSQL its successful insert all the data into Table.

 strSql = "LOAD  DATA LOCAL INFILE 'D:\\sample.txt'" & _
                " INTO TABLE TABLENAME" & _
                " FIELDS TERMINATED BY '|'" & _
                " LINES TERMINATED BY '\n'" & _
                " IGNORE 1 LINES" & _
                " (NO,MESSAGE);"


Func: ExecSQL|   at MySql.Data.MySqlClient.NativeDriver.SendFileToServer(String filename)
at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId)
at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet()
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at MNC.COM.DBConn.ExecSQL(String strSQL)|MySql.Data|Error during LOAD DATA LOCAL INFILE|LOAD  DATA LOCAL INFILE 'D:\\sample.txt' INTO TABLE FILE_RECORD_LOG_TEST FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 LINES (NO,MESSAGE);
alkapunzi
  • 1
  • 2
  • 1
    What is the error message? – Alex B. May 03 '16 at 09:41
  • at MySql.Data.MySqlClient.NativeDriver.SendFileToServer(String filename) at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId) at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet() at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() – alkapunzi May 03 '16 at 10:01
  • 2
    That's the stack trace and please add it to your question by editing it rather than placing it in a comment. – Chris Dunaway May 03 '16 at 13:24

2 Answers2

0

I recommend you check the setting of max_allowed_packet on the server and on the client, to make sure they match.

The fact you report the "limit" as 4096K, and the fact that the stack trace seems to report the issue at "SendFileToServer" makes it sound like a possible mismatch in max_allowed_packet.

Also, I'd compare that to read_buffer_size.

 SELECT @@global.max_allowed_packet
      , @@session.max_allowed_packet
      , @@global.read_buffer_size
      , @@session.read_buffer_size

The other thing to verify is that there is sufficient space in the temp directory on the server. (With the LOCAL, the file will be transferred to the server, and that will go into the temp directory of the Operating System (not the directory specified in the tmpdir variable. If there's insufficient space, the LOAD DATA will fail with an error.

The fact you report that the statement is working with a small (<4MB) file makes it seem like your syntax is correct, if you are getting the rows added that you expect.

I'd also check that the line separator in the larger file is the same as it is in the smaller file.

What we don't see is the actual error message from MySQL server.

I'm not familiar with VB.NET. But I believe that the MySQLException class provides "Number" and "Error" members that will return the MySQL error number and message, respectively.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thanks, I run the SQL query and get the result below: @@global.max_allowed_packet: 4194304, @@session.max_allowed_packet: 4194304 @@global.read_buffer_size: 262144 @@session.read_buffer_size: 262144 actually when I run the sql statement to load the data through SQLyog its successful load the data, its only failed when I run through VB.NET. – alkapunzi May 05 '16 at 06:17
  • Add an exception handler to your code, catch the MySQL exception, and output the error number and message. Also, run the query to check the session variables from the VB.NET client. The settings of those variables look like they are from your SQLyog client, where the `LOAD DATA` statement works. Run the `SELECT @@session.max_allowed_packet, @@session.read_buffer_size` query from your VB.NET application, from the environment where the `LOAD DATA` is throwing the error. – spencer7593 May 07 '16 at 13:25
0

Problem solve

I use old version MySql.Data.dll, after used the latest version 6.7.4.0 its working fine, can load huge data into database.

thanks all

alkapunzi
  • 1
  • 2