Extending MySQL Capabilies with UDFs, Plugins and Components - Part 2

MySQL offers three different approaches to extending the SQL capabilities with the default product you download and install. These are:

• User Defined Function (UDF) MySQL Manual
• MySQL Plugin MySQL Manual
• MySQL Component MySQL Manual

In my prior post I provided a new uuidv function that accepted a numeric argument to return a string of the version of UUID specified. The same C++ code was used as a uuidv_udf(), uuidv_plugin() and uuidv_component() functions. They were named this way so all three could be run concurrently. In practice you would select one method and use a name agnostic function name.

For the purposes of this post I will be using the current LTS version MySQL 8.4 . You can find all code at https://github.com/ronaldbradford/mysql-tutorial/tree/main/uuidv

Leveraging plugin and component improvements

The original UDF functionality only allowed you to create a new function for use in your SQL. With the newer frameworks you can access more of MySQL functionality. In this post I am going to demonstrate with the plugin and component architecture:

• How to use system variables to alter the function output
• How to use status variables to track function usage
• How to use Performance Schema to gather usage statistics

System variables: daemon plugin vs component

Both daemon server plugins and components support MySQL system variables — they appear in SHOW VARIABLES and are accessible as @@variable_name. The mechanisms differ in style: plugins are declarative, components are imperative.

Daemon server plugin

System variables are defined with MYSQL_SYSVAR_* macros and registered through the system_vars field in mysql_declare_plugin. MySQL registers them automatically at INSTALL PLUGIN time.

static int uuidv_version_default = 4;

static MYSQL_SYSVAR_INT(default_version, uuidv_version_default,
    PLUGIN_VAR_RQCMDARG,
    "Default UUID version to generate",
    nullptr, nullptr,   /* check, update */
    4, 1, 7, 0);        /* default, min, max, block */

static SYS_VAR *uuidv_sysvars[] = {
    MYSQL_SYSVAR(default_version),
    nullptr
};

mysql_declare_plugin(uuidv_plugin){
    ...
    uuidv_sysvars,  /* system_vars field — replaces nullptr */
    ...
} mysql_declare_plugin_end;

Component

Components register system variables programmatically inside component_init() using the mysql_system_variable_* service API — there is no declarative struct field.

Comparison

What about a legacy UDF?

A loadable function registered only with CREATE FUNCTION has no access to system or status variables at all. This is one of the practical advantages of the daemon plugin over a pure UDF — the same .so that exposes a callable function can also expose tuneable server variables.

MySQL Plugin Example - Variables

We can add MySQL variables that enable configuration of the output of the function without modifying the SQL function call. There are two variables in this example:

• uuidv_plugin_default_version allows you to specify a version, and not be required to pass this to the function.
• uuidv_plugin_formatted allows you to return an unformatted value (i.e. without - characters)

mysql> SHOW GLOBAL VARIABLES LIKE 'uuid%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| uuidv_plugin_default_version | 4     |
| uuidv_plugin_formatted       | ON    |
+------------------------------+-------+
2 rows in set (0.00 sec)

Setting the default version

When setting to 7, you can see the sequential nature of this version in the values produced.

mysql> SET GLOBAL uuidv_plugin_default_version=7;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin() AS CHAR)         |
+--------------------------------------+
| 019ef0d8-8407-72dc-9008-dd2d9f59a1bd |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin() AS CHAR)         |
+--------------------------------------+
| 019ef0d8-8cd6-7fd0-8f8d-5862dafb72de |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin() AS CHAR)         |
+--------------------------------------+
| 019ef0d8-9199-7371-adae-582f1011858e |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin() AS CHAR)         |
+--------------------------------------+
| 019ef0d8-94ca-7d37-a28b-65c00458e3b0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin() AS CHAR)         |
+--------------------------------------+
| 019ef0d8-971c-753a-b534-da3f4b7c4918 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin() AS CHAR)         |
+--------------------------------------+
| 019ef0d8-98f5-70d5-ac48-66dd7e85c65b |
+--------------------------------------+
1 row in set (0.00 sec)

If we set the default version back to 4 you will see the UUID values are no longer sequential.

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin() AS CHAR)         |
+--------------------------------------+
| 019ef0d8-9b1c-7baf-81a6-06795207c4da |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> set global uuidv_plugin_default_version=4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin() AS CHAR)         |
+--------------------------------------+
| 59a1875a-3805-499e-9836-5fa2a14820b0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin() AS CHAR)         |
+--------------------------------------+
| d052f9d0-5fe1-4b56-809f-4af11691fbe1 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin() AS CHAR)         |
+--------------------------------------+
| 7c613132-050a-4fbc-9919-3fa783b2c1b5 |
+--------------------------------------+
1 row in set (0.00 sec)

You can also remove the output formatting, returning just a hex-value string.

mysql> SET GLOBAL uuidv_plugin_formatted=off;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+----------------------------------+
| CAST(uuidv_plugin() AS CHAR)     |
+----------------------------------+
| 46a78cbade13403993438786fcf378cd |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+----------------------------------+
| CAST(uuidv_plugin() AS CHAR)     |
+----------------------------------+
| ccacbbc41e674e5a9daf1856d47dab1d |
+----------------------------------+
1 row in set (0.00 sec)

mysql> set global uuidv_plugin_formatted=on;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(uuidv_plugin() AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin() AS CHAR)         |
+--------------------------------------+
| 81e41690-912e-41d4-8665-a894f443a347 |
+--------------------------------------+
1 row in set (0.00 sec)

MySQL Plugin Example - Status Values

Status variables — the idiomatic approach for simple counters:

mysql> INSTALL PLUGIN uuidv_plugin SONAME 'uuidv_plugin.so';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE FUNCTION uuidv_plugin RETURNS STRING SONAME 'uuidv_plugin.so';
Query OK, 0 rows affected (0.08 sec)

mysql> SHOW GLOBAL STATUS LIKE 'uuidv%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| uuidv_plugin_v1_count | 0     |
| uuidv_plugin_v4_count | 7     |
| uuidv_plugin_v6_count | 0     |
| uuidv_plugin_v7_count | 9     |
+-----------------------+-------+
4 rows in set (0.01 sec)

We can verify the change of values using the 1 format.

mysql> SELECT CAST(uuidv_plugin(1) AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin(1) AS CHAR)        |
+--------------------------------------+
| 89a6a228-6e9e-11f1-beae-15d8320f757a |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(uuidv_plugin(1) AS CHAR);
+--------------------------------------+
| CAST(uuidv_plugin(1) AS CHAR)        |
+--------------------------------------+
| 8a56c6f8-6e9e-11f1-bb37-53be1a81d610 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> show global status like 'uuidv_plugin_v1%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| uuidv_plugin_v1_count | 2     |
+-----------------------+-------+
1 row in set (0.00 sec)

MySQL Component Example - Performance Schema Table

For uuidv specifically, we create a recent-history ring buffer: a fixed-size circular buffer of the last N UUID generations, one row per call, with structured columns:

This justifies the need for a custom table because:

• Multiple rows — a status variable can only hold one scalar; a ring buffer has N rows
• TRUNCATE makes semantic sense (clear the history), matching how events_statements_history works
• Timestamp — impossible in a status variable, natural in a table row

mysql> SELECT * FROM performance_schema.uuidv_history;
ERROR 1146 (42S02): Table 'performance_schema.uuidv_history' doesn't exist

mysql> INSTALL COMPONENT 'file://component_uuidv';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM performance_schema.uuidv_history;
+--------------------+---------+--------------------------------------+----------------------------+
| THREAD_ID          | VERSION | UUID                                 | EVENT_TIME                 |
+--------------------+---------+--------------------------------------+----------------------------+
| 764173409842420687 |       7 | 019ef24b-bb22-7ac0-9559-0f6f7bb8e1f6 | 2026-06-22 22:25:09.922147 |
| 764173409842420687 |       7 | 019ef24b-bb22-7f8c-985f-896a8d4abd13 | 2026-06-22 22:25:09.922148 |
| 764173409842420687 |       7 | 019ef24b-bb22-7b30-b0c5-14c2276d29ca | 2026-06-22 22:25:09.922148 |
| 764173409842420687 |       4 | e86a5730-2464-409c-a55e-da51424eabf4 | 2026-06-22 22:25:09.943749 |
+--------------------+---------+--------------------------------------+----------------------------+
4 rows in set (0.01 sec)

As you can see the component has created a new performance_schema.uuidv_history table. This is populated on the execution of the function.

mysql> select uuidv_component(1);
+----------------------------------------------------------------------------+
| uuidv_component(1)                                                         |
+----------------------------------------------------------------------------+
| 0x30346163646134382D366561632D313166312D386139392D643965323064333035656636 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM performance_schema.uuidv_history;
+--------------------+---------+--------------------------------------+----------------------------+
| THREAD_ID          | VERSION | UUID                                 | EVENT_TIME                 |
+--------------------+---------+--------------------------------------+----------------------------+
| 764173409842420687 |       7 | 019ef24b-bb22-7ac0-9559-0f6f7bb8e1f6 | 2026-06-22 22:25:09.922147 |
| 764173409842420687 |       7 | 019ef24b-bb22-7f8c-985f-896a8d4abd13 | 2026-06-22 22:25:09.922148 |
| 764173409842420687 |       7 | 019ef24b-bb22-7b30-b0c5-14c2276d29ca | 2026-06-22 22:25:09.922148 |
| 764173409842420687 |       4 | e86a5730-2464-409c-a55e-da51424eabf4 | 2026-06-22 22:25:09.943749 |
| 764173409842420687 |       1 | 04acda48-6eac-11f1-8a99-d9e20d305ef6 | 2026-06-22 22:34:11.706330 |
+--------------------+---------+--------------------------------------+----------------------------+
5 rows in set (0.01 sec)

mysql> SELECT CAST(0x30346163646134382D366561632D313166312D386139392D643965323064333035656636 as CHAR);
+------------------------------------------------------------------------------------------+
| CAST(0x30346163646134382D366561632D313166312D386139392D643965323064333035656636 as CHAR) |
+------------------------------------------------------------------------------------------+
| 04acda48-6eac-11f1-8a99-d9e20d305ef6                                                     |
+------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

The table shows a formatted UUID value rather than a raw value. The component also has the same capabilities of the plugin with status values and variables that define a default version and format.

mysql> SHOW GLOBAL STATUS like 'uuidv%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| uuidv_component_v1_count | 2     |
| uuidv_component_v4_count | 32    |
| uuidv_component_v6_count | 0     |
| uuidv_component_v7_count | 22    |
+--------------------------+-------+
4 rows in set (0.01 sec)

mysql> set session uuidv_component.formatted = off;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(uuidv_component(1) AS CHAR);
+----------------------------------+
| CAST(uuidv_component(1) AS CHAR) |
+----------------------------------+
| e26d570c6f5811f183680bc962cdc598 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL uuidv_component.default_version=7;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(uuidv_component() AS CHAR);
+----------------------------------+
| CAST(uuidv_component() AS CHAR)  |
+----------------------------------+
| 019ef6c21095724ebeb58bccbf32a536 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM performance_schema.uuidv_history;
+---------------------+---------+--------------------------------------+----------------------------+
| THREAD_ID           | VERSION | UUID                                 | EVENT_TIME                 |
+---------------------+---------+--------------------------------------+----------------------------+
...
| 1668215637703613080 |       7 | 019ef6c2-1095-724e-beb5-8bccbf32a536 | 2026-06-23 19:12:53.909458 |
+---------------------+---------+--------------------------------------+----------------------------+

Recommendations

It is possible to have a custom performance_schema table in a plugin also. However the recommended strategy for MySQL is the defintion of functionality via components?

The MySQL 9.7 LTS community editions provides a number of new components previously available only in MySQL Enterprise. A number are still Enterprise only.