Archive for April, 2008

MySQL Connector/Net 5.0.9, a new version of the all-managed .NET driver for MySQL has been released. This release is an update to the existing production-quality 5.0 series.

We plan for this to be the last release in the 5.0 series. We will only be updating the 5.0 product in the event a "data-loss" type bug is discovered. We encourage all new products to use the new 5.1 product.

Version 5.0.9 works with all versions of MySQL including MySQL-4.1, MySQL-5.0, MySQL-5.1 beta or the MySQL-6.0 Alpha.

It is now available in source and binary form from here and mirror sites (note that not all mirror sites may be up to date at this point of time – if you can’t find this version on some mirror, please try again later or choose another download site.) 

Features or behavior changes

• added implementation of MySqlCommandBuilder methods QuoteIdentifier and UnquoteIdentifier (bug #35492)

Bugs fixed

Fixed problem where fields that were blobs but did not include the BLOB flag were treated as binary when they should have been treated as text. (Bug #30233)

I was so looking forward to hanging out in Santa Clara this week with all the fine people that come to see my .NET and Windows presentations (yes, both of you!) at the annual MySQL Users Conference.  The problem is that I hurt my knee Saturday evening and now I can barely walk.  So I decided it was better for me to stay home and try to stay off the knee as much as I can.  Sorry for those people who were planning on attending my building and debugging on Windows session however the entity framework session should still happen with the ever-capable David Sceppa at the helm.

So go have fun while I sit here and wince. 

Recently I’ve read lots of forum posts from frustrated users claiming that their is something wrong with the Connector/Net installer.  They reference the connector in their web app, then deploy the app to their remote host only to find that the app no longer works.  I’ll walk through a simple web app sample and show where the confusion comes from and what they should be doing instead.  This sample assumes you have a recent build of Connector/Net installed with Visual Studio integration enabled.

First, we’ll create a web app using Visual Studio 2008.

The next thing that many developers do is create some type of page that references the connector.  Often this is a SqlDataSource that they will be connecting to a datagrid.  To do that you drop a SqlDataSource control and a GridView control on the page.  After setting the grid view to use the sql data source you now need to configure the data source control.  Normally  to do that you would select ‘Configure Data Source…’ from the smart tasks popup and walk through adding a data connection to your app (as well as Server Explorer).  Due to a bug in our implementation currently you need to add the connection to Server Explorer first and then pick that connection in the Configure Data Source dialog.  Once done with this you can find the new connection string in your web.config and see how the data source control is tied to it.   Here is the connection string added to my web.config.

<connectionStrings>
    <add name="testConnectionString"
     connectionString="server=localhost;user id=root;database=test"
     providerName="MySql.Data.MySqlClient" />
</connectionStrings>

Here is how my SqlDataSource is configured now.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
     ConnectionString="<%$ ConnectionStrings:testConnectionString %>"
     ProviderName="<%$ ConnectionStrings:testConnectionString.ProviderName %>"
     SelectCommand="select * from t1"></asp:SqlDataSource>

At this point you can hit F5 in VS and the web app will run and show you the data you specified in your query.  However, if you deploy this application to a remote server that does not have Connector/Net installed then it will fail and you may see something like this:

What’s going on?  The first thing to understand is how the SqlDataSource connects to a data provider.  If you refer back to our connection string you’ll see the attribute providerName have the value ‘MySql.Data.MySqlClient’.  That is the invariant name of Connector/Net.  However this name is not enough to locate the right assembly to load.  This mapping is done in either the machine.config or web.config files.  The Connector/Net installer makes the proper registrations in the machine.config file.  Here is the DbProviderFactories section from my machine.config (I’ve omitted all entries but Connector/Net for brevity).

<system.data>
  <DbProviderFactories>
    <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient"
         description=".Net Framework Data Provider for MySQL"
         type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data,
               Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
  </DbProviderFactories>
</system.data>

With this entry in machine.config then the system can match the invariant name to a fully qualified assembly name and also identify the client factory class that should be used.  You can see that by examining the type attribute above.

So what do you do if you are deploying this app to a remote server that you can’t install Connector/Net on?  Simple.  Add this registration to your web.config.  Just drop it in right outside the system.web block like this:

<system.data>
  <DbProviderFactories>
    <clear/>
    <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient"
         description=".Net Framework Data Provider for MySQL"
         type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data,
               Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
  </DbProviderFactories>
</system.data>

So why the <clear/> tag?  You can’t doubly add provider factories to the config system so if you have Connector/Net installed on your dev system and you register the provider factory in your web.config then you will be doubly adding them.  The clear tag clears out all the provider factories pulled in from machine.config and then adds in  yours.  Now if you are mixing providers then you’ll need to adjust this to your situation.

I hope this blog post has cleared up some of the reasons why people have working web apps on their dev systems but then are confused when it doesn’t work after deploy.