While testing MySqlConnector for use in Tortuga Chain, I discovered that it does something right, which broke a bunch of tests.

For some background, MySqlConnector is a MySQL database driver for .NET. While Oracle offers their own, MySqlConnector does some nice things like actually support asynchronous database calls. So, of course I started swapping out drivers when I heard that.

Most of the tests passed without changes, but I did run into two problems. The first is that MySqlConnector requires that you always pass in a transaction object to the command object if the associated connection is in an explicit transaction. Which is fair and does catch potential problems.

The other was a bit trickier to understand. For some reason half by UPDATE statements were returning 1 row affected, the other half were returning no rows affected. Under my current design, I throw an exception if the client code tries to update a record that doesn’t exist. And how do I know that it doesn’t exist? By checking the affected rows count.

Digging into it, I discovered that MySQL distinguishes between “found rows” and “affected rows”. The difference is an update statement that doesn’t actually change any values is counted as a “found row”, but not an “affected row”. And that’s exactly what my code was doing; performing an update by passing in only the original values.

No other .NET database driver that I’ve encountered works this way. Though the documentation says that the number of affected rows should be returned, in reality it returns the found rows count.

This puts MySqlConnector in a difficult position. They can implement the feature in a way that is consistent with the documentation, which wants affected rows, or in a way that’s consistent with other database drivers, which want found rows.

Arguably both options are useful, but MySQL requires that one be chosen for the lifetime of the connection. So MySqlConnector decided to go with the technically correct choice of returning the affected rows by default.

This decision was made several year ago, and at this point it is too late to change. And for most people that’s not going to be a problem. But when Pomelo Foundation built their EF Core database provider on top of MySqlConnector, they discovered that they really needed “found rows” semantics.

To address this Pomelo modifies the user-supplied connection string, setting UseAffectedRows to false. And I will be modifying Tortuga Chain’s MySQL implementation to do the same.