Description:
From time to time in production we encounter the following problem. When querying a table by key MySql .Net connector returns empty record set when it definitely should return one record (if we repeat the same request we get correct answer).
We have a table 'Boxes' with primary key 'boxId' having ~500000 distinct records. This table changes very rarely and there are only inserts/updates to this table. Reads of this table are much more frequent. To read it we use a simple SELECT query: "SELECT * FROM Boxes WHERE boxId = @boxId".
Presumably in the face of network errors MySql connector occasionally returns empty record set for that query for existing boxId keys (in logs we see timeouts prior to faulty requests).
Our production environment is as follows:
* MySql Server v5.1 running on CentOS release 6.4 (Final) Linux 2.6.32-358.2.1.el6.x86_64
* .Net4 console app running on Windows Server 2008 R2 SP1 x64
* MySql .Net Connector v6.6.5
* Connection string is like this: Server=<host-name>;Database=<db-name>;Uid=<user-name>;Pwd=<password>
How to repeat:
To reproduce described behavior we set up the following testing environment:
* MySql Server v5.1 running on CentOS release 6.3
* Testing console .Net4 app running on Windows 7 SP1 x64
* MySql .Net connector (MySql.Data.dll, Version=6.6.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d) downloaded from here: http://dev.mysql.com/downloads/connector/net/#downloads
* Connection string: Server=xxx;Database=yyy;Uid=xx;Pwd=yy;Connection Timeout=1;Pooling=true;Max Pool Size=100;Min Pool Size=0;ConnectionReset=false;ConnectionLifeTime=0;
We created table 'Boxes' with the following script:
CREATE TABLE `Boxes` (
`boxId` char(36) NOT NULL,
`orgId` char(36) NOT NULL,
PRIMARY KEY (`boxId`)
)
And filled it with ~10000 records using random GUIDs.
Then to simulate bad network we set up the following iptables rules on the server:
* -A INPUT -m state --state NEW -m tcp -p tcp -s 192.168.13.181 --dport 3306 -j ACCEPT
* -A INPUT -m tcp -p tcp -s 192.168.13.181 --dport 3306 -m statistic --mode random --probability 0.15 -j DROP
* -A OUTPUT -m tcp -p tcp -d 192.168.13.181 --sport 3306 -m statistic --mode random --probability 0.15 -j DROP
where 192.168.13.181 is the IP-address of the client.
With the following code in the client app we were able to consistently reproduce the bug:
private void Run()
{
var allBoxes = GetAllBoxes();
while (true)
{
foreach (var boxId in allBoxes)
{
try
{
var box = PerformReadonlyRequest(boxId);
if (box != null)
log.InfoFormat("OK! box was found for boxId: {0}", boxId); //<-- getting here when request succeedes
else
log.ErrorFormat("BUG!!! box not found for boxId: {0}", boxId); //<-- this should never happen
}
catch (Exception e)
{
log.Error(string.Format("Error connecting to mysql"), e); //<-- getting here on connection errors
}
}
}
}
private static Box PerformReadonlyRequest(Guid boxId)
{
Box box = null;
var sql = "SELECT * FROM Boxes WHERE boxId = '" + boxId + "'";
using (var rdr = MySqlHelper.ExecuteReader(connectionString, sql))
{
if (rdr.Read())
{
box = new Box
{
BoxId = (Guid)rdr["boxId"],
OrgId = (Guid)rdr["orgId"],
};
}
}
return box;
}
allBoxes variable is filled with a fixed list of all different keys from table 'Boxes'.
Instead of getting exceptions for bad requests we are observing "BUG!!!" records in the log file in a couple of minutes after the client starts.
When we change connection string to "Server=xxx;Database=yyy;Uid=xx;Pwd=yy;Connection Timeout=1;Pooling=false;" everything works as expected.
We've tried several versions of .Net-connector and found out that bug reproduces with v6.7.1 (latest dev version) and does not show up when using v6.0.7.
