Using MySQL User-Defined Functions (UDF) to get MySQL internal informations

In one of my previous posts I was writing about how to read other processes memory [ 1 ]. As an example I tried to get the value of the hard coded MySQL internal InnoDB variable spin_wait_delay (srv_spin_wait_delay).

In this example we were using gdb or the operating system ptrace function to retrieve this value. This method has the disadvantage that it is pretty invasive.

When I was working on a customer support case I had the idea to solve this by the much less invasive method of User-Defined Functions (UDF).

UDF were introduced in MySQL 3.23 [ 2 ]. They provide the feasibility to enlarge the MySQL functionality by adding external code.

The clue is now that you also can use this external code to do some MySQL internal stuff.

My idea was now, instead of using gdb/ptrace to get the value of spin_wait_delay, to write and UDF to get and set this value.

The code for the UDF looks as follows:

/*

  CREATE FUNCTION spin_wait_delay
  RETURNS INTEGER SONAME "udf_spin_wait_delay.so";
  SELECT spin_wait_delay(5);
  SELECT spin_wait_delay();
  DROP FUNCTION spin_wait_delay;

*/

#ifdef STANDARD
/* STANDARD is defined, don't use any mysql functions */
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#ifdef __WIN__
typedef unsigned __int64 ulonglong;   /* Microsofts 64 bit types */
typedef __int64 longlong;
#else
typedef unsigned long long ulonglong;
typedef long long longlong;
#endif /*__WIN__*/
#else
#include <my_global.h>
#include <my_sys.h>
#if defined(MYSQL_SERVER)
#include <m_string.h>   /* To get strmov() */
#else
/* when compiled as standalone */
#include <string.h>
#define strmov(a,b) stpcpy(a,b)
#define bzero(a,b) memset(a,0,b)
#define memcpy_fixed(a,b,c) memcpy(a,b,c)
#endif
#endif
#include <mysql.h>
#include <ctype.h>

// This is for our spin_wait_delay
#include "srv0srv.h"

static pthread_mutex_t LOCK_hostname;

#ifdef HAVE_DLOPEN

/* These must be right or mysqld will not find the symbol! */

my_bool spin_wait_delay_init(UDF_INIT *initid, UDF_ARGS *args, char *message);

longlong spin_wait_delay(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);

longlong spin_wait_delay(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,
                    char *is_null __attribute__((unused)), char *error __attribute__((unused)))
{
  longlong val = 0;

  // We set it the value
  if ( args->arg_count == 1 ) {
    val = *((longlong*) args->args[0]);
          srv_spin_wait_delay = val;
        }

  // We get the value
  val = (ulint)srv_spin_wait_delay;

        return val;
}

my_bool spin_wait_delay_init(UDF_INIT *initid __attribute__((unused)),
                        UDF_ARGS *args __attribute__((unused)),
                        char *message __attribute__((unused)))
{
  return 0;
}

#endif /* HAVE_DLOPEN */

This code can be compiled for example with the following command:

shell> gcc -shared -I/home/mysql/source/mysql-5.1.30/include \
-I/home/mysql/source/mysql-5.1.30/storage/innobase/include \
-o udf_spin_wait_delay.so udf_spin_wait_delay.c

The achieved shared object file has to be copied to the following location in the MySQL binary tree:

mysql> SHOW VARIABLES LIKE 'plugin_dir';
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| plugin_dir    | /usr/local/mysql/lib/mysql/plugin |
+---------------+-----------------------------------+

shell> cp udf_spin_wait_delay.so /usr/local/mysql/lib/mysql/plugin/

Keep in mind that in older MySQL releases this procedure could be slightly different.

Then the UDF has to be loaded and activated in the database:

mysql> CREATE FUNCTION spin_wait_delay
       RETURNS INTEGER SONAME "udf_spin_wait_delay.so";

To remove the UDF again you can use the following command:

mysql> DROP FUNCTION spin_wait_delay;

To check if an UDF is installed or to see which ones are installed the following command gives you the right answer:

mysql> SELECT * FROM mysql.func;
+-----------------+-----+------------------------+----------+
| name            | ret | dl                     | type     |
+-----------------+-----+------------------------+----------+
| spin_wait_delay |   2 | udf_spin_wait_delay.so | function |
+-----------------+-----+------------------------+----------+

When the UDF is compiled and properly loaded into the database you can get the value of spin_wait_delay as follows:

mysql> SELECT spin_wait_delay();
+--------------------+
| spin_wait_delay(5) |
+--------------------+
|                  5 |
+--------------------+

And now the real nice thing is that you can even set this value as follows:

mysql> SELECT spin_wait_delay(8);
+--------------------+
| spin_wait_delay(8) |
+--------------------+
|                  8 |
+--------------------+

With this function we can make a static hard coded InnoDB value dynamically changeable. To make it permanent also after a database restart possibly the functionality of init_file could help you further [ 3 ].

With this concept we can think about implementing many missing things without touching the MySQL code itself or recompiling MySQL. Please let me know what is missing in your opinion and I can try to implement it. Because I am not a programmer the help of those guys would be very appreciated.

If anybody sees a problem with this method please let me know. I do not know about such things like thread safe and mutexes etc. But I think at least reading should not harm.

Caution: When you have a crash in your UDF the whole MySQL server will crash. So be careful and test it intensively!

Binary

You can find the udf_spin_wait_delay UDF in our User-Defined Function (UDF) collection.

Literature:

• [ 1 ] Blog: Reading other processes memory
• [ 2 ] MySQL documentation: Adding a New User-Defined Function
• [ 3 ] MySQL documentation: The init-file option