Documentation Home
Extending MySQL 8.0
Download this Manual
PDF (US Ltr) - 420.3Kb
PDF (A4) - 419.7Kb


Extending MySQL 8.0  / Adding Functions to MySQL  /  Adding a Loadable Function

6.2 Adding a Loadable Function

The MySQL interface for loadable functions provides the following features and capabilities:

  • Functions can return string, integer, or real values and can accept arguments of those same types.

  • You can define simple functions that operate on a single row at a time, or aggregate functions that operate on groups of rows.

  • Information is provided to functions that enables them to check the number, types, and names of the arguments passed to them.

  • You can tell MySQL to coerce arguments to a given type before passing them to a function.

  • You can indicate that a function returnsNULL or that an error occurred.

For the loadable function mechanism to work, functions must be written in C++ and your operating system must support dynamic loading. MySQL source distributions include a filesql/udf_example.cc that defines five loadable function interface functions. Consult this file to see how loadable function calling conventions work. Theinclude/mysql_com.h header file defines loadable function-related symbols and data structures, although you need not include this header file directly; it is included bymysql.h.

A loadable function contains code that becomes part of the running server, so when you write a loadable function, you are bound by any and all constraints that apply to writing server code. For example, you may have problems if you attempt to use functions from thelibstdc++ library. These constraints may change in future versions of the server, so it is possible that server upgrades will require revisions to loadable functions that were originally written for older servers. For information about these constraints, seeMySQL Source-Configuration Options, andDealing with Problems Compiling MySQL.

To be able to use loadable functions, you must linkmysqld dynamically. If you want to use a loadable function that needs to access symbols frommysqld (for example, themetaphone function insql/udf_example.cc usesdefault_charset_info), you must link the program with-rdynamic (seeman dlopen).

For each function that you want to use in SQL statements, you should define corresponding C++ functions. In the following discussion, the name“xxx” is used for an example function name. To distinguish between SQL and C++ usage,XXX() (uppercase) indicates an SQL function call, andxxx() (lowercase) indicates a C++ function call.

Note

When using C++, encapsulate your C functions within this construct:

extern "C" { ... }

This ensures that your C++ function names remain readable in the completed function.

Loadable Function Interface Functions

The following list describes the C++ functions that you write to implement the interface for a function namedXXX(). The main function,xxx(), is required. In addition, a loadable function requires at least one of the other functions described here, for reasons discussed inLoadable Function Security Precautions.

  • xxx()

    The main function. This is where the function result is computed. The correspondence between the SQL function data type and the return type of your C++ function is shown here.

    SQL TypeC++ Type
    STRINGchar *
    INTEGERlong long
    REALdouble

    It is also possible to declare aDECIMAL function, but the value is returned as a string, so you should write the function as though it were aSTRING function.ROW functions are not implemented.

  • xxx_init()

    The initialization function forxxx(). If present, it can be used for the following purposes:

    • To check the number of arguments toXXX().

    • To verify that the arguments are of a required type or, alternatively, to tell MySQL to coerce arguments to the required types when the main function is called.

    • To allocate any memory required by the main function.

    • To specify the maximum length of the result.

    • To specify (forREAL functions) the maximum number of decimal places in the result.

    • To specify whether the result can beNULL.

  • xxx_deinit()

    The deinitialization function forxxx(). If present, it should deallocate any memory allocated by the initialization function.

When an SQL statement invokesXXX(), MySQL calls the initialization functionxxx_init() to let it perform any required setup, such as argument checking or memory allocation. Ifxxx_init() returns an error, MySQL aborts the SQL statement with an error message and does not call the main or deinitialization functions. Otherwise, MySQL calls the main functionxxx() once for each row. After all rows have been processed, MySQL calls the deinitialization functionxxx_deinit() so that it can perform any required cleanup.

For aggregate functions that work likeSUM(), you must also provide the following functions:

  • xxx_clear()

    Reset the current aggregate value but do not insert the argument as the initial aggregate value for a new group.

  • xxx_add()

    Add the argument to the current aggregate value.

MySQL handles aggregate loadable functions as follows:

  1. Callxxx_init() to let the aggregate function allocate any memory it needs for storing results.

  2. Sort the table according to theGROUP BY expression.

  3. Callxxx_clear() for the first row in each new group.

  4. Callxxx_add() for each row that belongs in the same group.

  5. Callxxx() to get the result for the aggregate when the group changes or after the last row has been processed.

  6. Repeat steps 3 to 5 until all rows has been processed

  7. Callxxx_deinit() to let the function free any memory it has allocated.

All functions must be thread-safe. This includes not just the main function, but the initialization and deinitialization functions as well, and also the additional functions required by aggregate functions. A consequence of this requirement is that you are not permitted to allocate any global or static variables that change! If you need memory, you must allocate it inxxx_init() and free it inxxx_deinit().

Loadable Function Calling Sequences for Simple Functions

This section describes the different interface functions that you must define to create a simple loadable function. For information about the order in which MySQL calls these functions, seeLoadable Function Interface Functions.

The mainxxx() function should be declared as shown in this section. Note that the return type and parameters differ, depending on whether you declare the SQL functionXXX() to returnSTRING,INTEGER, orREAL in theCREATE FUNCTION statement:

ForSTRING functions:

char *xxx(UDF_INIT *initid, UDF_ARGS *args,          char *result, unsigned long *length,          char *is_null, char *error);

ForINTEGER functions:

long long xxx(UDF_INIT *initid, UDF_ARGS *args,              char *is_null, char *error);

ForREAL functions:

double xxx(UDF_INIT *initid, UDF_ARGS *args,              char *is_null, char *error);

DECIMAL functions return string values and are declared the same way asSTRING functions.ROW functions are not implemented.

Declare the initialization and deinitialization functions like this:

bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);void xxx_deinit(UDF_INIT *initid);

Theinitid parameter is passed to all three functions. It points to aUDF_INIT structure that is used to communicate information between functions. TheUDF_INIT structure members follow. The initialization function should fill in any members that it wishes to change. (To use the default for a member, leave it unchanged.)

  • bool maybe_null

    xxx_init() should setmaybe_null to1 ifxxx() can returnNULL. The default value is1 if any of the arguments are declaredmaybe_null.

  • unsigned int decimals

    The number of decimal digits to the right of the decimal point. The default value is the maximum number of decimal digits in the arguments passed to the main function. For example, if the function is passed1.34,1.345, and1.3, the default would be 3, because1.345 has 3 decimal digits.

    For arguments that have no fixed number of decimals, thedecimals value is set to 31, which is 1 more than the maximum number of decimals permitted for theDECIMAL,FLOAT, andDOUBLE data types. This value is available as the constantNOT_FIXED_DEC in themysql_com.h header file.

    Adecimals value of 31 is used for arguments in cases such as aFLOAT orDOUBLE column declared without an explicit number of decimals (for example,FLOAT rather thanFLOAT(10,3)) and for floating-point constants such as1345E-3. It is also used for string and other nonnumber arguments that might be converted within the function to numeric form.

    The value to which thedecimals member is initialized is only a default. It can be changed within the function to reflect the actual calculation performed. The default is determined such that the largest number of decimals of the arguments is used. If the number of decimals isNOT_FIXED_DEC for even one of the arguments, that is the value used fordecimals.

  • unsigned int max_length

    The maximum length of the result. The defaultmax_length value differs depending on the result type of the function. For string functions, the default is the length of the longest argument. For integer functions, the default is 21 digits. For real functions, the default is 13 plus the number of decimal digits indicated byinitid->decimals. (For numeric functions, the length includes any sign or decimal point characters.)

    If you want to return a blob value, you can setmax_length to 65KB or 16MB. This memory is not allocated, but the value is used to decide which data type to use if there is a need to temporarily store the data.

  • char *ptr

    A pointer that the function can use for its own purposes. For example, functions can useinitid->ptr to communicate allocated memory among themselves.xxx_init() should allocate the memory and assign it to this pointer:

    initid->ptr = allocated_memory;

    Inxxx() andxxx_deinit(), refer toinitid->ptr to use or deallocate the memory.

  • bool const_item

    xxx_init() should setconst_item to1 ifxxx() always returns the same value and to0 otherwise.

Loadable Function Calling Sequences for Aggregate Functions

This section describes the different interface functions that you need to define when you create an aggregate loadable function. For information about the order in which MySQL calls these functions, seeLoadable Function Interface Functions.

  • xxx_reset()

    This function is called when MySQL finds the first row in a new group. It should reset any internal summary variables and then use the givenUDF_ARGS argument as the first value in your internal summary value for the group. Declarexxx_reset() as follows:

    void xxx_reset(UDF_INIT *initid, UDF_ARGS *args,               char *is_null, char *error);

    xxx_reset() is not needed or used in MySQL 8.0, in which the loadable function interface usesxxx_clear() instead. However, you can define bothxxx_reset() andxxx_clear() if you want to have your function work with older versions of the server. (If you do include both functions, thexxx_reset() function in many cases can be implemented internally by callingxxx_clear() to reset all variables, and then callingxxx_add() to add theUDF_ARGS argument as the first value in the group.)

  • xxx_clear()

    This function is called when MySQL needs to reset the summary results. It is called at the beginning for each new group but can also be called to reset the values for a query where there were no matching rows. Declarexxx_clear() as follows:

    void xxx_clear(UDF_INIT *initid, char *is_null, char *error);

    is_null is set to point toCHAR(0) before callingxxx_clear().

    If something went wrong, you can store a value in the variable to which theerror argument points.error points to a single-byte variable, not to a string buffer.

    xxx_clear() is required by MySQL 8.0.

  • xxx_add()

    This function is called for all rows that belong to the same group. You should use it to add the value in theUDF_ARGS argument to your internal summary variable.

    void xxx_add(UDF_INIT *initid, UDF_ARGS *args,             char *is_null, char *error);

Thexxx() function for an aggregate loadable function should be declared the same way as for a nonaggregate loadable function. SeeLoadable Function Calling Sequences for Simple Functions.

For an aggregate loadable function, MySQL calls thexxx() function after all rows in the group have been processed. You should normally never access itsUDF_ARGS argument here but instead return a value based on your internal summary variables.

Return value handling inxxx() should be done the same way as for a nonaggregate loadable function. SeeLoadable Function Return Values and Error Handling.

Thexxx_reset() andxxx_add() functions handle theirUDF_ARGS argument the same way as functions for nonaggregate UDFs. SeeLoadable Function Argument Processing.

The pointer arguments tois_null anderror are the same for all calls toxxx_reset(),xxx_clear(),xxx_add() andxxx(). You can use this to remember that you got an error or whether thexxx() function should returnNULL. You should not store a string into*error!error points to a single-byte variable, not to a string buffer.

*is_null is reset for each group (before callingxxx_clear()).*error is never reset.

If*is_null or*error are set whenxxx() returns, MySQL returnsNULL as the result for the group function.

Loadable Function Argument Processing

Theargs parameter points to aUDF_ARGS structure that has the members listed here:

  • unsigned int arg_count

    The number of arguments. Check this value in the initialization function if you require your function to be called with a particular number of arguments. For example:

    if (args->arg_count != 2){    strcpy(message,"XXX() requires two arguments");    return 1;}

    For otherUDF_ARGS member values that are arrays, array references are zero-based. That is, refer to array members using index values from 0 toargs->arg_count − 1.

  • enum Item_result *arg_type

    A pointer to an array containing the types for each argument. The possible type values areSTRING_RESULT,INT_RESULT,REAL_RESULT, andDECIMAL_RESULT.

    To make sure that arguments are of a given type and return an error if they are not, check thearg_type array in the initialization function. For example:

    if (args->arg_type[0] != STRING_RESULT ||    args->arg_type[1] != INT_RESULT){    strcpy(message,"XXX() requires a string and an integer");    return 1;}

    Arguments of typeDECIMAL_RESULT are passed as strings, so you handle them the same way asSTRING_RESULT values.

    As an alternative to requiring your function's arguments to be of particular types, you can use the initialization function to set thearg_type elements to the types you want. This causes MySQL to coerce arguments to those types for each call toxxx(). For example, to specify that the first two arguments should be coerced to string and integer, respectively, do this inxxx_init():

    args->arg_type[0] = STRING_RESULT;args->arg_type[1] = INT_RESULT;

    Exact-value decimal arguments such as1.3 orDECIMAL column values are passed with a type ofDECIMAL_RESULT. However, the values are passed as strings. To receive a number, use the initialization function to specify that the argument should be coerced to aREAL_RESULT value:

    args->arg_type[2] = REAL_RESULT;

  • char **args

    args->args communicates information to the initialization function about the general nature of the arguments passed to your function. For a constant argumenti,args->args[i] points to the argument value. (See later for instructions on how to access the value properly.) For a nonconstant argument,args->args[i] is0. A constant argument is an expression that uses only constants, such as3 or4*7-2 orSIN(3.14). A nonconstant argument is an expression that refers to values that may change from row to row, such as column names or functions that are called with nonconstant arguments.

    For each invocation of the main function,args->args contains the actual arguments that are passed for the row currently being processed.

    If argumenti representsNULL,args->args[i] is a null pointer (0). If the argument is notNULL, functions can refer to it as follows:

    • An argument of typeSTRING_RESULT is given as a string pointer plus a length, to enable handling of binary data or data of arbitrary length. The string contents are available asargs->args[i] and the string length isargs->lengths[i]. Do not assume that the string is null-terminated.

      For additional information about string arguments, seeLoadable Function Character Set Handling.

    • For an argument of typeINT_RESULT, you must castargs->args[i] to along long value:

      long long int_val;int_val = *((long long*) args->args[i]);

    • For an argument of typeREAL_RESULT, you must castargs->args[i] to adouble value:

      double    real_val;real_val = *((double*) args->args[i]);

    • For an argument of typeDECIMAL_RESULT, the value is passed as a string and should be handled like aSTRING_RESULT value.

    • ROW_RESULT arguments are not implemented.

  • unsigned long *lengths

    For the initialization function, thelengths array indicates the maximum string length for each argument. You should not change these. For each invocation of the main function,lengths contains the actual lengths of any string arguments that are passed for the row currently being processed. For arguments of typesINT_RESULT orREAL_RESULT,lengths still contains the maximum length of the argument (as for the initialization function).

  • char *maybe_null

    For the initialization function, themaybe_null array indicates for each argument whether the argument value might be null (0 if no, 1 if yes).

  • char **attributes

    args->attributes communicates information about the names of the function arguments. For argumenti, the attribute name is available as a string inargs->attributes[i] and the attribute length isargs->attribute_lengths[i]. Do not assume that the string is null-terminated.

    By default, the name of a function argument is the text of the expression used to specify the argument. For loadable functions, an argument may also have an optional[AS]alias_name clause, in which case the argument name isalias_name. Theattributes value for each argument thus depends on whether an alias was given.

    Suppose that a loadable functionmy_udf() is invoked as follows:

    SELECT my_udf(expr1, expr2 AS alias1, expr3 alias2);

    In this case, theattributes andattribute_lengths arrays will have these values:

    args->attributes[0] = "expr1"args->attribute_lengths[0] = 5args->attributes[1] = "alias1"args->attribute_lengths[1] = 6args->attributes[2] = "alias2"args->attribute_lengths[2] = 6

  • unsigned long *attribute_lengths

    Theattribute_lengths array indicates the length of each argument name.

Loadable Function Return Values and Error Handling

The initialization function should return0 if no error occurred and1 otherwise. If an error occurs,xxx_init() should store a null-terminated error message in themessage parameter. The message is returned to the client. The message buffer isMYSQL_ERRMSG_SIZE characters long. Try to keep the message to less than 80 characters so that it fits the width of a standard terminal screen.

The return value of the main functionxxx() is the function value, forlong long anddouble functions. A string function should return a pointer to the result and set*length to the length (in bytes) of the return value. For example:

memcpy(result, "result string", 13);*length = 13;

MySQL passes a buffer to thexxx() function using theresult parameter. This buffer is sufficiently long to hold 255 characters, which can be multibyte characters. Thexxx() function can store the result in this buffer if it fits, in which case the return value should be a pointer to the buffer. If the function stores the result in a different buffer, it should return a pointer to that buffer.

If your string function does not use the supplied buffer (for example, if it needs to return a string longer than 255 characters), you must allocate the space for your own buffer withmalloc() in thexxx_init() function or thexxx() function and free it in yourxxx_deinit() function. You can store the allocated memory in theptr slot in theUDF_INIT structure for reuse by futurexxx() calls. SeeLoadable Function Calling Sequences for Simple Functions.

For additional information about string arguments, seeLoadable Function Character Set Handling.

To indicate a return value ofNULL in the main function, set*is_null to1:

*is_null = 1;

To indicate an error return in the main function, set*error to1:

*error = 1;

Ifxxx() sets*error to1 for any row, the function value isNULL for the current row and for any subsequent rows processed by the statement in whichXXX() was invoked. (xxx() is not even called for subsequent rows.)

Loadable Function Character Set Handling

Prior to MySQL 8.0.19, loadable functions take no account of the character set or collation of string arguments or return values. In effect, string arguments and return values are treated as binary strings, with the implication that only string arguments containing single-byte characters can be handled reliably.

As of MySQL 8.0.19, loadable function behavior is still the same by default, but the interface for writing loadable functions has been extended to enable loadable functions to determine the character set and collation of string arguments, and to return strings that have a particular character set and collation. These capabilities are optional for loadable function writers, who may take advantage of them as desired.

Of the loadable functions distributed with MySQL, those associated with the following features and extensions have been modified to take advantage of the character-set capabilities: MySQL Enterprise Audit, MySQL Enterprise Firewall, MySQL Enterprise Data Masking and De-Identification, MySQL Keyring (the general-purpose keyring loadable functions only, not those specific to particular keyring plugins), and Group Replication. The modification applies only where it make sense. For example, a loadable function that returns encrypted data isintended to return a binary string, not a character string.

Character-set capabilities for loadable functions are implemented using themysql_udf_metadata server component service. For information about this service, see the MySQL Server Doxygen documentation, available athttps://dev.mysql.com/doc/index-other.html (search fors_mysql_mysql_udf_metadata andudf_metadata_imp). Source code for the MySQL Keyring loadable functions is available in Community source distributions and may be examined as examples for third-party loadable function writers who wish to modify their own loadable functions to be character set-aware.

If a loadable function takes string arguments or returns a string value and is modified to be character set-aware, the following compatibility considerations apply:

  • With respect to the arguments they pass to the loadable function, applications will continue to work because the function is now capable of handling string arguments in any character set, including binary strings.

  • If a loadable function is to return a string result in a character set different from the character set of its arguments, the function must perform the character set conversion internally. For example, this is the case if a function acceptslatin1 arguments but returns autf8mb4 result.

Loadable Function Compiling and Installing

Files implementing loadable functions must be compiled and installed on the host where the server runs. The process is described here for the example loadable function filesql/udf_example.cc that is included in MySQL source distributions. For additional information about loadable function installation, seeInstalling and Uninstalling Loadable Functions.

If a loadable function will be referred to in statements that will be replicated to replicas, you must ensure that every replica also has the function available. Otherwise, replication fails on the replicas when they attempt to invoke the function.

Theudf_example.cc file contains the following functions:

  • metaphon() returns a metaphon string of the string argument. This is something like a soundex string, but it is more tuned for English.

  • myfunc_double() returns the sum of the ASCII values of the characters in its arguments, divided by the sum of the length of its arguments.

  • myfunc_int() returns the sum of the length of its arguments.

  • sequence([const int]) returns a sequence starting from the given number or 1 if no number has been given.

  • lookup() returns the IP address for a host name.

  • reverse_lookup() returns the host name for an IP address. The function may be called either with a single string argument of the form'xxx.xxx.xxx.xxx' or with four numbers.

  • avgcost() returns an average cost. This is an aggregate function.

On Unix and Unix-like systems, compile loadable functions using the following procedure:

A dynamically loadable file should be compiled as a sharable library file, using a command something like this:

gcc -shared -o udf_example.so udf_example.cc

If you are usinggcc withCMake (which is how MySQL itself is configured), you should be able to createudf_example.so with a simpler command:

make udf_example

After compiling a shared object containing loadable functions, you must install it and tell MySQL about it. Compiling a shared object fromudf_example.cc usinggcc directly produces a file namedudf_example.so. Copy the shared object to the server's plugin directory and name itudf_example.so. This directory is given by the value of theplugin_dir system variable.

On some systems, theldconfig program that configures the dynamic linker does not recognize a shared object unless its name begins withlib. In this case you should rename a file such asudf_example.so tolibudf_example.so.

On Windows, compile loadable functions using the following procedure:

  1. Obtain a MySQL source distribution. SeeHow to Get MySQL.

  2. Obtain theCMake build utility, if necessary, fromhttp://www.cmake.org. (Version 2.6 or later is required).

  3. In the source tree, look in thesql directory for files namedudf_example.def andudf_example.cc. Copy both files from this directory to your working directory.

  4. Create aCMakemakefile (CMakeLists.txt) with these contents:

    PROJECT(udf_example)# Path for MySQL include directoryINCLUDE_DIRECTORIES("c:/mysql/include")ADD_DEFINITIONS("-DHAVE_DLOPEN")ADD_LIBRARY(udf_example MODULE udf_example.cc udf_example.def)TARGET_LINK_LIBRARIES(udf_example wsock32)

  5. Create the VC project and solution files, substituting an appropriategenerator value:

    cmake -G "generator"

    Invokingcmake --help shows you a list of valid generators.

  6. Createudf_example.dll:

    devenv udf_example.sln /build Release

On all platforms, after the shared library file has been copied to theplugin_dir directory, notifymysqld about the new functions with the following statements. The file name suffix differs per platform (for example,.so for Unix and Unix-like systems,.dll for Windows), so adjust the.so suffix for your platform as necessary.

CREATE FUNCTION metaphon RETURNS STRING  SONAME 'udf_example.so';CREATE FUNCTION myfunc_double RETURNS REAL  SONAME 'udf_example.so';CREATE FUNCTION myfunc_int RETURNS INTEGER  SONAME 'udf_example.so';CREATE FUNCTION sequence RETURNS INTEGER  SONAME 'udf_example.so';CREATE FUNCTION lookup RETURNS STRING  SONAME 'udf_example.so';CREATE FUNCTION reverse_lookup RETURNS STRING  SONAME 'udf_example.so';CREATE AGGREGATE FUNCTION avgcost RETURNS REAL  SONAME 'udf_example.so';

Once installed, a function remains installed until it is uninstalled.

To remove functions, useDROP FUNCTION:

DROP FUNCTION metaphon;DROP FUNCTION myfunc_double;DROP FUNCTION myfunc_int;DROP FUNCTION sequence;DROP FUNCTION lookup;DROP FUNCTION reverse_lookup;DROP FUNCTION avgcost;

TheCREATE FUNCTION andDROP FUNCTION statements update themysql.func system table that serves as a loadable function registry. These statements require theINSERT andDELETE privilege, respectively, for themysql database.

During the normal startup sequence, the server loads functions registered in themysql.func table. If the server is started with the--skip-grant-tables option, functions registered in the table are not loaded and are unavailable.

Loadable Function Security Precautions

MySQL takes several measures to prevent misuse of loadable functions.

Loadable function library files cannot be placed in arbitrary directories. They must be located in the server's plugin directory. This directory is given by the value of theplugin_dir system variable.

To useCREATE FUNCTION orDROP FUNCTION, you must have theINSERT orDELETE privilege, respectively, for themysql database. This is necessary because those statements add and delete rows from themysql.func table.

Loadable functions should have at least one symbol defined in addition to thexxx symbol that corresponds to the mainxxx() function. These auxiliary symbols correspond to thexxx_init(),xxx_deinit(),xxx_reset(),xxx_clear(), andxxx_add() functions.mysqld also supports an--allow-suspicious-udfs option that controls whether Loadable functions that have only anxxx symbol can be loaded. By default, the option is disabled, to prevent attempts at loading functions from shared library files other than those containing legitimate Loadable functions. If you have older Loadable functions that contain only thexxx symbol and that cannot be recompiled to include an auxiliary symbol, it may be necessary to specify the--allow-suspicious-udfs option. Otherwise, you should avoid enabling it.