MySQL Exception - Fatal Error Encountered During Data Read

Question

I am working on a C# console program that grabs large numbers of records from a table, runs them through a medical grouper, and then updates each of the records. It uses MySQL Connector/NET 5.2.7. The way it works is that I grab chunks of data at a time (i.e. 20,000 rows) using SQL_BUFFER_RESULT to avoid locks. Each record is run through the grouper, and then an individual update query is done on that one record. There are two connections used, a read connection and a write connection.

So as the program executes and it loops through records from the read query, its using result.Read() to do so, where result is a MySqlDataReader. The result.Read call is where there exception is thrown. It happens randomly (not on the same record or anything). Once it is encountered on the first record, it is also encountered on every subsequent read call for the data reader. I've tried many things and searched high and low for related problems others have had. Any insight would be great, and feel free to let me know what other info I need to provide.

What was the exception that was thrown? – John M Sep 16 '10 at 19:37 — John M, Sep 16 '10 at 19:37

score 18 · Accepted Answer · edited Jul 12 '11 at 20:25

18

Between connection.Open(); and command.ExecuteNonQuery(); I just added two lines like this:

connection.Open();

MySqlCommand cmd = new MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", connection); // Setting tiimeout on mysqlServer
cmd.ExecuteNonQuery();

int numOfRecordsUpdated = command.ExecuteNonQuery();

Problem Fixed :)

edited Jul 12 '11 at 20:25

dmc

2,596
21
24

answered Jul 12 '11 at 19:16

Adil

196
1
3

1

This seems to have helped me as well, but is there another/better way to set these timeouts permanently (on a windows installation) rather than by executing a command like this after each `connection.Open()`? – Christopher King Aug 03 '16 at 18:30
This seems to have worked for me too, the cool thing is I set the timeouts all properly in the damn connection string and this garbage connector still doesn't understand a bit of what I want it to do. – Felype Jan 05 '19 at 18:50

score 3 · Answer 2 · answered Oct 11 '18 at 15:12

3

Or just simply:

cmd.CommandTimeout = 99999;

answered Oct 11 '18 at 15:12

Andrew Sin

63
5

score 1 · Answer 3 · answered Jan 26 '11 at 16:14

1

I believe there are buffer size constraints with the .NET connector when reading large datasets. I have worked around this problem by reading 5000 records at a time.

answered Jan 26 '11 at 16:14

Cappannari

11
1

i also solved it like this. I changed my query to var sql = "SELECT * from object " + increase + " offset " + counter; and then increased the counter with the increase value every time and then kept looping through it until it reached the end – WtFudgE Jan 05 '19 at 03:34

score 0 · Answer 4 · answered Nov 15 '10 at 19:42

I have had similar sorts of issues with the .NET connector. The error may be speed related - C# may be trying to process the data faster than MySQL can keep up. I end up doing two things to remedy - 1. Add a sleep timer when a error occurs (or after processing a large section) so the system can "breathe" or 2. try to read again.

MySQL Exception - Fatal Error Encountered During Data Read

4 Answers4

Linked