🌐

MariaDB Miscellaneous & Network Functions

MariaDB

Complete reference for MariaDB miscellaneous and network functions covering UUID(), SLEEP(), INET_ATON(), INET_NTOA(), GET_LOCK(), IS_IPV4(), IS_IPV6(), FORMAT_BYTES(), and replication helper functions. Updated for MariaDB 11.x.

25 functions

What are MariaDB Miscellaneous & Network Functions?

MariaDB miscellaneous functions cover a range of utility operations that do not fit into other categories. UUID() generates a UUID v1 value (time-based, unlike PostgreSQL's gen_random_uuid() which is v4). INET_ATON() and INET_NTOA() convert between IPv4 address strings and their integer representation. GET_LOCK() and RELEASE_LOCK() implement named application-level locks similar to PostgreSQL's advisory locks. SLEEP() introduces a delay in seconds and is useful for testing lock timeouts.

UUID

→ VARCHAR(36)

Generates a universally unique identifier (UUID) based on RFC 4122 version 1 (time-based).

Signature

UUID()

Examples

Basic UUID generation

sql

SELECT UUID();

→-- '6ccd780c-baba-1026-9564-5b8c656024db'

Each call returns a unique value

sql

SELECT UUID(), UUID();

→-- Two different UUIDs

As primary key

sql

INSERT INTO orders (id, item) VALUES (UUID(), 'Widget');

→-- 1 row inserted

Always 36 characters

sql

SELECT LENGTH(UUID());

→-- 36

Never returns NULL

sql

SELECT UUID() IS NULL;

→-- 0

⚠Anti-Pattern— Using UUID as a clustered key without consideration

Using UUID() as a VARCHAR clustered primary key causes severe index fragmentation due to random ordering.

✓ Instead: Store as BINARY(16) with UNHEX(REPLACE(UUID(),'-','')) or use UUID_SHORT() for monotonic integer keys.

◆MariaDB Note

UUID() generates a v1 UUID derived from timestamp and MAC address; output is non-random but globally unique.

⇄ vs PostgreSQL: PostgreSQL uses gen_random_uuid() (pgcrypto or built-in in 13+) for UUID v4 (random); uuid-ossp extension provides uuid_generate_v1() for v1.

UUID_SHORT

→ BIGINT UNSIGNED

Returns a 64-bit monotonically increasing integer unique across servers when server_id and server startup time differ.

Signature

UUID_SHORT()

Examples

Basic call

sql

SELECT UUID_SHORT();

→-- 92395783831158784

As BIGINT primary key

sql

INSERT INTO events (id, name) VALUES (UUID_SHORT(), 'login');

→-- 1 row inserted

Always positive

sql

SELECT UUID_SHORT() > 0;

→-- 1

Each call increments

sql

SELECT UUID_SHORT(), UUID_SHORT();

→-- Two incrementing values

Never NULL

sql

SELECT UUID_SHORT() IS NULL;

→-- 0

⚠Anti-Pattern— Assuming global uniqueness across all servers

UUID_SHORT() uniqueness relies on server_id (0-255) being unique across all replication nodes; duplicate server_ids cause collisions.

✓ Instead: Ensure each MariaDB instance has a unique server_id set in my.cnf before using UUID_SHORT() in a multi-master setup.

◆MariaDB Note

UUID_SHORT() value encodes server_id (8 bits), startup time (32 bits), and an auto-incrementing counter (24 bits).

⇄ vs PostgreSQL: PostgreSQL has no direct equivalent; use BIGSERIAL for auto-incrementing or gen_random_uuid() for UUID-style uniqueness.

SLEEP

→ INT

Pauses execution for the specified number of seconds (can be fractional) and returns 0 on normal completion or 1 if interrupted.

Signature

SLEEP(seconds)

Parameters

Parameter	Type	Description
seconds	DECIMAL	Number of seconds to sleep; supports fractional values like 0.5

Examples

Sleep 1 second, returns 0

sql

SELECT SLEEP(1);

→-- 0

Sleep half a second

sql

SELECT SLEEP(0.5);

→-- 0

No-op sleep

sql

SELECT SLEEP(0);

→-- 0

Check return value

sql

SELECT IF(SLEEP(2) = 0, 'Done', 'Interrupted');

→-- 'Done'

NULL input returns NULL

sql

SELECT SLEEP(NULL);

→-- NULL

⚠Anti-Pattern— Using SLEEP() in production to throttle queries

SLEEP() holds connections and locks for its duration, degrading concurrency and potentially causing deadlocks under load.

✓ Instead: Use application-level rate limiting or MariaDB's built-in query governor features for throttling.

◆MariaDB Note

Returns 1 (not an error) when the sleep is interrupted by KILL QUERY; the interrupted session resumes immediately.

⇄ vs PostgreSQL: PostgreSQL uses pg_sleep(seconds) which returns void; pg_sleep_for(interval) and pg_sleep_until(timestamp) are also available.

INET_ATON

→ BIGINT UNSIGNED

Converts a dotted-decimal IPv4 address string to its numeric representation as a BIGINT UNSIGNED.

Signature

INET_ATON(expr)

Parameters

Parameter	Type	Description
expr	VARCHAR	Dotted-decimal IPv4 address string (e.g., '192.168.1.1')

Examples

Standard IPv4

sql

SELECT INET_ATON('192.168.1.1');

→-- 3232235777

Private range

sql

SELECT INET_ATON('10.0.0.1');

→-- 167772161

Max IPv4

sql

SELECT INET_ATON('255.255.255.255');

→-- 4294967295

Min IPv4

sql

SELECT INET_ATON('0.0.0.0');

→-- 0

NULL handling

sql

SELECT INET_ATON(NULL);

→-- NULL

⚠Anti-Pattern— Using INET_ATON for IPv6 addresses

INET_ATON only handles IPv4; passing an IPv6 address returns NULL silently.

✓ Instead: Use INET6_ATON() which handles both IPv4 and IPv6 and returns a VARBINARY(16).

◆MariaDB Note

Returns NULL for invalid IPv4 strings; does not support abbreviated forms like '192.168'.

⇄ vs PostgreSQL: PostgreSQL uses the inet data type and operators directly; no exact equivalent function, but host(inet) and inet_client_addr() exist.

INET_NTOA

→ VARCHAR(15)

Converts a numeric IPv4 address (BIGINT UNSIGNED) to its dotted-decimal string representation.

Signature

INET_NTOA(expr)

Parameters

Parameter	Type	Description
expr	BIGINT UNSIGNED	Numeric IPv4 address as produced by INET_ATON()

Examples

Reverse of INET_ATON

sql

SELECT INET_NTOA(3232235777);

→-- '192.168.1.1'

Private range

sql

SELECT INET_NTOA(167772161);

→-- '10.0.0.1'

Round-trip

sql

SELECT INET_NTOA(INET_ATON('10.20.30.40'));

→-- '10.20.30.40'

Zero address

sql

SELECT INET_NTOA(0);

→-- '0.0.0.0'

NULL handling

sql

SELECT INET_NTOA(NULL);

→-- NULL

⚠Anti-Pattern— Passing an IPv6 numeric value to INET_NTOA

INET_NTOA only works with 32-bit IPv4 numeric values; larger values produce incorrect or NULL output.

✓ Instead: Use INET6_NTOA() for IPv6 numeric VARBINARY values.

◆MariaDB Note

Returns NULL for out-of-range values (greater than 4294967295).

⇄ vs PostgreSQL: PostgreSQL uses host(inet_value)::text or the inet type's text representation directly.

INET6_ATON

→ VARBINARY(16)

Converts an IPv4 or IPv6 address string to its binary representation (4 bytes for IPv4, 16 bytes for IPv6).

Signature

INET6_ATON(expr)

Parameters

Parameter	Type	Description
expr	VARCHAR	IPv4 dotted-decimal or IPv6 colon-separated address string

Examples

IPv4 as 4-byte binary

sql

SELECT HEX(INET6_ATON('192.168.1.1'));

→-- 'C0A80101'

IPv6 loopback

sql

SELECT HEX(INET6_ATON('::1'));

→-- '00000000000000000000000000000001'

IPv6 address

sql

SELECT HEX(INET6_ATON('2001:db8::1'));

→-- '20010DB8000000000000000000000001'

IPv6 is always 16 bytes

sql

SELECT LENGTH(INET6_ATON('::1'));

→-- 16

NULL handling

sql

SELECT INET6_ATON(NULL);

→-- NULL

⚠Anti-Pattern— Storing INET6_ATON result as VARCHAR

The VARBINARY result of INET6_ATON contains raw binary bytes which will be corrupted or truncated if stored in a VARCHAR column.

✓ Instead: Always use VARBINARY(16) for the storage column when using INET6_ATON/INET6_NTOA.

◆MariaDB Note

Returns NULL for invalid address strings; supports both compact (::1) and full IPv6 notation.

⇄ vs PostgreSQL: PostgreSQL uses the inet or cidr data type natively; no binary conversion function is needed for storage.

INET6_NTOA

→ VARCHAR(39)

Converts a binary IPv4 (4 bytes) or IPv6 (16 bytes) address to its human-readable string representation.

Signature

INET6_NTOA(expr)

Parameters

Parameter	Type	Description
expr	VARBINARY	Binary IP address as produced by INET6_ATON() — 4 bytes for IPv4, 16 bytes for IPv6

Examples

IPv4 round-trip

sql

SELECT INET6_NTOA(INET6_ATON('192.168.1.1'));

→-- '192.168.1.1'

IPv6 loopback round-trip

sql

SELECT INET6_NTOA(INET6_ATON('::1'));

→-- '::1'

IPv6 compressed form

sql

SELECT INET6_NTOA(INET6_ATON('2001:db8::1'));

→-- '2001:db8::1'

From column

sql

SELECT INET6_NTOA(ip_bin) FROM connections;

→-- human-readable IPs

NULL handling

sql

SELECT INET6_NTOA(NULL);

→-- NULL

⚠Anti-Pattern— Comparing INET6_NTOA output as strings for range checks

String comparison of IP addresses does not produce correct numeric ordering.

✓ Instead: Compare the raw VARBINARY values from INET6_ATON() directly for range queries.

◆MariaDB Note

Returns NULL if the input is not exactly 4 or 16 bytes.

⇄ vs PostgreSQL: PostgreSQL uses host(inet_value) or the inet type's implicit text cast.

IS_IPV4

→ INT (0 or 1)

Returns 1 if the argument is a valid dotted-decimal IPv4 address string, otherwise 0.

Signature

IS_IPV4(expr)

Parameters

Parameter	Type	Description
expr	VARCHAR	String to test as an IPv4 address

Examples

Valid IPv4

sql

SELECT IS_IPV4('192.168.1.1');

→-- 1

Out-of-range octet

sql

SELECT IS_IPV4('256.0.0.1');

→-- 0

IPv6 is not IPv4

sql

SELECT IS_IPV4('::1');

→-- 0

Invalid string

sql

SELECT IS_IPV4('not-an-ip');

→-- 0

NULL handling

sql

SELECT IS_IPV4(NULL);

→-- NULL

⚠Anti-Pattern— Using IS_IPV4 to validate IPv6-mapped IPv4 addresses

IS_IPV4 returns 0 for IPv6-mapped IPv4 addresses like '::ffff:192.168.1.1'.

✓ Instead: Use IS_IPV4_MAPPED() to detect IPv6-mapped IPv4 addresses stored in IPv6 format.

⇄ vs PostgreSQL: PostgreSQL uses family(inet_value) = 4 or a regex; the inet type enforces validity at storage time.

IS_IPV4_COMPAT

→ INT (0 or 1)

Returns 1 if the given VARBINARY IPv6 address is an IPv4-compatible address (::w.x.y.z form, deprecated format), otherwise 0.

Signature

IS_IPV4_COMPAT(expr)

Parameters

Parameter	Type	Description
expr	VARBINARY(16)	Binary IPv6 address as returned by INET6_ATON()

Examples

IPv4-compatible IPv6

sql

SELECT IS_IPV4_COMPAT(INET6_ATON('::192.168.1.1'));

→-- 1

IPv4-mapped is not IPv4-compat

sql

SELECT IS_IPV4_COMPAT(INET6_ATON('::ffff:192.168.1.1'));

→-- 0

Regular IPv6

sql

SELECT IS_IPV4_COMPAT(INET6_ATON('2001:db8::1'));

→-- 0

Loopback is IPv4-compat

sql

SELECT IS_IPV4_COMPAT(INET6_ATON('::1'));

→-- 1

NULL handling

sql

SELECT IS_IPV4_COMPAT(NULL);

→-- NULL

⚠Anti-Pattern— Confusing IS_IPV4_COMPAT with IS_IPV4_MAPPED

::ffff:192.168.1.1 (IPv4-mapped) and ::192.168.1.1 (IPv4-compatible) are different formats; the functions test different bit patterns.

✓ Instead: Check both IS_IPV4_COMPAT() and IS_IPV4_MAPPED() when filtering for any IPv4-derived IPv6 address.

◆MariaDB Note

The argument must be the binary result of INET6_ATON(), not a string.

⇄ vs PostgreSQL: No direct PostgreSQL equivalent; use family() and masklen() checks on the inet type.

IS_IPV4_MAPPED

→ INT (0 or 1)

Returns 1 if the given VARBINARY IPv6 address is an IPv4-mapped address (::ffff:w.x.y.z), otherwise 0.

Signature

IS_IPV4_MAPPED(expr)

Parameters

Parameter	Type	Description
expr	VARBINARY(16)	Binary IPv6 address as returned by INET6_ATON()

Examples

IPv4-mapped address

sql

SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:192.168.1.1'));

→-- 1

IPv4-compat, not mapped

sql

SELECT IS_IPV4_MAPPED(INET6_ATON('::192.168.1.1'));

→-- 0

Pure IPv6

sql

SELECT IS_IPV4_MAPPED(INET6_ATON('2001:db8::1'));

→-- 0

Private IPv4-mapped

sql

SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.0.1'));

→-- 1

NULL handling

sql

SELECT IS_IPV4_MAPPED(NULL);

→-- NULL

⚠Anti-Pattern— Storing IPv4-mapped addresses and not normalizing

The same client may appear as '192.168.1.1' (IPv4) and '::ffff:192.168.1.1' (IPv4-mapped IPv6) depending on connection path.

✓ Instead: Normalize on insert: if IS_IPV4_MAPPED(ip), strip the mapping prefix before storage.

◆MariaDB Note

The argument must be the VARBINARY result of INET6_ATON(), not a plain string.

⇄ vs PostgreSQL: PostgreSQL: use (family(addr) = 6 AND addr << '::ffff:0.0.0.0/96'::inet) to detect IPv4-mapped.

IS_IPV6

→ INT (0 or 1)

Returns 1 if the argument is a valid IPv6 address string (colon-separated), otherwise 0.

Signature

IS_IPV6(expr)

Parameters

Parameter	Type	Description
expr	VARCHAR	String to test as an IPv6 address

Examples

IPv6 loopback

sql

SELECT IS_IPV6('::1');

→-- 1

Valid IPv6

sql

SELECT IS_IPV6('2001:db8::1');

→-- 1

IPv4 is not IPv6

sql

SELECT IS_IPV6('192.168.1.1');

→-- 0

Invalid hex group

sql

SELECT IS_IPV6('gggg::1');

→-- 0

NULL handling

sql

SELECT IS_IPV6(NULL);

→-- NULL

⚠Anti-Pattern— Using IS_IPV6 on binary data from INET6_ATON

IS_IPV6 expects a string argument; passing binary VARBINARY bytes returns 0 even for valid IPv6 binary data.

✓ Instead: Apply IS_IPV6() before calling INET6_ATON(), not after.

⇄ vs PostgreSQL: PostgreSQL validates at INSERT time using the inet type; family(addr) = 6 tests for IPv6.

FORMAT_BYTES

Available from MariaDB 10.7.0.→ VARCHAR

Converts a raw byte count to a human-readable string with an appropriate binary unit suffix (e.g., '1.00 MiB').

Signature

FORMAT_BYTES(count)

Parameters

Parameter	Type	Description
count	BIGINT UNSIGNED	Number of bytes to format

Examples

Kilobytes

sql

SELECT FORMAT_BYTES(1024);

→-- '1.00 KiB'

Megabytes

sql

SELECT FORMAT_BYTES(1048576);

→-- '1.00 MiB'

Gigabytes

sql

SELECT FORMAT_BYTES(1073741824);

→-- '1.00 GiB'

Under 1 KiB

sql

SELECT FORMAT_BYTES(512);

→-- '512 bytes'

NULL handling

sql

SELECT FORMAT_BYTES(NULL);

→-- NULL

⚠Anti-Pattern— Using FORMAT_BYTES on older MariaDB versions

FORMAT_BYTES was introduced in MariaDB 10.7; calling it on earlier versions causes an error.

✓ Instead: Check the version with SELECT VERSION() or use a CASE expression with manual calculation for compatibility.

◆MariaDB Note

Uses binary prefixes (KiB, MiB, GiB, TiB, PiB, EiB) per IEC 80000-13 standard.

⇄ vs PostgreSQL: PostgreSQL uses pg_size_pretty(bigint) for the same purpose.

SYS_GUID

Available from MariaDB 10.3 with Oracle mode.→ VARCHAR(32)

Oracle-compatibility alias for UUID() that returns a 32-character UUID without hyphens when used in Oracle SQL_MODE.

Signature

SYS_GUID()

Examples

32-char uppercase, no hyphens

sql

SELECT SYS_GUID();

→-- '6CCD780CBABA102695645B8C656024DB'

In Oracle mode

sql

SET sql_mode='ORACLE'; SELECT SYS_GUID();

→-- '6CCD780CBABA102695645B8C656024DB'

Always 32 characters

sql

SELECT LENGTH(SYS_GUID());

→-- 32

Each call is unique

sql

SELECT SYS_GUID() = SYS_GUID();

→-- 0

Never NULL

sql

SELECT SYS_GUID() IS NULL;

→-- 0

⚠Anti-Pattern— Relying on SYS_GUID outside Oracle compatibility mode

SYS_GUID() availability may depend on the active sql_mode in some MariaDB versions.

✓ Instead: Use UUID() for standard SQL and REPLACE(UUID(),'-','') if you need the hyphen-free format portably.

◆MariaDB Note

Available when MariaDB is run with Oracle SQL compatibility mode (sql_mode=ORACLE).

⇄ vs MySQL: MySQL does not have SYS_GUID(); this is a MariaDB Oracle-compatibility feature.

⇄ vs PostgreSQL: PostgreSQL has no SYS_GUID; use gen_random_uuid() or uuid_generate_v1() from uuid-ossp.

MASTER_POS_WAIT

→ BIGINT

Blocks until the replica has applied all binary log events up to the specified file and position, or until the timeout expires.

Signatures

MASTER_POS_WAIT(log_name, log_pos)

MASTER_POS_WAIT(log_name, log_pos, timeout)

MASTER_POS_WAIT(log_name, log_pos, timeout, channel_name)

Parameters

Parameter	Type	Description
log_name	VARCHAR	Binary log file name on the primary
log_pos	BIGINT	Binary log position to wait for
timeout	INT	Maximum seconds to wait; 0 or omitted means wait indefinitely
channel_name	VARCHAR	Replication channel name for multi-source replication

Examples

Wait indefinitely

sql

SELECT MASTER_POS_WAIT('mariadb-bin.000001', 500);

→-- 0 (events applied)

Timeout after 10 seconds

sql

SELECT MASTER_POS_WAIT('mariadb-bin.000001', 500, 10);

→-- -1 (timeout)

Explicit infinite wait

sql

SELECT MASTER_POS_WAIT('mariadb-bin.000001', 500, 0);

→-- 0

Conditional check

sql

SELECT IF(MASTER_POS_WAIT('mariadb-bin.000001',1000,5)>=0,'Synced','Lagging');

→-- 'Synced'

NULL handling

sql

SELECT MASTER_POS_WAIT(NULL, NULL);

→-- NULL

⚠Anti-Pattern— Calling MASTER_POS_WAIT on the primary server

MASTER_POS_WAIT is a replica-side function; calling it on a primary returns NULL immediately.

✓ Instead: Call this function only on replica servers; use SHOW MASTER STATUS to obtain the coordinates from the primary.

◆MariaDB Note

Returns NULL if not a replica or replication is not running; returns -1 on timeout; returns >= 0 (events skipped count) on success.

⇄ vs PostgreSQL: PostgreSQL uses pg_wal_replay_wait() (v16+) or application-level polling of pg_last_wal_replay_lsn().

MASTER_GTID_WAIT

→ INT

Blocks until the replica has applied all transactions up to the specified GTID position, returning 0 on success or -1 on timeout.

Signatures

MASTER_GTID_WAIT(gtid_pos)

MASTER_GTID_WAIT(gtid_pos, timeout)

Parameters

Parameter	Type	Description
gtid_pos	VARCHAR	GTID position string (e.g., '0-1-100') to wait for
timeout	DECIMAL	Maximum seconds to wait; negative or omitted means wait indefinitely

Examples

Wait for GTID position

sql

SELECT MASTER_GTID_WAIT('0-1-100');

→-- 0

With 10-second timeout

sql

SELECT MASTER_GTID_WAIT('0-1-100', 10);

→-- 0 or -1

Multiple GTID domains

sql

SELECT MASTER_GTID_WAIT('0-1-100,0-2-50', 5);

→-- 0 or -1

Conditional check

sql

SELECT IF(MASTER_GTID_WAIT('0-1-200',3)=0,'OK','Timeout');

→-- 'OK'

NULL handling

sql

SELECT MASTER_GTID_WAIT(NULL);

→-- NULL

⚠Anti-Pattern— Using MASTER_GTID_WAIT without GTID replication enabled

If gtid_mode is not enabled, MASTER_GTID_WAIT returns immediately with 0 without actually waiting for any events.

✓ Instead: Verify GTID replication is active with SHOW VARIABLES LIKE 'gtid_mode' before relying on this function.

◆MariaDB Note

MariaDB GTID format is domain_id-server_id-sequence_no, different from MySQL's UUID-based GTIDs.

⇄ vs PostgreSQL: PostgreSQL has no GTID concept; pg_wal_replay_wait() (v16+) serves a similar purpose using LSN positions.

SOURCE_POS_WAIT

Available from MariaDB 10.6.0.→ BIGINT

Alias for MASTER_POS_WAIT() introduced for naming consistency; blocks until the replica has applied events up to the specified binlog file and position.

Signatures

SOURCE_POS_WAIT(log_name, log_pos)

SOURCE_POS_WAIT(log_name, log_pos, timeout)

SOURCE_POS_WAIT(log_name, log_pos, timeout, channel_name)

Parameters

Parameter	Type	Description
log_name	VARCHAR	Binary log file name on the source/primary
log_pos	BIGINT	Binary log position to wait for
timeout	INT	Maximum seconds to wait; 0 or omitted waits indefinitely
channel_name	VARCHAR	Replication channel name for multi-source replication

Examples

Wait for position

sql

SELECT SOURCE_POS_WAIT('mariadb-bin.000001', 1024);

→-- 0

30-second timeout

sql

SELECT SOURCE_POS_WAIT('mariadb-bin.000001', 1024, 30);

→-- 0 or -1

Timeout example

sql

SELECT SOURCE_POS_WAIT('mariadb-bin.000002', 256, 5);

→-- -1 (timed out)

With conditional

sql

SELECT IF(SOURCE_POS_WAIT('mariadb-bin.000001',2048,10)>=0,'Caught up','Lagging');

→-- 'Caught up'

NULL handling

sql

SELECT SOURCE_POS_WAIT(NULL, NULL);

→-- NULL

⚠Anti-Pattern— Mixing SOURCE_POS_WAIT and MASTER_POS_WAIT in the same codebase

They are equivalent but mixing names causes confusion during code review and maintenance.

✓ Instead: Standardize on SOURCE_POS_WAIT() for new code while MASTER_POS_WAIT() remains available for backward compatibility.

◆MariaDB Note

Functionally identical to MASTER_POS_WAIT(); introduced as a terminology update.

⇄ vs MySQL: MySQL also added SOURCE_POS_WAIT as an alias in MySQL 8.0.26.

⇄ vs PostgreSQL: PostgreSQL uses pg_wal_replay_wait() (v16+) or pg_last_wal_replay_lsn() polling.

GTID_SUBSET

→ INT (0 or 1)

Returns 1 if all GTIDs in the first GTID set are also contained in the second GTID set (subset check), otherwise 0.

Signature

GTID_SUBSET(subset, set)

Parameters

Parameter	Type	Description
subset	VARCHAR	GTID set to test as the potential subset
set	VARCHAR	GTID set to test against (the superset candidate)

Examples

10 is within 100

sql

SELECT GTID_SUBSET('0-1-10', '0-1-100');

→-- 1

100 is not within 10

sql

SELECT GTID_SUBSET('0-1-100', '0-1-10');

→-- 0

Multi-domain subset

sql

SELECT GTID_SUBSET('0-1-5,0-2-3', '0-1-10,0-2-10');

→-- 1

Empty set is subset of anything

sql

SELECT GTID_SUBSET('', '0-1-100');

→-- 1

NULL handling

sql

SELECT GTID_SUBSET(NULL, '0-1-100');

→-- NULL

⚠Anti-Pattern— Using GTID_SUBSET with MySQL-format GTIDs

MariaDB GTID format (domain-server-seq) is incompatible with MySQL UUID-based GTIDs; mixing formats returns NULL or incorrect results.

✓ Instead: Ensure all servers in the topology use MariaDB-native GTID format.

◆MariaDB Note

Operates on MariaDB's domain_id-server_id-sequence GTID format.

⇄ vs MySQL: MySQL also has GTID_SUBSET() but uses UUID-based GTID sets; syntax differs.

⇄ vs PostgreSQL: No direct equivalent in PostgreSQL; LSN comparison uses pg_lsn operators like <=.

GTID_SUBTRACT

→ VARCHAR

Returns the set of GTIDs in the first set that are not present in the second set (set difference).

Signature

GTID_SUBTRACT(set1, set2)

Parameters

Parameter	Type	Description
set1	VARCHAR	GTID set to subtract from
set2	VARCHAR	GTID set to subtract (remove from set1)

Examples

GTIDs in set1 not in set2

sql

SELECT GTID_SUBTRACT('0-1-10', '0-1-5');

→-- GTIDs in set1 not in set2

Identical sets give empty result

sql

SELECT GTID_SUBTRACT('0-1-10', '0-1-10');

→-- ''

Multi-domain subtraction

sql

SELECT GTID_SUBTRACT('0-1-10,0-2-5', '0-1-10');

→-- '0-2-5'

Subtract empty = original

sql

SELECT GTID_SUBTRACT('0-1-10', '');

→-- '0-1-10'

NULL handling

sql

SELECT GTID_SUBTRACT(NULL, '0-1-5');

→-- NULL

⚠Anti-Pattern— Assuming subtraction order doesn't matter

GTID_SUBTRACT(A, B) and GTID_SUBTRACT(B, A) return completely different results; the function is not commutative.

✓ Instead: Always place the larger/primary set first and the subset to remove second.

◆MariaDB Note

Returns an empty string '' (not NULL) when the result is an empty set.

⇄ vs MySQL: MySQL also has GTID_SUBTRACT() but uses UUID-based GTID format.

⇄ vs PostgreSQL: No direct equivalent in PostgreSQL.

WSREP_LAST_SEEN_GTID

→ VARCHAR

Returns the GTID of the last write transaction seen (received from any node) by the current Galera cluster node.

Signature

WSREP_LAST_SEEN_GTID()

Examples

Last seen GTID on this node

sql

SELECT WSREP_LAST_SEEN_GTID();

→-- '1-1-12345'

Check Galera is active

sql

SELECT WSREP_LAST_SEEN_GTID() IS NOT NULL;

→-- 1 (on Galera node)

Capture and sync

sql

SET @gtid = WSREP_LAST_SEEN_GTID(); SELECT WSREP_SYNC_WAIT_UPTO_GTID(@gtid);

→-- 1

Compare seen vs written

sql

SELECT WSREP_LAST_SEEN_GTID(), WSREP_LAST_WRITTEN_GTID();

→-- Two GTID values

Non-Galera returns NULL

sql

SELECT WSREP_LAST_SEEN_GTID() IS NULL;

→-- 1 (non-Galera) or 0

⚠Anti-Pattern— Using WSREP functions on non-Galera MariaDB

WSREP_* functions return NULL or error on standard MariaDB replication setups without Galera.

✓ Instead: Check the wsrep_on variable or test for NULL before using WSREP_* functions in portable code.

◆MariaDB Note

Only meaningful on nodes running Galera Cluster (wsrep_on=ON).

⇄ vs MySQL: MySQL does not have WSREP functions; PXC (Percona XtraDB Cluster) has similar functions.

⇄ vs PostgreSQL: PostgreSQL Patroni/BDR have different consistency mechanisms; no direct equivalent function.

WSREP_LAST_WRITTEN_GTID

→ VARCHAR

Returns the GTID of the last write transaction written (originated) by the current Galera cluster node.

Signature

WSREP_LAST_WRITTEN_GTID()

Examples

GTID of last local write

sql

SELECT WSREP_LAST_WRITTEN_GTID();

→-- '1-2-9876'

After a write transaction

sql

BEGIN; UPDATE accounts SET balance=100 WHERE id=1; COMMIT; SELECT WSREP_LAST_WRITTEN_GTID();

→-- GTID after commit

Check if node is writer

sql

SELECT WSREP_LAST_WRITTEN_GTID() = WSREP_LAST_SEEN_GTID();

→-- 0 or 1

No writes on this node

sql

SELECT WSREP_LAST_WRITTEN_GTID() IS NULL;

→-- 1 (no writes yet)

Returns NULL outside Galera

sql

SELECT WSREP_LAST_WRITTEN_GTID();

→-- NULL (non-Galera)

⚠Anti-Pattern— Confusing WSREP_LAST_WRITTEN_GTID with WSREP_LAST_SEEN_GTID

WRITTEN tracks what this node wrote; SEEN tracks the latest transaction applied from any node — they differ in multi-writer topologies.

✓ Instead: For read-after-write on the same node's writes, use WSREP_LAST_WRITTEN_GTID(); for cluster-wide sync, use WSREP_LAST_SEEN_GTID().

◆MariaDB Note

Returns NULL if no write transactions have been executed on this node or if Galera is not active.

⇄ vs MySQL: MySQL does not have WSREP functions.

⇄ vs PostgreSQL: No direct equivalent in PostgreSQL.

WSREP_SYNC_WAIT_UPTO_GTID

→ INT

Blocks until the Galera node has applied all transactions up to the specified GTID, returning 1 on success or raising an error on timeout.

Signatures

WSREP_SYNC_WAIT_UPTO_GTID(gtid)

WSREP_SYNC_WAIT_UPTO_GTID(gtid, timeout)

Parameters

Parameter	Type	Description
gtid	VARCHAR	GTID position to wait for (as returned by WSREP_LAST_WRITTEN_GTID or WSREP_LAST_SEEN_GTID)
timeout	INT	Maximum seconds to wait; if omitted, waits indefinitely

Examples

Wait for GTID, indefinitely

sql

SELECT WSREP_SYNC_WAIT_UPTO_GTID('1-1-100');

→-- 1

Wait up to 10 seconds

sql

SELECT WSREP_SYNC_WAIT_UPTO_GTID('1-1-100', 10);

→-- 1 or error

Causal read pattern

sql

SET @g = WSREP_LAST_WRITTEN_GTID(); SELECT WSREP_SYNC_WAIT_UPTO_GTID(@g, 5);

→-- 1

Sync to latest seen

sql

SELECT WSREP_SYNC_WAIT_UPTO_GTID(WSREP_LAST_SEEN_GTID(), 30);

→-- 1

NULL handling

sql

SELECT WSREP_SYNC_WAIT_UPTO_GTID(NULL);

→-- NULL

⚠Anti-Pattern— Calling WSREP_SYNC_WAIT_UPTO_GTID without a timeout in production

Without a timeout, a lagging or partitioned node will block the calling thread indefinitely.

✓ Instead: Always specify a reasonable timeout and handle the error case in application code.

◆MariaDB Note

Raises ER_LOCK_WAIT_TIMEOUT error (not returns -1) when timeout is exceeded, unlike MASTER_GTID_WAIT.

⇄ vs MySQL: MySQL/PXC uses a wsrep_sync_wait session variable approach rather than a function.

⇄ vs PostgreSQL: No direct equivalent; BDR extension has similar functionality.

NAME_CONST

→ Same as value type

Returns the given value while assigning a specific column name to it in the result set; primarily used internally by stored procedures for replication.

Signature

NAME_CONST(name, value)

Parameters

Parameter	Type	Description
name	VARCHAR	Column name to assign in the result set
value	any literal	Constant literal value to return (must be a literal, not an expression or column reference)

Examples

Integer with custom name

sql

SELECT NAME_CONST('my_col', 42);

→-- Column named 'my_col' with value 42

String value

sql

SELECT NAME_CONST('greeting', 'Hello');

→-- Column 'greeting' = 'Hello'

Decimal value

sql

SELECT NAME_CONST('pi_approx', 3.14159);

→-- Column 'pi_approx' = 3.14159

Boolean value

sql

SELECT NAME_CONST('flag', TRUE);

→-- Column 'flag' = 1

NULL value

sql

SELECT NAME_CONST('empty', NULL);

→-- NULL

⚠Anti-Pattern— Using NAME_CONST with non-literal expressions

NAME_CONST requires its second argument to be a constant literal; passing a function call or column reference causes an error.

✓ Instead: Use a subquery with AS alias syntax: SELECT expr AS col_name FROM ...

◆MariaDB Note

Mainly used in CREATE PROCEDURE binlog events to preserve parameter names for replication; not intended for general query use.

⇄ vs PostgreSQL: No equivalent in PostgreSQL; use AS alias in SELECT.

CONVERT

→ Varies (depends on target type)

Converts a value to a specified data type, or converts a string's character set using the USING clause.

Signatures

CONVERT(expr, type)

CONVERT(expr USING charset_name)

Parameters

Parameter	Type	Description
expr	any	Value or expression to convert
type	type keyword	Target data type (e.g., CHAR, SIGNED, UNSIGNED, DECIMAL, DATE, DATETIME, BINARY)
charset_name	VARCHAR	Target character set name when using USING clause (e.g., utf8mb4, latin1)

Examples

String to integer

sql

SELECT CONVERT('42', SIGNED);

→-- 42

Float to unsigned int (rounds)

sql

SELECT CONVERT(3.7, UNSIGNED);

→-- 4

String to DATE

sql

SELECT CONVERT('2024-01-15', DATE);

→-- 2024-01-15

Character set conversion

sql

SELECT CONVERT('hello' USING utf8mb4);

→-- 'hello' in utf8mb4

NULL handling

sql

SELECT CONVERT(NULL, CHAR);

→-- NULL

⚠Anti-Pattern— Using CONVERT for locale-sensitive formatting

CONVERT does not apply locale-specific number or date formatting; it performs type casting only.

✓ Instead: Use FORMAT() for locale-aware number formatting or DATE_FORMAT() for date string formatting.

◆MariaDB Note

CONVERT(expr, type) and CAST(expr AS type) are equivalent for type conversion; CONVERT USING is unique to CONVERT.

⇄ vs PostgreSQL: PostgreSQL uses expr::type syntax or CAST(expr AS type); character set conversion uses convert(bytea, name, name) or client_encoding settings.

CAST

CAST to FLOAT/DOUBLE available from MariaDB 10.7.→ Varies (depends on target type)

Converts a value to the specified SQL data type using standard SQL syntax.

Signature

CAST(expr AS type)

Parameters

Parameter	Type	Description
expr	any	Value or expression to cast
type	type keyword	Target type: CHAR[(n)], BINARY[(n)], DATE, DATETIME, TIME, DECIMAL[(m[,d])], SIGNED [INTEGER], UNSIGNED [INTEGER], JSON, FLOAT, DOUBLE, INTERVAL

Examples

String to signed integer

sql

SELECT CAST('123' AS SIGNED);

→-- 123

Float to decimal with precision

sql

SELECT CAST(9.99 AS DECIMAL(5,1));

→-- 10.0

String to DATE

sql

SELECT CAST('2024-06-01' AS DATE);

→-- 2024-06-01

Integer to string

sql

SELECT CAST(255 AS CHAR);

→-- '255'

NULL handling

sql

SELECT CAST(NULL AS UNSIGNED);

→-- NULL

⚠Anti-Pattern— Casting to FLOAT expecting lossless precision

CAST(decimal_value AS FLOAT) can introduce floating-point rounding errors for monetary or exact decimal values.

✓ Instead: Use CAST(expr AS DECIMAL(m,d)) to preserve exact decimal precision.

◆MariaDB Note

MariaDB 10.4+ added CAST(expr AS INTERVAL); MariaDB 10.7+ added CAST(expr AS FLOAT) and CAST(expr AS DOUBLE).

⇄ vs PostgreSQL: PostgreSQL supports CAST(expr AS type) and the shorthand expr::type; supports more types like ARRAY, JSON, custom types.

VALUES

→ Same as the referenced column type

In an INSERT ... ON DUPLICATE KEY UPDATE statement, returns the value that would have been inserted into the specified column (the new value, not the existing one).

Signature

VALUES(col_name)

Parameters

Parameter	Type	Description
col_name	column reference	Name of the column whose prospective INSERT value you want to reference in the ON DUPLICATE KEY UPDATE clause

Examples

Increment on duplicate

sql

INSERT INTO stats (id, hits) VALUES (1, 10) ON DUPLICATE KEY UPDATE hits = hits + VALUES(hits);

→-- hits incremented by 10

Multi-column upsert

sql

INSERT INTO cache (k, v, updated_at) VALUES ('key1','val',NOW()) ON DUPLICATE KEY UPDATE v=VALUES(v), updated_at=VALUES(updated_at);

→-- Upsert pattern

Single column update

sql

INSERT INTO t (id, a, b) VALUES (1,10,20) ON DUPLICATE KEY UPDATE a=VALUES(a);

→-- a updated to 10

Counter pattern

sql

INSERT INTO counters (name, cnt) VALUES ('views', 1) ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);

→-- counter incremented

NULL handling

sql

INSERT INTO t (id, val) VALUES (1, NULL) ON DUPLICATE KEY UPDATE val = VALUES(val);

→-- val set to NULL

⚠Anti-Pattern— Using VALUES() outside of ON DUPLICATE KEY UPDATE

VALUES() is only meaningful inside the ON DUPLICATE KEY UPDATE clause; using it elsewhere returns NULL or causes an error.

✓ Instead: Use VALUES() exclusively within ON DUPLICATE KEY UPDATE, or consider INSERT ... SELECT with explicit conflict handling.

◆MariaDB Note

The VALUES() function approach works in all MariaDB versions supporting ON DUPLICATE KEY UPDATE; the new_row alias (MariaDB 10.3.3+) is preferred for readability.

⇄ vs PostgreSQL: PostgreSQL uses INSERT ... ON CONFLICT DO UPDATE SET col = EXCLUDED.col (EXCLUDED pseudo-table alias, not VALUES()).

Related MariaDB Categories

System & Info →Encryption & Security →

MariaDB Miscellaneous & Network Functions

Jump to function

What are MariaDB Miscellaneous & Network Functions?

UUID

Signature

Examples

UUID_SHORT

Signature

Examples

SLEEP

Signature

Parameters

Examples

INET_ATON

Signature

Parameters

Examples

INET_NTOA

Signature

Parameters

Examples

INET6_ATON

Signature

Parameters

Examples

INET6_NTOA

Signature

Parameters

Examples

IS_IPV4

Signature

Parameters

Examples

IS_IPV4_COMPAT

Signature

Parameters

Examples

IS_IPV4_MAPPED

Signature

Parameters

Examples

IS_IPV6

Signature

Parameters

Examples

FORMAT_BYTES

Signature

Parameters

Examples

SYS_GUID

Signature

Examples

MASTER_POS_WAIT

Signatures

Parameters

Examples

MASTER_GTID_WAIT

Signatures

Parameters

Examples

SOURCE_POS_WAIT

Signatures

Parameters

Examples

GTID_SUBSET

Signature

Parameters

Examples

GTID_SUBTRACT

Signature

Parameters

Examples

WSREP_LAST_SEEN_GTID

Signature

Examples

WSREP_LAST_WRITTEN_GTID

Signature

Examples

WSREP_SYNC_WAIT_UPTO_GTID

Signatures