{"id":376,"date":"2006-09-27T11:57:18","date_gmt":"2006-09-27T17:57:18","guid":{"rendered":"http:\/\/www.mooreds.com\/wordpress\/?p=376"},"modified":"2007-04-11T16:20:07","modified_gmt":"2007-04-11T22:20:07","slug":"installing-the-median-user-defined-function-on-mysql","status":"publish","type":"post","link":"https:\/\/www.mooreds.com\/wordpress\/archives\/376","title":{"rendered":"Installing the median user defined function on MySQL"},"content":{"rendered":"

I just re-read “How To Lie With Statistics”<\/a>, which is so good I think it should be required reading in every middle school. In it, the author makes the point that there are three kinds of ‘averages’: arithmetic mean<\/a>, median<\/a> and mode<\/a> (here I am, contributing to Wikipedia’s dominance<\/a>, due to my laziness in looking up alternative definitions of statistical concepts). In general, the median is the most informative average, because it’s not skewed by a small number of outliers.But mysql (and other databases I’ve worked on) don’t natively supprt the medan, whereas I believe most support average (by which they mean ‘arithmetic mean’). Sure, you can use a stored procedure (as suggested here for PostgreSQL<\/a>. However, I’m working with MySQL 4, which does not support stored procs. However, there is another solution: user defined functions<\/a>. These seem like stored procedures, except you have to write them in C (or C++).<\/p>\n

Now, I’m not a C programmer. Luckily, someone has written and released a set of mysql user defined functions that include median<\/a> (as well as many other statistical manipulations). The bad news is that it hasn’t been updated for years. The good news is that with a bit of luck and many downloads, I was able to get the median function working on mysql, both on windows as a dll, and on linux as a shared library. To repeat, I am not a C programmer, so if you see any head thumping errors below, please let me know and I’ll update this document.<\/p>\n

First off, I was working with these versions of mysql: c:\\Program Files\\MySQL\\MySQL Server 4.1\\bin\\mysql.exe Ver 14.7 Distrib 4.1.10a, for Win95\/Win98 (i32)<\/code> and mysql Ver 14.7 Distrib 4.1.7, for pc-linux (i686)<\/code><\/p>\n

To get median working on windows, you need to:<\/p>\n

    \n
  1. Download the mysql-udf tarball<\/a>.<\/li>\n
  2. patch the files if you’re running a version of mysql greater than 4.1.1. patch available here<\/a>, or the patched tarball is here<\/a>.<\/li>\n
  3. Download and install Visual C++ Express<\/a>. (If you have a C compiler on Windows, you can skip this step and the next. Oh, and the ones following that will probably be different. (Here’s a blog post about creating a UDF using Visual Studio C++ 2003<\/a>.)<\/li>\n
  4. Download and install the platform SDK<\/a>; I only followed through step 3. If you don’t, you’ll get ‘windows.h’ errors when you try to compile the UDF.<\/li>\n
  5. Untar the myslq-udf tarball. Patch if needed.<\/li>\n
  6. Install the mysql header files. I was able to do this via the Windows Installer, which let me modify my existing mysql installation; I had to add the ‘C Include Files \/ Lib Files’ feature.<\/li>\n
  7. Create a new directory. Copy udf_median.cc<\/code> from the untarred directory to this new directory.<\/li>\n
  8. Create a new file in that directory called udf_median.def<\/code>. This file contains all the methods the UDF is exporting. Or you can just download the file I used here<\/a>.<\/li>\n
  9. Open Visual C++ Express<\/li>\n
  10. Choose File \/ New \/ Project From Existing Code. Hit Next. Browse to the directory you just created. Create a name for the project. Hit Finish<\/li>\n
  11. Edit the udf_median.cc<\/code> file and comment out the #ifdef HAVE_DLOPEN<\/code> line as well as the corresponding #endif<\/code>. If I didn’t do this, I kept getting link errors, as I guess everything between those preprocessor directives was not being compiled.<\/li>\n
  12. Add the mysql include files: right click on the project and choose properties. Expand ‘Configuration Properties’ then ‘C\/C++’ and click ‘General’. On the right, add an include directory. Navigate to the Mysql include directory and add that.<\/li>\n
  13. Add the module definition file: right click on the project and choose properties. Expand ‘Configuration Properties’ then ‘Linker’ and click ‘Input’. Add ‘udf_median.def’ to the key ‘Module Definition File’.<\/li>\n
  14. Make sure VC knows this is a DLL: right click on the project and choose properties. Expand ‘Configuration Properties’ and click ‘General’. Choose ‘Dynamic Library (.dll)’ for Configuration Type. If you don’t do this, you’ll get errors like: error LNK2019: unresolved external symbol _WinMain<\/code> because the compiler thinks you’re trying to build an application.<\/li>\n
  15. Right click on the project and choose ‘Build’. This gives you a DLL in the Debug directory.<\/li>\n
  16. Copy the DLL to the bin directory of your mysql installation.<\/li>\n
  17. Create the function by logging in to mysql and running this command: CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.dll';<\/code>. (The user you log in as will need to have the ability to insert rows into the mysql tables.)<\/li>\n
  18. Test and enjoy.<\/li>\n<\/ol>\n

    Deploying the UDF to linux is much simpler, mostly because you don’t have to install a compiler, linker, etc. I used ‘gcc (GCC) 3.3.4’.<\/p>\n

      \n
    1. Download the mysql-udf tarball<\/a>.<\/li>\n
    2. patch the files if you’re running a version of mysql greater than 4.1.1. patch available here<\/a>, or the patched tarball is here<\/a>.<\/li>\n
    3. Untar the myslq-udf tarball. Patch if needed.<\/li>\n
    4. Edit the udf_median.cc<\/code> file and comment out the #ifdef HAVE_DLOPEN <\/code> line as well as the corresponding #endif<\/code>.<\/li>\n
    5. Compile and link the code. Do not use the instructions on the mysql-udf<\/a> homepage. If you compile with those flags, you’ll get this error when you try to add the function: mysql> CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.so';
      \nERROR 1126 (HY000): Can't open shared library 'udf_median.so' (errno: 22 \/usr\/lib\/udf_median.so: undefined symbol: _Znwj)<\/code>. Rather, use the instructions in       this bug report<\/a>: gcc -shared -lstdc++ -I \/usr\/include -I \/usr\/local\/include -I \/usr\/local\/mysql\/include\/ -o udf_median.so udf_median.cc' <\/code><\/li>\n
    6. Copy the shared library to a directory where mysql will see it. I put it in \/usr\/lib<\/code>.<\/li>\n
    7. Create the function by logging in to mysql and running this command: CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.so';<\/code>. (The user you log in as will need to have the ability to insert rows into the mysql tables.)<\/li>\n
    8. Test and enjoy.<\/li>\n<\/ol>\n

      We have pushed this UDF to production with replicated servers and haven’t seen any issues with it yet.<\/p>\n

      I want to extend my thanks to:<\/p>\n