How to import large SQL file using mysql.exe through StreamReader -> StandardInput

Question

I have .sql file (550 MB) and I want to import it to running mysql server. I know path to mysql.exe.

My idea is to immitate command line import mysql -u user -ppass db_name < file.sql. This from command line works well (I have set high max_allowed_packet). According to another thread here on Stackoverflow I found this working:

Process process = new Process();
process.StartInfo.FileName = mysqlexepath;
process.StartInfo.Arguments = "-v -u user -ppassworddbname";
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardInput = true;

try
{
    process.Start();
    StreamWriter input = process.StandardInput;
    using (StreamReader sr = new StreamReader(sqlfilepath))
    {
        while ((line = sr.ReadLine()) != null)
        {
            if (process.HasExited == true)
                throw new Exception("DB went away.");

            input.WriteLine(line);
            input.Flush();
        }
    }
    process.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

I can see how tables are being created in DB. BUT my problem is that in about half the process exits. I was googling for some timeout settings but couldnt find anything.

I also tried to read file first:

var file = FileInfo(sqlfilepath);
StreamReader reader = file.OpenText();
string fileContents = reader.ReadToEnd();
StreamWriter input = process.StandardInput;
input.AutoFlush = true;
input.Write(fileContents);
input.Close();

But I get OutOfMemory exception. So the proper way doesnt lead through string.

I would be very thankful for any advice how to find out where is the problem. I dont even know if its Process timeout or mysql timeout or if the problem is in StreamReader.

Answer 1

I know this isn't a direct answer to your problem, honestly I'm not sure what is wrong with your approach. I can help by sharing how we run very large sql scripts using mysql.exe...

"C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin\mysql.exe" -C -B --password=[password] -P 3306 --user=[username] --host=localhost --database=[database] -e "\. C:\Backups\Mybackup.sql"

Most of these parameters are obvious, connection info, etc.

What isn't obvious is the magical part -e "\. [filename]" the -e parameter specified that mysql should run the following command and exit. The prefix "\. " indicates that an input file should be used and is followed by that file name.

We use this to restore multi-gigabyte databases without issue. So here is the complete 'run a scirpt' with mssql...

public static int RunMySql(string server, int port, string user, string password, string database, string filename)
{
    var process = Process.Start(
        new ProcessStartInfo
        {
            FileName = @"C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin\mysql.exe",
            Arguments =
                String.Format(
                    "-C -B --host={0} -P {1} --user={2} --password={3} --database={4} -e \"\\. {5}\"",
                    server, port, user, password, database, filename),
            ErrorDialog = false,
            CreateNoWindow = true,
            UseShellExecute = false,
            RedirectStandardError = true,
            RedirectStandardInput = true,
            RedirectStandardOutput = true,
            WorkingDirectory = Environment.CurrentDirectory,
        }
        );

    process.OutputDataReceived += (o, e) => Console.Out.WriteLine(e.Data);
    process.ErrorDataReceived += (o, e) => Console.Error.WriteLine(e.Data);
    process.Start();
    process.BeginErrorReadLine();
    process.BeginOutputReadLine();
    process.StandardInput.Close();
    process.WaitForExit();

    return process.ExitCode;
}