Explore miscellaneous functions in MariaDB Server. This section details various SQL functions that provide additional capabilities for data manipulation, conversion, and general utility tasks.
GET_LOCK(str,timeout)Tries to obtain a lock with a name given by the string str, using a timeout of timeout seconds. Returns 1 if the lock was obtained successfully, 0 if the attempt timed out (for example, because another client has previously locked the name), or NULL if an error occurred (such as running out of memory or the thread was killed with mariadb-admin kill).
A lock is released with RELEASE_LOCK(), when the connection terminates (either normally or abnormally). A connection can hold multiple locks at the same time, so a lock that is no longer needed needs to be explicitly released.
The IS_FREE_LOCK function returns whether a specified lock a free or not, and the IS_USED_LOCK whether the function is in use or not.
Locks obtained with GET_LOCK() do not interact with transactions. That is, committing a transaction does not release any such locks obtained during the transaction.
It is also possible to recursively set the same lock. If a lock with the same name is set n times, it needs to be released n times as well.
str is case insensitive for GET_LOCK() and related functions. If str is an empty string or NULL, GET_LOCK() returns NULL and does nothing. timeout supports microseconds.
If the metadata_lock_info plugin is installed, locks acquired with this function are visible in the Information Schema METADATA_LOCK_INFO table.
This function can be used to implement application locks or to simulate record locks. Names are locked on a server-wide basis. If a name has been locked by one client, GET_LOCK() blocks any request by another client for a lock with the same name. This allows clients that agree on a given lock name to use the name to perform cooperative advisory locking. But be aware that it also allows a client that is not among the set of cooperating clients to lock a name, either inadvertently or deliberately, and thus prevent any of the cooperating clients from locking that name. One way to reduce the likelihood of this is to use lock names that are database-specific or application-specific. For example, use lock names of the form db_name.str or app_name.str.
Statements using the GET_LOCK function are not safe for statement-based replication.
The patch to permit multiple locks was contributed by Konstantin "Kostja" Osipov (MDEV-3917).
SELECT GET_LOCK('lock1',10);
+----------------------+
| GET_LOCK('lock1',10) |
+----------------------+
| 1 |
+----------------------+
SELECT IS_FREE_LOCK('lock1'), IS_USED_LOCK('lock1');
+-----------------------+-----------------------+
| IS_FREE_LOCK('lock1') | IS_USED_LOCK('lock1') |
+-----------------------+-----------------------+
| 0 | 46 |
+-----------------------+-----------------------+
SELECT IS_FREE_LOCK('lock2'), IS_USED_LOCK('lock2');
+-----------------------+-----------------------+
| IS_FREE_LOCK('lock2') | IS_USED_LOCK('lock2') |
+-----------------------+-----------------------+
| 1 | NULL |
+-----------------------+-----------------------+Multiple locks can be held:
SELECT GET_LOCK('lock2',10);
+----------------------+
| GET_LOCK('lock2',10) |
+----------------------+
| 1 |
+----------------------+
SELECT IS_FREE_LOCK('lock1'), IS_FREE_LOCK('lock2');
+-----------------------+-----------------------+
| IS_FREE_LOCK('lock1') | IS_FREE_LOCK('lock2') |
+-----------------------+-----------------------+
| 0 | 0 |
+-----------------------+-----------------------+
SELECT RELEASE_LOCK('lock1'), RELEASE_LOCK('lock2');
+-----------------------+-----------------------+
| RELEASE_LOCK('lock1') | RELEASE_LOCK('lock2') |
+-----------------------+-----------------------+
| 1 | 1 |
+-----------------------+-----------------------+It is possible to hold the same lock recursively. This example is viewed using the metadata_lock_info plugin:
SELECT GET_LOCK('lock3',10);
+----------------------+
| GET_LOCK('lock3',10) |
+----------------------+
| 1 |
+----------------------+
SELECT GET_LOCK('lock3',10);
+----------------------+
| GET_LOCK('lock3',10) |
+----------------------+
| 1 |
+----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
+-----------+---------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+---------------------+---------------+-----------+--------------+------------+
| 46 | MDL_SHARED_NO_WRITE | NULL | User lock | lock3 | |
+-----------+---------------------+---------------+-----------+--------------+------------+
SELECT RELEASE_LOCK('lock3');
+-----------------------+
| RELEASE_LOCK('lock3') |
+-----------------------+
| 1 |
+-----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
+-----------+---------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+---------------------+---------------+-----------+--------------+------------+
| 46 | MDL_SHARED_NO_WRITE | NULL | User lock | lock3 | |
+-----------+---------------------+---------------+-----------+--------------+------------+
SELECT RELEASE_LOCK('lock3');
+-----------------------+
| RELEASE_LOCK('lock3') |
+-----------------------+
| 1 |
+-----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
Empty set (0.000 sec)Timeout example: Connection 1:
SELECT GET_LOCK('lock4',10);
+----------------------+
| GET_LOCK('lock4',10) |
+----------------------+
| 1 |
+----------------------+Connection 2:
SELECT GET_LOCK('lock4',10);After 10 seconds...
+----------------------+
| GET_LOCK('lock4',10) |
+----------------------+
| 0 |
+----------------------+Deadlocks are automatically detected and resolved. Connection 1:
SELECT GET_LOCK('lock5',10);
+----------------------+
| GET_LOCK('lock5',10) |
+----------------------+
| 1 |
+----------------------+Connection 2:
SELECT GET_LOCK('lock6',10);
+----------------------+
| GET_LOCK('lock6',10) |
+----------------------+
| 1 |
+----------------------+Connection 1:
SELECT GET_LOCK('lock6',10);
+----------------------+
| GET_LOCK('lock6',10) |
+----------------------+
| 0 |
+----------------------+Connection 2:
SELECT GET_LOCK('lock5',10);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionThis page is licensed: GPLv2, originally from fill_help_tables.sql
INET6_ATON(expr)Given an IPv6 or IPv4 network address as a string, returns a binary string that represents the numeric value of the address.
No trailing zone ID's or traling network masks are permitted. For IPv4 addresses, or IPv6 addresses with IPv4 address parts, no classful addresses or trailing port numbers are permitted and octal numbers are not supported.
The returned binary string will be VARBINARY(16) or VARBINARY(4) for IPv6 and IPv4 addresses respectively.
Returns NULL if the argument is not understood.
MariaDB starting with 10.5.0
INET6_ATON can take INET6 as an argument.
INET6_ATON cannot take INET6 as an argument.
SELECT HEX(INET6_ATON('10.0.1.1'));
+-----------------------------+
| HEX(INET6_ATON('10.0.1.1')) |
+-----------------------------+
| 0A000101 |
+-----------------------------+
SELECT HEX(INET6_ATON('48f3::d432:1431:ba23:846f'));
+----------------------------------------------+
| HEX(INET6_ATON('48f3::d432:1431:ba23:846f')) |
+----------------------------------------------+
| 48F3000000000000D4321431BA23846F |
+----------------------------------------------+INET6 Data Type
This page is licensed: CC BY-SA / Gnu FDL
INET6_NTOA(expr)Given an IPv6 or IPv4 network address as a numeric binary string, returns the address as a nonbinary string in the connection character set.
The return string is lowercase, and is platform independent, since it does not use functions specific to the operating system. It has a maximum length of 39 characters.
Returns NULL if the argument is not understood.
SELECT INET6_NTOA(UNHEX('0A000101'));
+-------------------------------+
| INET6_NTOA(UNHEX('0A000101')) |
+-------------------------------+
| 10.0.1.1 |
+-------------------------------+
SELECT INET6_NTOA(UNHEX('48F3000000000000D4321431BA23846F'));
+-------------------------------------------------------+
| INET6_NTOA(UNHEX('48F3000000000000D4321431BA23846F')) |
+-------------------------------------------------------+
| 48f3::d432:1431:ba23:846f |
+-------------------------------------------------------+This page is licensed: CC BY-SA / Gnu FDL
INET_ATON(expr)Given the dotted-quad representation of an IPv4 network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4- or 8-byte addresses.
Returns NULL if the argument is not understood.
SELECT INET_ATON('192.168.1.1');
+--------------------------+
| INET_ATON('192.168.1.1') |
+--------------------------+
| 3232235777 |
+--------------------------+This is calculated as follows: 192 x 2563 + 168 x 256 2 + 1 x 256 + 1.
This page is licensed: GPLv2, originally from fill_help_tables.sql
INET_NTOA(expr)Given a numeric IPv4 network address in network byte order (4 or 8 byte), returns the dotted-quad representation of the address as a string.
SELECT INET_NTOA(3232235777);
+-----------------------+
| INET_NTOA(3232235777) |
+-----------------------+
| 192.168.1.1 |
+-----------------------+192.168.1.1 corresponds to 3232235777 since 192 x 2563 + 168 x 256 2 + 1 x 256 + 1 = 3232235777.
This page is licensed: GPLv2, originally from fill_help_tables.sql
IS_FREE_LOCK(str)Checks whether the lock named str is free to use (that is, not locked). Returns 1 if the lock is free (no one is using the lock),0 if the lock is in use, and NULL if an error occurs (such as an incorrect argument, like an empty string or NULL). str is case insensitive.
If the metadata_lock_info plugin is installed, the Information Schema metadata_lock_info table contains information about locks of this kind (as well as metadata locks).
Statements using the IS_FREE_LOCK function are not safe for statement-based replication.
This page is licensed: GPLv2, originally from fill_help_tables.sql
IS_IPV4(expr)If the expression is a valid IPv4 address, returns 1, otherwise returns 0.
IS_IPV4() is stricter than INET_ATON(), but as strict as INET6_ATON(), in determining the validity of an IPv4 address. This implies that if IS_IPV4 returns 1, the same expression will always return a non-NULL result when passed to INET_ATON(), but that the reverse may not apply.
SELECT IS_IPV4('1110.0.1.1');
+-----------------------+
| IS_IPV4('1110.0.1.1') |
+-----------------------+
| 0 |
+-----------------------+
SELECT IS_IPV4('48f3::d432:1431:ba23:846f');
+--------------------------------------+
| IS_IPV4('48f3::d432:1431:ba23:846f') |
+--------------------------------------+
| 0 |
+--------------------------------------+This page is licensed: CC BY-SA / Gnu FDL
IS_IPV4_COMPAT(expr)Returns 1 if a given numeric binary string IPv6 address, such as returned by INET6_ATON(), is IPv4-compatible, otherwise returns 0.
The function understands only binary(16) representation.
SELECT IS_IPV4_COMPAT(INET6_ATON('::10.0.1.1'));
+------------------------------------------+
| IS_IPV4_COMPAT(INET6_ATON('::10.0.1.1')) |
+------------------------------------------+
| 1 |
+------------------------------------------+
SELECT IS_IPV4_COMPAT(INET6_ATON('::48f3::d432:1431:ba23:846f'));
+-----------------------------------------------------------+
| IS_IPV4_COMPAT(INET6_ATON('::48f3::d432:1431:ba23:846f')) |
+-----------------------------------------------------------+
| 0 |
+-----------------------------------------------------------+This page is licensed: CC BY-SA / Gnu FDL
IS_IPV4_MAPPED(expr)Returns 1 if a given a numeric binary string IPv6 address, such as returned by INET6_ATON(), is a valid IPv4-mapped address, otherwise returns 0.
The function understands only binary(16) representation.
SELECT IS_IPV4_MAPPED(INET6_ATON('::10.0.1.1'));
+------------------------------------------+
| IS_IPV4_MAPPED(INET6_ATON('::10.0.1.1')) |
+------------------------------------------+
| 0 |
+------------------------------------------+
SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.1.1'));
+-----------------------------------------------+
| IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.1.1')) |
+-----------------------------------------------+
| 1 |
+-----------------------------------------------+This page is licensed: CC BY-SA / Gnu FDL
IS_IPV6(expr)Returns 1 if the expression is a valid IPv6 address specified as a string, otherwise returns 0. Does not consider IPv4 addresses to be valid IPv6 addresses.
SELECT IS_IPV6('48f3::d432:1431:ba23:846f');
+--------------------------------------+
| IS_IPV6('48f3::d432:1431:ba23:846f') |
+--------------------------------------+
| 1 |
+--------------------------------------+
1 row in set (0.02 sec)
SELECT IS_IPV6('10.0.1.1');
+---------------------+
| IS_IPV6('10.0.1.1') |
+---------------------+
| 0 |
+---------------------+INET6 data type
This page is licensed: CC BY-SA / Gnu FDL
IS_USED_LOCK(str)Checks whether the lock named str is in use (that is, locked). If so, it returns the connection identifier of the client that holds the lock. Otherwise, it returns NULL. str is case insensitive.
If the metadata_lock_info plugin is installed, the Information Schema metadata_lock_info table contains information about locks of this kind (as well as metadata locks).
Statements using the IS_USED_LOCK function are not safe for statement-based replication.
This page is licensed: GPLv2, originally from fill_help_tables.sql
MASTER_GTID_WAIT(gtid-list[, timeout)This function takes a string containing a comma-separated list of global transaction id's (similar to the value of, for example, gtid_binlog_pos). It waits until the value of gtid_slave_pos has the same or higher seq_no within all replication domains specified in the gtid-list; in other words, it waits until the replica has reached the specified GTID position.
An optional second argument gives a timeout in seconds. If the timeout expires before the specified GTID position is reached, then the function returns -1. Passing NULL or a negative number for the timeout means no timeout, and the function will wait indefinitely.
If the wait completes without a timeout, 0 is returned. Passing NULL for the gtid-list makes the function return NULL immediately, without waiting.
The gtid-list may be the empty string, in which case MASTER_GTID_WAIT() returns immediately. If the gtid-list contains fewer domains thangtid_slave_pos, then only those domains are waited upon. If gtid-list contains a domain that is not present in @@gtid_slave_pos, then MASTER_GTID_WAIT() will wait until an event containing such domain_id arrives on the replica (or until timed out or killed).
MASTER_GTID_WAIT() can be useful to ensure that a replica has caught up to a master. Simply take the value of gtid_binlog_pos on the master, and use it in a MASTER_GTID_WAIT() call on the replica; when the call completes, the replica will have caught up with that master position.
MASTER_GTID_WAIT() can also be used in client applications together with the last_gtid session variable. This is useful in a read-scaleout replication setup, where the application writes to a single master but divides the reads out to a number of replica to distribute the load. In such a setup, there
is a risk that an application could first do an update on the master, and then a bit later do a read on a replica, and if the replica is not fast enough, the data read from the slave might not include the update just made, possibly confusing the application and/or the end-user. One way to avoid this is to request the value of last_gtid on the master just after the update. Then before doing the read on the replica, do a MASTER_GTID_WAIT() on the value obtained from the master; this will ensure that the read is not performed until the replica has replicated sufficiently far for the update to have become visible.
Note that MASTER_GTID_WAIT() can be used even if the replica is configured not to use GTID for connections (CHANGE MASTER TO master_use_gtid=no). This is because from MariaDB 10, GTIDs are always logged on the master server, and always recorded on the replica servers.
MASTER_GTID_WAIT() is global; it waits for any master connection to reach the specified GTID position. MASTER_POS_WAIT() works only against a specific connection. This also means that while MASTER_POS_WAIT() aborts if its master connection is terminated with STOP REPLICA or due to an error, MASTER_GTID_WAIT() continues to wait while replicas are stopped.
MASTER_GTID_WAIT() can take its timeout as a floating-point value, so a timeout in fractional seconds is supported, eg. MASTER_GTID_WAIT("0-1-100", 0.5). (The minimum wait is one microsecond, 0.000001 seconds).
MASTER_GTID_WAIT() allows one to specify a timeout of zero in order to do a non-blocking check to see if the replicas have progressed to a specific GTID position (MASTER_POS_WAIT() takes a zero timeout as meaning an infinite wait). To do an infinite MASTER_GTID_WAIT(), specify a negative timeout, or omit the timeout argument.
MASTER_GTID_WAIT() does not return the number of events executed since the wait started, nor does it return NULL if a replica thread is stopped. It always returns either 0 for successful wait completed, or -1 for timeout reached (or NULL if the specified gtid-pos is NULL).
Since MASTER_GTID_WAIT() looks only at the seq_no part of the GTIDs, not the server_id, care is needed if a replica becomes diverged from another server so that two different GTIDs with the same seq_no (in the same domain) arrive at the same server. This situation is in any case best avoided; setting gtid_strict_mode is recommended, as this will prevent any such out-of-order sequence numbers from ever being replicated on a replica.
This page is licensed: CC BY-SA / Gnu FDL
MASTER_POS_WAIT(log_name,log_pos[,timeout,["connection_name"]])This function is useful in replication for controlling primary/replica synchronization. It blocks until the replica has read and applied all updates up to the specified position (log_name,log_pos) in the primary log. The return value is the number of log events the replica had to wait for to advance to the specified position. The function returns NULL if the replica SQL thread is not started, the replica's primary information is not initialized, the arguments are incorrect, or an error occurs. It returns -1 if
the timeout has been exceeded. If the replica SQL thread stops whileMASTER_POS_WAIT() is waiting, the function returns NULL. If the replica is past the specified position, the function returns immediately.
If a timeout value is specified, MASTER_POS_WAIT() stops waiting when timeout seconds have elapsed. timeout must be greater than 0; a zero or negative timeout means no timeout.
The connection_name is used when you are using multi-source-replication. If you don't specify it, it's set to the value of the default_master_connection system variable.
Statements using the MASTER_POS_WAIT() function are not safe for statement-based replication.
This page is licensed: GPLv2, originally from fill_help_tables.sql
FORMAT_BYTES(double)Given a byte count, returns a string consisting of a value and the units in a human-readable format. The units will be in bytes, KiB (kibibytes), MiB (mebibytes), GiB (gibibytes), TiB (tebibytes), PiB (pebibytes) or EiB (exbibytes).
The binary prefixes (kibi, mebi, gibi, tebi, pebi and exbi) were created in December 1998 by the International Electrotechnical Commission to avoid possible ambiguity, as the widely-used prefixes kilo, mega, giga, tera, peta and exa can be used to refer to both the power-of-10 decimal system multipliers and the power-of-two binary system multipliers.
This function is similar to the Sys Schema format_bytes function, except that function does not display exbibytes.
SELECT FORMAT_BYTES(1000)FORMAT_BYTES(1024);
+--------------------+--------------------+
| FORMAT_BYTES(1000) | FORMAT_BYTES(1024) |
+--------------------+--------------------+
| 1000 bytes | 1.00 KiB |
+--------------------+--------------------+
SELECT FORMAT_BYTES(1000000),FORMAT_BYTES(1048576);
+-----------------------+-----------------------+
| FORMAT_BYTES(1000000) | FORMAT_BYTES(1048576) |
+-----------------------+-----------------------+
| 976.56 KiB | 1.00 MiB |
+-----------------------+-----------------------+
SELECT FORMAT_BYTES(1000000000),FORMAT_BYTES(1073741874);
+--------------------------+--------------------------+
| FORMAT_BYTES(1000000000) | FORMAT_BYTES(1073741874) |
+--------------------------+--------------------------+
| 953.67 MiB | 1.00 GiB |
+--------------------------+--------------------------+
SELECT FORMAT_BYTES(1000000000000),FORMAT_BYTES(1099511627776);
+-----------------------------+-----------------------------+
| FORMAT_BYTES(1000000000000) | FORMAT_BYTES(1099511627776) |
+-----------------------------+-----------------------------+
| 931.32 GiB | 1.00 TiB |
+-----------------------------+-----------------------------+
SELECT FORMAT_BYTES(1000000000000000),FORMAT_BYTES(1125899906842624);
+--------------------------------+--------------------------------+
| FORMAT_BYTES(1000000000000000) | FORMAT_BYTES(1125899906842624) |
+--------------------------------+--------------------------------+
| 909.49 TiB | 1.00 PiB |
+--------------------------------+--------------------------------+This page is licensed: CC BY-SA / Gnu FDL
NAME_CONST(name,value)Returns the given value. When used to produce a result set column,NAME_CONST() causes the column to have the given name. The arguments should be constants.
This function is used internally when replicating stored procedures. It makes little sense to use it explicitly in SQL statements, and it was not supposed to be used like that.
SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
| 14 |
+--------+This page is licensed: GPLv2, originally from fill_help_tables.sql
RELEASE_ALL_LOCKS()Releases all named locks held by the current session. Returns the number of locks released, or 0 if none were held.
Statements using the RELEASE_ALL_LOCKS function are not safe for statement-based replication.
SELECT RELEASE_ALL_LOCKS();
+---------------------+
| RELEASE_ALL_LOCKS() |
+---------------------+
| 0 |
+---------------------+
SELECT GET_LOCK('lock1',10);
+----------------------+
| GET_LOCK('lock1',10) |
+----------------------+
| 1 |
+----------------------+
SELECT RELEASE_ALL_LOCKS();
+---------------------+
| RELEASE_ALL_LOCKS() |
+---------------------+
| 1 |
+---------------------+This page is licensed: CC BY-SA / Gnu FDL
RELEASE_LOCK(str)Releases the lock named by the string str that was obtained with GET_LOCK(). Returns 1 if the lock was released, 0 if the lock was not established by this thread (in which case the lock is not released), and NULL if the named lock did not exist. The lock does not exist if it was never obtained by a call to GET_LOCK() or if it has previously been released.
str is case insensitive. If str is an empty string or NULL, RELEASE_LOCK() returns NULL and does nothing.
Statements using the RELEASE_LOCK function are not safe for statement-based replication.
The DO statement is convenient to use with RELEASE_LOCK().
Connection1:
SELECT GET_LOCK('lock1',10);
+----------------------+
| GET_LOCK('lock1',10) |
+----------------------+
| 1 |
+----------------------+Connection 2:
SELECT GET_LOCK('lock2',10);
+----------------------+
| GET_LOCK('lock2',10) |
+----------------------+
| 1 |
+----------------------+Connection 1:
SELECT RELEASE_LOCK('lock1'), RELEASE_LOCK('lock2'), RELEASE_LOCK('lock3');
+-----------------------+-----------------------+-----------------------+
| RELEASE_LOCK('lock1') | RELEASE_LOCK('lock2') | RELEASE_LOCK('lock3') |
+-----------------------+-----------------------+-----------------------+
| 1 | 0 | NULL |
+-----------------------+-----------------------+-----------------------+It is possible to hold the same lock recursively. This example is viewed using the metadata_lock_info plugin:
SELECT GET_LOCK('lock3',10);
+----------------------+
| GET_LOCK('lock3',10) |
+----------------------+
| 1 |
+----------------------+
SELECT GET_LOCK('lock3',10);
+----------------------+
| GET_LOCK('lock3',10) |
+----------------------+
| 1 |
+----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
+-----------+---------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+---------------------+---------------+-----------+--------------+------------+
| 46 | MDL_SHARED_NO_WRITE | NULL | User lock | lock3 | |
+-----------+---------------------+---------------+-----------+--------------+------------+
SELECT RELEASE_LOCK('lock3');
+-----------------------+
| RELEASE_LOCK('lock3') |
+-----------------------+
| 1 |
+-----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
+-----------+---------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+---------------------+---------------+-----------+--------------+------------+
| 46 | MDL_SHARED_NO_WRITE | NULL | User lock | lock3 | |
+-----------+---------------------+---------------+-----------+--------------+------------+
SELECT RELEASE_LOCK('lock3');
+-----------------------+
| RELEASE_LOCK('lock3') |
+-----------------------+
| 1 |
+-----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
Empty set (0.000 sec)This page is licensed: GPLv2, originally from fill_help_tables.sql
SLEEP(duration)Sleeps (pauses) for the number of seconds given by the duration argument, then returns 0. If SLEEP() is interrupted, it returns 1. The duration may have a fractional part given in microseconds.
Statements using the SLEEP() function are not safe for statement-based replication.
SELECT SLEEP(5.5);
+------------+
| SLEEP(5.5) |
+------------+
| 0 |
+------------+
1 row in set (5.50 sec)This page is licensed: GPLv2, originally from fill_help_tables.sql
The SYS_GUID function was introduced to enhance Oracle compatibility. Similar functionality can be achieved with the UUID function.
SYS_GUID()Returns a 16-byte globally unique identifier (GUID), similar to the UUID function, but without the - character.
SELECT SYS_GUID();
+----------------------------------+
| SYS_GUID() |
+----------------------------------+
| 2C574E45BA2811EBB265F859713E4BE4 |
+----------------------------------+This page is licensed: CC BY-SA / Gnu FDL
UUID()Returns a Universally Unique Identifier (UUID) version 1. Functions to generate v4 and v7 UUIDs are available from MariaDB 11.7. See UUIDv4 and UUIDv7 respectively.
A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.
UUID() results are intended to be unique, but cannot always be relied upon to be unpredictable and unguessable.
A UUID is a 128-bit number represented by a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format:
The first three numbers are generated from a timestamp.
The fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (for example, due to daylight saving time).
The fifth number is an IEEE 802 node number that provides spatial uniqueness. A random number is substituted if the latter is not available (for example, because the host computer has no Ethernet card, or we do not know how to find the hardware address of an interface on your operating system). In this case, spatial uniqueness cannot be guaranteed. Nevertheless, a collision should have very low probability.
Currently, the MAC address of an interface is taken into account only on FreeBSD and Linux. On other operating systems, MariaDB uses a randomly generated 48-bit number.
Statements using the UUID() function are not safe for statement-based replication.
The function generates a UUIDv1 and the results are generated according to the "DCE 1.1:Remote Procedure Call" (Appendix A) CAE (Common Applications Environment) Specifications published by The Open Group in October 1997 (Document Number C706).
SELECT UUID();
+--------------------------------------+
| UUID() |
+--------------------------------------+
| cd41294a-afb0-11df-bc9b-00241dd75637 |
+--------------------------------------+UUID_SHORT() - Return short (64 bit) Universal Unique Identifier
SYS_GUID - UUID without the - character for Oracle compatibility
MDEV-11339 (Support UUID v4 generation)
MDEV-32637 (Implement native UUID7 function)
This page is licensed: GPLv2, originally from fill_help_tables.sql
UUID_SHORT()Returns a "short" universally unique identifier as a 64-bit unsigned integer (rather than a string-form 128-bit identifier as returned by the UUID() function).
The value of UUID_SHORT() is guaranteed to be unique if the following conditions hold:
The server_id of the current host is unique among your set of master and replica servers.
server_id is between 0 and 255.
You don't set back your system time for your server between mariadbd restarts.
You do not invoke UUID_SHORT() on average more than 16 million times per second between mariadbd restarts
The UUID_SHORT() return value is constructed this way:
(server_id & 255) << 56
+ (server_startup_time_in_seconds << 24)
+ incremented_variable++;Statements using the UUID_SHORT() function are not safe for statement-based replication.
SELECT UUID_SHORT();
+-------------------+
| UUID_SHORT() |
+-------------------+
| 21517162376069120 |
+-------------------+CREATE TABLE t1 (a BIGINT UNSIGNED DEFAULT(uuid_short()) PRIMARY KEY);
INSERT INTO t1 VALUES(),();
SELECT * FROM t1;
+-------------------+
| a |
+-------------------+
| 98113699159474176 |
| 98113699159474177 |
+-------------------+UUID() ; Return full (128 bit) Universally Unique Identifier
Sequences - an alternative to auto_increment available from MariaDB 10.3
SYS_GUID - UUID without the - character for Oracle compatibility
This page is licensed: GPLv2, originally from fill_help_tables.sql
It is possible to generate UUIDv4 and UUIDv7, in addition to UUIDv1.
UUID_v4()Returns a Universally Unique Identifier (UUID) version 4. To generate a version 1 UUID, see the UUID function. To generate a version 7 UUID, see UUIDv7.
A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.
UUID_v4() results are intended to be unique, but cannot always be relied upon to be unpredictable and unguessable.
A UUID is a 128-bit number represented by a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format.
Statements using the UUID_v4() function are not safe for statement-based replication.
SELECT UUID(),UUID_v4(),UUID_v7()\G
*************************** 1. row ***************************
UUID(): 63ae8c92-799a-11ef-98b2-f859713e4be4
UUID_v4(): a2443495-1b94-415b-b6fa-fe8e79ba4812
UUID_v7(): 01921e85-f198-7490-9b89-7dd0d468543b
CREATE TABLE t1 (a int primary key not null, u UUID DEFAULT UUID_v4(), unique key(u));UUID() v1
UUID_SHORT() - Return short (64 bit) Universal Unique Identifier
SYS_GUID - UUID without the - character for Oracle compatibility
MDEV-11339 (Support UUID v4 generation)
MDEV-32637 (Implement native UUID7 function)
This page is licensed: CC BY-SA / Gnu FDL
It is possible to generate UUIDv4 and UUIDv7, in addition to UUIDv1.
UUID_v7()Returns a Universally Unique Identifier (UUID) version 7. To generate a version 1 UUID, see the UUID function. To generate a version 4 UUID, see UUID_v4.
A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.
A UUID is a 128-bit number represented by a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format.
Statements using the UUID_v7() function are not safe for statement-based replication.
SELECT UUID(),UUID_v4(),UUID_v7()\G
*************************** 1. row ***************************
UUID(): 63ae8c92-799a-11ef-98b2-f859713e4be4
UUID_v4(): a2443495-1b94-415b-b6fa-fe8e79ba4812
UUID_v7(): 01921e85-f198-7490-9b89-7dd0d468543bUUID() v1
UUID_SHORT() - Return short (64 bit) Universal Unique Identifier
SYS_GUID - UUID without the - character for Oracle compatibility
MDEV-11339 (Support UUID v4 generation)
MDEV-32637 (Implement native UUID7 function)
This page is licensed: CC BY-SA / Gnu FDL
VALUE(col_name)In an INSERT ... ON DUPLICATE KEY UPDATE statement, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts.
The VALUES() function is meaningful only in INSERT ... ON DUPLICATE KEY UPDATE statements and returns NULL otherwise.
This function was renamed to VALUE(), because it's incompatible with the standard Table Value Constructors syntax.
The VALUES() function can still be used but only in INSERT ... ON DUPLICATE KEY UPDATE statements; it's a syntax error otherwise.
INSERT INTO t (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUE(a)+VALUE(b);This page is licensed: GPLv2, originally from fill_help_tables.sql