Loading a large data table from a MySQL database into a text file in C #

Question

I have a MySQL database "DB", which has a table "TABLE" of one field and contains about 8 million rows. I am trying to load this table into a text file as follows:

//Open connection. connection.Open(); //Create command. MySqlCommand command = connection.CreateCommand(); command.CommandText = "SELECT * FROM `DB`.`TABLE`"; //Execute command. MySqlDataReader result = command.ExecuteReader(); //If result isn't null if (result != null) { //Open stream to write result. System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\StackOverflow\Desktop\ID_List.txt", true); //For each line row of result. while (result.Read()) { //Write result in a line. file.WriteLine(result.GetValue(0).ToString()); } } //Close connection. connection.Close(); 

After starting it starts loading the data and outputting it to a text file, but after a minute or two it gives:

 An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in test program.exe Additional information: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. 

What can I change or do differently to make it work? Thanks for any suggestions and answers :)

[EDIT]: I added command.CommandTimeout = 240; and tested three times, each time it was downloaded for a couple of minutes (about 40 MB of data each time), before giving:

 An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in test program.exe Additional information: Fatal error encountered during data read. 

And these are the last few lines of debug output:

 at MySql.Data.MySqlClient.NativeDriver.FetchDataRow(Int32 statementId, Int32 columns) in :line 0 at MySql.Data.MySqlClient.Driver.FetchDataRow(Int32 statementId, Int32 columns) in :line 0 at MySql.Data.MySqlClient.ResultSet.GetNextRow() in :line 0 at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior) in :line 0 at MySql.Data.MySqlClient.MySqlDataReader.Read() in :line 0</ExceptionString><InnerException><ExceptionType>System.IO.EndOfStreamException, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Attempted to read past the end of the stream.</Message><StackTrace> at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count) in :line 0 at MySql.Data.MySqlClient.MySqlStream.LoadPacket() in :line 0</StackTrace><ExceptionString>System.IO.EndOfStreamException: Attempted to read past the end of the stream. at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count) in :line 0 at MySql.Data.MySqlClient.MySqlStream.LoadPacket() in :line 0</ExceptionString></InnerException></InnerException></Exception></TraceRecord> An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in test program.exe Additional information: Fatal error encountered during data read. The program '[4548] test program.exe: Program Trace' has exited with code 0 (0x0). The program '[4548] test program.exe: Managed (v4.0.30319)' has exited with code 0 (0x0). 

[EDIT2]: I added MySqlDataReader result = command.ExecuteReader(System.Data.CommandBehavior.SingleResult); to fix the above error, and now I get:

 An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in test program.exe Additional information: Query execution was interrupted 

After some easy google-ing, I find this to be GoDaddy, which limits the execution time of the request. Therefore, a working solution is to try / catch using a counter to keep track of how many records were retrieved before the connection was closed, and to re-open new connections with the counter as a starting point for LIMIT until the table size was exhausted.

Thanks for the help!

Answer 1

Increase the wait_timeout parameter in my.ini file.

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout

There are other timeout options that may be the problem, but I think this is the one.

If you do not have access to your ini file, use "limit" to get a limited set of results.

First get the total number of rows (count (*)) Second, get a small result set thanks

  for (int i=0; i<=numberOfRowsInYourTable ; i=i+1000){ command.CommandText = "SELECT * FROM `DB`.`TABLE` LIMIT " + i + " " + new String(i + 1000); // Do what you want } 

Answer 2

Add this to the connection string:

 default command timeout=240 

and adjust if necessary.

Answer 3

You have several options.

• you can set a longer timeout, which means that you need to set the CommandTimeout parameter in the connection string

• you can use SELECT INTO , which will generate a file on the server

• you can use the mysqldump utility, which will also generate a file on the server

EDIT SO SO users point to other types of timeout parameters. I am not the user who is the key here.