Fatal error encounterd when using MySqlDataReader

Question

I'm getting the following error at my C# WinForms application:

fatal error encountered during data read

I'm using mysql.data V4.0.30319.

Here's my code:

int catCount = 1;     
while (catCount < 13)
{
    MySqlConnection con = new MySqlConnection("server=server;user id=user;password=password;database=db;");
    con.Open();
    MySqlDataReader Rd;
    string query = "SELECT oc_newsletter_old_system.email FROM oc_newsletter_old_system WHERE oc_newsletter_old_system.cat_uid = "  + catCount;
    MySqlCommand mC = new MySqlCommand(query, con);
    Rd = mC.ExecuteReader();
    try
    {      
        while (Rd.Read())
        {
            string email = Rd.GetString(0);
            Boolean insert = true;
            int realCat = 0;
            switch (catCount)
            {
                case '1':
                    fileBrian += email + "\r\n";
                    break;
                case '2':
                    fileDunamis += email + "\r\n";
                    break;
                case '3':
                    fileFrohlich += email + "\r\n";
                    break;
                case '4':
                    fileGaithers += email + "\r\n";
                    break;
                case '5':
                    fileGospel7 += email + "\r\n";
                    break;
                case '6':
                    fileLifeshop += email + "\r\n";
                    realCat = 1;
                    break;
                case '7':
                    fileMeyer += email + "\r\n";
                    break;
                case '8':
                    fileOpwekking += email + "\r\n";
                    break;
                case '9':
                    filePelgrimKerken += email + "\r\n";
                    realCat = 2;
                    break;
                case 10:
                    filePelgrimKlanten += email + "\r\n";
                    realCat = 2;
                    break;
                case 11:
                    filePelgrimPers += email + "\r\n";
                    insert = false;
                    break;
                case 12:
                    filePelgrimScholen += email + "\r\n";
                    insert = false;
                    break;
            }

            if (insert == true)
            {    
                string salt = "h1bsqnp6wyxjxhf29ziuwsihg8nixe05";
                byte[] asciiBytes = ASCIIEncoding.ASCII.GetBytes(salt + email);
                byte[] hashedBytes = MD5CryptoServiceProvider.Create().ComputeHash(asciiBytes);
                string hashedString = BitConverter.ToString(hashedBytes).Replace("-", "").ToLower();
                string query2 = "INSERT IGNORE INTO `oc_ne_marketing` (`email`,`code`, `subscribed`, `store_id`) VALUES ('" + email + "', '" + hashedString + "', '1', '" + realCat + "')";
                MySqlConnection connection = new MySqlConnection("server=server;user id=user;password=password;database=db;");
                connection.Open();
                try
                {
                    MySqlCommand cmd1 = new MySqlCommand(query2, connection);
                    cmd1.CommandText = query2;
                    cmd1.ExecuteNonQuery();
                    cmd1.Dispose();
                }
                finally
                {
                    connection.Close();
                }
            }
            Application.DoEvents();
        }
    }
    finally
    {
        Rd.Close();
        con.Close();
    }

    catCount++;
    progressBar1.PerformStep();
    progressBar1.Refresh();
    Application.DoEvents();
}

I'm getting the error at this line:

while (Rd.Read())

The only thing that i found out so far was an problem with closing the reader and connection, but as far as i know is that all setup right...

So do you guys got any idea what's the matter???

@nawfal, i can try, but i don't think that's necessary... The first query takes only integers, and the second takes only save ( already in the DB ) data. Also the problem is at an reader. So do you really think that dat would fix the problem??? — Mathlight, Jul 31 '13 at 22:34
Do a google search for this problem. It yields some results. Seems like a bug. strange indeed. See http://bugs.mysql.com/bug.php?id=61807 http://stackoverflow.com/questions/2546115/mysql-exception-fatal-error-encountered-during-data-read http://stackoverflow.com/questions/11131429/fatal-error-encountered-during-data-read — nawfal, Jul 31 '13 at 22:39
Why are you using try blocks with no catch? Put in a catch block in your try/finally and see if that reveals any further information. — Tim, Jul 31 '13 at 22:40
@Tim, i'm using that to always close everything, even when en error happans. And this is inside an another try, with an catch, and that gives this error.. — Mathlight, Jul 31 '13 at 22:44
@Mathlight - Use `using` blocks instead of try/finally - they will clean up the resources even if an exception occurs. — Tim, Jul 31 '13 at 22:45
@Tim, could u make an answer of that ( with some code )? I'm pretty new to that... — Mathlight, Jul 31 '13 at 22:46
@nawfal, thanks for the bug link. I'm trying the work around they provided. Be back in 10 minuts... — Mathlight, Jul 31 '13 at 22:47
@nawfal, it looks like that the work around in that bug link you provided did the trick. If you can provide an answer of it i can accept it. `"The workaround then was to customize the net_xxx_timeout settings. For example, issue the following after openning your connection and before executing the datareader: MySqlCommand cmd = new MySqlCommand( "set net_write_timeout=99999; set net_read_timeout=99999", con ); cmd.ExecuteNonQuery(); "` — Mathlight, Jul 31 '13 at 23:22
@Mathlight I answered, but I will vote to close this as duplicate.. — nawfal, Jul 31 '13 at 23:29

score 2 · Answer 1 · answered Jul 31 '13 at 23:07

While not a direct answer to your problem, per your request here's some info and a code example on using.

It's generally accepted best practice to use using statements with objects that implement IDisposable - it essentially does the same thing as a try/finally block.

Once the scope of the using statement is exited (either the operation completes or an exception is thrown), Dispose will be called on the object. See using Statement for more details.

Here's an abbreviated example based on your code:

int catCount = 1;     
string connectionString = "server=server;user id=user;password=password;database=db;"
string query = "SELECT email FROM oc_newsletter_old_system WHERE cat_uid=@cat_uid";

while (catCount < 13)
{

    using (MySqlConnection con = new MySqlConnection(connectionString))
    {

        con.Open();

        MySqlCommand mC = new MySqlCommand(query, con);
        mC.Parameters.AddWithValue("@cat_uid", catCount);

        using (MySqlDataReader Rd = mC.ExecuteReader())
        {

            while (Rd.Read())
            {

                 // rest of your code here
            }
        }
    }    
}

Note that the above is based on the .NET SqlConnection, SqlCommand and SqlDataReader classes, but should be the same (or very similar) for MySql.

I moved the connection string and query string to their own variables (but that's a personal preference). I also gave an example of how to do parameterized queries (the mCParameters.AddWithValue line - getting in the habit of using parmaeterized queries is (IMO) a good thing, especially if you move from WinForms to web-based apps, as it prevents SQL Injection attacks.

Thank you for the explanation. But 1 thing about the connection. The say that you have to close instead ( or on top ) of the dispose. Where do i close the connection then ( even when an exception has thrown ) — Mathlight, Jul 31 '13 at 23:10
Where did you see that? I would expect a properly implemented `Dispose` would close the existing connection - I've never explicitly closed a SqlConnection. Perhaps it's something unique to MySql? — Tim, Jul 31 '13 at 23:13
See this SO answer - Dispose closes the SqlConnection: http://stackoverflow.com/a/1195854/745969 Again, MySql might be doing it differently. — Tim, Jul 31 '13 at 23:17
It got something to do with the connection pool. this of there documentation some where menthoid [here in the comments](http://stackoverflow.com/a/61171/1846175) `"It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled." So Close() should be sufficient to keep the connection pool from overflowing` — Mathlight, Jul 31 '13 at 23:17
@Mathlight I have been using MySQL for a long time now, you dont have to dispose or close. Just wrap it in `using` clause, its enough.. — nawfal, Jul 31 '13 at 23:19
@nawfal, alright then. From now on i will try to always use `using`. Thanks a lot Tim ;) — Mathlight, Jul 31 '13 at 23:21

score 2 · Accepted Answer · edited May 23 '17 at 12:20

A2A, for the sake of completeness.

From here:

The workaround then was to customize the net_xxx_timeout settings. For example, issue the following after openning your connection and before executing the datareader:

var c = new MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", con);
c.ExecuteNonQuery();

Here's source from MySql.Data in connection class:

protected override void Dispose(bool disposing)
{
  if (State == ConnectionState.Open)
    Close();
  base.Dispose(disposing);
}

which basically means disposing is enough to close it (its bizarre otherwise). Same holds true for SqlConnection too..

Fatal error encounterd when using MySqlDataReader

2 Answers2