Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Explore specialized utilities. This category includes functions for benchmarking, managing UUIDs, converting IP addresses, and handling binary data formats.
Convert bytes to a human-readable string. This function formats a numeric byte count into units like KiB, MiB, GiB, up to EiB.
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 function, except that function does not display exbibytes.
This page is licensed: CC BY-SA / Gnu FDL
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 |
+--------------------------------+--------------------------------+Convert an IPv4 address to an integer. This function takes a dotted-quad IP string (e.g., '1.2.3.4') and returns its numeric representation.
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.
This is calculated as follows: 192 x 2563 + 168 x 256 2 + 1 x 256 + 1.
This page is licensed: GPLv2, originally from
Convert an integer to an IPv4 address. This function takes a numeric IP value and returns its dotted-quad string representation.
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.
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
Convert a binary IPv6 address to string. This function takes a binary IPv6 address (like from INET6_ATON) and returns its human-readable string representation.
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.
This page is licensed: CC BY-SA / Gnu FDL
Check who holds a named lock. This function returns the connection identifier of the client holding the lock, or NULL if the lock is free.
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 plugin is installed, the table contains information about locks of this kind (as well as ).
Statements using the IS_USED_LOCK function are .
This page is licensed: GPLv2, originally from
Check validity of an IPv4 address. This function returns 1 if the argument is a valid IPv4 address string, and 0 otherwise.
IS_IPV4(expr)If the expression is a valid IPv4 address, returns 1, otherwise returns 0.
IS_IPV4() is stricter than , but as strict as , 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.
This page is licensed: CC BY-SA / Gnu FDL
Check if an IPv6 address is IPv4-mapped. This function returns 1 if the binary IPv6 address represents an IPv4-mapped address.
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.
When the argument is not , automatic implicit to INET6 is applied. As a consequence, IS_IPV4_MAPPED now understands arguments in both text representation and binary(16) representation.
The function understands only binary(16) representation.
This page is licensed: CC BY-SA / Gnu FDL
Wait for a replica to reach a log position. This function blocks until the replica has read and applied updates up to a specified position in the master's binary log.
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 . If you don't specify it, it's set to the value of the system variable.
Statements using the MASTER_POS_WAIT() function are not .
This page is licensed: GPLv2, originally from
Force a column name for an expression. This function is primarily used internally for replication to ensure consistent column naming.
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.
This page is licensed: GPLv2, originally from
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
INET6 Data Type
This page is licensed: CC BY-SA / Gnu FDL
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.
This page is licensed: CC BY-SA / Gnu FDL
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 |
+--------------------------------------+SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
| 14 |
+--------+Check if a named lock is free. This function returns 1 if the specified lock is available (not currently held), and 0 if it is in use.
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 plugin is installed, the table contains information about locks of this kind (as well as ).
Statements using the IS_FREE_LOCK function are .
This page is licensed: GPLv2, originally from
Wait for a replica to apply a GTID. This function blocks until the replica has executed transactions up to the specified Global Transaction ID.
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 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 than , 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 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 session variable. This is useful in a read-scaleout 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 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 (). 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. works only against a specific connection. This also means that while MASTER_POS_WAIT() aborts if its master connection is terminated with 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).
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 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
INET6_ATON(expr)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 |
+----------------------------------------------+RELEASE_ALL_LOCKS()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 |
+---------------------+SELECT INET_ATON('192.168.1.1');
+--------------------------+
| INET_ATON('192.168.1.1') |
+--------------------------+
| 3232235777 |
+--------------------------+SELECT INET_NTOA(3232235777);
+-----------------------+
| INET_NTOA(3232235777) |
+-----------------------+
| 192.168.1.1 |
+-----------------------+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 |
+-------------------------------------------------------+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 |
+-----------------------------------------------+MASTER_GTID_WAIT(gtid-list[, timeout)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).
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.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SLEEP()1Statements using the SLEEP() function are not safe for statement-based replication.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SLEEP(duration)VALUE(col_name)INSERT INTO t (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUE(a)+VALUE(b);SELECT SLEEP(5.5);
+------------+
| SLEEP(5.5) |
+------------+
| 0 |
+------------+
1 row in set (5.50 sec)Check validity of an IPv6 address. This function returns 1 if the argument is a valid IPv6 address string, and 0 otherwise.
Generate a random UUID (v4). This function returns a version 4 UUID, which is generated using random numbers.
It is possible to generate UUIDv4 and UUIDv7, in addition to UUIDv1.
Returns a Universally Unique Identifier (UUID) version 4. To generate a version 1 UUID, see the function. To generate a version 7 UUID, see .
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 .
v1
- Return short (64 bit) Universal Unique Identifier
- UUID without the - character for Oracle compatibility
This page is licensed: CC BY-SA / Gnu FDL
Check if an IPv6 address is IPv4-compatible. This function returns 1 if the binary IPv6 address represents an IPv4-compatible address.
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.
When the argument is not , automatic implicit to INET6 is applied. As a consequence, IS_IPV4_COMPAT now understands arguments in both text representation and binary(16) representation.
The function understands only binary(16) representation.
This page is licensed: CC BY-SA / Gnu FDL
Generate a short 64-bit UUID. This function returns a unique, monotonically increasing integer suitable for use as a compact identifier.
UUID_v4()timeout10NULLA 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 is 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).
Multiple locks can be held:
It is possible to hold the same lock recursively. This example is viewed using the metadata_lock_info plugin:
Timeout example: Connection 1:
Connection 2:
After 10 seconds...
Deadlocks are automatically detected and resolved. Connection 1:
Connection 2:
Connection 1:
Connection 2:
This page is licensed: GPLv2, originally from fill_help_tables.sql
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:
Connection 2:
Connection 1:
It is possible to hold the same lock recursively. This example is viewed using the metadata_lock_info plugin:
This page is licensed: GPLv2, originally from fill_help_tables.sql
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 |
+---------------------+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 |
+-----------------------------------------------------------+GET_LOCK(str,timeout)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 |
+-----------------------+-----------------------+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 |
+-----------------------+-----------------------+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)SELECT GET_LOCK('lock4',10);
+----------------------+
| GET_LOCK('lock4',10) |
+----------------------+
| 1 |
+----------------------+SELECT GET_LOCK('lock4',10);+----------------------+
| GET_LOCK('lock4',10) |
+----------------------+
| 0 |
+----------------------+SELECT GET_LOCK('lock5',10);
+----------------------+
| GET_LOCK('lock5',10) |
+----------------------+
| 1 |
+----------------------+SELECT GET_LOCK('lock6',10);
+----------------------+
| GET_LOCK('lock6',10) |
+----------------------+
| 1 |
+----------------------+SELECT GET_LOCK('lock6',10);
+----------------------+
| GET_LOCK('lock6',10) |
+----------------------+
| 0 |
+----------------------+SELECT GET_LOCK('lock5',10);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionRELEASE_LOCK(str)SELECT GET_LOCK('lock1',10);
+----------------------+
| GET_LOCK('lock1',10) |
+----------------------+
| 1 |
+----------------------+SELECT GET_LOCK('lock2',10);
+----------------------+
| GET_LOCK('lock2',10) |
+----------------------+
| 1 |
+----------------------+SELECT RELEASE_LOCK('lock1'), RELEASE_LOCK('lock2'), RELEASE_LOCK('lock3');
+-----------------------+-----------------------+-----------------------+
| RELEASE_LOCK('lock1') | RELEASE_LOCK('lock2') | RELEASE_LOCK('lock3') |
+-----------------------+-----------------------+-----------------------+
| 1 | 0 | NULL |
+-----------------------+-----------------------+-----------------------+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)MDEV-11339 (Support UUID v4 generation)
MDEV-32637 (Implement native UUID7 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:
Statements using the UUID_SHORT() function are not safe for statement-based replication.
This page is licensed: GPLv2, originally from fill_help_tables.sql
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.
UUID() v1
UUID_SHORT() - Return short (64 bit) Universal Unique Identifier
SYS_GUID - UUID without the - character for Oracle compatibility
(Support UUID v4 generation)
(Implement native UUID7 function)
This page is licensed: CC BY-SA / Gnu FDL
Generate a UUID without hyphens. This Oracle-compatible function returns a globally unique identifier as a 32-character hexadecimal string.
The SYS_GUID function was introduced to enhance Oracle compatibility. Similar functionality can be achieved with the UUID function.
Returns a 16-byte globally unique identifier (GUID), similar to the function, but without the - character.
This page is licensed: CC BY-SA / Gnu FDL
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_SHORT()(server_id & 255) << 56
+ (server_startup_time_in_seconds << 24)
+ incremented_variable++;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_v7()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-7dd0d468543bSYS_GUID()SELECT SYS_GUID();
+----------------------------------+
| SYS_GUID() |
+----------------------------------+
| 2C574E45BA2811EBB265F859713E4BE4 |
+----------------------------------+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).
UUID_SHORT() - Return short (64 bit) Universal Unique Identifier
SYS_GUID - UUID without the - character for Oracle compatibility
(Support UUID v4 generation)
(Implement native UUID7 function)
This page is licensed: GPLv2, originally from fill_help_tables.sql
UUID()SELECT UUID();
+--------------------------------------+
| UUID() |
+--------------------------------------+
| cd41294a-afb0-11df-bc9b-00241dd75637 |
+--------------------------------------+