MySQL udf_median on Windows

A few years ago I had to get a MySQL UDF (User Defined Function) working on my Windows workstation for a project I was working on. I had the benefit of a couple of other folks to help me get my environment set up and a .dll compiled. I was recently contacted about my project files and realized I had an orphaned link out on the Internet so I thought I’d better fix that up.

I’ve captured the text from my previous post and put it here. I apologize for not having the images available but will see what I can do about that. Anyway, here’s the text I first published in 2008:

UDF median on Windows

On my current project we use MySQL as part of our stack and I needed to setup MySQL on my Windows XP workstation for development. A custom feature of the database is the use of a MySQL UDF (User Defined Function), discussed in depth here, here and lots of other places.

We had a pre-compiled Windows binary DLL available that did not work. Dan Moore told me that he needed to recompile on his workstation as well so I set about figuring out how to rebuild on my workstation.

While this is somewhat of a digression, it might be useful information. The error that was returned with our pre-compiled DLL was this:

ERROR 1126 (HY000): Can't open shared library 'file.dll'

The error message is supposed to indicate that the location of the DLL is incorrect, however that was not the issue in this case. It was in the bin/ directory of the MySQL installation where the recompiled DLL was later found, loaded and made usable by the server. I did not look any further into this issue since recompiling fixed the problem.

Returning to the subject at hand, because I found that there are differences in the process to setup between Visual Studio Express Edition 2005, as described in Roland Bouman’s blog post, and Visual Studio 2008 Professional, I decided to post a write up describing my process. Most of the changes have to do with the New Project wizard and it’s ability to take care of most of the configuration of the project. However if you haven’t already, please stop and read the article on Roland Bouman’s blog before going through this as it has more detail and includes the all important steps of installing the MySQL header files and development libraries used to compile the UDF code.

If you are like me and sometimes prefer “doing” over reading or you just want a compiled binary, you can find the project files here. You will find the bonus feature of a Makefile and some other niceties for building on Linux and Unix variants too!

Note that my source file has been modified to work with MySQL 5.0.x and I’ve only tested it against 5.0.45 since that was the only requirement I had at the time. Please post a comment if you have solutions to problems with other versions.

First, you will want to make sure that you have installed MySQL version 5.0 or later with the header and other development components per the instructions in Roland Bouman’s post.

Next, install the version of VS08 that you have access to. I use VS08 Professional so there may some differences if you use the Express version. After installing VS, download the source file and put it in it’s own folder on your hard drive. This will be your project directory. Once you have the project director and the source file, udf_median.cc in my case, you can start VS08 in order to start setting up the project.

Once VS08 is running, go to the “File” menu and click “New” then “Project from Existing Code…”, as such:

Project from Existing Code…

The next screen will allow you to choose a C++ project (I also use C# so this step is required):

Choose C++

The next dialog allows you to select the folder that contains your source file. This is what it should look like once you select your project folder and give the project a name:

The next dialog allows you to define the project type. We’re building a DLL (Dynamically Linked Library) so select the appropriate item from the drop down:

In the next two dialogs, we are configuring properties for the build. There are two builds, Debug and Release. All we are going to add is the path to the MySQL header files.

Because the wizard is so helpful, you don’t have do anything to setup the Release target. Just make it the same as Debug:

Click the “Finish” button and you will be taken to your project. You can try building the DLL at this point by either pressing F6 or choosing it from the “Build” menu:

If you used the source I’ve provided, you should end up with a compiler warning that looks like this:
Command line warning D9035 : option ‘Wp64’ has been deprecated and will be removed in a future release

Since I’m a stickler for clean builds, I decided to fix this. First right-click on your project (not the “Solution” at the top level) then select “Properties”:

Once in the Properties dialog, expand the “Configuration Properties” item in the left pane, then expand the “C/C++” item. Select “General”. In the right pane, sixth row down you should see the property “Detect 64-bit Portability Issues”. Set the property to “No”:

To fix the release build, select “Release” from the “Configuration” drop down at the very top of the dialog and set the same property to “No”. Click “OK” to close this dialog.

Speaking of the Release build, once you’ve made it to this point we should try that build as well. At the top of the IDE on the tool bar there is a drop down that should be set to “Debug”. Click on this drop down and select “Release”, as such:

Press the F6 key to build the release. Again if you are using my source and following these instructions, you should get the following build warnings:

warning LNK4075: ignoring '/INCREMENTAL' due to '/OPT:ICF' specification

To remedy this, you again want to right-click on the project and select “Properties”. Make sure that the “Release” configuration is selected at the top of the dialog. This time drill down to the “Linker” item and select “General”. In the right-hand pane you will find a property labeled “Enable Incremental Linking”. Set the value to “Default” then click “OK” to to close the dialog.

Once you are out of the dialog press F6 to build and you should get a clean build with no errors or warnings. The output DLL will be in a subfolder of the project named “Release”. Copy the udf_median.dll file into the bin directory of the MySQL server instance. Start a MySQL client and connect to the database server. From a query window as the root or other user with the appropriate privileges, issue this SQL query:

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

You should now be able to use the function in your SQL queries.

You can see example output in one of the web applications udf_median is used in here.

Last and certainly not least, I would like to acknowledge the work of Dan Moore and Roland Bouman without who’s excellent work I would not have been able to figure this out. Cheers!

  1. […] the minor but ongoing saga of maintaining support for the venerable MySQL UDF function udf_median, I can now add a HOWTO for […]

Leave a Reply