MySQL异常 - 数据读取期间遇到的致命错误

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.

我正在开发一个C#控制台程序,它从表中获取大量记录,通过医学分析器运行它们,然后更新每个记录。它使用MySQL Connector / NET 5.2.7。它的工作方式是我使用SQL_BUFFER_RESULT一次抓取数据块(即20,000行)以避免锁定。每条记录都通过分组器运行,然后在该记录上完成单独的更新查询。使用了两个连接,即读连接和写连接。

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.

因此,当程序执行并循环读取查询中的记录时,它使用result.Read()来执行此操作,其中result是MySqlDataReader。 result.Read调用是抛出异常的地方。它是随机发生的(不是在同一记录或任何东西上)。一旦在第一条记录上遇到它,它也会在数据读取器的每次后续读取调用中遇到。我尝试过很多东西,并搜寻其他人有过的相关问题。任何见解都会很棒,并随时让我知道我需要提供的其他信息。

3 个解决方案

#1

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

在connection.Open()之间;和command.ExecuteNonQuery();我刚添加了两行,如下所示:

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 :)

问题修正:)

#2

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.

我有与.NET连接器类似的问题。错误可能与速度有关 - C#可能试图以比MySQL可以跟上的速度更快地处理数据。我最终做了两件事来补救 - 1.在发生错误时(或处理大部分后)添加睡眠定时器,以便系统可以“呼吸”或2.尝试再次阅读。

#3

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.

我相信在读取大型数据集时,.NET连接器存在缓冲区大小限制。我通过一次读取5000条记录解决了这个问题。

#1