MySQL udf_median on Windows 7 64bit

In a minor but ongoing saga of supporting the venerable MySQL UDF function udf_median, I can now add a HOWTO for building it on Windows 7 x64 using Microsoft Visual C++ Express 2010.

I should point out my previous article on the subject since there are parts of it that are still applicable.

This is likely applicable to other MySQL UDFs as well but I haven’t tried.

I used the MySQL 5.1.57 x64 version for my system and I downloaded the zip archive rather than the installer. (Note that the server can still be installed as a service but you will need to run the cmd.exe program as an administrator in order to run the command line installation process.)

I also used Microsoft Visual C++ Express 2010 for this and my Windows version is Windows 7 Ultimate x64.

In addition to VS C++, you will also need the Windows SDK for Windows 7 which you can download here. This addition is critical since it contains the x64 compiler and other tools.

When you have everything installed, follow the instructions on Roland Bouman’s blog post about building UDFs on Windows but stop before building and installing the function then follow these steps to 64bit glory (if you are specifically using udf_median, you might also be interested in this post):

  1. Right-click on the project (not the solution), choose “Properties”. At the top of the dialog, from the “Configuration” dropdown, select “All Configurations”.
  2. Next expand “Configuration Properties” then select “General”. In the field on the right labeled “Platform Toolset” make sure the value “Windows7.1SDK” is selected
  3. Now let’s make it x64. At the top of the dialog box, click on the “Configuration Manager” button.
  4. In the resultant grid, select the “Platform” dropdown and choose “New…”. When the “New Project Platform” comes up, select x64 from the top dropdown then click “OK” then “Close” then close the Properties dialog.
  5. Now you can try building the project
  6. If the build is successful, you find the .dll, in my case ufd_median.dll, in the “Debug” or “Release” folder. Put that in the MySQL lib/plugin/ directory
  7. Install the plugin using SQL like this:

    CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.dll';

And that should be it.

Here are a few things that I came across while setting up my project that might be helpful:

  1. In one instance the linker reported this error:
    1>LINK : fatal error LNK1104: cannot open file 'kernel32.lib'
    The fix for this was making sure that the “Platform Toolset” in the project configuration properties was set to “Windows7.1SDK”
  2. If you get this error:
    ERROR 1126 (HY000): Can't open shared library 'file.dll'
    this can sometimes mean that the .dll wasn’t compiled correctly (i.e. this is what you get when you try to use a 32bit .dll with a 64bit server) or the symbol exports didn’t work. I used the dumpbin program to make sure that the functions that needed to be exported were. Under the “Tools” menu in VC++, select “Visual Studio Command Prompt” then navigate to the directory with your .dll file and run this command:
    dumpbin /exports udf_median.dll
    You should see the exported functions in the output.
  3. I did not have to alter any of the C code to make this work even though I saw some comments on Roland’s post indicating that might be necessary.

That’s about it. Feel free to contact me if you have any questions or if the steps didn’t work for you.

  1. Idan Zalzberg says:

    Hi,
    I tried to follow your method but it didn’t work for me (I got the 1126 error). The difference in my case that I am using visual studio 2010 ultimate (with the preinstalled tools). I see the exported methods in the resulting dll but still it won’t work

  2. Jeff says:

    @Idan: I spoke with the engineer that built udf_median on his laptop a couple weeks ago and while he had the 1126 error he couldn’t remember exactly what he did to resolve it. We both remembered going through the project configuration together and after that the library built correctly. There were some issues he had with the output file name and some of the header file includes.

    I also discussed that error in a previous post on the subject:

    http://jeffbeard.org/2011/04/mysql-udf_median-on-windows/

    Let me know if you still need some help.

    –Jeff

  3. hans says:

    question – when compiling on one machine, but installing the plugin to another machine where visual studio or visual C++ express is not installed does not seem to work. i was able to make it work by only installing the windows sdk on the 2nd box. (leaving out visual studio or the mysql include directories) Also just the C++ redistributable package libraries didn’t work. I’m trying to minimize what is getting installed on the production box – which items in the windows 7.1 sdk are required on the target box?

  4. Jeff says:

    @hans: I’m sorry but I don’t know. All my deployments were actually on Linux so I never had to figure out what was needed to share the compiled plugin. If you figure it out please post a comment. You might also check out Roland Bouman’s blog on the subject as well.

  5. Emanuel says:

    Jefff,

    Thank you for writing this up.
    I have tried implementing this on Roland’s lib_mysqludf_udf.c to compile an dll to use on my x64 machine. But i have had no success so far.

    When following your guide (after reinstalling 7.1 dtk cause of bug in visual c++ 2010 express) I get following error when trying to create function:

    Error 1127 on the first command.
    Any advice?

    Regards Emanuel

  6. Jeff says:

    Emanuel,

    It’s been a while since I’ve worked with the UDF on Windows but the error sounds vaguely familiar. I was able to find a Stackoverflow post that seems like it describes 3 things that need to happen in order for the MySQL server to find the UDF:

    1. Function signatures need to be correct
    2. The compiled dll needs to be in the lib/plugins folder of the server install
    3. The “CREATE FUNCTION” SQL need to look like this:

    CREATE AGGREGATE FUNCTION example RETURNS REAL SONAME ‘udf_example.dll’;

    Here’s the link:

    http://stackoverflow.com/questions/6523166/error-1127-in-mysql-udf-creation

    I hope this is helpful.

  1. There are no trackbacks for this post yet.

Leave a Reply