Monday, September 24, 2007

Creating MySQL UDFs with Microsoft Visual C++ Express

Some time ago, I announced the MySQL UDF Repository. In short, the MySQL UDF Repository tries to be a one stop place to obtain high quality LGPL licensed libraries containing MySQL UDFs, including documentation and binaries. Since the announcement, our Google Group has grown to a 22 members (including a number of MySQL employees and prominent community members), and we've gained a few interesting new UDF libraries:

lib_mysqludf_preg
A library authored by Rich Waters providing PERL compatible regular expressions.

lib_mysqludf_xql
A library authored by Arnold Daniels with many useful functions to map and export relational data from MySQL to XML.

Another thing that we see happening now is that people are starting to ask for windows binaries. Although we intend to provide binaries for major operating systems, we haven't really got round to it yet for Windows in particular.

I want to make a start now by explaining how to create and run MySQL UDFs on Windows using the Express Edition of the popular Microsoft Visual C++ IDE. I hope this information will be useful to the many MS oriented developers out there that have trouble getting started with MySQL UDFs.

Preparation

Before we can actually start, we need to install and configure some software and obtain a few resources.

Installing Visual C++ 2005 Express Edition

First, you'll need to download and install Microsoft Visual C++ 2005 Express Edition. At present, this is the latest stable release of the free (As in Beer) version of the popular Visual Studio IDE, set up to create, compile and debug C++ programs.

The installation procedure can take a little while, but is otherwise pretty straightforward.

If you already have a paid-for version of Visual Studio 2005, you should not install the express edition (you are at risk of messing up the existing installation if you do). In that case, you should use your paid-for version or alternatively, download the upcoming release (Microsoft Visual C++ 2008 Express Edition, now in Beta) and give that a spin.

Installing the Microsoft Platform SDK

Apart from Visual Studio, you also need to have the Microsoft Platform SDK installed. Although this SDK is officially entitled "Microsoft ® Windows Server® 2003 R2 Platform SDK", it includes the resources for many flavours of Windows, including Windows 2000, Windows 2003 and Windows XP.

Installing the MySQL Development resources

Source files for UDFs contain references to header files supplied by MySQL. The easiest way to obtain them is by installing them using the Setup.exe installer program that you use to install the MySQL Server.

If you are installing a new server, you can ensure that the files are installed by choosing "custom" in the "Setup Type" step of the wizard started by the Setup.exe:

MySQL-custom

After that, you will be able to choose which features you want to install. You need to ensure that the "C Include files/Lib Files" under the "Developer Components" is selected:

MySQL-dev-components

If you are not installing a new server, you should first check to see if you have an include directory immediately beneath the MySQL base direction. If so, you probably don't need to do anything right now.

If you don't have the include directory, it probably means you did not choose to install the "C Include files/Lib Files" when installing the server (by default, they are not installed). Running the Setup.exe program again will offer you the possibility to add new components to the installation:

MySQL-modify

And from here, you will be led to the step where you can choose to install the include files and library files.

Setting up a VC++EE Project for MySQL UDFs

Once you fulfilled all necessary prerequisites, the next step is to create a Visual Studio Project. In this context, a project is a container for source files, resources, references to existing libraries, as well as a number of options to compile the source files.

In this article we will set up a VS project to compile the (existing) source of lib_mysqludf_udf. This is a library that demonstrates the basic features of the MySQL UDF interface, such as processing arguments, returning values and handling errors. It is great to get started programming MySQL UDFs. In order to walk through this example, you only need to download the C source file. (Tip: right click the link and choose an option from the context menu to download the file to your local file system.)

Creating a new Project

To actually create the project, we can use the File/New/Project... menu or else the "create project" hyperlink on the the Visual Studio Startpage:

VC++EE-new-project-menu

This opens a dialog where we must enter a few details about our project:

VC++EE-new-project-dialog-project

For the Visual C++ Express edition, it works best to choose a General/Empty Project. (The paid-for edition of Visual Studio provides templates for projects to create dynamically linked libraries a.k.a. DLLs but as we shall see later on we have to configure this manually.)

We are also required to provide a name for the project. In this case, we use a name that corresponds directly to the source file: lib_mysqludf_udf.

Visual Studio Solution

In Visual Studio, a project is always part of a Solution, which is basically a container for a number of related projects. Because we just started a new project, we are implicitly creating a new solution too, so we have to specify a few things about that as well:

VC++EE-new-project-dialog-solution

In this case, we create a separate directory for the solution itself, and we use the same name for the solution as for the project. It is important to realize that there can be multiple projects per solution, in which case it probably makes more sense to choose a distinct name for the solution as a whole.

After confirming the dialog, a number of directories and files are created:

VC++EE-project-dirs

Adding a source file

Now it is time to add the source file to our project, so if you didn't download the lib_mysqludf_udf C source file yet, you should do so now. Be sure to copy the lib_mysqludf_udf.c source file to the lib_mysqludf_udf project directory beneath the lib_mysqludf_udf solution directory:

VC++EE-copy-source-file

Copying the source file there is just a matter of convenience - I like to keep things that belong together in one place. If you don't keep the file in the project directory, things might may (and probably will) go wrong if you move the source file or the project to another location later on.

Copying the file to the directory still does not formally add the file to the project. To actually add the file to the project, you can right-click the "Source Files" folder beneath the project folder in the Visual Studio Solution Explorer window and use the context menu to add the existing item:

VC++EE-add-existing-item-menu

As an alternative to using the menu you can also add the source file to your project by simply dragging it into the Solution Explorer and dropping it into the "Source Files" folder of the project.

If all went well, the source file is now part of the project and can be opened from the solution explorer:

VC++EE-source-file-added

Project Configuration

Although we already defined the structure of the project, we need to configure it in order to compile it. The configuration can be edited through a single dialog which can be accessed by clicking the "Properties" item in the project folder's context menu:

VC++EE-opening-properties

General Properties

We first need to take care of the general configuration. A Visual Studio project can have several configurations - something which is very useful if you want to create different builds (debug or release) from the same project. However, it is a good idea to first configure all project properties that are the same for all configurations. To do that, we choose "All configurations" in the top left listbox of the configuration dialog. (For this example, we do not separately configure for debug and release builds.)

VC++EE-general-properties

The rest of the configuration process is a matter of editing individual properties. Related properties are organized in property pages, each of which covers a particular aspect of the project. By default, the "General" property page is selected and it makes sense to start editing properties there right away.

In the "General" property page we need to set the "Configuration Type" property to "Dynamic library (.dll)" as we need to be able to load the UDF library dynamically into the MySQL Server.

Configuring the Include path

MySQL UDFs refer to types and contants defined in C header files provided by MySQL. In turn, these refer to header files from the Microsoft Platform SDK. The project does not know automatically where to locate these header files, so we need to configure the project and point it to the location(s) manually.

To specify the location of the header files, we need to activate the "C/C++" property page and edit the "Additional Include Directories" property. You can either directly type the paths in the text box, or otherwise click the elipsis buttons (...) to browse for them.

VC++EE-include-path

For this example, we need to specify two locations:

  • The location of the "include" directory beneath the MySQL installation directory.

  • The location of the "include" directory beneath the Microsoft Platform SDK installation directory.

If you can't find these directorie, you most likely need to revisit the "preparation" section of this article.

Adding the HAVE_DLOPEN macro

The lib_mysqludf_udf.c source file was created using the udf_example.c source file from the MySQL source distribution as an example. The structure of that code uses conditional compilation according to wheter HAVE_DLOPEN is defined:

#ifdef HAVE_DLOPEN

...code goes here...

#endif /* HAVE_DLOPEN */
And this is also used in lib_mysqludf_udf.

I admit that I don't understand why that is there, or what it is supposed to achieve, and I would very much like someone to comment on this blog entry to explain it. Anyway, for Visual C++ it means we have to explicitly define it using a Preprocessor definition:

VC++EE-preprocessor

Configuring the library path

We configured the project to compile a Dynamic-Link Library. For the compiler, this means it cannot just compile the code and package it in a file: the dll target file needs to adhere to a certain specification. In order to make that happen, it needs to link to existing libraries from the platform SDK.

Just like we did for the include path, we need to tell Visual C++ where it can find the libraries it must link to. This can be configured by editing the "Additional Library Directories" property in the "Linker" property page:

VC++EE-linker

In this case, we only need to specify the path of the "Lib" directory find immediately beneath the Platform SDK installation directory.

Compiling the UDFs

At this point, we are ready to compile the project and/or solution. In most cases, you will want to choose the build configuration to choose between a debug or a release build. This can be done by clicking the "Configuration Manager" item in the build menu to invoke the Configuration Manager dialog:

VC++EE-config-manager

Actually building the project is done using the "Build Solution" or " Build Project" item in the "Build" menu:

VC++EE-build

The result of building the solution should be as indicated in the screenshot. If the final line does not read
1>lib_mysqludf_udf - 0 error(s), 0 warning(s)
you might want to read the remainder of this section to figure out what the problem is.

Common problems

Of course, no programming task is complete without running into trouble. In this section, a few common problems compiling the project are listed, as well as their solutions.
fatal error C1083: Cannot open include file: 'filename.h'
The output of the build process might look something like this:
1>Compiling...
1>lib_mysqludf_udf.c
1>..\..\..\temp\lib_mysqludf_udf.c(41) : fatal error C1083: Cannot open include file: 'my_global.h': No such file or directory
1>Build log was saved at "file://c:\projects\lib_mysqludf_udf\lib_mysqludf_udf\Release\BuildLog.htm"
1>lib_mysqludf_udf - 1 error(s), 0 warning(s)
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========
This indicates that you did not configure the include path properly. You should revisit the section on configuring the include path and ensure that the path does in fact point to the include directory that appears under the mysql installation directory.

The build output might look something like this:
1>Compiling...
1>lib_mysqludf_udf.c
1>C:\MySQL Server 5.1.21\include\config-win.h(30) : fatal error C1083: Cannot open include file: 'winsock2.h': No such file or directory
1>Build log was saved at "file://c:\projects\lib_mysqludf_udf\lib_mysqludf_udf\Release\BuildLog.htm"
1>lib_mysqludf_udf - 1 error(s), 0 warning(s)
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========
This is a similar problem. It occurs when you did include the "include" directory beneath the MySQL installation directory but forgot the one beneath the Microsoft Platform SDK installation directory. Because the latter is referenced by the former, both have to be added to the include path.
fatal error LNK1104: cannot open file 'file.lib'
Your build output might look like this:
1>------ Build started: Project: lib_mysqludf_udf, Configuration: Release Win32 ------
1>Linking...
1>LINK : fatal error LNK1104: cannot open file 'uuid.lib'
1>Build log was saved at "file://c:\projects\lib_mysqludf_udf\lib_mysqludf_udf\Release\BuildLog.htm"
1>lib_mysqludf_udf - 1 error(s), 0 warning(s)
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========
This indicates that you did not properly configure the path where Visual Studio looks for Additional Libraries. You should revisit the relevant section in this article and ensure the configured path does in fact contain the specified missing library.

Installing the UDFs

If you managed to succesfully compile the project you are ready to install the UDFs in your MySQL Server. Depending on the whether you chose to do a "Release" or a "Debug" build, you will find the lib_mysqludf_udf.dll in the "Release" or "Debug" directory directly beneath the Solution directory respectively:

VC++EE-dll-location

The .ddl needs to be copied to a location that is accessible to the MySQL Server. For MySQL versions lower than 5.1.19, the bin and/or lib directories right beneath the MySQL installation directory should work. For MySQL version 5.1.19 and beyond, you are required to copy the dll to the plugin_dir. The plugin_dir can be determined by running the following query:
mysql> show variables like 'plugin_dir';
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| plugin_dir | C:\MySQL Server 5.1.21\lib/ |
+---------------+-----------------------------+
1 row in set (0.00 sec)

When the dll is in place, the UDFs can be installed using the CREATE FUNCTION syntax. The following script will install all functions in lib_mysqludf_udf.dll:
CREATE FUNCTION lib_mysqludf_udf_info 
RETURNS STRING
SONAME 'lib_mysqludf_udf.dll'
;
CREATE FUNCTION udf_arg_count
RETURNS INTEGER
SONAME 'lib_mysqludf_udf.dll'
;
CREATE FUNCTION udf_arg_type
RETURNS INTEGER
SONAME 'lib_mysqludf_udf.dll'
;
CREATE FUNCTION udf_arg_value
RETURNS STRING
SONAME 'lib_mysqludf_udf.dll'
;
CREATE FUNCTION udf_arg_value_length
RETURNS INTEGER
SONAME 'lib_mysqludf_udf.dll'
;
CREATE FUNCTION udf_arg_maybe_null
RETURNS INTEGER
SONAME 'lib_mysqludf_udf.dll'
;
CREATE FUNCTION udf_arg_attribute
RETURNS STRING
SONAME 'lib_mysqludf_udf.dll'
;
CREATE FUNCTION udf_arg_attribute_length
RETURNS INTEGER
SONAME 'lib_mysqludf_udf.dll'
;
CREATE FUNCTION udf_init_error
RETURNS INTEGER
SONAME 'lib_mysqludf_udf.dll'
;

Common Problems

Even if you successfully compiled the solution, you still might run into a few problems at this stage. A few common ones are described in the remainder of this section.
ERROR 1126 (HY000): Can't open shared library 'file.dll'
If you encounter this error, it means that MySQL cannot find the library you are referring to in the SONAME clause of the CREATE FUNCTION statement. You may have made a typo in your statement, or the MySQL may be looking in another location for the libarary than you might think it does. Verify that you typed the correct location. For MySQL 5.1.18 and earlier, ensure that the dll is copied to either the bin and/or lib directory beneath the MySQL installation directory. For MySQL 5.1.19 and beyond, ensure that the file is located in the plugin_dir.
ERROR 1127 (HY000): Can't find symbol 'functionname' in library
If you encounter this error, a few things might be the matter. You might have made a typo in the function identifier in the CREATE FUNCTION statement. Another possibility is that you forgot to add the HAVE_DLOPEN macro to the preprocessor definitions. If needed, revisit that section in this article.
ERROR 1046 (3D000): No database selected
This error occurs when you did not set the default database. The workaround is to set any database as default database using the USE statement:
USE test;
. Arguably this is a bug in the MySQL Server: it somehow thinks we are trying to create a stored function which is bound to a database. It's as if MySQL cannot distinguish between a UDF and a stored function at this point.
ERROR 1044 (42000): Access denied for user 'user'@'host' to database 'mysql'
This error occurs when the user that is trying to create the function is not privileged to write to the mysql system database. To the best of my knowledge, UDFs are written only the mysql.func table so I would expect that granting privileges on that table would be enough to be allowed to create UDFs. It turns out that this is not the case. Granting all privileges on the mysql database does allow a user to install UDFs, but I don't know if that is indeed the minimal set of privileges required to install UDFs.

Using UDFs

If you succesfully installed the UDFs, any user will be able to use them, regardless of the setting for the default database . Here is a quick set of examples for lib_mysqludf_udf. The examples in itself are not very useful in itself, but you they can be useful tools for general UDF development when debugging or testing. Also, you can learn from the code how to work with the UDF interface)
mysql> -- show the lib_mysqludf_udf version
mysql> select lib_mysqludf_udf_info();
+--------------------------------+
| lib_mysqludf_udf_info() |
+--------------------------------+
| lib_mysqludf_sys version 0.0.2 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> -- returns the number of passed arguments
mysql> select udf_arg_count(1,2,3,4);
+------------------------+
| udf_arg_count(1,2,3,4) |
+------------------------+
| 4 |
+------------------------+
1 row in set (0.00 sec)

mysql> -- returns the type of the passed argument
mysql> select udf_arg_type(1), udf_arg_type('string');
+-----------------+------------------------+
| udf_arg_type(1) | udf_arg_type('string') |
+-----------------+------------------------+
| 2 | 0 |
+-----------------+------------------------+
1 row in set (0.00 sec)

mysql> -- returns the (string)value of the passed argument
mysql> select udf_arg_value(1);
+------------------+
| udf_arg_value(1) |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

mysql> -- returns the string-length of the passed argument
mysql> select udf_arg_value_length(123);
+---------------------------+
| udf_arg_value_length(123) |
+---------------------------+
| 3 |
+---------------------------+
1 row in set (0.00 sec)

mysql> -- returns 1 or 0 according to whether the passed argument might be null
mysql> select udf_arg_maybe_null(null), udf_arg_maybe_null(1);
+--------------------------+-----------------------+
| udf_arg_maybe_null(null) | udf_arg_maybe_null(1) |
+--------------------------+-----------------------+
| 1 | 0 |
+--------------------------+-----------------------+
1 row in set (0.00 sec)

mysql> -- returns the expression text or its alias
mysql> select udf_arg_attribute(1+1), udf_arg_attribute(1+1 as alias);
+------------------------+---------------------------------+
| udf_arg_attribute(1+1) | udf_arg_attribute(1+1 as alias) |
+------------------------+---------------------------------+
| 1+1 | alias |
+------------------------+---------------------------------+
1 row in set (0.00 sec)

mysql> -- returns the length of the expression text or its alias
mysql> select udf_arg_attribute_length(1+1);
+-------------------------------+
| udf_arg_attribute_length(1+1) |
+-------------------------------+
| 3 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> -- throws an error
mysql> select udf_init_error('Whoops!');
ERROR:
Whoops!

139 comments:

Dossy Shiobara said...

Have you seen my MySQL UDF that embeds a Tcl interpreter:

http://dossy.org/archives/000503.html

I have produced Win32 binaries for it, too.

rpbouman said...

Hi Dossy!

No, I never heard of the Tcl interpreter UDF. That's probably because I have not been looking for it.

I just checked out your blog post on it: it's quite interesting!

Would you be interested in joining the MySQL UDF Repository?

jbalint said...

Roland,
Great write-up! Especially useful to include solutions for common problems.

rpbouman said...

Thanks Jess!

"Especially useful to include solutions for common problems."

Yeah, it felt like kind of a drag at the time to provoke them and catch the output for this write up, but I ran into all of these problems myself at some point, so I figured it might be useful for others to get a quick pointer to the solution of the problem.

Anyway, at some point you explained to me what this HAVE_DLOPEN thing is, but I forgot. Do you remember? If, just leave a comment for posterity ;-)

TIA,

Roland

Murali.V said...

Hi Roland,

I need your help right now.

I want to know how to install the UDF Functions to my MYSQL Server. As i have downloaded a UDF funtion of UDF-regexp-1.0 from Net.

But struggling a lot to installing that to my MYSQL Server.

Can u please help me?

rpbouman said...

Hi!

"I want to know how to install the UDF Functions to my MYSQL Server."

This is described in the article.

"As i have downloaded a UDF funtion of UDF-regexp-1.0 from Net."

URL?

"But struggling a lot to installing that to my MYSQL Server."

Well, it is possible that that library is dependent upon other libraries. The distribution should contain a rename or so that explains how to use it.

I hope that helps,

Roland.

passerby said...

Finally, finally got around to reading this tutorial. An excellent explanation of a fairly convoluted process. Everything worked swimmingly and was extremely easy to follow - even for a C++ dummy like me. I originally installed XAMPP from Apache Friends, so that whole part about the MySQL Server Installer was a bit unfamiliar to me. However, the XAMPP installation seems to provide the include directory by default so I was able to skip that step altogether.

Well done, and thanks - it's the first explanation of this subject that I've come anywhere close to being able to understand. OK, so now to start writing my own...

Cheers.

rpbouman said...

Hi Strawberry,

thanks for you kind words!

I'm glad this was useful to you ;)

Roland

Jeff Beard said...

When I received the error "ERROR 1126 (HY000): Can't open shared library 'file.dll'" it was a problem with the binary itself rather than it's location. Recompiling the DLL fixed the issue. I don't know for sure but I'm assuming that it had to do with it being compiled against an older version of the MySQL libraries.

--Jeff

Unknown said...

Hi:
Thanks, this is an excellent article.
I have only one complain:
There are some "header" files that I can't find, even searching within all my computer.
I have copied some of them from my LINUX" machine, but apparently, they aren't compatible with the former.
Where can I find the missing ".h" files?
- libxml/tree.h
- etc.

Craig said...

Hi

Great article, and I've got all that to work fine, but how do I then add more functions to it? I've been trying to just add something that simply returns an integer:
DLLEXP
my_bool udf_GetNumber_init();

DLLEXP
void udf_GetNumber_deinit();

DLLEXP
int udf_GetNumber();

...

my_bool udf_GetNumber_init()
{
return 1;
}
void udf_GetNumber_deinit()
{}

int udf_GetNumber(){
return 99;
}

--------------------------------

The function got added into mysql ok but when I try to call it:
select udf_GetNumber()

I get:
Can't initialize function 'udf_GetNumber'; joN

I'm completely stuck now.

Cheers

Craig.

rpbouman said...

@marcoR-it: whoops, I just noticed this comment - apperently I never answered you, I apologize.

"Where can I find the missing ".h" files?"

Gosh, I don't know...it depends on what you are trying to do...libxml/tree.h sounds like something you should get off the libxml project.


@Craig: thanks for your kind words! I'm so glad you liked it! Regarding your problem: you just ran into a gotcha...the init function must return 0 (false) inn case all is well, and 1 (true) in case some error occurred.

So this is why you get the error message: MySQL can't init the UDF because you are telling the server something's wrong.

Just change it to:
my_bool udf_GetNumber_init()
{
return 0;
}

I hope this helps!

kind regards,

Roland

Craig said...

Hi Roland

Thanks for getting back to me, and your solution has worked...sort of.

I can call the function now, and a number gets returned, but instead of 99 I get:

590379607438393443

I'm sure it's something pretty stupid I'm missing here but I don't really know where to start looking.

Cheers

Craig.

rpbouman said...

Hi Craig,

you made a boo boo in the return type. This:

int udf_GetNumber(){
return 99;
}

should be

long long udf_GetNumber(){
return 99;
}

(you can also use the my_longlong typedef)

You als need to ensure you install the UDF as INT:

CREATE FUNCTION my_udf RETURNS INT SONAME 'bla'

if you use the wrong data type here in the DDL statement, bad things will happen.

HTH, Roland

Craig said...

Hi Roland

Works like a charm, thanks.

Cheers

Craig

Craig said...

Hi again...

I've got a function written that returns a string value after taking in 2 string parameters. I've tried to drop it in with the existing code:

DLLEXP
my_bool udf_GetQueryElementValue_init();

DLLEXP
void udf_GetQueryElementValue_deinit();

DLLEXP
string udf_GetQueryElementValue(string,string);

but I don't think it likes being declared as a string. Sorry to keep posting questions but this is all a bit alien to me and this page is the only good resource I can find.

Cheers

Craig.

rpbouman said...

Hi Craig!

string is not a valid type. char * is what you want. In C/C++ it works like this:

char is a primitive data type that is meant to represent a single byte. Typically ascii characters are represented with this type.
Example:

char c; /* declare one byte variable */
c = 'a'; /* put the byte 'a' into the variable */

Now, in C, a "string" is simply a stretch of bytes - a byte array. Now C has quite a tight coupling between data structures and how they are represented in memory. Just like a char is a byte in memory, a "string" is actually a range of bytes. All C needs to work with it is it needs to know where the string starts (location of the first byte) and it has to know where it ends. Start location is denoted using a pointer - a pointer is basically an integer that represents an address in memory. If we continue the previous example:

char c;
c = 'a';
char *ptr; /* create a pointer variable */
ptr = &c; /* set variable to address of c */
ptr_value = *ptr; /* the * is the dereference operator. It gets the value stored at address contained in the pointer variable */

So ptr is now a string that reads "c"...or well, almost. Although we know the start of the string (it is at the address stored in ptr) we stil don;t know the end of the string. This is solved by terminating strings with a special character, 0 (the NUL byte, numeric value zero)

If you write a string literal, it is automatically terminated by the zero char. Consider this:

char *str = "A String";
char c0 = str[0]; //c0 now contains 'A'
char c1 = str[1]; //c1 now contains ' ' (space)
..
char c8 = str[8]; //c8 now contains '\0'

As you can see we can treat the char * as if its an array

Another way to deal with this is by pointer aritmetic:

char c1 = *(str + 1);

is exactly the same as

char c1 = str[1];

Craig, i think the best thing for you to do now is to grab a copy of lib_mysqludf_udf to see how to deal with these things. Get it here:

http://www.mysqludf.org/lib_mysqludf_udf/index.php

Craig said...

Ok, makes sense. Thanks a lot Roland.

Gorka said...

Hi Roland,
I've tried to install the lib_mysqludf_sys library from http://www.mysqludf.org/lib_mysqludf_sys/index.php, I've followed your tutorial, I compiled the library but I received the error "ERROR 1126 (HY000): Can't open shared library 'lib_mysqludf_sys.dll'". My plugin_dir points to the proper (apparently) dir:

mysql> show variables like 'plugin_dir';
+---------------+-----------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------+
| plugin_dir | C:\Program Files\MySQL\MySQL Server 5.1\lib\plugin/ |
+---------------+-----------------------------------------------------+
1 row in set (0.00 sec)
My mysql version is: Ver 14.14 Distrib 5.1.34, for Win64 (unknown)
I tried to insert manually in mysql.func
INSERT INTO mysql.func (name,dl) VALUES ('lib_mysqludf_sys_info','C:\Program Files\MySQL\MySQL Server 5.1\lib\plugin/lib_mysqludf_sys.dll');
and afterwards try to create the function
CREATE FUNCTION lib_mysqludf_sys_info RETURNS string SONAME 'lib_mysqludf_sys.dll'; but I got all the time the same error :(
Do you know what Could I do?
Thank you in Advance,
Gorka
(sorry for my English)

rpbouman said...

Hi Gorka,

(your English is fine, really :)

I can't see any flaw in what you're doing. The only thing I see that may be different is the fact that you're running a 64 bit MySQL. Did you compile for 64bit?

If that does not help, Can you pls try to do grab a 32bit MySQL, compile dll for 32 bit and see if that makes a difference? I'm just rying to troubleshoot...

Gorka said...

Hi again,
actually, I though that the first line
#if defined(_WIN32) || defined(_WIN64) || defined(__WIN32__) || defined(WIN32)
will detect my 64bits system but now you tell me that I'm not sure. Anyway, I gonna install the 32bit MySQL and compile for 32 and tomorrow or so I'll tell you the result.
You're very quick replying!
Thanks,
Gorka

rpbouman said...

Hi Gorka,

well, this stuff is intended to include a section for any windows platform. Later I learned (just hearsay, pls check and verify) that this:

if defined(__WIN32__)

should do the trick *even for win64*. But I'm not the expert here.

At any rate, this stuff will *not* cause MS VC to start a 64bit compile. You have to choose that for yourself.

(are you running MS VC express? I think that doesn't support 64 bit compiles, I maybe wrong though...)

Gorka said...

Hi Roland,
yes, I'm running MS VC express and I don't know what the problem was but I finally tried with MySQL 32bits and it runs perfectly!! Thank you for your advice. Your tutorial is very helpful!
Thanks again,
Gorka

Unknown said...

Hi Rolando, your tutorial is very good, i made with sucess a udf to return the serial of Volum C.
but to install the dll in a custumer server i need to install C++ redistributable package..
how i can create a no dependency Dll ?

thank's.

rpbouman said...

Hi Eduardo,

glad you liked it. I am not entirely sure how to do it, but I think there must be some compiler flag that can wrap all dependencies in your target lib. If I find it, I'll try and remember to post it. But in the mean while, please look at all compiler options to see if you find something.

Anonymous said...

Hi, Roland, your tutorial is very useful. Thank you very much.
I compile my c program on solaris, and I have no right to write the MySQL installation directory, Would you please tell me how I can install the *.so file to let mysql know where the file is? Thanks

Yan said...

Hi, Roland, your tutorial is very useful. Thank you very much.
I compile my c program on solaris, and I have no right to write the MySQL installation directory, Would you please tell me how I can install the *.so file to let mysql know where the file is? Thanks

rpbouman said...

Hi Yan!

I'm glad the tutorial was useful for you ;)

"I have no right to write the MySQL installation directory, Would you please tell me how I can install the *.so file to let mysql know where the file is?"

I'm afraid you can't. In MySQL 5.1 your UDF .so must be in the plugin directory - this is by design and the purpose is to prevent just any random binary file to be loaded into the server.

Yan said...

Roland, thank you very much.
now I install mysql5.0 on another PC, the OS is fedora8, I use yum to install mysql, but I don't know to put *.so to which directory, the reference manual of MySQL says copy *.so to /usr/lib/, but it can't work. When I execute (CREATE FUNCTION...), error 1126,no such file.
How should I do to solve this problem? thanks again.

Mark said...

Hi Roland,
I've gone through the tutorial and completed the tasks. Right now, I'm trying to create a MySQL UDF in visual C++ that connects to a remote SQL Server 2005 database using SQLAPI. I'm not making much progress because it seems that the complier doesn't like the class syntax. Is such a connection possible?

rpbouman said...

Hi Mark,

what class syntax? What error messages are you getting? I am pretty sure its possible. At the same time, I am curious...what are you trying to achieve? Is a UDF really the right tool for the job?

let us know perhaps we can help.

kind regards,
Roland

Mark said...

Hi Roland,

Taking the original lib_mysqludf_udf.c file, I have added the SQLAPI.lib file (Properties->Existing Item) to the source.

I have added #include for SQLAPI.h to the .c file.

The errors refer to the following lines in the SQLAPI.h file:
class ISAClient;
class ISAConnection;
class ISACursor;

When hit F7 I get the following errors on the SQLAPI.h file:

1>------Build started: Project lib_mysqludf_udf,Configuration:Release Win32------
1>Compiling...
1>Lib_mysqludf_udf.c
1>C:\Program Files\SQLAPI\include\SQLAPI.h(48):error C2061:syntax error:identifier 'ISAClient'
1>C:\Program Files\SQLAPI\include\SQLAPI.h(48):error C2061:syntax error:';'
1>C:\Program Files\SQLAPI\include\SQLAPI.h(48):error C2061:syntax error:identifier 'ISA Connection'
These types of errors continue until the 100 error limit is reached.

I've also questioned whether or not the UDF is the right tool for the job. The alternative is to put this functionality in the application layer which I might do. But regardless I would like to see the options that are available.

Andrew Toth said...

Hello Rich, Hello Roland!

I could follow your instrucions to dress my MySql with preg_replace under Linux, and lib_mysqludf_udf also.

Unfortunatelly I could not insert perg_replace.
pcre.h was missing, however I downloaded it and added to source.

Would you please help me how to make preg_replace UDF into my MySql under Windows.

Thanx in advance:
Andrew

jiff said...

cheers man, thanks a lot

Andrew Toth said...

Hi Roland!
I had some help,
I copied the DLLEXP definition and HAVE_DLOPEN
to your source and it has been build without errors and warnings.

Your lib_mysqludf_udf.dll has been succesfully add to my database, but not the preg.dll.

1126 Can't open...Errno:2

I checked the dll: the functions are there.
I copyied it into the same (bin) directory as udf.dll.

Would you please help me ti finish the process to create the function?

After succeed i will summarize the process for newbies, if you do not mind.

Unknown said...

Thanks Roland for the great article.

I recently needed to do this but using Windows Vista x64 and Visual Studio 2008. The overall process was similar but there are a few differences to note:
1) Visual Studio 2008 comes with the platform SDK included. Thus, I did not need to add the platform SDK paths noted above. (You will find the platform SDK includes/libs in C:\Program Files\Microsoft SDKs\Windows\v6.0A)
2) I am running 64-bit mysql server and so I had to compile a 64-bit binary. Failing to do so would result in the "ERROR 1126 (HY000): Can't open shared library 'file.dll'" when attempting to create the function in MySQL.
3) After compiling everything and resolving the file open problem, I still got the "ERROR 1127 Can't find symbol". I was sure I spelled everything correctly and had defined HAVE_DLOPEN when compiling. Turns out, my UDF code was not windows friendly. You must have __declspec(dllexport) before each extern'd item for it to be actually exported in the dll. You can verify your functions are exported by using the VisualStudio command prompt and typing: dumpbin /exports file.dll
The recommended way of marking up your UDF for Windows support is this:
#if defined(_WIN32) || defined(_WIN64)
#define DLLEXP __declspec(dllexport)
#else
#define DLLEXP
#endif

/* Prototypes */

extern "C" {
DLLEXP my_bool function_init( UDF_INIT* initid, UDF_ARGS* args, char* message );
DLLEXP ulonglong function( UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error );
// etc...
}

Having all this taken care of, I was able to add the UDF to mysql on windows x64. I hope this helps someone else!

rpbouman said...

Hi FredK,

glad you liked the article, and thanks for your addition - this is really useful, and I would not've found time on the short term to amend to article with this info. Much obliged!

kind regards,

Roland.

Anonymous said...

Hi Roland,

I'm impressed! In 10 Minutes I had the sample working in MySQL 5.1.48 (compiled with Visual C++ 2008 Express Edition).
The configuration was easy (no path to Plattform SDK needed).
Now I'll start the serious work :-) the Damerau-Levesthein distance for string comparison. When finished I'll send you the code.
Eugen Schülter

rpbouman said...

Hi Eugen!

glad to hear this got you started so quickly - thanks for letting me know.

If you want to contribute some code, that's great! Please visit mysqludf.org and find contact details there. I will be on vacation and not answering mail as regularly as usual, this way it will get picked up sooner.

kind regards,

Roland

Anonymous said...

Hi Roland. I am attempting to install the lib_mysqludf_stat ( version 0.0.3) udf on Vista from the UDF repo. I used VC Studio express 2010 to compile it with the MySQL 5.1.33 server complete with the include and lib directories.

I am receiving the
ERROR 1127 (HY000): Can't find symbol 'lib_mysqludf_stat_info' in library
error.

I verified that the code had the following:
#ifdef HAVE_DLOPEN

Since those were the two instances that seemed to cause this error, are there other ones that I need to check?

Thanks, Cliff.

rpbouman said...

Hi Cliff!

what is the output of

SELECT version(), @@plugin_dir;

?

Where did you put the DLL ?

TIA,Roland.

Anonymous said...

Thanks for the quick response Roland.

Here is the output:
select version(), @@plugin_dir;

version() @@plugin_dir
| 5.1.33-community | C:\Program Files\MySQL\MySQL Server 5.1\lib/plugin |

That is also the directory in which I have the file located:
dir "C:\Program Files\MySQL\MySQL Server 5.1\lib/plugin"

28/07/2010 04:53 PM 6,656 lib_mysqludf_stat.dll
1 File(s) 6,656 bytes

when I ran the dumpbin command I received the following output - which makes me think that nothing is being exported - but then again I am not a programmer...

dumpbin /exports lib_mysqludf_stat.dll

Dump of file lib_mysqludf_stat.dll

File Type: DLL

Summary

1000 .data
1000 .rdata
1000 .reloc
1000 .rsrc
1000 .text

Any thoughts? I am wondering if I had to do what Fred (above) indicated - but when I looked at the top of the C source code the same information is there.

rpbouman said...

Ok, I didn't read your initial post carefully enough - it is clear that indeed the library is loaded, it's just that the symbols are missing.

Can you remove the entire #ifdef HAVE_DLOPEN ? Just to see if that makes any difference?

kind regards,
ROland

Anonymous said...

Thanks Roland. That did the trick. I commented out the following:
//#ifdef HAVE_DLOPEN
//#endif /* HAVE_DLOPEN */

Works wonderously now. Thank you.

For non-programmers -this is what you should get out of the "dumpbin /exports lib_mysqludf_stat.dll" command

Dump of file lib_mysqludf_stat.dll

File Type: DLL

Section contains the following exports for lib_mysqludf_stat.dll

00000000 characteristics
4C519611 time date stamp Thu Jul 29 08:54:09 2010
0.00 version
1 ordinal base
21 number of functions
21 number of names

ordinal hint RVA name

1 0 00001040 lib_mysqludf_stat_info = _lib_mysqludf_stat_info
2 1 00001030 lib_mysqludf_stat_info_deinit = _lib_mysqludf_stat_inf
o_deinit
3 2 00001000 lib_mysqludf_stat_info_init = _lib_mysqludf_stat_info_
init
4 3 00001180 stat_accum_double = _stat_accum_double
5 4 00001230 stat_accum_double_deinit = _stat_accum_double_deinit
6 5 00001110 stat_accum_double_init = _stat_accum_double_init
7 6 000010E0 stat_accum_int = _stat_accum_int
8 7 00001230 stat_accum_int_deinit = _stat_accum_double_deinit
9 8 00001070 stat_accum_int_init = _stat_accum_int_init
10 9 00001360 stat_pmcc_samp = _stat_pmcc_samp
11 A 000012A0 stat_pmcc_samp_add = _stat_pmcc_samp_add
12 B 00001280 stat_pmcc_samp_clear = _stat_pmcc_samp_clear
13 C 00001230 stat_pmcc_samp_deinit = _stat_accum_double_deinit
14 D 000011B0 stat_pmcc_samp_init = _stat_pmcc_samp_init
15 E 00001250 stat_pmcc_samp_reset = _stat_pmcc_samp_reset
16 F 00001560 stat_ptbis_samp = _stat_ptbis_samp
17 10 000014D0 stat_ptbis_samp_add = _stat_ptbis_samp_add
18 11 000014A0 stat_ptbis_samp_clear = _stat_ptbis_samp_clear
19 12 00001230 stat_ptbis_samp_deinit = _stat_accum_double_deinit
20 13 000013E0 stat_ptbis_samp_init = _stat_ptbis_samp_init
21 14 00001460 stat_ptbis_samp_reset = _stat_ptbis_samp_reset

Summary

1000 .data
1000 .rdata
1000 .reloc
1000 .rsrc
1000 .text

Is there a way that I can contribute the windows .dll to the udf library?

Cliff.

Anonymous said...

Hi,
I would like to know how you can compile udf on 64-bit systems (e.g. Windows XP Pro 64).
Thanks

Arun Chaitanya said...

HI,

I followed the steps mentioned in the below link to install UDF on my Linux where i installed mySQL 5.1

http://www.mysqludf.org/lib_mysqludf_sys/index.php

Steps done:
1. downloaded the tar.gz and tried running the install.sh but its not working and throwing errors.

Can you suggest the correct way to get this installed. Basically my requirement is to invike a shell scriot from a mySQL trigger...Please suggest if anyother easy way is there.

rpbouman said...

Arun,

it is impossible to troubleshoot your problem if you don't provide specific information to indicate the nature of the errors.

Arun Chaitanya said...

"./install.sh
Compiling the MySQL UDF
gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so
make: gcc: Command not found
make: *** [install] Error 127
ERROR: You need libmysqlclient development software installed
to be able to compile this UDF, on Debian/Ubuntu just run:
apt-get install libmysqlclient15-dev"


This is what the error i got after running install.sh. I'm totally new to this and so dont ghetting how to fix this.

rpbouman said...

Hi Arun,

it seems there are two things wrong.
1) you don't have gcc.
2) you don't seem to have the development files for mysql.

For #2, just follow the instruction in the error message, and run this in a terminal:

sudo apt-get install libmysqlclient15-dev

This might also pull in gcc, but if it doesn't, please google a bit and find out how to install it (hint: it probably starts with sudo apt-get too :)

Arun Chaitanya said...

Thanks for the info Roland. I finally got the UDF compiled and get to see system calls working. However i now have another query related to triggers if u can answer. I'm trying something like this in TRIGGER CODE:

CREATE TRIGGER upd_check AFTER INSERT ON
FOR EACH ROW
BEGIN
sys_exec('TimeOutBatch.sh')
END;

from the error log i understood that way of calling sys_exec is wrong. can u suggest the correct way. My aim is to call the shell script in the trigger.

rpbouman said...

Arun,

Glad you got that working. Your last question does not seem to have any direct bearing on the main blog article. I'll answer this time around, but please use public forums and google next time you want to ask questions that are not related to this blog.

Thanks in advance.

UDFs are functions. You should use the proper syntax to invoke the functions - either use:

SELECT sys_exec('TimeOutBatch.sh') INTO @dummy;

or the DO syntax:

http://dev.mysql.com/doc/refman/5.5/en/do.html

Arun Chaitanya said...

Thanks Roland. That helped me a lot. Thanks a lot!!!

Anonymous said...

Hi guys,

you wrote about high quality?? I just tested the lib_mysqludf_sys for Windows, in particular the sys_eval() function which sucks totally. It is simply not working that way and if you really compile this code for windows and execute it via a mysql-udf-call, the mysql-server will crash.

Quality, especially high one, is, at least in my opinion, something different.

Who told you that _popen() will work under Windows as it does under Linux/Unix? Pure bullshit, if one really needs these async-calls made, please refer to the msdn before porting ..nix-code to Windows.

I guess when one is crafting open source software his/her spirit is: Hey, it compiles - ship it!

rpbouman said...

Hi Anonymous,

thank you so much for your constructive criticisms. And thanks for the patches too - we'll be able to fix all the problems you just mentioned in a snap because of your kind contributions. You're really, really too kind, and we're humbled by your modest and sincere gesture.

yours sincerely,

Roland Bouman.

Bremboz said...

Hi Roland, thanks for the UDF

one thing that i wanna ask, does sys_exec() works on SQL trigger?

Thx

Bremboz said...

here is my screen shot about what have i done to check it, i use ubuntu 9.10, with mysql 5.1.37 version

http://img5.imageshack.us/img5/2134/screenshotvn.png

nothing happened when i do the sys_exec command, is it because of ubuntu issues?

Thx

rpbouman said...

Hi Bremboz,

yes - UDFs, including sys_exec, does work inside a trigger.

As for your second question: usually when it doesn't work on linux, it's because AppArmor is blocking it.

HTH,

Roland

Anonymous said...

@Anonymous from Tuesday, October 19, 2010 2:33:00 AM GMT+02:00
Have you tried running sys_exec while not running mysql as a service?
That fixed the crashing on my end.

btw roland thanks for the awesome tools

rpbouman said...

Hi Anonymous!

thanks for this insight re running MySQL as windows service. And - thanks for the thanks, I appreciate it a lot :)

Silver MLM said...

what's the difference between microsoft expression web 2 and microsoft expression web 3?

rpbouman said...

Silverr MLM, MSEW3 is more recent than MSEW2.

HTH,

Roland.

John P said...

Hi

Awesome article. I was succesful in compiling and setting up lib_mysqludf_sys for MySQL 5.1.18 on windows.

Now I'm trying to get it working with MySQL 5.5 and am getting the dreaded "[Err] 1126 - Can't open shared library 'lib_mysqludf_sys2.dll' (errno: 2 )" error.

I'm using Visual C++ Express 2010, with the SDK 7.1.

Any immediate thoughts on the problem?

TIA

rpbouman said...

Hi John!

thanks for the kind words - I'm glad you like it.

I'm sorry, I haven't tried yet with MySQL 5.5.

John P said...

Aha!

Had to force it to compile in x64. Which makes sense since it's 64bit MySQL.

Jeff Beard said...

Roland,

I wanted to mention to you and the folks that refer this post that I've built a UDF (udf_median) on Windows 7 against MySQL 5.1.x, both 64bit. I did a brief write up on it here:

http://jeffbeard.org/2011/05/mysql-udf_median-on-windows-7-64bit/


Cheers,

Jeff

rpbouman said...

Jeff, thanks - great work :)

I occasionally get people asking me about 64 bit issues and I never know what to tell them - now I can point them to your post and tell em to try that. thanks again !

LuigiEd said...

Hi Roland,

I am currntly trying to develope a UDF that can send a udp message via the network.
Background is, that I am planning to create a trigger, that sends changes in the database to another application.

I have successfully build and installed the UDF. But when I call it, nothing is being sent. The sendto-Function returns 8, so no error.

The client I sent the message to in written in vb .net.

Is there anything I have to configure in MySQL?

Regards,
Luigi

rpbouman said...

Hi LuigiEd!

sorry - no idea. As far as I'm aware, there is nothing on the MySQL side you can do, except install the UDF.

Have you checked the obvious things, like for example that the port you're using is not being blocked by your firewall?

Anonymous said...

Hallo Herr Bouman
Mein Name ist Khosro Tvakkoli und habe folgende Probleme.
Mein Email-Adresse ist khosrotavakkoli@web.de.
Beim Installation bekomme ich folgende Felher.
Kannst du mir vielleicht dabei helfen?
Herzlichen Dank
=====================
Mysql-version: mysql-server-5.1
Config-file: /et c/mysql/my.cf
=====================
lib_mysqludf_preg-1.0.1# uname -a
Linux milkyway 2.6.32-5-686 #1 SMP Wed May 18 07:08:50 UTC 2011 i686 GNU/Linux
=====================
lib_mysqludf_preg-1.0.1# ./configure
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gawk... no
checking for mawk... mawk
checking whether make sets $(MAKE)... yes
checking build system type... i686-pc-linux-gnu
checking host system type... i686-pc-linux-gnu
checking for style of include used by make... GNU
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables...
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking dependency style of gcc... gcc3
checking for a sed that does not truncate output... /bin/sed
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for fgrep... /bin/grep -F
checking for ld used by gcc... /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... yes
checking for BSD- or MS-compatible name lister (nm)... /usr/bin/nm -B
checking the name lister (/usr/bin/nm -B) interface... BSD nm
checking whether ln -s works... yes
checking the maximum length of command line arguments... 1572864
checking whether the shell understands some XSI constructs... yes
checking whether the shell understands "+="... yes
checking for /usr/bin/ld option to reload object files... -r
checking how to recognize dependent libraries... pass_all
checking for ar... ar
checking for strip... strip
checking for ranlib... ranlib
checking command to parse /usr/bin/nm -B output from gcc object... ok
checking how to run the C preprocessor... gcc -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking for dlfcn.h... yes
checking for objdir... .libs
checking if gcc supports -fno-rtti -fno-exceptions... no
checking for gcc option to produce PIC... -fPIC -DPIC
checking if gcc PIC flag -fPIC -DPIC works... yes
checking if gcc static flag -static works... yes
checking if gcc supports -c -o file.o... yes
checking if gcc supports -c -o file.o... (cached) yes
checking whether the gcc linker (/usr/bin/ld) supports shared libraries... yes
checking whether -lc should be explicitly linked in... no
checking dynamic linker characteristics... GNU/Linux ld.so
checking how to hardcode library paths into programs... immediate
checking whether stripping libraries is possible... yes
checking if libtool supports shared libraries... yes
checking whether to build shared libraries... yes
checking whether to build static libraries... yes
checking for gcc... (cached) gcc
checking whether we are using the GNU C compiler... (cached) yes
checking whether gcc accepts -g... (cached) yes
checking for gcc option to accept ISO C89... (cached) none needed
checking dependency style of gcc... (cached) gcc3
checking for mysqlbin... checking for mysql_config... no
no
configure: error: "Can't find mysql library"
lib_mysqludf_preg-1.0.1#
=====================

Luigied said...

Hi Roland,
I have ported my UDF to TCP usage. Now I get a "connection refused" error. Even though my client is running and listening to the correct port. Might be worth mentioning, that I am doing this in windows environment. So far I have only found examples for Linux.

Could it be that this is a windows specific problem? Except for the libraries used, my source code is practically identical to linux-based opeb source examples online.

Regards LuigiEd

rpbouman said...

Hi Luigied!

sorry - I really don't know. I can't think of something on the mysql side that would prevent this. Maybe it is a windows specific thing. One way to test that is to try it again on linux.

Good luck, I hope you'll succeed.

Anonymous said...

does lib_mysqludf_sys work on Windows ?

Ummu Abdurrahman said...

Hi Roland,
I have followed ur tutorial and its went well. However, when I tried to use the sys_exec(), it doesnt work as I wanted.

Is it because I called it wrongly?
DROP TRIGGER ai_sentEmail_fer;|
CREATE TRIGGER ai_sentEmail_fer AFTER INSERT ON monbp3
FOR EACH ROW
BEGIN
do sys_exec('C:\Program\email.sh');
END;

FYI,I work on Win7.

Thanks..

rpbouman said...

Ummu, it is impossible to say if you don't say what it is that doesn't work.

anyway, here are 2 possible explanations:

1) you have unescaped backslashes in the command line. try again using sys_exec(C:/Program/email.sh') or sys_exec('C:\\Program\\email.sh')

2) .sh is not a regular executable extension. Perhaps you should use the full command line, like: sys_exec('bash C:\\Program\\email.sh')

Ummu Abdurrahman said...

Hi Roland,
Thanks for a fast reply..
What I mean is that the sys_exec() is not executing the file. You are right about the double slashes. I replace it as the following and its works..:);

do sys_exec('java -jar C:\\Java\\Email.jar');

However, calling shell script is not working so i just use bat instead of sh n works like a charm..

Anonymous said...

Hi, Roland.
I have knowledge about .net clr udf, but only for MSSQL, so I find your article great.
I have couple of questions. First of all I have only basic experience in C and I do not know how to solve it.

I used windows server 2008 SDK and VS 2010. I am trying to use x64 platform, because I am using mysql x64.
I got to next link error:
Error 12 error LNK2001: unresolved external symbol _DllMainCRTStartup D:\Work\MySqlUdf\test_udf\lib_mysqludf_udf\LINK lib_mysqludf_udf

What am I doing wrong?

Thanks in advance. I am expecting your answer soon.

rpbouman said...

Hi entersb!

please look in the comments for FredK's comments. He has some useful info on compiling for windows 64 bit.

HTH, Roland.

Z Balint said...

Has somebody tried to compile a regex replace function for MySql 64 bit server?

source codes for regexp f. :
http://www.mysqludf.org/lib_mysqludf_preg/index.php
OR
https://launchpad.net/mysql-udf-regexp

Thanks

MarkusZelg said...

Hi Roland,
I do have MySql 5.1.58 on WinXP running nice. But when doing the
>CREATE FUNCTION lib_mysqludf_sys_info RETURNS string SONAME 'lib_mysqludf_sys.so';

it takes me to an error message:
>SQL Error 1126, Cannt open shared libary 'lib_mysqludf_sys.so' "errno2).

I do have the so file in the plug-in dir as defined in the my.ini and plotet on mysql "show plugin-dir".

Do you have any idea how I may get the sys_eval into my DB?

Anonymous said...

Where can I get the DLL instead of having to compile on my own?

Anonymous said...

In MS Visual 2010, You Need WinSock.h

#include
#include
#include <mysql.

Dirar said...

I compiled the lib_mysqludf_sys for the sys_eval and it's working great. Thank you very much

Anonymous said...

Roland,

You are a great expert in compiling UDF's with MS Visual C++. Due to the good instruction in this blog, I was successfully able to implement your udf's in mysql. Many Thanks!

In the meantime, I've tried to add a udf ("median.cc") to mysql - following your instruction - from an external page (see below) but it didn’t work. I got a ErrorMsg in MySQL (Can`t find symbol 'median' in library) while executing in MySQL: CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME "Median_udf.dll"
Could you help me, please?

Kind Regards,
Testuser

UDF Soure Page:
http://mysql-udf.sourceforge.net/

Download link on this page: http://sourceforge.net/projects/mysql-udf/files/mysql-udf/mysql-udf-0.3/mysql-udf-0.3.tar.gz/download

Filename:
median.cc

Anonymous said...

Did fix the median problem. Thx.

William said...

Hi, I write a UDF to run system(command). However, the commands like "Java","Java helloworld" never work. An error number 1 is returned. If system(command) is called in a C++ program, it wroks for all kinds of commands.

My SQL server is 5.5.

I also used the udf system() to run a program inside mysql. The program is to send a http requester. The program runs successfully outside mysql. However, if using udf call, the final step of sending http request (HttpSendRequest) will never work.

rpbouman said...

Hi William,

are you running Linux?

Apparmor usually seems to be responsible for blocking these kinds of calls.

William said...

Hi Roland,

I am running mysql5.5 on windows 7. I am using microsoft WinINet to send http requester. The program alone has been tested to send the http request successfully. Then I transferred the code and make the DLL file mysql needs. The UDF just fails to send the http request.

Thanks,
William

rpbouman said...

Hi William,

Just trying to understand what you're doing here.

First, you wrote:

"I also used the udf system() to run a program inside mysql. The program is to send a http requester. The program runs successfully outside mysql. However, if using udf call, the final step of sending http request (HttpSendRequest) will never work."

Did you try to execute something very basic with your udf, like 'dir' or 'del'? Did that work?

William said...

Hi Roland,

Yes, the basic commands like 'cd ' 'dir ' works. Even 'Java -version' works. However, 'Java', 'java hellowworld' won't work.

I will change to work under ubuntu 11.10 now.

My objective is to send http request insider a trigger in mySQL.

Arno said...

Is the link for the download to lib_mysqludf_udf.c still working? I get an error that the page cannot be found:

Not Found

The requested URL /park/MySQL/UDF/lib_mysqludf_udf/lib_mysqludf_udf.c was not found on this server.

Arno said...

Great article.

Is the link for the download to lib_mysqludf_udf.c still working? I get an error that the page cannot be found:

Not Found

The requested URL /park/MySQL/UDF/lib_mysqludf_udf/lib_mysqludf_udf.c was not found on this server.

ikke said...

Does anyone have a Windows 64-bit dll of lib_mysqludf_preg? I don't have a compiler and I cannot find the compiled version anywhere. Thanx for helping!
Emile

neosarchizo said...

I tried the code in your post. And I succeeded. Next, I tried the code at following link.

github.com/megastep/mysql-udf

Also I tried the code by same way that you posted, but I failed to compile.
I don't know how to compile c++ code by way that you posted. Please, help me. I wanna know how to do that!!

Marco Mannessen said...

I try to install it on Ubuntu 12.04 LTS no apparmor installed and i installed the libmyslqclient15-dev

but still get this error

Compiling the MySQL UDF
gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so
/usr/bin/ld: /tmp/cckbh4Er.o: relocation R_X86_64_32 against `.rodata' can not be used when making a shared object; recompile with -fPIC
/tmp/cckbh4Er.o: could not read symbols: Bad value
collect2: ld returned 1 exit status
make: *** [install] Error 1
ERROR: You need libmysqlclient development software installed
to be able to compile this UDF, on Debian/Ubuntu just run:
apt-get install libmysqlclient15-dev

rpbouman said...

@Marco,

it is impossible to list all requirements for all possible platforms. If I were you I'd look at the error messages and try the suggested -fPIC flag, as well as installing the mysql client dev package.

Marco Mannessen said...

Where do i need to set the -fPIC flag?

rpbouman said...

Well, you're using gcc to compile, and gcc is offering this as an error message. So, on the gcc command line, together with all the other flags (like -Wall, -shared and so on.)

Marco Mannessen said...

Okay thanks

there is only one problem left but i solved it by my self.

when you install i get this message:
ERROR 1126 (HY000) at line 29: Can't open shared library 'lib_mysqludf_sys.so' (errno: 0 /usr/lib/mysql/plugin/lib_mysqludf_sys.so: cannot open shared object file: No such file or directory)
ERROR: unable to install the UDF

and this is because the compiler put the file /usr/lib/lib_mysqludf_sys.so

but the rest of the script search the file in here /usr/lib/mysql/plugin/lib_mysqludf_sys.so

after changing the compling path the problem is solved

thanks

rpbouman said...

@marco,

cool! Glad you got it to work. Happy UDF-ing :)

Anonymous said...

Looks like that the files are not avaible for download

Anonymous said...

looks like that the download links are broken

rpbouman said...

@Anonymous: what? The links are broken? And it's only been little over 5 years...I'm shocked.

I'm afraid you're going to have to actually type all that stuff yourself.

Ezhil said...

I wrote simple MYSQL UDF function.return type is string .but blob datatype value returns in mysql.

my Code
char *TestStr(UDF_INIT *initid, UDF_ARGS *args, char *result,
unsigned long *length, char *is_null, char *error) {
char *res= "Testing";
*length = 26;

return(res);
}


MYSQL => select TestStr();

rpbouman said...

@Ezhil,


see: http://dev.mysql.com/doc/refman/5.5/en/udf-calling.html

You have to use udf_init->max_length

HTH.

Anonymous said...

Hi Roland,

Great article! Hoping to code my first UDF soon.

Following your article, I've managed to compiled the DLL, purely using "lib_mysqludf_udf.c" alone in VS 2012 Desktop. My mySQL installation is in Win8 64 bit.

I've also set the plugin_dir to "C:\try\" and have restarted the mySQL server and tested that the DLL file is placed correctly there.

However to my despair, when I run the create function command, I'm getting an error with no explanations:

"ERROR 1126 (HY000): Can't open shared library 'udf_example.dll' (errno: 193 )".

I've scoured the mySQL forums with no answers so far....kindly help.

KW

KW said...

Hi Roland,

Great article! Hoping to code my first UDF soon.

Following your article, I've managed to compiled the DLL, purely using "lib_mysqludf_udf.c" alone in VS 2012 Desktop. My mySQL installation is in Win8 64 bit.

I've also set the plugin_dir to "C:\try\" and have restarted the mySQL server and tested that the DLL file is placed correctly there.

However to my despair, when I run the create function command, I'm getting an error with no explanations:

"ERROR 1126 (HY000): Can't open shared library 'udf_example.dll' (errno: 193 )".

I've scoured the mySQL forums with no answers so far....kindly help.

KW

rpbouman said...

So what happens if you remove your custom setting of the plugin dir and ask MySQL what it thinks the plugin dir is?

Personally I never used a custom location for it, I'm not even sure if it can be outside mysql's lib directory.

Robert Farquharson said...

OK Roland, so you say the link to the main file lib_mysqludf_udf.c is broken and we should just type out the code ourselves, but what *is* the code? What is the text inside this file that no longer exists?

rpbouman said...

@Robert, the MySQL UDF repository moved here: http://www.mysqludf.org/

Hud Trautman said...

I tryed this with lib_mysqludf_sys
but id didn't work. A lot of syntax error in .h files.

Hud Trautman said...

I tried this with lib_mysqludf_sys
but got a lot o syntaxe erros. Could someone privide me the lib_mysqludf_sys DLL ?

Arab4Justice said...

Hi Roland,

I have just discovered your blog while searching for a solution of my problem, which I have posted on stackoverflow. I wanted to ask you whether it is possible to have a look at this issue as you seem to be an expert in creating MySQL UDF's. I would really appreciate it. Here is the link to the post. Cannot install MySQL UDF

Regards,
Chris

rpbouman said...

Hi!

hey, I'm really sorry, but I haven't got a clue :(

Arab4Justice said...

OK, thank you anyway for taking the time.

Best regards,
Chris

Unknown said...

Hi
Well done, and thanks
but please
i need your help .
i'm beginner
I am trying to execute a php script from mysql with a trigger
it is possible with this configuration??

if yes ,I want to know how to install the UDF Functions . As i have wamp server and Visual Studio 2010
Can u please help me?

Unknown said...

Hi
Well done, and thanks
But please
I need your help .
i'm beginner
I am trying to execute a php script from mysql with a trigger
it is possible with this configuration??

If yes ,I want to know how to install the UDF Functions . As i have wamp server and Visual Studio 2010
Can u please help me?

rpbouman said...

@djamel bourahla,

hey, this is not a very easy question to answer. You can use the sys_exec udf to run the php command line, but it's really not a good solution for many reasons. You could technically write your own udf that invokes php, but I would not recommend that kind of project to a beginner. Perhaps you can explain what your script does? Perhaps you can solve it in a diffeent way?

Anonymous said...

Hi Roland!

Thanks a lot for this. I downloaded a copy of the .so from GitHub, however, had problems creating the functions. I knew I had to recompile it but I had no idea where to start! This was exactly what I needed, so thanks a lot for the information :))

Anonymous said...

Great Article!
I am using mingw32 instead of visual studio. I am trying to build some calls for http_get and http_post to control some internal devices (private subnet). I have successfully built and ran your sample. I know I can just punk out and do sys_exec('curl.exe'), but what I am trying to do is call out to libcurl.dll. Does the plugin DLLs reach out to other DLLs in the OS path? Whenever I have calls in my code that call out to import functionality from libcurl, I cannot CREATE (get 1126 error), however if I comment them out and just put fake text to test the plumbing, all works. I cannot for the life of me get it to see the dll.

I built the mingw version of curl (C:\curl-7.41.0\) (mingw32-make.exe mingw32) and copied libcurl.dll into plugins.

My code:
http://richardsandoz.com/lib_mysqludf_http.c

Commandline to make EXE (test with a main()):
gcc lib_mysqludf_http.c -o lib_mysqludf_http.exe -IC:\xampp\mysql\include -IC:\MinGW\include -IC:\curl-7.41.0\include -LC:\MinGW\lib -LC:\curl-7.41.0\bin -lcurl

Commandline to make DLL:
gcc lib_mysqludf_http.c -shared -o C:\xampp\mysql\lib\plugin\lib_mysqludf_http.dll -IC:\xampp\mysql\include -IC:\MinGW\include -IC:\curl-7.41.0\include -LC:\MinGW\lib -LC:\curl-7.41.0\bin -lcurl

My Test:
create function http_get returns string soname 'lib_mysqludf_http.dll';
DROP FUNCTION IF EXISTS http_get;

Will return:

(A)
# MySQL returned an empty result set (i.e. zero rows).
If all CURL calls are commented out, plus I can call the do-not-much functions.

(B)
#1126 - Can't open shared library 'mysql-udf-http.dll' (errno: 126 The specified module could not be found.)
If I just comment in one call such as curl_global_init(CURL_GLOBAL_ALL);

richardSandoz said...

Well, I finally punked out and built sys_eval. First thing I tried was CD (was mysql data dir), next was PATH (was missing the path I added to my env even though I restarted mysql).
From this, I figured out the error of my ways. Needed a reboot since mysql was running as system user and not the me.

rpbouman said...

Richard Sandoz,

thanks for your feedback! This will be very useful to other mingw users.

kind regards,

Roland

Sebastian Nicanor Ferrari Aliau said...

the link http://www.xcdsql.org/MySQL/UDF/lib_mysqludf_udf/lib_mysqludf_udf.c does not work.
regards

rpbouman said...

Sebastian, I know - thanks.

Horsemen said...

hi Roland,
I am using windows, I have created an trigger that will give call to php script eventually when it gets fired.
I have tried using sys_exec() and sys_eval() but none works in my case. can you please suggest me the flow I should maintain to get it done.

example code (inside trigger):
DECLARE STR varchar(255);
SET STR = CONCAT("cmd.exe /c C:\\...\\php-cgi.exe -f C:\\..\\*.php","arguments");
DECLARE RESULT int(10);
SET RESULT = sys_exec(STR);

The above code have failed each time as it says that sys_exec does not exists. Are there any plugins which are required to install ?
Thanking you

Sandeep

rpbouman said...

> The above code have failed each time as it says that sys_exec does not exists.

Sandeep, have you compiled the udfs and installed them?

Horsemen said...

Dear Roland, Thanks for quick reply ,
I am creating the new project in vs 12 version but while downloading the c source file I found that the above link is no longer available.
Can you explain what is C and its header files contains or can you please forward those files to me ?
my mail id is: sandysachin@gmail.com
thank you.


Sandeep

Horsemen said...

hi Roland,
I have pulled the source code from GIT related lib_mysqludf_sys and currently working on it as per your steps, will let you know when it will get done.
Really awesome stuff by you
thanking you


with kind regards
Sandeep

Horsemen said...

hi Roland,
It worked out to be dynamic solution to communicate with windows OS.
Thanks For Your steps, really found helpful.

kind regards,
Sandeep

rpbouman said...

@Horsemen, glad you worked it out. Thanks for the kind words!

Unknown said...

Hiya,

I have followed your tutorial and created a lib_mysqludf_sys.dll file and placed it in the lib/plugin folder. I've got as far as using the CREATE FUNCTION syntax in the MySQL client and after sorting ERROR 1126 (no file or directory) - I'm now getting a ERROR 1124 - No paths allowed for shared library. I've been googling this for nearly 2 days and can't find a solution or detailed description of the error. Any help would be greatly appreciated.

Kind Regards
Dan

rpbouman said...

Hi @Daniel Brown,

what happens if you do

show variables like '%plug%';

Do you see a path that you expect for the plugin dir?
In the CREATE FUNCTION statement, you should only need to put the filename of the dll - not the path (because the path is implied - it can only be the plugin dir).

Unknown said...

Yeah - I get the expected absolute path to the plugin folder. The command line I add is > CREATE FUNCTION sys_eval RETURNS STRING SONAME '/lib_mysqludf_sys.dll';

with the slash I get the ERROR 1124: No paths allowed for shared library
if I remove the slash I get ERROR 1126: Can't open shared library

rpbouman said...

Hi Daniel!

well, the slash def. should not be there. So now its down to solving "Can't open shared library".

I'm sure you checked yourself but indulge me - please post output of

mysql: show variables like '%plug%';

and then

dir

rpbouman said...

That last line should be:

dir %your plugin dir%

Unknown said...

Hi got it to work in the end. I found this .dll online here and I was able to create the function. I suspect the .dll I built using Microsoft Visual C++ 2010 may of been incompatible somehow.

Thanks
Dan

rpbouman said...

Wow. Well be careful - you don't know what's in there. I think it's probably a better idea to try and troubleshoot why your own dll is not working. I don't know quite how to troubleshoot it though.

Unknown said...

Ok thanks for your comments - I'm only working locally at the moment and so will get my .dll to work before we go live!!

Firdous said...

Hi Roland,

Thanks for the great post. I am a newbie in MySql and currently I am looking for similar functionality on Email through C++ dll. Can you please help me to know if its possible to send email through Mysql UDF?

I have done MSSQL CLR to send email with attachment. But I have no idea how to do in MySql.

rpbouman said...

Hi @Firdous,


thanks for interest!
I don't think there's anything MySQL specific about what you want to do - once you implement the MySQL UDF interface and you have your UDF installed, MySQL doesn't care what you do once it gets called - calculate a value, run some javascript, or send email - it's all the same.

So basically what you need to do is figure out how to send email in C or C++, once you got that working, you have to design an interface for your UDF: what will the SQL user have to pass to your function so that you can send email. And then you put them together.

I hope this helps!

ROland.

SAP HANA Trick: DISTINCT STRING_AGG

Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...