Racing a jet engine

• Posted by reggie on May 19, 2008
• 3 comments

I've spent the last few days working on a support case where a customer was comparing the speed of Connector/Net with an application that uses our native C api.  His test was using a server side prepared statement to insert 3 sets of values in a loop that executed 600,000 times.  The table is a simple 3 column table and the SQL looks like this:

INSERT INTO test VALUES (@p1, @p2, @p3), (@p4, @p5, @p6), (@p7, @p8, @p9)

He was seeing 34,529 records / second using libmysql.dll but only about 24,000 records / second using Connector/Net.  He  understandably wanted to know why there was such a difference and if we could do something about it.  Absolutely!

We solved this problem through a combination of optimizations made to our code and some changes made to the testing application.  I won't say much about the optimizations.  We made some obvious changes like moving a lot more of the prepared statement work to the prepare phase and out of the execute phase.  We also discovered that BitConverter.ToBytes() is a lot slower than our hand-coded methods for reading and writing integer values.  We replaced usages of ArrayList with generic equivalents (it's pretty amazing how much faster generics are actually).

One of the bigger changes we made was to use a second hash for parameter name lookups.  When you are talking about loops with lots of iterations even the smallest inefficiencies can kill you.  Since .NET supports name lookup for parameters we use a hashtable to map parameter names to indices (we made this change when one of our customer was complaining that parameter lookup was slow on one of his commands that had > 30,000 parameters.  Yeah.).  Name lookup should be case-insensitive so we use a case-insensitive comparer with our hashtable.  So the optimization we made this time is to use two hash tables.  The first is case sensitive and is checked first.  If that fails then we check the second and return failure only if both fail.

That's what we did in our code.  We also made some changes to the testing app to make sure the comparison is truly apples to apples.  Let's first look at the loop code in each case.

			while (count < 600000)
			

			{    
			

			    for (int row = 0; row < 3; ++row)
			

			    {
			

			        *(p1 + row) = count;
			

			        *(p2 + row) = count;
			

			        *(p3 + row) = count;
			

			        ++count;
			

			    }
			

			    mysql_stmt_execute(stmt);
			

			}
			

			while (count < numberOfRows)
			

			{
			

			    for (int row = 0; row < 3; ++row)
			

			    {
			

			        cmd.Parameters[row].Value = count;
			

			        cmd.Parameters[row+3].Value = count;
			

			        cmd.Parameters[row+6].Value = count++;
			

			    }
			

			    cmd.ExecuteNonQuery();
			

			}
			

How I found out that mixed mode is cool

• Posted by reggie on May 13, 2008
• 1 comments

We ship a DDEX provider with Connector/Net.  This provider plugs into Visual Studio and integrates into server explorer allowing a user to create data connections to MySQL from within the IDE.  This integration is handled, in large part, by a lengthy series of registry entries.  Until 5.2.2, these entries were made by my installer which is written in WiX.  Having the registry changes made in the installer has two problems.  First, I often need to register the provider during debugging and I don't want to do a full install of the product so I end up hand editing a registry file and manually merging that file.  This a awkward at best.  Second I plan to ship a stand-alone configuration utility in 5.3 that will allow the user to configure which installed version of Connector/Net should be used for VS integration. Currently you can only have one instance installed at a time.

My approach to solving this was to write an installer class that made the registry changes for me and just use installutil to register the assembly.  I was already doing this with my core and web assemblies so this is nothing new.  The problem is that installutil will scan the assembly and all referenced assemblies for types looking for installer classes.  This fails when some of the Microsoft assemblies are scanned.  After much effort I gave up and decided to write my own installutil that I would ship with my installer. 

I had no trouble creating this application and then set about executing it from within my installer.  I attempted to use the CAQuietExec custom action available with WiX v3 but just couldn't make it work right.  So I gave up and decided to write my own execute custom action.

So I cracked open Visual Studio 2008 and read a couple of blogs about custom action writing.  One of them mentioned mixing managed and unmanaged code, the unmanaged code being necessary for the proper DLL exports.  I decided I had to see this work.

Within minutes I had a DLL project setup with the /clr option and had hacked out the following code:

   1: extern "C" __declspec(dllexport) UINT InstallAssembly(MSIHANDLE hMSI)

   2: {

   3:     System::Windows::Forms::MessageBox::Show("boo");

   4:     TCHAR name[261]={0};

   5:     DWORD len=261;

   6:  

   7:     UINT result = ::MsiGetProperty(hMSI, TEXT("CustomActionData"), name, &len);

   8:     InstallVSAssembly(name, true);

   9:     return ERROR_SUCCESS;

  10: }

  11:  

  12: bool InstallVSAssembly(char *assemblyName)

  13: {

  14:     String^ str = gcnew String(assemblyName);

  15:     bool uninstalling = false;

  16:     IDictionary mySavedState = new Hashtable();

  17:     int arg = 0;

  18:  

  19:     InstallContext context = new InstallContext();

  20:     while (arg < args.Length)

  21:     {

  22:         string[] parts = args[arg++].Split('=');

  23:         context.Parameters.Add(parts[0], parts[1]);

  24:     }

  25:  

  26:     Installer installer = null;

  27:     try

  28:     {

  29:         Assembly assem = Assembly.LoadFrom(file);

  30:         installer = (Installer)assem.CreateInstance("MySql.Data.VisualStudio.MyInstaller");

  31:         if (installer == null)

  32:         {

  33:             Console.WriteLine("Unable to find an installer in that assembly.");

  34:             return;

  35:         }

  36:         installer.Context = context;

  37:         if (uninstalling)

  38:             installer.Uninstall(mySavedState);

  39:         else

  40:             installer.Install(mySavedState);

  41:     }

  42:     catch (Exception e)

  43:     {

  44:         Console.WriteLine(e.Message);

  45:     }

  46:     return true;

  47: }

Yes, that is managed code and unmanaged code *in the same function*!  Now this code might not compile as it is not what I wound up using and I just grabbed it out of an old folder but you get the idea (and yes I did test a version of this so I know the concept works).  I didn't use this approach because the /clr switch requires the dynamic CRT and I didn't feel like bundling that up.

And, in case you were wondering about the "boo" messagebox on line 3, that is my debugging trap.  You run the installer until that pops up, attach to the proper msiexec using Visual Studio, set a break point, and go.  Yup, that's cool.

MySQL Connector/Net 5.1.6 has been released

• Posted by reggie on May 12, 2008
• 1 comments

Hi,
MySQL Connector/Net 5.1.6 a new version of the all-managed .NET driver for MySQL has been released. This is a minor release involving mainly bug fixes.
Version 5.1.6 works with all versions of MySQL including MySQL-4.1,  MySQL-5.0, MySQL-5.1 beta or the MySQL-6.0 Falcon "Preview".

It is now available in source and binary form from [http://dev.mysql.com/downloads/connector/net/5.1.html] 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.)

Bugs fixed

• Fixed problem where parameters lists were not showing when you tried to alter a routine in server explorer.  (bug #34359)
• Fixed a problem in procedure cache where it was possible to get into a race condition and cause a memory leak (bug #34338)
• Fixed problem where attempting to use an isolation level other than the default with a transaction scope would use the default instead (bug #34448)
• Fixed problem that causes the TableAdapter wizard to only generate insert statements.  The problem was that our code to retrieve index columns was broken. (bug #31338)
• Fixed problem with connections staying open after being used with SqlDataSource.  The problem was that we were not returning an enumerator for our reader with the  closeReader option set to true when we were supposed to.  (Bug #34460)
• Fixed problem where the bit data type would continue to return null values once it saw a null value in a previous row (bug #36313)     
• Fixed problem with MembershipUser.GetPassword where attempting to retrieve a  password on a user where password Q&A is not required would throw an exception (bug #36159)     
• Fixed a problem with MembershipUser.GetNumberOfUsersOnline.  It actually works now  :) (bug #36157)
• Fixed documentation that still stated that setting port to -1 was necessary for a named pipe connection (bug #35356)     
• Fixed data type processing so that geometry fields are returned as binary.  (bug #36081)     
• Fixed problem that kept our provider from showing up in the provider list when configuring a new connection from a SqlDataSource     
• Fixed problem where setting the ConnectionString property of MySqlConnection to null would throw an exception (bug #35619)

Enjoy and thanks for the support!

MySQL Connector/Net 5.0.9 has been released

• Posted by reggie on April 17, 2008
• 0 comments

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

Well isn't that a crapper

• Posted by reggie on April 14, 2008
• 1 comments

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.  :(

Referencing Connector/Net on a remote machine

• Posted by reggie on April 8, 2008
• 2 comments

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.

Getting the built in data browser to work in 2005 and 2008

• Posted by reggie on March 14, 2008
• 2 comments

When we last left our hero he had just got the global data menu working under '05 and '08.  Next step was use of the table browser.  Here's a shot of it working with SQL Server data.  Now there is no particular reason to use this data browser instead of writing our own other than it's just less work and it has a nice look and feel.

On the surface this looks pretty easy.  After a little digging I discovered that this command binding will add a "Show Table Data" menu option and bring up a nice OleDB-based data browser under VS 2005.

<CommandBinding name="Browse_Data" guid="501822E1-B5AF-11d0-B4DC-00A0C91506EF" cmdid="12384"
                handler="Microsoft.VisualStudio.DataTools.DBPackage.VDT_OLEDB_CommandHandler_TableTools">
  <Parameter value="Open"/>
</CommandBinding>

The problem, of course, is that this doesn't work under VS 2008.  The handler under VS 2008 is implemented by an object with the guid of 884DD964-5327-461f-9F06-6484DD540F8F.  My first approach was to define two of these bindings and no matter which version of VS ran one of them would fail (and not add any menu item) while the other would succeed.  VS doesn't like that.  Apparently the guid and cmdId together form a key and you get a key added twice error. Hmmm

My good buddy Stephen at Microsoft offered up the solution of overriding the GetDataViews method of DataViewSupport and tweaking the XML that is returned.  It turns out that he was spot on.  I set all the handlers in the XML file to be proper for VS 2005, changed my DataViewSupport derived class to now use the no parameter constructor base, and then overrode the GetDataViews method like so.

public override Stream GetDataViews()
{
  string xmlName = "MySql.Data.VisualStudio.DDEX.MySqlDataViewSupport.xml";
  Assembly executingAssembly = Assembly.GetExecutingAssembly();
  Stream stream = executingAssembly.GetManifestResourceStream(xmlName);
  StreamReader reader = new StreamReader(stream);
  string xml = reader.ReadToEnd();
  reader.Close();

  // if we are running under VS 2008 then we need to switch out a couple
  // of command handlers
  DTE dte = Package.GetGlobalService(typeof(DTE)) as DTE;
  if (dte.Version.StartsWith("9"))
     xml = xml.Replace
          ("Microsoft.VisualStudio.DataTools.DBPackage.VDT_OLEDB_CommandHandler_TableTools",
                    "884DD964-5327-461f-9F06-6484DD540F8F");

  MemoryStream ms = new MemoryStream(xml.Length);
  StreamWriter writer = new StreamWriter(ms);
  writer.Write(xml);
  writer.Flush();
  ms.Position = 0;
  return ms;
}

I won't bother with showing you a pic of it working in MySQL because it looks just like the other pic.  But, then again, that's the point.  :)

Supporting a global data menu option under 2005 and 2008

• Posted by reggie on March 14, 2008
• 1 comments

It's no secret that that SQL Server integrates better into Visual Studio than any other database.  We are trying to change that.  Two of the things that I wanted to implement in the new version of our integration code is to replicate the Add New submenu and use the the built-in table browsers that Sql Server uses.  Here is a screen shot of the global data menu we wanted to reproduce.

This menu should also work in Visual Studio 2005 though the top four items (Schema Compare, Data Compare, Refactor, and T-SQL Editor) won't be there.  The problem is that adding items to the global data menu is not directly supported in Visual Studio 2005 but it is supported in 2008.  This is further complicated due to the fact that Microsoft moved from using CTC files to VSCT files to define package resources.  I didn't want to ship two binaries (one for 2005 and one for 2008) so I have continued to use the CTC format.  Here is what I had to do to get it working.

First, I defined a constant for the appropriate menu group in VS 2005.  I put this in my file named guids.h

#define guidVS2005Data          { 0x501822e5, 0xb5af, 0x11d0, { 0xb4, 0xdc, 0x00, 0xa0, 0xc9, 0x15, 0x06, 0xef } }

Then I defined two menus and two groups (one for 2005 and one for 2008).

MENUS_BEGIN
    guidMySqlProviderCmdSet:menuAddNew2005, guidVS2005Data:8706, 0x1000,, "MySQLAdd", "&Add New";
    guidMySqlProviderCmdSet:menuAddNew2008, guidVSData:IDG_DV_GLOBAL1, 0x1000,, "MySQLAdd", "&Add New";
MENUS_END

NEWGROUPS_BEGIN
    guidMySqlProviderCmdSet:groupAddNew2005, guidMySqlProviderCmdSet:menuAddNew2005, 0x0000;
    guidMySqlProviderCmdSet:groupAddNew2008, guidMySqlProviderCmdSet:menuAddNew2008, 0x0000;
NEWGROUPS_END

Note here that 8706 is the id of the global data menu in VS 2005 and IDG_DV_GLOBAL1 is the id under VS 2008.  menuAddNew2005, menuAddNew2008, groupAddNew2005, and groupAddNew2008 are just simple ids I assigned. The reason you need to define the menu twice is because no matter which version of VS you use it will balk at adding the same menu to two different top level groups.

Then I define the commands I want to appear on the menus but I don't put them on the menus here since I need to put them on two different menus.

/* global data menu items */
guidMySqlProviderCmdSet:cmdidAddNewTableGlobal, Group_Undefined:0, 0x0000, OI_NOID, BUTTON, DIS_DEF, "&Table";
guidMySqlProviderCmdSet:cmdidAddNewViewGlobal, Group_Undefined:0, 0x0001, OI_NOID, BUTTON, DIS_DEF, "Vie&w";
guidMySqlProviderCmdSet:cmdidAddNewProcedureGlobal, Group_Undefined:0, 0x0002, OI_NOID, BUTTON, DIS_DEF, "Stored &Procedure";
guidMySqlProviderCmdSet:cmdidAddNewFunctionGlobal, Group_Undefined:0, 0x0003, OI_NOID, BUTTON, DIS_DEF, "Stored &Function";
guidMySqlProviderCmdSet:cmdidAddNewUDFGlobal, Group_Undefined:0, 0x0004, OI_NOID, BUTTON, DIS_DEF, "&UDF";

Now I add the commands to both menus

guidMySqlProviderCmdSet:cmdidAddNewTableGlobal, guidMySqlProviderCmdSet:groupAddNew2005, 1;
guidMySqlProviderCmdSet:cmdidAddNewTableGlobal, guidMySqlProviderCmdSet:groupAddNew2008, 1;

/* others omitted for brevity  */

Now we need to add our commands to our data view XML file.  Here is what one command binding looks like.

<CommandBinding name="AddNewTable" guid="B87CB51F-8A01-4c5e-BF3E-5D0565D5397D"
                cmdid="500" handler="MySql.Data.VisualStudio.MySqlDataViewCommandHandler">
  <Parameter value="HighLevel"/>
</CommandBinding>

Notice the command parameter named "HighLevel".  This is important because without it the menus will not function correctly in VS 2005.  You also need to add all the commands into the command bindings of every node.  Yes, this is very repetitious but is necessary due to a bug in VS 2005.  Without doing that the menu will appear when the connection node is selected but will disappear when on the other nodes.

I would also like to thank a developer at Microsoft who answered tons of my questions and helped me sort this out.  His name is Stephen Provine and I really appreciate all his hard work.  With all this done, here is the same shot working with MySQL.

Switched to BlogEngine.NET

• Posted by reggie on March 6, 2008
• 6 comments

Well, I ran out of database room on my hosting account yesterday so the only thing to do was to move back to a blogging package that is not database backed.  I thought about going back to dasBlog but instead decided to try out BlogEngine.NET.  It's a new blog engine written in .NET and open source.

It seems to have quite a bit of traction and has some nice features.  After a few minor skirmishes I got my blog exported out of Subtext in a BlogML format and it imported into BlogEngine very easily.  The only issue right now is existing URLs that are out there and waiting for Google to re-index the site.

Anyway, so far I like it.  Let me know what you think!

Syntax highlighting on the way!

• Posted by reggie on March 4, 2008
• 1 comments

I've been working very hard on adding new features to our Visual Studio integration and one of those features is syntax highlighting of SQL when creating and editing stored procedures.  With this new build we are using the core editor that is built inside Visual Studio.  This brings other features such as the ability to split the code window.  Currently I am hoping to have these new features in our 5.3 product.