Connecting to a MySQL database on the .NET Compact Framework

Our mobile picking application at work runs on the .NET Compact Framework and typically connects to a SQL Server database. This is all nifty and supported with a vanilla install of NETCF and is as such uninteresting. But recently I’ve been tasked with an integration scenario where an alternative persistence implementation for the mobile picking application must communicate with a MySQL 5.1 database.

MySQL offers an ADO.NET driver called Connector/Net 6.0 at its Web site. Sweet. So you reference Mysql.Data.CF in your application and you’re good to go. But as soon as you call Open() on a MysqlConnection instance, you’ll get slammed with a System.IndexOutOfRangeException deep down in the innards of the of the Connector/Net library. If you take a look at the stack trace, you’ll see that it’s occurring in MemoryStream of all places. Apparently, upon calling Open(), the driver immediately issues a command to the database to get some metadata about its capabilities, and we blow up at trying to build that parameterized query.

Why is this broken?

The MySQL Connector/Net library is using the same code for the full framework as well as the compact framework, with little ifdefs for CF-specific or CF-omitted behavior sprinkled throughout the codebase. This isn’t a bad thing; in fact, it’s quite normal, and I do it for one of my common libraries that’s shared between our internal processing application and the mobile picker. The problem stems from an actual behavior difference between the MemoryStream implementations of the full and the compact frameworks. Here’s some sample code that demonstrates the problem:

MemoryStream ms = new MemoryStream();
ms.Position = 4;
ms.WriteByte((byte)0);
Console.WriteLine(ms.Capacity);
Console.ReadLine();

What we are doing is instancing a new MemoryStream via its default constructor; its internal byte[] buffer will be empty (capacity zero). We set its internal Position to somewhere and then try to write a byte.

In the full framework, the MemoryStream auto-expands the capacity by checking to see if the Position is greater than or equal to the Length. So after the above code executes, you end up with a MemoryStream instance whose internal byte[] buffer contains 5 bytes (all zeroes).

In the compact framework, the MemoryStream also auto-expands, but it only does it if the Position equals the Length–not if the Position is greater than the Length. So the above code results in a System.IndexOutOfRangeException. It’s not a documented exception for the WriteByte() method, so I would imagine it is an oversight in the compact framework. (Although the MySQL use case is a little strange; I would never use the MemoryStream class this way.)

The work around is to download the source distribution for Connector/Net 6.0 and edit the MySqlPacket.cs file. Around line 34, change

private MemoryStream buffer = new MemoryStream();

to

private MemoryStream buffer = new MemoryStream(5);

All we’re doing is starting the internal byte[] buffer with a capacity of 5, since the MySqlPacket.cs constructor sets the Position to 4. It shouldn’t affect actual behavior of the MySqlPacket since we’re only adding capacity and not length to the MemoryStream instance that it uses.

Other unrelated problems

If you’re upgrading the Connector/Net 6.0 source to NETCF v35 (just right-click the project in Visual Studio 2008 and choose “Upgrade Project,” you’ll need to delete the CompilerGeneratedAttribute.cs file. NETCF v35 already has one, but NETCF v20 didn’t; I guess that’s why the MySQL guys had created their own.

Finally, you may be getting a “Missing Manifest Exception” whenever Connector/Net 6.0 attempts to access its Resources object for error messages. That’s because they’re trying to share the same Resources.designer.cs file for the CF assembly that was created for the desktop assembly. The problem is that in line 42 of the generated Resources.designer.cs file, it’s trying to load the embedded resx file from the assembly–but it’s searching for “MySql.Data.MySqlClient.Properties.Resources”, which is the namespace for the desktop DLL. So it doesn’t find it in the NETCF assembly on your device and tosses the missing manifest error. The solution is to either fix up the name by editing it directly (e.g., “MySql.Data.CF.Properties.Resources”) or by deleting the file and letting Visual Studio regenerate it properly on the next recompile.

Conclusions and delusions

I’ve filed two bugs, one with MySQL and one on Connect, who knows what will happen with them:

• The bug at MySQL
• The bug at Microsoft Connect

The CF version of Connector/Net seems pretty alpha, but it’s what we’ve got to work with. Good luck!

Update 7/6/2009: Download my custom version of the file.