MySQL Gotchas for .NET Developers

I think I’ve mentioned a few times on the blog that I’ve been using the combination of .NET 2.0 and MySQL for Approver.com. I made this choice after very careful consideration. I spent about a month looking at Postgresql and MySQL earlier this year and came to the conclusion that either one is a good choice for Windows developers today; MySQL edged out Postgresql mainly because of the strength of its developer ecosystem.

Yet when I tell people in Silicon Valley that I’m using C# and MySQL together, they look at me like I just stepped off a flying saucer. I am here to say that using MySQL is 110% righteous on Windows and you don’t have to sacrifice much of anything (including GUI tools, which were one of my big requirements) to use it.

I am going to do more to evangelize the .NET and MySQL combination because it’s worked so well for us to date. To start, I’m going to describe two gotchas I’ve run into while developing Approver.com. Neither of these are things that I’d consider to be major problems, although they did require a bit of research and testing to figure out.

1. Date values. MySQL has a concept of a date that equals zero, but something in the .NET stack (ADO.NET? System.DateTime?) does not. So, the workaround is to always set your DateTime fields to default to ‘0001-01-01 00:00:00’ instead of the normal MySQL default, which is ‘0000-00-00 00:00:00’.

2. Unsigned integers. This one bit me over the weekend when I was trying to create a table that stores IP addresses for a <mumble mumble> we’re planning to release soon. The 1.07 version of the ADO.NET provider for MySQL (which is, by the way, provided by and officially supported by MySQL AB under the name "MySQL Connector .NET") has a bug where it barfs on unsigned integers. There are two workarounds for this: treat the column as a signed integer (System.Int32), or upgrade to 1.08 of MySQL Connector, which handles unsigned integers correctly. (The signed integer workaround won’t work for the use case of storing an IP address, so I upgraded to 1.08.)

I also tried MySQL Connector 5.0 beta when investigating this problem and discarded it because it broke a lot of other stuff in my app — notably, the 5.0 beta forces you to check for null values for all nullable fields in a DataReader, which I think is a mistake, particularly since it’s different than the behavior in the 1.07 connector and from most other ADO.NET providers I’ve used. Is this nullability check some new .NET 2.0 thing that is there because there are nullable types in the framework now? Hmmph. Anyway.

The MySQL ADO.NET provider is open source, by the way (just like the rest of MySQL), so if you ever wanted to see what a soup-to-nuts ADO.NET provider looks like, you can download the source and pry at it to your heart’s content.

Update: In the comments,
Reggie points out that the SQL Server and Oracle ADO.NET providers
exhibit the null assignment behavior with DataReaders as well, so the MySQL folks changed their
provider’s behavior to mimic what the other guys do. Throwing an error when assigning a null value still seems
unnecessary to me since the DataReader should just know what to do when
assigning a null field to a String, but I’m sure someone will be along at some point to let me know why this behavior is sensible and correct, even though it’s really awkward for developers.

Also: I mentioned that the change from sensible handling of null types to strict handling didn’t start with the 5.0 provider; it was introduced in 1.08 (as I discovered yesterday when my application started throwing up after encountering unexpected nulls). Not a huge deal, though, since I have my own object-relational mapping tool that I use specifically for situations like this.