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.
Related posts:
- MySQL .NET Connector Madness As I’ve been writing about here for a while, my big learning...
- Programming MySQL for .NET Developers MySQL is the relational database behind some of the largest web sites...
- Installing the MySQL ADO.NET Connector into the Global Assembly Cache This morning I sat through a MySQL webinar on Visual Studio tools...
I’m not sure how the database and .net minimum dates not being synced should be singled out as a MySql issue since they also don’t sync up for any other database — so you should already have some code in place to deal with situations like this.
Really? I don’t ever remember running into this problem with SQL Server.
What gui tools are you using? Not that SqlServer has the best gui tools, but, they’re better than any of the tools that I’ve tried for MySql. My background is in SqlServer, but, I have moved to MySql at the request of clients for a couple of projects that I’m working on and I’d like to be more efficient with it. Also, any suggestions for OS X gui’s?
I haven’t run into too many others using .NET and MySQL. I pretty large site running this way. Currently I’m just using ODBC to connect. I used the MySql connector.net but did not convert everything to it.
I still have a ghost memory leak that forces me to restart the applcation pool & MySQL service about 1-2 times a month. I cannot figure it out. I’ve tried everything I can think of, but every so often the main web app cannot connect to the MySQL server at all. But the crazy part is that other web apps (running in seperate App Pools on a IIS 6 box) can still connect!
For dates I always cast them ToString(”yyyy-mm-dd hh:mm:ss”) or something similar (see the ToString stuff for DateTime to get the right syntax). Never had any of the date problems people mentioned using that method.
For people on a budget with existing Windows servers, MySQL fits the bill just fine.
Please excuse my mastery of the English language. Sentence 2: I run a pretty large site this way.
Cheers!
Jeffrey
Thanks for the positive post about the provider. The datetime issue is related to System.DateTime not supporting values < 1/1/1 while MySQL can support 0/0/0 has a date.
As for returning null values, please check SqlClient and OracleClient. They both require you to call IsDBNull to check for a fields nullness. The only method that could return null would be GetString(). It just made sense to me to mirror what is done in the SQL Server and Oracle providers. I hope you give Connector/Net 5.0 a second chance.
I updated the post to include your feedback, Reggie, thanks!
I will do a follow-up post on GUI tools soon, but in a nutshell, I’ve been pretty happy with MySQL Administrator and MySQL Query Browser. There are some tricks (like, it took me four months to realize that you could edit records in Query Browser and I’m not wild about the way that Query Browser provides complex query authoring — I miss Microsoft Access-style graphical joins and QBE).
If you are looking for a MySQL GUI give Heidi SQL a try….
http://www.heidisql.com/
I have been working with VB6 + MySQL since a year ago and it works greate!
Wild! You must be using the ODBC driver then?
Thanks for the tip on HeidiSQL, Pete, I am trying it out and it looks cool.
I’m pitching a MySQL/.NET talk to a 2007 developer conference, fingers crossed.
I’m beginning to experiment by porting a few programs from the C#/MS SQL Server environment. Here’s a few more things to add to your list.
The “MySQL Connector .NET” also incorporates the “Microsoft Application Blocks for .NET” which makes porting easier if your original application uses this layer.
During the port, I discovered that direct SQL statements with parameters use “?” instead of the “@” that we are all familiar with.
Also you need to choose your database engine per a table. This is a neat feature of MySql that treats SQL statements like an standardized “API” into the database engine.
The common ones are MyIsam and InnoDB. I believe MyISAM is the default. One is good for read mostly apps like Blogs (MyIsam) and has full text search VERSES Read/Write Transaction apps like shopping carts, auctions (Innodb) InnoDb has no Full text search support.
By the way InnoDb is the engine that Oracle bought the intellectual property rights to last year. So it has been a concern in the MySql world. I was waiting for the longest time for the Falcon engine which MySQL is developing from their own acquisition (as a knee jerk response to the Oracle purchase) but it looks like its not coming anytime soon as it was promised to delivered by the end of this year.
Either way I believe MySql/InnoDB-Oracle should be a hell of lot cheaper than what Microsoft charges for their database today and in the future. So I plan to use InnoDB when situation calls for it.
I’m interested in a .Net/MySQL event, prefer East US or SouthEast.
I’ve been using MySQL 4.1 and ASP for quite a while. I recently started making .Net 2.0 pages that use the same MySQL Db. One problem I continue to have is mysql server flips out and quits. It happens at least once a month, sometimes more often.
I really want to use MySQL 5.0 in the next version of our website which will be completely .Net 2.0 (VB). I think Microsoft continues to make a mistake limiting their free database. Development using SQL Server is much easier and more stable in my experience than MySQL. But I admit I don’t know many people using this mix either. If MySQL was rock solid stable like SQL SErver I would prefer to use it. I find it hard to justify over $6k for a dual server setup with redundancy.
Sorry to ramble on but I stubled upon your website and wanted to put my name in the hat for the .Net/MySQL conference.