Well, “easy” if you know just a tiny bit of C++.
MySQL is well known for its ease of use, being easy to install, easy to configure, and easy to maintain. What if there is something more that you’d like MySQL to do? How would you integrate some new fancy processing library into MySQL without having to recreate the complexities in pure SQL?
MySQL Loadable Functions would be the way to go. In this blog post, you’ll learn how to set up a build environment for compiling your own MySQL plugin to be loaded into MySQL as a function. Our function will implement a ULID generator using a C++ library from ChrisBove/ulid.
Creating the build environment
The first step is downloading the source code to MySQL / Percona Server for MySQL 8.0.32, then extracting the tarball.
1 2 3 | $ cd $HOME $ wget https://downloads.percona.com/downloads/Percona-Server-8.0/Percona-Server-8.0.32-24/source/tarball/percona-server-8.0.32-24.tar.gz $ tar -xvzf percona-server-8.0.32-24.tar.gz |
Next, we need to set up a location for our new plugin within the source tree. We can also grab a copy of the library that we will use within the plugin.
1 2 3 | $ mkdir ~/percona-server-8.0.32-24/plugin/ulid/ $ cd ~/percona-server-8.0.32-24/plugin/ulid/ $ git clone https://github.com/ChrisBove/ulid |
Now we need to create two files. The first file is for cmake which informs the overall build process that it should include, and compile our plugin.
1 2 | $ cd ~/percona-server-8.0.32-24/plugin/ulid/ $ echo "MYSQL_ADD_PLUGIN(ulid ulid_udf.cc MODULE_ONLY)" > CMakeLists.txt |
MYSQL_ADD_PLUGIN is a macro for CMake that defines our plugin name, the plugin’s main source code file, and specifies that this is a loadable shared object (.so).
The second file we need to create is our actual source code for the plugin.
1 | $ curl https://gist.githubusercontent.com/utdrmac/168c757144562408976854c50724fe75/raw/87f2401e422d90ec8e0da55cdbf42f1568c96a04/uild_udf.cc >ulid_udf.cc |
We will go into more detail about the code itself later in this post. For now, let’s continue to get everything compiled and working.
Now that we have the code tree extracted and our plugin code ready, we need to create a build location. You can build within the extracted source tree, but this goes against best practices. Once inside the build directory, execute cmake targeted to the source tree. There are some helper flags that you should use to download the Boost library and disable features that are not needed for compiling a plugin. If there are any fundamental libraries missing, the output will do its best to let you know what commands to run (apt / rpm / yum) to install the needed libraries.
1 2 3 4 5 6 7 8 9 10 11 12 13 | $ mkdir /tmp/BUILD_PS $ cd /tmp/BUILD_PS $ cmake ~/percona-server-8.0.32-24/ -DWITH_BOOST=/tmp/boost -DDOWNLOAD_BOOST=1 -DWITH_AUTHENTICATION_LDAP=OFF ... ... -- Configuring done -- Generating done -- Build files have been written to: /tmp/BUILD_PS ## What I had to install; YMMV $ apt install gcc-8 g++-8 $ apt install libkrb5-dev libsasl2-dev libsasl2-modules-gssapi-mit libldap-dev $ apt install libncurses5-dev |
Cmake is now finished, and all the initial checks look good. Time to compile just our plugin. We don’t need to compile the entire Percona MySQL server codebase.
1 2 3 4 5 6 7 8 9 10 11 12 | $ cd /tmp/BUILD_PS/plugin/ulid $ make ... ... Scanning dependencies of target ulid [100%] Building CXX object plugin/ulid/CMakeFiles/ulid.dir/ulid_udf.cc.o /home/jeves/percona-server-8.0.32-24/plugin/ulid/ulid_udf.cc: In function ‘void ulid_udf::ulid_deinit(UDF_INIT*)’: /home/jeves/percona-server-8.0.32-24/plugin/ulid/ulid_udf.cc:132:39: warning: unused parameter ‘initid’ [-Wunused-parameter] extern "C" void ulid_deinit(UDF_INIT *initid) {} ~~~~~~~~~~^~~~~~ [100%] Linking CXX shared module ../../plugin_output_directory/ulid.so [100%] Built target ulid |
Success! Plugin compiled. Let’s load it into a running MySQL server and check that it works.
1 2 3 4 5 6 7 8 9 10 11 12 | $ cp /tmp/BUILD_PS/plugin_output_directory/ulid.so /usr/lib/mysql/plugins/ $ mysql mysql> CREATE FUNCTION ulid RETURNS STRING SONAME "ulid.so"; Query OK, 0 rows affected (0.00 sec) mysql> SELECT ULID(); +------------------------------------+ | ULID() | +------------------------------------+ | 0x018931B0EA52D664C7CBE83255C3468D | +------------------------------------+ 1 row in set (0.00 sec) |
More success! Our plugin works!
Understanding the plugin
Now that our plugin works, let’s rewind a bit and understand what is happening. The contents below is my tl;dr. If you want a more detailed explanation, please consult the documentation.
There are three functions within the code that you are required to define:
- ulid – The main function, called for each row
- ulid_init – Called at the beginning of the statement; used to verify number of arguments, and/or argument types.
- ulid_deinit – Called at the end of the statement; Typically empty unless you allocated additional memory or other structures in _init.
Let’s look at each of these functions as implemented:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | extern "C" bool ulid_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { if (args->arg_count > 1) { strcpy(message, "ULID takes 0 or 1 arguments"); return true; } if (args->arg_count == 1 && args->arg_type[0] != INT_RESULT) { strcpy(message, "Argument 1 must be an integer representing milliseconds"); return true; } initid->max_length = ULID_BINARY_LENGTH; return false; } |
Despite being all C++, you still need to externalize the function names so “C” can view them. The above _init function first checks that there are either 0 or 1 parameters and, strangely, returns true if there’s an error.
If the user provided 1 argument, check that the argument is an integer. Lastly, tell MySQL the maximum length of the string value we will return.
On to the main function. There are inline comments to explain various lines and sections of the code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | extern "C" char *ulid(UDF_INIT *initid [[maybe_unused]], UDF_ARGS *args, char *result, unsigned long *length, unsigned char *is_null, unsigned char *error) { // Inform mysql that this will not return NULL *is_null = 0; // Create our ULID object ulid::ULID my_ulid = 0; // If no arguments provided... if (args->arg_count == 0) { // Create a ULID using the current time (std::chrono) in nanoseconds ulid::EncodeTimeSystemClockNow(my_ulid); } else if (args->arg_count == 1) { // SELECT ULID(1687315770000); // Treat the argument (args[0] as milliseconds, casting to an appropriate integer type long long ms_seed; ms_seed = *((long long*) args->args[0]); // Create a chrono::time_point using the // timestamp from args[0] std::chrono::system_clock::time_point tp{std::chrono::milliseconds{ms_seed}}; // Encode the provided timestamp into the ULID ulid::EncodeTime(tp, my_ulid); } else { *error = 1; return result; } // Add randomness to the ULID ulid::EncodeEntropyRand(my_ulid); // Marshal ULID to binary array uint8_t dst[16]; ulid::MarshalBinaryTo(my_ulid, dst); // Copy binary array to mysql result pointer and set the length memcpy(result, dst, ULID_BINARY_LENGTH); *length = ULID_BINARY_LENGTH; return result; } |
Some example executions:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | mysql> SELECT ULID(1687315770000); +------------------------------------+ | ULID(1687315770000) | +------------------------------------+ | 0x0188DBDB6A9082D59C4BA2857DF84AC4 | +------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ULID(); +------------------------------------+ | ULID() | +------------------------------------+ | 0x018931CC56FD86C466E34859CDEA11F2 | +------------------------------------+ 1 row in set (0.00 sec) > SELECT LENGTH(ULID(1687315770000)); +-----------------------------+ | LENGTH(ULID(1687315770000)) | +-----------------------------+ | 16 | +-----------------------------+ mysql> CREATE TABLE ulidtest (ulid BINARY(16) NOT NULL PRIMARY KEY); Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO ulidtest VALUES (ULID()), (ULID()), (ULID()), (ULID()); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM ulidtest; +------------------------------------+ | ulid | +------------------------------------+ | 0x018931CEF0E113F20D842C3DCBBAA7F6 | | 0x018931CEF0E11A207EC1FBEEAE61BF5E | | 0x018931CEF0E14B3B953E26BA1FCA29BD | | 0x018931CEF0E1A3C117228413113475D1 | +------------------------------------+ 4 rows in set (0.00 sec) |
Conclusion
Extending MySQL’s functionality to utilize other libraries presents endless opportunities in expanding the capabilities of MySQL. How about writing a function that can send emails? Or blinking an LED? Hopefully, the above explanations and examples will spark something in you!
The above ULID plugin came as an idea from a post by Shopify, specifically #6, that mentioned moving to ULID over UUID for better sequential INSERTs in their MySQL databases. Since there was a C++ library for ULID, this presented a fantastic opportunity to showcase how MySQL can be extended to have native SQL-based ULID functionality.
Look for part two, where we benchmark ULID in MySQL! (*spolier* It’s better!)
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
