Belal Belal - 4 months ago 85
MySQL Question

Timeout expired in Mysql backup - C#

I was built an application to backup my MYSQL DB from server, Day by Day, the database become bigger than before, which trigger an Error in some times (from my point of view):


Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Full: MySql.Data.MySqlClient.MySqlException (0x80004005): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.TimeoutException: Timeout in IO operation
at MySql.Data.MySqlClient.TimedStream.StopTimer()
at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.FetchDataRow(Int32 statementId, Int32 columns)
at MySql.Data.MySqlClient.Driver.FetchDataRow(Int32 statementId, Int32 columns)
at MySql.Data.MySqlClient.ResultSet.GetNextRow()
at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlDataReader.Read()
at MySql.Data.MySqlClient.ExceptionInterceptor.Throw(Exception exception)
at MySql.Data.MySqlClient.MySqlConnection.Throw(Exception ex)
at MySql.Data.MySqlClient.MySqlConnection.HandleTimeoutOrThreadAbort(Exception ex)
at MySql.Data.MySqlClient.MySqlDataReader.Read()
at MySql.Data.MySqlClient.MySqlBackup.Export_RowsData(String tableName, String selectSQL)
at MySql.Data.MySqlClient.MySqlBackup.Export_Rows(String tableName, String selectSQL)
at MySql.Data.MySqlClient.MySqlBackup.Export_TableRows()
at MySql.Data.MySqlClient.MySqlBackup.ExportStart()
at MySql.Data.MySqlClient.MySqlBackup.ExportToFile(String filePath)
at MYSQL_Auto_Backup.Form1.Backup() in c:\Users\Belal\Documents\Visual Studio 2012\Projects\MYSQL Auto Backup\MYSQL Auto Backup\Form1.cs:line 132


Code:

// Backup...
DateTime Time = DateTime.Now;
year = Time.Year;
month = Time.Month;
day = Time.Day;
hour = Time.Hour;
minute = Time.Minute;
second = Time.Second;
millisecond = Time.Millisecond;

//Save file to Path with the current date as a filename
string path;
path = txb_Path.Text + year + "-" + month + "-" + day + "--" + hour + "-" + minute + "-" + second + ".sql";
file = path;
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand())
{
using (MySqlBackup mb = new MySqlBackup(cmd))
{
cmd.Connection = conn;
conn.Open();
mb.ExportToFile(file);
conn.Close();
}
}
}

Answer

You can change the timeout with the "CommandTimeout" property.

There are a lot of other ways of doing the backup (e.g. from the database's administrative tools) that you might want to consider as well. See, for example, below:
https://dev.mysql.com/doc/mysql-enterprise-backup/3.11/en/meb-scheduled-backups.html

One other minor point. For the following line:

string path;
path = txb_Path.Text + year + "-" + month + "-" + day + "--" + hour + "-" + minute + "-" + second + ".sql";
file = path;

why not do something like

file = String.Format("{0}{1}-{2}-3--{4}-{5}...", txb_Path.Text, year, month...);

String concatenation like you're doing is actually fairly expensive in .NET because .NET strings are immutable.