MySQL useful add-on collection using UDF

I really like this new toy (for me) called UDF. So I try to provide some more, hopefully useful, functionality.

The newest extension I like is the possibility to write to the MySQL error log through the application. Oracle can do that since long. Now we can do this as well...

A list of what I have done up to now you can find here:

• Query and change InnoDB spin_wait_delay: udf_spin_wait_delay-5.1.30-linux-i686-glibc23.so (md5 807c6bc09b5dc88a8005788519f2483a)


• Send message to the MySQL error log: udf_log_error-5.1.30-linux-i686-glibc23.so (md5 dc8ef3e91cf6dec84ab3ad95626ec9b5)


• ...

If you have some more suggestions, please let me know.

The complete details you can find here.

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 5.0 [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.

More details about the UDF itself, how to compile and load it you can find on my website [3].

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 sping_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 [4].

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 programer 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

udf_spin_wait_delay.so (md5 807c6bc09b5dc88a8005788519f2483a)

Determine in MySQL if we are in summer time or winter time (daylight saving time, DST)

Recently a colleague at Sun was asking me if MySQL can tell him to determine if we are currently in summer time or winter time. He was doing some data analysis of his house where he has installed solar panels.

I am not aware of what he wants to do exactly, but possibly he wants all the data in solar time. So UTC could help him because UTC does not change much over time.

Next thing which came to my mind is, that possibly the good place to do such math calculations is the application code and not the database.

But never the less I was interested in how to solve this IN the database.

By default your MySQL server relies on your servers time zone. [1]

So if your server is set-up correctly you should be capable to determine if you are in summer time or winter time by your current time, UTC time and the offset you have to UTC.

mysql> SELECT IF(ROUND(TIME_TO_SEC(SUBTIME(TIME(SYSDATE()), UTC_TIME())) / 3600, 0) = 2, 'summer time', 'winter time') AS time;

Have fun calculating how much power is produced by your solar panels according to winter or sumer time...

If you have smarter solutions please let me know.

[1] Time zone support
[2] Date and time functions