$I?II$I$I$I$I$$Oܶm۶m۶m۶m۶m۶mm۷߶{o۶m۶m۶}oo۶mmo6F H HELP_DATEHelp Contents generated from the MariaDB Knowledge Base on 25 July 2023. Y HELP_VERSIONHelp Contents generated for MariaDB 11.0 from the MariaDB Knowledge Base on 25 July 2023.O2AREAA synonym for ST_AREA. URL: https://mariadb.com/kb/en/polygon-properties-area/https://mariadb.com/kb/en/polygon-properties-area/D#CENTROIDA synonym for ST_CENTROID. URL: https://mariadb.com/kb/en/centroid/https://mariadb.com/kb/en/centroid/ _:ExteriorRingA synonym for ST_ExteriorRing. URL: https://mariadb.com/kb/en/polygon-properties-exteriorring/https://mariadb.com/kb/en/polygon-properties-exteriorring/ a;InteriorRingNA synonym for ST_InteriorRingN. URL: https://mariadb.com/kb/en/polygon-properties-interiorringn/https://mariadb.com/kb/en/polygon-properties-interiorringn/g>NumInteriorRingsA synonym for ST_NumInteriorRings. URL: https://mariadb.com/kb/en/polygon-properties-numinteriorrings/https://mariadb.com/kb/en/polygon-properties-numinteriorrings/ "ST_AREASyntax ------ ST_Area(poly) Area(poly) Description ----------- Returns as a double-precision number the area of the Polygon value poly, as measured in its spatial reference system. ST_Area() and Area() are synonyms. Examples -------- SET @poly = 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))'; SELECT Area(GeomFromText(@poly)); +---------------------------+ | Area(GeomFromText(@poly)) | +---------------------------+ | 4 | +---------------------------+ URL: https://mariadb.com/kb/en/st_area/https://mariadb.com/kb/en/st_area/ %&ST_CENTROIDSyntax ------ ST_Centroid(mpoly) Centroid(mpoly) Description ----------- Returns a point reflecting the mathematical centroid (geometric center) for the MultiPolygon mpoly. The resulting point will not necessarily be on the MultiPolygon. ST_Centroid() and Centroid() are synonyms. Examples -------- SET @poly = ST_GeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0))'); SELECT ST_AsText(ST_Centroid(@poly)) AS center; +--------------+ | center | +--------------+ | POINT(10 10) | +--------------+ URL: https://mariadb.com/kb/en/st_centroid/https://mariadb.com/kb/en/st_centroid/ l*ST_ExteriorRingSyntax ------ ST_ExteriorRing(poly) ExteriorRing(poly) Description ----------- Returns the exterior ring of the Polygon value poly as a LineString. ST_ExteriorRing() and ExteriorRing() are synonyms. Examples -------- SET @poly = 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'; SELECT AsText(ExteriorRing(GeomFromText(@poly))); +-------------------------------------------+ | AsText(ExteriorRing(GeomFromText(@poly))) | +-------------------------------------------+ | LINESTRING(0 0,0 3,3 3,3 0,0 0) | +-------------------------------------------+ URL: https://mariadb.com/kb/en/st_exteriorring/https://mariadb.com/kb/en/st_exteriorring/ +ST_InteriorRingNSyntax ------ ST_InteriorRingN(poly,N) InteriorRingN(poly,N) Description ----------- Returns the N-th interior ring for the Polygon value poly as a LineString. Rings are numbered beginning with 1. ST_InteriorRingN() and InteriorRingN() are synonyms. Examples -------- SET @poly = 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'; SELECT AsText(InteriorRingN(GeomFromText(@poly),1)); +----------------------------------------------+ | AsText(InteriorRingN(GeomFromText(@poly),1)) | +----------------------------------------------+ | LINESTRING(1 1,1 2,2 2,2 1,1 1) | +----------------------------------------------+ URL: https://mariadb.com/kb/en/st_interiorringn/https://mariadb.com/kb/en/st_interiorringn/ .ST_NumInteriorRingsSyntax ------ ST_NumInteriorRings(poly) NumInteriorRings(poly) Description ----------- Returns an integer containing the number of interior rings in the Polygon value poly. Note that according the the OpenGIS standard, a POLYGON should have exactly one ExteriorRing and all other rings should lie within that ExteriorRing and thus be the InteriorRings. Practically, however, some systems, including MariaDB's, permit polygons to have several 'ExteriorRings'. In the case of there being multiple, non-overlapping exterior rings ST_NumInteriorRings() will return 1. ST_NumInteriorRings() and NumInteriorRings() are synonyms. Examples -------- SET @poly = 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'; SELECT NumInteriorRings(GeomFromText(@poly)); +---------------------------------------+ | NumInteriorRings(GeomFromText(@poly)) | +---------------------------------------+ | 1 | +---------------------------------------+ Non-overlapping 'polygon': SELECT ST_NumInteriorRings(ST_PolyFromText('POLYGON((0 0,10 0,10 10,0 10,0 0), (-1 -1,-5 -1,-5 -5,-1 -5,-1 -1))')) AS NumInteriorRings; +------------------+ | NumInteriorRings | +------------------+ | 1 | +------------------+ URL: https://mariadb.com/kb/en/st_numinteriorrings/https://mariadb.com/kb/en/st_numinteriorrings/F%AsTextA synonym for ST_AsText(). URL: https://mariadb.com/kb/en/wkt-astext/https://mariadb.com/kb/en/wkt-astext/E$AsWKTA synonym for ST_AsText(). URL: https://mariadb.com/kb/en/wkt-aswkt/https://mariadb.com/kb/en/wkt-aswkt/X/GeomCollFromTextA synonym for ST_GeomCollFromText. URL: https://mariadb.com/kb/en/wkt-geomcollfromtext/https://mariadb.com/kb/en/wkt-geomcollfromtext/^5GeometryCollectionFromTextA synonym for ST_GeomCollFromText. URL: https://mariadb.com/kb/en/geometrycollectionfromtext/https://mariadb.com/kb/en/geometrycollectionfromtext/P+GeometryFromTextA synonym for ST_GeomFromText. URL: https://mariadb.com/kb/en/geometryfromtext/https://mariadb.com/kb/en/geometryfromtext/ P+GeomFromTextA synonym for ST_GeomFromText. URL: https://mariadb.com/kb/en/wkt-geomfromtext/https://mariadb.com/kb/en/wkt-geomfromtext/ P+LineFromTextA synonym for ST_LineFromText. URL: https://mariadb.com/kb/en/wkt-linefromtext/https://mariadb.com/kb/en/wkt-linefromtext/R-LineStringFromTextA synonym for ST_LineFromText. URL: https://mariadb.com/kb/en/linestringfromtext/https://mariadb.com/kb/en/linestringfromtext/ (MLineFromTextSyntax ------ MLineFromText(wkt[,srid]) MultiLineStringFromText(wkt[,srid]) Description ----------- Constructs a MULTILINESTRING value using its WKT representation and SRID. MLineFromText() and MultiLineStringFromText() are synonyms. Examples -------- CREATE TABLE gis_multi_line (g MULTILINESTRING); SHOW FIELDS FROM gis_multi_line; INSERT INTO gis_multi_line VALUES (MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')), (MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')), (MLineFromWKB(AsWKB(MultiLineString( LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))))); URL: https://mariadb.com/kb/en/mlinefromtext/https://mariadb.com/kb/en/mlinefromtext/M)MPointFromTextSyntax ------ MPointFromText(wkt[,srid]) MultiPointFromText(wkt[,srid]) Description ----------- Constructs a MULTIPOINT value using its WKT representation and SRID. MPointFromText() and MultiPointFromText() are synonyms. Examples -------- CREATE TABLE gis_multi_point (g MULTIPOINT); SHOW FIELDS FROM gis_multi_point; INSERT INTO gis_multi_point VALUES (MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')), (MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')), (MPointFromWKB(AsWKB(MultiPoint(Point(3, 6), Point(4, 10))))); URL: https://mariadb.com/kb/en/mpointfromtext/https://mariadb.com/kb/en/mpointfromtext/U'lK|m_r [dDTm}iq ]yt}N )WKT DefinitionDescription ----------- The Well-Known Text (WKT) representation of Geometry is designed to exchange geometry data in ASCII form. Examples of the basic geometry types include: +-----------------------------------------------------------------------------+ | Geometry Types | +-----------------------------------------------------------------------------+ | POINT | +-----------------------------------------------------------------------------+ | LINESTRING | +-----------------------------------------------------------------------------+ | POLYGON | +-----------------------------------------------------------------------------+ | MULTIPOINT | +-----------------------------------------------------------------------------+ | MULTILINESTRING | +-----------------------------------------------------------------------------+ | MULTIPOLYGON | +-----------------------------------------------------------------------------+ | GEOMETRYCOLLECTION | +-----------------------------------------------------------------------------+ | GEOMETRY | +-----------------------------------------------------------------------------+ URL: https://mariadb.com/kb/en/wkt-definition/https://mariadb.com/kb/en/wkt-definition/ 5(MPolyFromTextSyntax ------ MPolyFromText(wkt[,srid]) MultiPolygonFromText(wkt[,srid]) Description ----------- Constructs a MULTIPOLYGON value using its WKT representation and SRID. MPolyFromText() and MultiPolygonFromText() are synonyms. Examples -------- CREATE TABLE gis_multi_polygon (g MULTIPOLYGON); SHOW FIELDS FROM gis_multi_polygon; INSERT INTO gis_multi_polygon VALUES (MultiPolygonFromText('MULTIPOLYGON( ((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)), ((59 18,67 18,67 13,59 13,59 18)))')), (MPolyFromText('MULTIPOLYGON( ((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)), ((59 18,67 18,67 13,59 13,59 18)))')), (MPolyFromWKB(AsWKB(MultiPolygon(Polygon( LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))))); URL: https://mariadb.com/kb/en/mpolyfromtext/https://mariadb.com/kb/en/mpolyfromtext/U2MultiLineStringFromTextA synonym for MLineFromText. URL: https://mariadb.com/kb/en/multilinestringfromtext/https://mariadb.com/kb/en/multilinestringfromtext/Q-MultiPointFromTextA synonym for MPointFromText. URL: https://mariadb.com/kb/en/multipointfromtext/https://mariadb.com/kb/en/multipointfromtext/R/MultiPolygonFromTextA synonym for MPolyFromText. URL: https://mariadb.com/kb/en/multipolygonfromtext/https://mariadb.com/kb/en/multipolygonfromtext/ R,PointFromTextA synonym for ST_PointFromText. URL: https://mariadb.com/kb/en/wkt-pointfromtext/https://mariadb.com/kb/en/wkt-pointfromtext/ P+PolyFromTextA synonym for ST_PolyFromText. URL: https://mariadb.com/kb/en/wkt-polyfromtext/https://mariadb.com/kb/en/wkt-polyfromtext/O*PolygonFromTextA synonym for ST_PolyFromText. URL: https://mariadb.com/kb/en/polygonfromtext/https://mariadb.com/kb/en/polygonfromtext/ 5$ST_AsTextSyntax ------ ST_AsText(g) AsText(g) ST_AsWKT(g) AsWKT(g) Description ----------- Converts a value in internal geometry format to its WKT representation and returns the string result. ST_AsText(), AsText(), ST_AsWKT() and AsWKT() are all synonyms. Examples -------- SET @g = 'LineString(1 1,4 4,6 6)'; SELECT ST_AsText(ST_GeomFromText(@g)); +--------------------------------+ | ST_AsText(ST_GeomFromText(@g)) | +--------------------------------+ | LINESTRING(1 1,4 4,6 6) | +--------------------------------+ URL: https://mariadb.com/kb/en/st_astext/https://mariadb.com/kb/en/st_astext/ D#ST_ASWKTA synonym for ST_ASTEXT(). URL: https://mariadb.com/kb/en/st_aswkt/https://mariadb.com/kb/en/st_aswkt/!d.ST_GeomCollFromTextSyntax ------ ST_GeomCollFromText(wkt[,srid]) ST_GeometryCollectionFromText(wkt[,srid]) GeomCollFromText(wkt[,srid]) GeometryCollectionFromText(wkt[,srid]) Description ----------- Constructs a GEOMETRYCOLLECTION value using its WKT representation and SRID. ST_GeomCollFromText(), ST_GeometryCollectionFromText(), GeomCollFromText() and GeometryCollectionFromText() are all synonyms. Example ------- CREATE TABLE gis_geometrycollection (g GEOMETRYCOLLECTION); SHOW FIELDS FROM gis_geometrycollection; INSERT INTO gis_geometrycollection VALUES (GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')), (GeometryFromWKB(AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))), (GeomFromText('GeometryCollection()')), (GeomFromText('GeometryCollection EMPTY')); URL: https://mariadb.com/kb/en/st_geomcollfromtext/https://mariadb.com/kb/en/st_geomcollfromtext/"a8ST_GeometryCollectionFromTextA synonym for ST_GeomCollFromText. URL: https://mariadb.com/kb/en/st_geometrycollectionfromtext/https://mariadb.com/kb/en/st_geometrycollectionfromtext/#S.ST_GeometryFromTextA synonym for ST_GeomFromText. URL: https://mariadb.com/kb/en/st_geometryfromtext/https://mariadb.com/kb/en/st_geometryfromtext/$*ST_GeomFromTextSyntax ------ ST_GeomFromText(wkt[,srid]) ST_GeometryFromText(wkt[,srid]) GeomFromText(wkt[,srid]) GeometryFromText(wkt[,srid]) Description ----------- Constructs a geometry value of any type using its WKT representation and SRID. GeomFromText(), GeometryFromText(), ST_GeomFromText() and ST_GeometryFromText() are all synonyms. Example ------- SET @g = ST_GEOMFROMTEXT('POLYGON((1 1,1 5,4 9,6 9,9 3,7 2,1 1))'); URL: https://mariadb.com/kb/en/st_geomfromtext/https://mariadb.com/kb/en/st_geomfromtext/%*ST_LineFromTextSyntax ------ ST_LineFromText(wkt[,srid]) ST_LineStringFromText(wkt[,srid]) LineFromText(wkt[,srid]) LineStringFromText(wkt[,srid]) Description ----------- Constructs a LINESTRING value using its WKT representation and SRID. ST_LineFromText(), ST_LineStringFromText(), ST_LineFromText() and ST_LineStringFromText() are all synonyms. Examples -------- CREATE TABLE gis_line (g LINESTRING); SHOW FIELDS FROM gis_line; INSERT INTO gis_line VALUES (LineFromText('LINESTRING(0 0,0 10,10 0)')), (LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')), (LineStringFromWKB(AsWKB(LineString(Point(10, 10), Point(40, 10))))); URL: https://mariadb.com/kb/en/st_linefromtext/https://mariadb.com/kb/en/st_linefromtext/&U0ST_LineStringFromTextA synonym for ST_LineFromText. URL: https://mariadb.com/kb/en/st_linestringfromtext/https://mariadb.com/kb/en/st_linestringfromtext/'+ST_PointFromTextSyntax ------ ST_PointFromText(wkt[,srid]) PointFromText(wkt[,srid]) Description ----------- Constructs a POINT value using its WKT representation and SRID. ST_PointFromText() and PointFromText() are synonyms. Examples -------- CREATE TABLE gis_point (g POINT); SHOW FIELDS FROM gis_point; INSERT INTO gis_point VALUES (PointFromText('POINT(10 10)')), (PointFromText('POINT(20 10)')), (PointFromText('POINT(20 20)')), (PointFromWKB(AsWKB(PointFromText('POINT(10 20)')))); URL: https://mariadb.com/kb/en/st_pointfromtext/https://mariadb.com/kb/en/st_pointfromtext/ aakQ8}Kp F "x JŒ(d*ST_PolyFromTextSyntax ------ ST_PolyFromText(wkt[,srid]) ST_PolygonFromText(wkt[,srid]) PolyFromText(wkt[,srid]) PolygonFromText(wkt[,srid]) Description ----------- Constructs a POLYGON value using its WKT representation and SRID. ST_PolyFromText(), ST_PolygonFromText(), PolyFromText() and ST_PolygonFromText() are all synonyms. Examples -------- CREATE TABLE gis_polygon (g POLYGON); INSERT INTO gis_polygon VALUES (PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')), (PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')); URL: https://mariadb.com/kb/en/st_polyfromtext/https://mariadb.com/kb/en/st_polyfromtext/)R-ST_PolygonFromTextA synonym for ST_PolyFromText. URL: https://mariadb.com/kb/en/st_polygonfromtext/https://mariadb.com/kb/en/st_polygonfromtext/*fDIVSyntax ------ DIV Description ----------- Integer division. Similar to FLOOR(), but is safe with BIGINT values. Incorrect results may occur for non-integer operands that exceed BIGINT range. If the ERROR_ON_DIVISION_BY_ZERO SQL_MODE is used, a division by zero produces an error. Otherwise, it returns NULL. The remainder of a division can be obtained using the MOD operator. Examples -------- SELECT 300 DIV 7; +-----------+ | 300 DIV 7 | +-----------+ | 42 | +-----------+ SELECT 300 DIV 0; +-----------+ | 300 DIV 0 | +-----------+ | NULL | +-----------+ URL: https://mariadb.com/kb/en/div/https://mariadb.com/kb/en/div/+ABSSyntax ------ ABS(X) Description ----------- Returns the absolute (non-negative) value of X. If X is not a number, it is converted to a numeric type. Examples -------- SELECT ABS(42); +---------+ | ABS(42) | +---------+ | 42 | +---------+ SELECT ABS(-42); +----------+ | ABS(-42) | +----------+ | 42 | +----------+ SELECT ABS(DATE '1994-01-01'); +------------------------+ | ABS(DATE '1994-01-01') | +------------------------+ | 19940101 | +------------------------+ URL: https://mariadb.com/kb/en/abs/https://mariadb.com/kb/en/abs/,ACOSSyntax ------ ACOS(X) Description ----------- Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1. Examples -------- SELECT ACOS(1); +---------+ | ACOS(1) | +---------+ | 0 | +---------+ SELECT ACOS(1.0001); +--------------+ | ACOS(1.0001) | +--------------+ | NULL | +--------------+ SELECT ACOS(0); +-----------------+ | ACOS(0) | +-----------------+ | 1.5707963267949 | +-----------------+ SELECT ACOS(0.234); +------------------+ | ACOS(0.234) | +------------------+ | 1.33460644244679 | +------------------+ URL: https://mariadb.com/kb/en/acos/https://mariadb.com/kb/en/acos/-ASINSyntax ------ ASIN(X) Description ----------- Returns the arc sine of X, that is, the value whose sine is X. Returns NULL if X is not in the range -1 to 1. Examples -------- SELECT ASIN(0.2); +--------------------+ | ASIN(0.2) | +--------------------+ | 0.2013579207903308 | +--------------------+ SELECT ASIN('foo'); +-------------+ | ASIN('foo') | +-------------+ | 0 | +-------------+ SHOW WARNINGS; +---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' | +---------+------+-----------------------------------------+ URL: https://mariadb.com/kb/en/asin/https://mariadb.com/kb/en/asin/.ATANSyntax ------ ATAN(X) Description ----------- Returns the arc tangent of X, that is, the value whose tangent is X. Examples -------- SELECT ATAN(2); +--------------------+ | ATAN(2) | +--------------------+ | 1.1071487177940904 | +--------------------+ SELECT ATAN(-2); +---------------------+ | ATAN(-2) | +---------------------+ | -1.1071487177940904 | +---------------------+ URL: https://mariadb.com/kb/en/atan/https://mariadb.com/kb/en/atan//U ATAN2Syntax ------ ATAN(Y,X), ATAN2(Y,X) Description ----------- Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result. Examples -------- SELECT ATAN(-2,2); +---------------------+ | ATAN(-2,2) | +---------------------+ | -0.7853981633974483 | +---------------------+ SELECT ATAN2(PI(),0); +--------------------+ | ATAN2(PI(),0) | +--------------------+ | 1.5707963267948966 | +--------------------+ URL: https://mariadb.com/kb/en/atan2/https://mariadb.com/kb/en/atan2/0yCEILSyntax ------ CEIL(X) Description ----------- CEIL() is a synonym for CEILING(). URL: https://mariadb.com/kb/en/ceil/https://mariadb.com/kb/en/ceil/1"CEILINGSyntax ------ CEILING(X) Description ----------- Returns the smallest integer value not less than X. Examples -------- SELECT CEILING(1.23); +---------------+ | CEILING(1.23) | +---------------+ | 2 | +---------------+ SELECT CEILING(-1.23); +----------------+ | CEILING(-1.23) | +----------------+ | -1 | +----------------+ URL: https://mariadb.com/kb/en/ceiling/https://mariadb.com/kb/en/ceiling/3COSSyntax ------ COS(X) Description ----------- Returns the cosine of X, where X is given in radians. Examples -------- SELECT COS(PI()); +-----------+ | COS(PI()) | +-----------+ | -1 | +-----------+ URL: https://mariadb.com/kb/en/cos/https://mariadb.com/kb/en/cos/4COTSyntax ------ COT(X) Description ----------- Returns the cotangent of X. Examples -------- SELECT COT(42); +--------------------+ | COT(42) | +--------------------+ | 0.4364167060752729 | +--------------------+ SELECT COT(12); +---------------------+ | COT(12) | +---------------------+ | -1.5726734063976893 | +---------------------+ SELECT COT(0); ERROR 1690 (22003): DOUBLE value is out of range in 'cot(0)' URL: https://mariadb.com/kb/en/cot/https://mariadb.com/kb/en/cot/7N"DEGREESSyntax ------ DEGREES(X) Description ----------- Returns the argument X, converted from radians to degrees. This is the converse of the RADIANS() function. Examples -------- SELECT DEGREES(PI()); +---------------+ | DEGREES(PI()) | +---------------+ | 180 | +---------------+ SELECT DEGREES(PI() / 2); +-------------------+ | DEGREES(PI() / 2) | +-------------------+ | 90 | +-------------------+ SELECT DEGREES(45); +-----------------+ | DEGREES(45) | +-----------------+ | 2578.3100780887 | +-----------------+ URL: https://mariadb.com/kb/en/degrees/https://mariadb.com/kb/en/degrees/8EXPSyntax ------ EXP(X) Description ----------- Returns the value of e (the base of natural logarithms) raised to the power of X. The inverse of this function is LOG() (using a single argument only) or LN(). If X is NULL, this function returns NULL. Examples -------- SELECT EXP(2); +------------------+ | EXP(2) | +------------------+ | 7.38905609893065 | +------------------+ SELECT EXP(-2); +--------------------+ | EXP(-2) | +--------------------+ | 0.1353352832366127 | +--------------------+ SELECT EXP(0); +--------+ | EXP(0) | +--------+ | 1 | +--------+ SELECT EXP(NULL); +-----------+ | EXP(NULL) | +-----------+ | NULL | +-----------+ URL: https://mariadb.com/kb/en/exp/https://mariadb.com/kb/en/exp/." %;  41FV 96o D2qCONVSyntax ------ CONV(N,from_base,to_base) Description ----------- Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL, or if the second or third argument are not in the allowed range. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision. Some shortcuts for this function are also available: BIN(), OCT(), HEX(), UNHEX(). Also, MariaDB allows binary literal values and hexadecimal literal values. Examples -------- SELECT CONV('a',16,2); +----------------+ | CONV('a',16,2) | +----------------+ | 1010 | +----------------+ SELECT CONV('6E',18,8); +-----------------+ | CONV('6E',18,8) | +-----------------+ | 172 | +-----------------+ SELECT CONV(-17,10,-18); +------------------+ | CONV(-17,10,-18) | +------------------+ | -H | +------------------+ SELECT CONV(12+'10'+'10'+0xa,10,10); +------------------------------+ | CONV(12+'10'+'10'+0xa,10,10) | +------------------------------+ | 42 | +------------------------------+ URL: https://mariadb.com/kb/en/conv/https://mariadb.com/kb/en/conv/5 CRC32Syntax ------ <= MariaDB 10.7 CRC32(expr) From MariaDB 10.8 CRC32([par,]expr) Description ----------- Computes a cyclic redundancy check (CRC) value and returns a 32-bit unsigned value. The result is NULL if the argument is NULL. The argument is expected to be a string and (if possible) is treated as one if it is not. Uses the ISO 3309 polynomial that used by zlib and many others. MariaDB 10.8 introduced the CRC32C() function, which uses the alternate Castagnoli polynomia. MariaDB starting with 10.8 -------------------------- Often, CRC is computed in pieces. To facilitate this, MariaDB 10.8.0 introduced an optional parameter: CRC32('MariaDB')=CRC32(CRC32('Maria'),'DB'). Examples -------- SELECT CRC32('MariaDB'); +------------------+ | CRC32('MariaDB') | +------------------+ | 4227209140 | +------------------+ SELECT CRC32('mariadb'); +------------------+ | CRC32('mariadb') | +------------------+ | 2594253378 | +------------------+ From MariaDB 10.8.0 SELECT CRC32(CRC32('Maria'),'DB'); +----------------------------+ | CRC32(CRC32('Maria'),'DB') | +----------------------------+ | 4227209140 | +----------------------------+ URL: https://mariadb.com/kb/en/crc32/https://mariadb.com/kb/en/crc32/6R!CRC32CMariaDB starting with 10.8 -------------------------- Introduced in MariaDB 10.8.0 to compute a cyclic redundancy check (CRC) value using the Castagnoli polynomial. Syntax ------ CRC32C([par,]expr) Description ----------- MariaDB has always included a native unary function CRC32() that computes the CRC-32 of a string using the ISO 3309 polynomial that used by zlib and many others. InnoDB and MyRocks use a different polynomial, which was implemented in SSE4.2 instructions that were introduced in the Intel Nehalem microarchitecture. This is commonly called CRC-32C (Castagnoli). The CRC32C function uses the Castagnoli polynomial. This allows SELECT…INTO DUMPFILE to be used for the creation of files with valid checksums, such as a logically empty InnoDB redo log file ib_logfile0 corresponding to a particular log sequence number. The optional parameter allows the checksum to be computed in pieces: CRC32C('MariaDB')=CRC32C(CRC32C('Maria'),'DB'). Examples -------- SELECT CRC32C('MariaDB'); +-------------------+ | CRC32C('MariaDB') | +-------------------+ | 809606978 | +-------------------+ SELECT CRC32C(CRC32C('Maria'),'DB'); +------------------------------+ | CRC32C(CRC32C('Maria'),'DB') | +------------------------------+ | 809606978 | +------------------------------+ URL: https://mariadb.com/kb/en/crc32c/https://mariadb.com/kb/en/crc32c/9q FLOORSyntax ------ FLOOR(X) Description ----------- Returns the largest integer value not greater than X. Examples -------- SELECT FLOOR(1.23); +-------------+ | FLOOR(1.23) | +-------------+ | 1 | +-------------+ SELECT FLOOR(-1.23); +--------------+ | FLOOR(-1.23) | +--------------+ | -2 | +--------------+ URL: https://mariadb.com/kb/en/floor/https://mariadb.com/kb/en/floor/:LNSyntax ------ LN(X) Description ----------- Returns the natural logarithm of X; that is, the base-e logarithm of X. If X is less than or equal to 0, or NULL, then NULL is returned. The inverse of this function is EXP(). Examples -------- SELECT LN(2); +-------------------+ | LN(2) | +-------------------+ | 0.693147180559945 | +-------------------+ SELECT LN(-2); +--------+ | LN(-2) | +--------+ | NULL | +--------+ URL: https://mariadb.com/kb/en/ln/https://mariadb.com/kb/en/ln/< LOG10Syntax ------ LOG10(X) Description ----------- Returns the base-10 logarithm of X. Examples -------- SELECT LOG10(2); +-------------------+ | LOG10(2) | +-------------------+ | 0.301029995663981 | +-------------------+ SELECT LOG10(100); +------------+ | LOG10(100) | +------------+ | 2 | +------------+ SELECT LOG10(-100); +-------------+ | LOG10(-100) | +-------------+ | NULL | +-------------+ URL: https://mariadb.com/kb/en/log10/https://mariadb.com/kb/en/log10/=LOG2Syntax ------ LOG2(X) Description ----------- Returns the base-2 logarithm of X. Examples -------- SELECT LOG2(4398046511104); +---------------------+ | LOG2(4398046511104) | +---------------------+ | 42 | +---------------------+ SELECT LOG2(65536); +-------------+ | LOG2(65536) | +-------------+ | 16 | +-------------+ SELECT LOG2(-100); +------------+ | LOG2(-100) | +------------+ | NULL | +------------+ URL: https://mariadb.com/kb/en/log2/https://mariadb.com/kb/en/log2/?OCTSyntax ------ OCT(N) Description ----------- Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL. Examples -------- SELECT OCT(34); +---------+ | OCT(34) | +---------+ | 42 | +---------+ SELECT OCT(12); +---------+ | OCT(12) | +---------+ | 14 | +---------+ URL: https://mariadb.com/kb/en/oct/https://mariadb.com/kb/en/oct/@PISyntax ------ PI() Description ----------- Returns the value of π (pi). The default number of decimal places displayed is six, but MariaDB uses the full double-precision value internally. Examples -------- SELECT PI(); +----------+ | PI() | +----------+ | 3.141593 | +----------+ SELECT PI()+0.0000000000000000000000; +-------------------------------+ | PI()+0.0000000000000000000000 | +-------------------------------+ | 3.1415926535897931159980 | +-------------------------------+ URL: https://mariadb.com/kb/en/pi/https://mariadb.com/kb/en/pi/A]POWSyntax ------ POW(X,Y) Description ----------- Returns the value of X raised to the power of Y. POWER() is a synonym. Examples -------- SELECT POW(2,3); +----------+ | POW(2,3) | +----------+ | 8 | +----------+ SELECT POW(2,-2); +-----------+ | POW(2,-2) | +-----------+ | 0.25 | +-----------+ URL: https://mariadb.com/kb/en/pow/https://mariadb.com/kb/en/pow/C+  G ;>LOGSyntax ------ LOG(X), LOG(B,X) Description ----------- If called with one parameter, this function returns the natural logarithm of X. If X is less than or equal to 0, then NULL is returned. If called with two parameters, it returns the logarithm of X to the base B. If B is <= 1 or X <= 0, the function returns NULL. If any argument is NULL, the function returns NULL. The inverse of this function (when called with a single argument) is the EXP() function. Examples -------- LOG(X): SELECT LOG(2); +-------------------+ | LOG(2) | +-------------------+ | 0.693147180559945 | +-------------------+ SELECT LOG(-2); +---------+ | LOG(-2) | +---------+ | NULL | +---------+ LOG(B,X) SELECT LOG(2,16); +-----------+ | LOG(2,16) | +-----------+ | 4 | +-----------+ SELECT LOG(3,27); +-----------+ | LOG(3,27) | +-----------+ | 3 | +-----------+ SELECT LOG(3,1); +----------+ | LOG(3,1) | +----------+ | 0 | +----------+ SELECT LOG(3,0); +----------+ | LOG(3,0) | +----------+ | NULL | +----------+ URL: https://mariadb.com/kb/en/log/https://mariadb.com/kb/en/log/>AMODSyntax ------ MOD(N,M), N % M, N MOD M Description ----------- Modulo operation. Returns the remainder of N divided by M. See also Modulo Operator. If the ERROR_ON_DIVISION_BY_ZERO SQL_MODE is used, any number modulus zero produces an error. Otherwise, it returns NULL. The integer part of a division can be obtained using DIV. Examples -------- SELECT 1042 % 50; +-----------+ | 1042 % 50 | +-----------+ | 42 | +-----------+ SELECT MOD(234, 10); +--------------+ | MOD(234, 10) | +--------------+ | 4 | +--------------+ SELECT 253 % 7; +---------+ | 253 % 7 | +---------+ | 1 | +---------+ SELECT MOD(29,9); +-----------+ | MOD(29,9) | +-----------+ | 2 | +-----------+ SELECT 29 MOD 9; +----------+ | 29 MOD 9 | +----------+ | 2 | +----------+ URL: https://mariadb.com/kb/en/mod/https://mariadb.com/kb/en/mod/B POWERSyntax ------ POWER(X,Y) Description ----------- This is a synonym for POW(), which returns the value of X raised to the power of Y. URL: https://mariadb.com/kb/en/power/https://mariadb.com/kb/en/power/C "RADIANSSyntax ------ RADIANS(X) Description ----------- Returns the argument X, converted from degrees to radians. Note that π radians equals 180 degrees. This is the converse of the DEGREES() function. Examples -------- SELECT RADIANS(45); +-------------------+ | RADIANS(45) | +-------------------+ | 0.785398163397448 | +-------------------+ SELECT RADIANS(90); +-----------------+ | RADIANS(90) | +-----------------+ | 1.5707963267949 | +-----------------+ SELECT RADIANS(PI()); +--------------------+ | RADIANS(PI()) | +--------------------+ | 0.0548311355616075 | +--------------------+ SELECT RADIANS(180); +------------------+ | RADIANS(180) | +------------------+ | 3.14159265358979 | +------------------+ URL: https://mariadb.com/kb/en/radians/https://mariadb.com/kb/en/radians/DRANDSyntax ------ RAND(), RAND(N) Description ----------- Returns a random DOUBLE precision floating point value v in the range 0 <= v < 1.0. If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values. In the example below, note that the sequences of values produced by RAND(3) is the same both places where it occurs. In a WHERE clause, RAND() is evaluated each time the WHERE is executed. Statements using the RAND() function are not safe for statement-based replication. Practical uses -------------- The expression to get a random integer from a given range is the following: FLOOR(min_value + RAND() * (max_value - min_value +1)) RAND() is often used to read random rows from a table, as follows: SELECT * FROM my_table ORDER BY RAND() LIMIT 10; Note, however, that this technique should never be used on a large table as it will be extremely slow. MariaDB will read all rows in the table, generate a random value for each of them, order them, and finally will apply the LIMIT clause. Examples -------- CREATE TABLE t (i INT); INSERT INTO t VALUES(1),(2),(3); SELECT i, RAND() FROM t; +------+-------------------+ | i | RAND() | +------+-------------------+ | 1 | 0.255651095188829 | | 2 | 0.833920199269355 | | 3 | 0.40264774151393 | +------+-------------------+ SELECT i, RAND(3) FROM t; +------+-------------------+ | i | RAND(3) | +------+-------------------+ | 1 | 0.90576975597606 | | 2 | 0.373079058130345 | | 3 | 0.148086053457191 | +------+-------------------+ SELECT i, RAND() FROM t; +------+-------------------+ | i | RAND() | +------+-------------------+ | 1 | 0.511478140495232 | | 2 | 0.349447508668012 | | 3 | 0.212803152588013 | +------+-------------------+ Using the same seed, the same sequence will be returned: SELECT i, RAND(3) FROM t; +------+-------------------+ | i | RAND(3) | +------+-------------------+ | 1 | 0.90576975597606 | | 2 | 0.373079058130345 | | 3 | 0.148086053457191 | +------+-------------------+ Generating a random number from 5 to 15: SELECT FLOOR(5 + (RAND() * 11)); URL: https://mariadb.com/kb/en/rand/https://mariadb.com/kb/en/rand/FSIGNSyntax ------ SIGN(X) Description ----------- Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive. Examples -------- SELECT SIGN(-32); +-----------+ | SIGN(-32) | +-----------+ | -1 | +-----------+ SELECT SIGN(0); +---------+ | SIGN(0) | +---------+ | 0 | +---------+ SELECT SIGN(234); +-----------+ | SIGN(234) | +-----------+ | 1 | +-----------+ URL: https://mariadb.com/kb/en/sign/https://mariadb.com/kb/en/sign/G[SINSyntax ------ SIN(X) Description ----------- Returns the sine of X, where X is given in radians. Examples -------- SELECT SIN(1.5707963267948966); +-------------------------+ | SIN(1.5707963267948966) | +-------------------------+ | 1 | +-------------------------+ SELECT SIN(PI()); +----------------------+ | SIN(PI()) | +----------------------+ | 1.22460635382238e-16 | +----------------------+ SELECT ROUND(SIN(PI())); +------------------+ | ROUND(SIN(PI())) | +------------------+ | 0 | +------------------+ URL: https://mariadb.com/kb/en/sin/https://mariadb.com/kb/en/sin/H+SQRTSyntax ------ SQRT(X) Description ----------- Returns the square root of X. If X is negative, NULL is returned. Examples -------- SELECT SQRT(4); +---------+ | SQRT(4) | +---------+ | 2 | +---------+ SELECT SQRT(20); +------------------+ | SQRT(20) | +------------------+ | 4.47213595499958 | +------------------+ SELECT SQRT(-16); +-----------+ | SQRT(-16) | +-----------+ | NULL | +-----------+ SELECT SQRT(1764); +------------+ | SQRT(1764) | +------------+ | 42 | +------------+ URL: https://mariadb.com/kb/en/sqrt/https://mariadb.com/kb/en/sqrt/ITANSyntax ------ TAN(X) Description ----------- Returns the tangent of X, where X is given in radians. Examples -------- SELECT TAN(0.7853981633974483); +-------------------------+ | TAN(0.7853981633974483) | +-------------------------+ | 0.9999999999999999 | +-------------------------+ SELECT TAN(PI()); +-----------------------+ | TAN(PI()) | +-----------------------+ | -1.22460635382238e-16 | +-----------------------+ SELECT TAN(PI()+1); +-----------------+ | TAN(PI()+1) | +-----------------+ | 1.5574077246549 | +-----------------+ SELECT TAN(RADIANS(PI())); +--------------------+ | TAN(RADIANS(PI())) | +--------------------+ | 0.0548861508080033 | +--------------------+ URL: https://mariadb.com/kb/en/tan/https://mariadb.com/kb/en/tan/\ @yp m)Q?E@ ROUNDSyntax ------ ROUND(X), ROUND(X,D) Description ----------- Rounds the argument X to D decimal places. D defaults to 0 if not specified. D can be negative to cause D digits left of the decimal point of the value X to become zero. The rounding algorithm depends on the data type of X: * for floating point types (FLOAT, DOUBLE) the C libraries rounding function is used, so the behavior *may* differ between operating systems * for fixed point types (DECIMAL, DEC/NUMBER/FIXED) the "round half up" rule is used, meaning that e.g. a value ending in exactly .5 is always rounded up. Examples -------- SELECT ROUND(-1.23); +--------------+ | ROUND(-1.23) | +--------------+ | -1 | +--------------+ SELECT ROUND(-1.58); +--------------+ | ROUND(-1.58) | +--------------+ | -2 | +--------------+ SELECT ROUND(1.58); +-------------+ | ROUND(1.58) | +-------------+ | 2 | +-------------+ SELECT ROUND(1.298, 1); +-----------------+ | ROUND(1.298, 1) | +-----------------+ | 1.3 | +-----------------+ SELECT ROUND(1.298, 0); +-----------------+ | ROUND(1.298, 0) | +-----------------+ | 1 | +-----------------+ SELECT ROUND(23.298, -1); +-------------------+ | ROUND(23.298, -1) | +-------------------+ | 20 | +-------------------+ URL: https://mariadb.com/kb/en/round/https://mariadb.com/kb/en/round/J#TRUNCATEThis page documents the TRUNCATE function. See TRUNCATE TABLE for the DDL statement. Syntax ------ TRUNCATE(X,D) Description ----------- Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part. D can be negative to cause D digits left of the decimal point of the value X to become zero. Examples -------- SELECT TRUNCATE(1.223,1); +-------------------+ | TRUNCATE(1.223,1) | +-------------------+ | 1.2 | +-------------------+ SELECT TRUNCATE(1.999,1); +-------------------+ | TRUNCATE(1.999,1) | +-------------------+ | 1.9 | +-------------------+ SELECT TRUNCATE(1.999,0); +-------------------+ | TRUNCATE(1.999,0) | +-------------------+ | 1 | +-------------------+ SELECT TRUNCATE(-1.999,1); +--------------------+ | TRUNCATE(-1.999,1) | +--------------------+ | -1.9 | +--------------------+ SELECT TRUNCATE(122,-2); +------------------+ | TRUNCATE(122,-2) | +------------------+ | 100 | +------------------+ SELECT TRUNCATE(10.28*100,0); +-----------------------+ | TRUNCATE(10.28*100,0) | +-----------------------+ | 1028 | +-----------------------+ URL: https://mariadb.com/kb/en/truncate/https://mariadb.com/kb/en/truncate/L+UNINSTALL PLUGINSyntax ------ UNINSTALL PLUGIN [IF EXISTS] plugin_name Description ----------- This statement removes a single installed plugin. To uninstall the whole library which contains the plugin, use UNINSTALL SONAME. You cannot uninstall a plugin if any table that uses it is open. plugin_name must be the name of some plugin that is listed in the mysql.plugin table. The server executes the plugin's deinitialization function and removes the row for the plugin from the mysql.plugin table, so that subsequent server restarts will not load and initialize the plugin. UNINSTALL PLUGIN does not remove the plugin's shared library file. To use UNINSTALL PLUGIN, you must have the DELETE privilege for the mysql.plugin table. MariaDB starting with 10.4.0 ---------------------------- IF EXISTS --------- If the IF EXISTS clause is used, MariaDB will return a note instead of an error if the plugin does not exist. See SHOW WARNINGS. Examples -------- UNINSTALL PLUGIN example; From MariaDB 10.4.0: UNINSTALL PLUGIN IF EXISTS example; Query OK, 0 rows affected (0.099 sec) UNINSTALL PLUGIN IF EXISTS example; Query OK, 0 rows affected, 1 warning (0.000 sec) SHOW WARNINGS; +-------+------+-------------------------------+ | Level | Code | Message | +-------+------+-------------------------------+ | Note | 1305 | PLUGIN example does not exist | +-------+------+-------------------------------+ URL: https://mariadb.com/kb/en/uninstall-plugin/https://mariadb.com/kb/en/uninstall-plugin/M )INSTALL SONAMESyntax ------ INSTALL SONAME 'plugin_library' Description ----------- This statement is a variant of INSTALL PLUGIN. It installs all plugins from a given plugin_library. See INSTALL PLUGIN for details. plugin_library is the name of the shared library that contains the plugin code. The file name extension (for example, libmyplugin.so or libmyplugin.dll) can be omitted (which makes the statement look the same on all architectures). The shared library must be located in the plugin directory (that is, the directory named by the plugin_dir system variable). The library must be in the plugin directory itself, not in a subdirectory. By default, plugin_dir is plugin directory under the directory named by the pkglibdir configuration variable, but it can be changed by setting the value of plugin_dir at server startup. For example, set its value in a my.cnf file: [mysqld] plugin_dir=/path/to/plugin/directory If the value of plugin_dir is a relative path name, it is taken to be relative to the MySQL base directory (the value of the basedir system variable). INSTALL SONAME adds one or more lines to the mysql.plugin table that describes the plugin. This table contains the plugin name and library file name. INSTALL SONAME causes the server to read option (my.cnf) files just as during server startup. This enables the plugin to pick up any relevant options from those files. It is possible to add plugin options to an option file even before loading a plugin (if the loose prefix is used). It is also possible to uninstall a plugin, edit my.cnf, and install the plugin again. Restarting the plugin this way enables it to the new option values without a server restart. INSTALL SONAME also loads and initializes the plugin code to make the plugin available for use. A plugin is initialized by executing its initialization function, which handles any setup that the plugin must perform before it can be used. To use INSTALL SONAME, you must have the INSERT privilege for the mysql.plugin table. At server startup, the server loads and initializes any plugin that is listed in the mysql.plugin table. This means that a plugin is installed with INSTALL SONAME only once, not every time the server starts. Plugin loading at startup does not occur if the server is started with the --skip-grant-tables option. When the server shuts down, it executes the de-initialization function for each plugin that is loaded so that the plugin has a chance to perform any final cleanup. If you need to load plugins for a single server startup when the --skip-grant-tables option is given (which tells the server not to read system tables), use the --plugin-load mysqld option. If you need to install only one plugin from a library, use the INSTALL PLUGIN statement. Examples -------- To load the XtraDB storage engine and all of its information_schema tables with one statement, use INSTALL SONAME 'ha_xtradb'; This statement can be used instead of INSTALL PLUGIN even when the library contains only one plugin: INSTALL SONAME 'ha_sequence'; URL: https://mariadb.com/kb/en/install-soname/https://mariadb.com/kb/en/install-soname/[ " sd sK)INSTALL PLUGINSyntax ------ INSTALL PLUGIN [IF NOT EXISTS] plugin_name SONAME 'plugin_library' Description ----------- This statement installs an individual plugin from the specified library. To install the whole library (which could be required), use INSTALL SONAME. See also Installing a Plugin. plugin_name is the name of the plugin as defined in the plugin declaration structure contained in the library file. Plugin names are not case sensitive. For maximal compatibility, plugin names should be limited to ASCII letters, digits, and underscore, because they are used in C source files, shell command lines, M4 and Bourne shell scripts, and SQL environments. plugin_library is the name of the shared library that contains the plugin code. The file name extension can be omitted (which makes the statement look the same on all architectures). The shared library must be located in the plugin directory (that is, the directory named by the plugin_dir system variable). The library must be in the plugin directory itself, not in a subdirectory. By default, plugin_dir is plugin directory under the directory named by the pkglibdir configuration variable, but it can be changed by setting the value of plugin_dir at server startup. For example, set its value in a my.cnf file: [mysqld] plugin_dir=/path/to/plugin/directory If the value of plugin_dir is a relative path name, it is taken to be relative to the MySQL base directory (the value of the basedir system variable). INSTALL PLUGIN adds a line to the mysql.plugin table that describes the plugin. This table contains the plugin name and library file name. INSTALL PLUGIN causes the server to read option (my.cnf) files just as during server startup. This enables the plugin to pick up any relevant options from those files. It is possible to add plugin options to an option file even before loading a plugin (if the loose prefix is used). It is also possible to uninstall a plugin, edit my.cnf, and install the plugin again. Restarting the plugin this way enables it to the new option values without a server restart. INSTALL PLUGIN also loads and initializes the plugin code to make the plugin available for use. A plugin is initialized by executing its initialization function, which handles any setup that the plugin must perform before it can be used. To use INSTALL PLUGIN, you must have the INSERT privilege for the mysql.plugin table. At server startup, the server loads and initializes any plugin that is listed in the mysql.plugin table. This means that a plugin is installed with INSTALL PLUGIN only once, not every time the server starts. Plugin loading at startup does not occur if the server is started with the --skip-grant-tables option. When the server shuts down, it executes the de-initialization function for each plugin that is loaded so that the plugin has a chance to perform any final cleanup. If you need to load plugins for a single server startup when the --skip-grant-tables option is given (which tells the server not to read system tables), use the --plugin-load mysqld option. MariaDB starting with 10.4.0 ---------------------------- IF NOT EXISTS ------------- When the IF NOT EXISTS clause is used, MariaDB will return a note instead of an error if the specified plugin already exists. See SHOW WARNINGS. Examples -------- INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so'; The extension can also be omitted: INSTALL PLUGIN innodb SONAME 'ha_xtradb'; From MariaDB 10.4.0: INSTALL PLUGIN IF NOT EXISTS example SONAME 'ha_example'; Query OK, 0 rows affected (0.104 sec) INSTALL PLUGIN IF NOT EXISTS example SONAME 'ha_example'; Query OK, 0 rows affected, 1 warning (0.000 sec) SHOW WARNINGS; +-------+------+------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------+ | Note | 1968 | Plugin 'example' already installed | +-------+------+------------------------------------+ URL: https://mariadb.com/kb/en/install-plugin/https://mariadb.com/kb/en/install-plugin/N+UNINSTALL SONAMESyntax ------ UNINSTALL SONAME [IF EXISTS] 'plugin_library' Description ----------- This statement is a variant of UNINSTALL PLUGIN statement, that removes all plugins belonging to a specified plugin_library. See UNINSTALL PLUGIN for details. plugin_library is the name of the shared library that contains the plugin code. The file name extension (for example, libmyplugin.so or libmyplugin.dll) can be omitted (which makes the statement look the same on all architectures). To use UNINSTALL SONAME, you must have the DELETE privilege for the mysql.plugin table. MariaDB starting with 10.4.0 ---------------------------- IF EXISTS --------- If the IF EXISTS clause is used, MariaDB will return a note instead of an error if the plugin library does not exist. See SHOW WARNINGS. Examples -------- To uninstall the XtraDB plugin and all of its information_schema tables with one statement, use UNINSTALL SONAME 'ha_xtradb'; From MariaDB 10.4.0: UNINSTALL SONAME IF EXISTS 'ha_example'; Query OK, 0 rows affected (0.099 sec) UNINSTALL SONAME IF EXISTS 'ha_example'; Query OK, 0 rows affected, 1 warning (0.000 sec) SHOW WARNINGS; +-------+------+-------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------+ | Note | 1305 | SONAME ha_example.so does not exist | +-------+------+-------------------------------------+ URL: https://mariadb.com/kb/en/uninstall-soname/https://mariadb.com/kb/en/uninstall-soname/@ O>*Plugin OverviewP)MBR DefinitionDescription ----------- The MBR (Minimum Bounding Rectangle), or Envelope is the bounding geometry, formed by the minimum and maximum (X,Y) coordinates: Examples -------- ((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY)) URL: https://mariadb.com/kb/en/mbr-definition/https://mariadb.com/kb/en/mbr-definition/Q &MBRContainsSyntax ------ MBRContains(g1,g2) Description ----------- Returns 1 or 0 to indicate whether the Minimum Bounding Rectangle of g1 contains the Minimum Bounding Rectangle of g2. This tests the opposite relationship as MBRWithin(). Examples -------- SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); SET @g2 = GeomFromText('Point(1 1)'); SELECT MBRContains(@g1,@g2), MBRContains(@g2,@g1); +----------------------+----------------------+ | MBRContains(@g1,@g2) | MBRContains(@g2,@g1) | +----------------------+----------------------+ | 1 | 0 | +----------------------+----------------------+ URL: https://mariadb.com/kb/en/mbrcontains/https://mariadb.com/kb/en/mbrcontains/S#MBREqualSyntax ------ MBREqual(g1,g2) Description ----------- Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 are the same. Examples -------- SET @g1=GEOMFROMTEXT('LINESTRING(0 0, 1 2)'); SET @g2=GEOMFROMTEXT('POLYGON((0 0, 0 2, 1 2, 1 0, 0 0))'); SELECT MbrEqual(@g1,@g2); +-------------------+ | MbrEqual(@g1,@g2) | +-------------------+ | 1 | +-------------------+ SET @g1=GEOMFROMTEXT('LINESTRING(0 0, 1 3)'); SET @g2=GEOMFROMTEXT('POLYGON((0 0, 0 2, 1 4, 1 0, 0 0))'); SELECT MbrEqual(@g1,@g2); +-------------------+ | MbrEqual(@g1,@g2) | +-------------------+ | 0 | +-------------------+ URL: https://mariadb.com/kb/en/mbrequal/https://mariadb.com/kb/en/mbrequal/l$\, mʿPlugins are server components that enhance MariaDB in some way. These can be anything from new storage engines, plugins for enhancing full-text parsing, or even small enhancements, such as a plugin to get a timestamp as an integer. Querying Plugin Information --------------------------- There are a number of ways to see which plugins are currently active. A server almost always has a large number of active plugins, because the server contains a large number of built-in plugins, which are active by default and cannot be uninstalled. Querying Plugin Information with SHOW PLUGINS --------------------------------------------- The SHOW PLUGINS statement can be used to query information about all active plugins. For example: SHOW PLUGINS\G; ********************** 1. row ********************** Name: binlog Status: ACTIVE Type: STORAGE ENGINE Library: NULL License: GPL ********************** 2. row ********************** Name: mysql_native_password Status: ACTIVE Type: AUTHENTICATION Library: NULL License: GPL ********************** 3. row ********************** Name: mysql_old_password Status: ACTIVE Type: AUTHENTICATION Library: NULL License: GPL ... If a plugin's Library column has a NULL value, then the plugin is built-in, and it cannot be uninstalled. Querying Plugin Information with information_schema.PLUGINS ----------------------------------------------------------- The information_schema.PLUGINS table can be queried to get more detailed information about plugins. For example: SELECT * FROM information_schema.PLUGINS\G ... *************************** 6. row *************************** PLUGIN_NAME: CSV PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: STORAGE ENGINE PLUGIN_TYPE_VERSION: 100003.0 PLUGIN_LIBRARY: NULL PLUGIN_LIBRARY_VERSION: NULL PLUGIN_AUTHOR: Brian Aker, MySQL AB PLUGIN_DESCRIPTION: CSV storage engine PLUGIN_LICENSE: GPL LOAD_OPTION: FORCE PLUGIN_MATURITY: Stable PLUGIN_AUTH_VERSION: 1.0 *************************** 7. row *************************** PLUGIN_NAME: MEMORY PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: STORAGE ENGINE PLUGIN_TYPE_VERSION: 100003.0 PLUGIN_LIBRARY: NULL PLUGIN_LIBRARY_VERSION: NULL PLUGIN_AUTHOR: MySQL AB PLUGIN_DESCRIPTION: Hash based, stored in memory, useful for temporary tables PLUGIN_LICENSE: GPL LOAD_OPTION: FORCE PLUGIN_MATURITY: Stable PLUGIN_AUTH_VERSION: 1.0 ... If a plugin's PLUGIN_LIBRARY column has the NULL value, then the plugin is built-in, and it cannot be uninstalled. Querying Plugin Information with mysql.plugin --------------------------------------------- The mysql.plugin table can be queried to get information about installed plugins. This table only contains information about plugins that have been installed via the following methods: * The INSTALL SONAME statement. * The INSTALL PLUGIN statement. * The mariadb-plugin utility. This table does not contain information about: * Built-in plugins. * Plugins loaded with the --plugin-load-add option. * Plugins loaded with the --plugin-load option. This table only contains enough information to reload the plugin when the server is restarted, which means it only contains the plugin name and the plugin library. For example: SELECT * FROM mysql.plugin; +------+------------+ | name | dl | +------+------------+ | PBXT | libpbxt.so | +------+------------+ Installing a Plugin ------------------- There are three primary ways to install a plugin: * A plugin can be installed dynamically with an SQL statement. * A plugin can be installed with a mariadbd option, but it requires a server restart. * A plugin can be installed with the mariadb-plugin utility, while the server is completely offline. When you are installing a plugin, you also have to ensure that: * The server's plugin directory is properly configured, and the plugin's library is in the plugin directory. * The server's minimum plugin maturity is properly configured, and the plugin is mature enough to be installed. Installing a Plugin Dynamically ------------------------------- A plugin can be installed dynamically by executing either the INSTALL SONAME or the INSTALL PLUGIN statement. If a plugin is installed with one of these statements, then a record will be added to the mysql.plugins table for the plugin. This means that the plugin will automatically be loaded every time the server restarts, unless specifically uninstalled or deactivated. Installing a Plugin with INSTALL SONAME --------------------------------------- You can install a plugin dynamically by executing the INSTALL SONAME statement. INSTALL SONAME installs all plugins from the given plugin library. This could be required for some plugin libraries. For example, to install all plugins in the server_audit plugin library (which is currently only the server_audit audit plugin), you could execute the following: INSTALL SONAME 'server_audit'; Installing a Plugin with INSTALL PLUGIN --------------------------------------- You can install a plugin dynamically by executing the INSTALL PLUGIN statement. INSTALL PLUGIN installs a single plugin from the given plugin library. For example, to install the server_audit audit plugin from the server_audit plugin library, you could execute the following: INSTALL PLUGIN server_audit SONAME 'server_audit'; Installing a Plugin with Plugin Load Options -------------------------------------------- A plugin can be installed with a mariadbd option by providing either the --plugin-load-add or the --plugin-load option. If a plugin is installed with one of these options, then a record will not be added to the mysql.plugins table for the plugin. This means that if the server is restarted without the same option set, then the plugin will not automatically be loaded. Installing a Plugin with --plugin-load-add ------------------------------------------ You can install a plugin with the --plugin-load-add option by specifying the option as a command-line argument to mariadbd or by specifying the option in a relevant server option group in an option file. The --plugin-load-add option uses the following format: * Plugins can be specified in the format name=library, where name is the plugin name and library is the plugin library. This format installs a single plugin from the given plugin library. * Plugins can also be specified in the format library, where library is the plugin library. This format installs all plugins from the given plugin library. * Multiple plugins can be specified by separating them with semicolons. For example, to install all plugins in the server_audit plugin library (which is currently only the server_audit audit plugin) and also the ed25519 authentication plugin from the auth_ed25519 plugin library, you could set the option to the following values on the command-line: $ mariadbd --user=mysql --plugin-load-add='server_audit' --plugin-load-add='ed25519=auth_ed25519' You could also set the option to the same values in an option file: [mariadb] ... plugin_load_add = server_audit plugin_load_add = ed25519=auth_ed25519 Special care must be taken when specifying both the --plugin-load option and the --plugin-load-add option together. The --plugin-load option resets the plugin load list, and this can cause unexpected problems if you are not aware. The --plugin-load-add option does not reset the plugin load list, so it is much safer to use. See Specifying Multiple Plugin Load Options for more information. Installing a Plugin with --plugin-load -------------------------------------- You can install a plugin with the --plugin-load option by specifying the option as a command-line argument to mariadbd or by specifying the option in a relevant server option group in an option file. The --plugin-load option uses the following format: * Plugins can be specified in the format name=library, where name is the plugin name and library is the plugin library. This format installs a single plugin from the given plugin library. * Plugins can also be specified in the format library, where library is the plugin library. This for Tmat installs all plugins from the given plugin library. * Multiple plugins can be specified by separating them with semicolons. For example, to install all plugins in the server_audit plugin library (which is currently only the server_audit audit plugin) and also the ed25519 authentication plugin from the auth_ed25519 plugin library, you could set the option to the following values on the command-line: $ mariadbd --user=mysql --plugin-load='server_audit;ed25519=auth_ed25519' You could also set the option to the same values in an option file: [mariadb] ... plugin_load = server_audit;ed25519=auth_ed25519 Special care must be taken when specifying the --plugin-load option multiple times, or when specifying both the --plugin-load option and the --plugin-load-add option together. The --plugin-load option resets the plugin load list, and this can cause unexpected problems if you are not aware. The --plugin-load-add option does not reset the plugin load list, so it is much safer to use. See Specifying Multiple Plugin Load Options for more information. Specifying Multiple Plugin Load Options --------------------------------------- Special care must be taken when specifying the --plugin-load option multiple times, or when specifying both the --plugin-load option and the --plugin-load-add option. The --plugin-load option resets the plugin load list, and this can cause unexpected problems if you are not aware. The --plugin-load-add option does not reset the plugin load list, so it is much safer to use. This can have the following consequences: * If the --plugin-load option is specified multiple times, then only the last instance will have any effect. For example, in the following case, the first instance of the option is reset: [mariadb] ... plugin_load = server_audit plugin_load = ed25519=auth_ed25519 * If the --plugin-load option is specified after the --plugin-load-add option, then it will also reset the changes made by that option. For example, in the following case, the --plugin-load-add option does not do anything, because the subsequent --plugin-load option resets the plugin load list: [mariadb] ... plugin_load_add = server_audit plugin_load = ed25519=auth_ed25519 * In contrast, if the --plugin-load option is specified before the --plugin-load-add option, then it will work fine, because the --plugin-load-add option does not reset the plugin load list. For example, in the following case, both plugins are properly loaded: [mariadb] ... plugin_load = server_audit plugin_load_add = ed25519=auth_ed25519 Installing a Plugin with mariadb-plugin --------------------------------------- A plugin can be installed with the mariadb-plugin utility if the server is completely offline. The syntax is: mariadb-plugin [options] ENABLE|DISABLE For example, to install the server_audit audit plugin, you could execute the following: mariadb-plugin server_audit ENABLE If a plugin is installed with this utility, then a record will be added to the mysql.plugins table for the plugin. This means that the plugin will automatically be loaded every time the server restarts, unless specifically uninstalled or deactivated. Configuring the Plugin Directory -------------------------------- When a plugin is being installed, the server looks for the plugin's library in the server's plugin directory. This directory is configured by the plugin_dir system variable. This can be specified as a command-line argument to mariadbd or it can be specified in a relevant server option group in an option file. For example: [mariadb] ... plugin_dir = /usr/lib64/mysql/plugin Configuring the Minimum Plugin Maturity --------------------------------------- When a plugin is being installed, the server compares the plugin's maturity level against the server's minimum allowed plugin maturity. This can help prevent users from using unstable plugins on production servers. This minimum plugin maturity is configured by the plugin_maturity system variable. This can be specified as a command-line argument to mariadbd or it can be specified in a relevant server option group in an option file. For example: [mariadb] ... plugin_maturity = stable Configuring Plugin Activation at Server Startup ----------------------------------------------- A plugin will be loaded by default when the server starts if: * The plugin was installed with the INSTALL SONAME statement. * The plugin was installed with the INSTALL PLUGIN statement. * The plugin was installed with the mariadb-plugin utility. * The server is configured to load the plugin with the --plugin-load-add option. * The server is configured to load the plugin with the --plugin-load option. This behavior can be changed with special options that take the form --plugin-name. For example, for the server_audit audit plugin, the special option is called --server-audit. The possible values for these special options are: +---------------------------------------+------------------------------------+ | Option Value | Description | +---------------------------------------+------------------------------------+ | OFF | Disables the plugin without | | | removing it from the | | | mysql.plugins table. | +---------------------------------------+------------------------------------+ | ON | Enables the plugin. If the plugin | | | cannot be initialized, then the | | | server will still continue | | | starting up, but the plugin will | | | be disabled. | +---------------------------------------+------------------------------------+ | FORCE | Enables the plugin. If the plugin | | | cannot be initialized, then the | | | server will fail to start with an | | | error. | +---------------------------------------+------------------------------------+ | FORCE_PLUS_PERMANENT | Enables the plugin. If the plugin | | | cannot be initialized, then the | | | server will fail to start with an | | | error. In addition, the plugin | | | cannot be uninstalled with | | | UNINSTALL SONAME or UNINSTALL | | | PLUGIN while the server is | | | running. | +---------------------------------------+------------------------------------+ A plugin's status can be found by looking at the PLUGIN_STATUS column of the information_schema.PLUGINS table. Uninstalling Plugins -------------------- Plugins that are found in the mysql.plugin table, that is those that were installed with INSTALL SONAME, INSTALL PLUGIN or mariadb-plugin can be uninstalled in one of two ways: * The UNINSTALL SONAME or the UNINSTALL PLUGIN statement while the server is running * With mariadb-plugin while the server is offline. Plugins that were enabled as a --plugin-load option do not need to be uninstalled. If --plugin-load is omitted the next time the server starts, or the plugin is not listed as one of the --plugin-load entries, the plugin will not be loaded. UNINSTALL PLUGIN uninstalls a single installed plugin, while UNINSTALL SONAME uninstalls all plugins belonging to a given library. URL: https://mariadb.com/kb/en/plugin-overview/k https://mariadb.com/kb/en/plugin-overview/builds include a built-in plugin called wsrep. Prior to MariaDB 10.4.3, this plugin was internally considered an XA-capable storage engine. Consequently, these MariaDB Galera Cluster builds have multiple XA-capable storage engines by default, even if the only "real" storage engine that supports external XA transactions enabled on these builds by default is InnoDB. Therefore, when using one these builds MariaDB would be forced to use a transaction coordinator log by default, which could have performance implications. See Transaction Coordinator Log Overview: MariaDB Galera Cluster for more information. URL: https://mariadb.com/kb/en/xa-transactions/https://mariadb.com/kb/en/create-user/https://mariadb.com/kb/en/alter-user/cified via the | | | string issuer. This option implies REQUIRE | | | X509. This option can be combined with the | | | SUBJECT, and CIPHER options in any order. | +---------------------------+------------------------------------------------+ | REQUIRE SUBJECT 'subject' | The account must use TLS and must have a | | | valid X509 certificate. Also, the | | | certificate's Subject must be the one | | | specified via the string subject. This option | | | implies REQUIRE X509. This option can be | | | combined with the ISSUER, and CIPHER options | | | in any order. | +---------------------------+------------------------------------------------+ | REQUIRE CIPHER 'cipher' | The account must use TLS, but no valid X509 | | | certificate is required. Also, the encryption | | | used for the connection must use a specific | | | cipher method specified in the string cipher. | | | This option implies REQUIRE SSL. This option | | | can be combined with the ISSUER, and SUBJECT | | | options in any order. | +---------------------------+------------------------------------------------+ The REQUIRE keyword must be used only once for all specified options, and the AND keyword can be used to separate individual options, but it is not required. For example, you can create a user account that requires these TLS options with the following: GRANT USAGE ON *.* TO 'alice'@'%' REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland' AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/emailAddress=p.parker@marvel.com' AND CIPHER 'SHA-DES-CBC3-EDH-RSA'; If any of these options are set for a specific user account, then any client who tries to connect with that user account will have to be configured to connect with TLS. See Securing Connections for Client and Server for information on how to enable TLS on the client and server. Roles ----- Syntax ------ GRANT role TO grantee [, grantee ... ] [ WITH ADMIN OPTION ] grantee: rolename username [authentication_option] The GRANT statement is also used to grant the use of a role to one or more users or other roles. In order to be able to grant a role, the grantor doing so must have permission to do so (see WITH ADMIN in the CREATE ROLE article). Specifying the WITH ADMIN OPTION permits the grantee to in turn grant the role to another. For example, the following commands show how to grant the same role to a couple different users. GRANT journalist TO hulda; GRANT journalist TO berengar WITH ADMIN OPTION; If a user has been granted a role, they do not automatically obtain all permissions associated with that role. These permissions are only in use when the user activates the role with the SET ROLE statement. TO PUBLIC --------- MariaDB starting with 10.11 --------------------------- Syntax ------ GRANT ON . TO PUBLIC; REVOKE ON . FROM PUBLIC; GRANT ... TO PUBLIC grants privileges to all users with access to the server. The privileges also apply to users created after the privileges are granted. This can be useful when one only wants to state once that all users need to have a certain set of privileges. When running SHOW GRANTS, a user will also see all privileges inherited from PUBLIC. SHOW GRANTS FOR PUBLIC will only show TO PUBLIC grants. Grant Examples -------------- Granting Root-like Privileges ----------------------------- You can create a user that has privileges similar to the default root accounts by executing the following: CREATE USER 'alexander'@'localhost'; GRANT ALL PRIVILEGES ON *.* to 'alexander'@'localhost' WITH GRANT OPTION; URL: https://mariadb.com/kb/en/grant/ion file and if mariadb-install-db is executed, then mariadb-install-db will read this option from the option file, and it will automatically set this option. Altering the User Account to Revert to the Previous Authentication Method ------------------------------------------------------------------------- If you have already installed MariaDB, and if the root@localhost user account is already using unix_socket authentication, then you can revert to the old mysql_native_password authentication method for the user account by executing the following: ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("verysecret") URL: https://mariadb.com/kb/en/authentication-from-mariadb-104/https://mariadb.com/kb/en/inet6/https://mariadb.com/kb/en/timestamp/are marked as closed. * Check that all tables supports FOR EXPORT * No changes to these tables allowed until UNLOCK TABLES This is basically the same behavior as in old MariaDB version if one first lock the tables, then do FLUSH TABLES. The tables will be copyable until UNLOCK TABLES. FLUSH SSL --------- MariaDB starting with 10.4 -------------------------- The FLUSH SSL command was first added in MariaDB 10.4. In MariaDB 10.4 and later, the FLUSH SSL command can be used to dynamically reinitialize the server's TLS context. This is most useful if you need to replace a certificate that is about to expire without restarting the server. This operation is performed by reloading the files defined by the following TLS system variables: * ssl_cert * ssl_key * ssl_ca * ssl_capath * ssl_crl * ssl_crlpath These TLS system variables are not dynamic, so their values can not be changed without restarting the server. If you want to dynamically reinitialize the server's TLS context, then you need to change the certificate and key files at the relevant paths defined by these TLS system variables, without actually changing the values of the variables. See MDEV-19341 for more information. Reducing Memory Usage --------------------- To flush some of the global caches that take up memory, you could execute the following command: FLUSH LOCAL HOSTS, QUERY CACHE, TABLE_STATISTICS, INDEX_STATISTICS, USER_STATISTICS; URL: https://mariadb.com/kb/en/flush/^$> ub%&6 *YR Y&MBRDisjointSyntax ------ MBRDisjoint(g1,g2) Description ----------- Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 are disjoint. Two geometries are disjoint if they do not intersect, that is touch or overlap. Examples -------- SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); SET @g2 = GeomFromText('Polygon((4 4,4 7,7 7,7 4,4 4))'); SELECTmbrdisjoint(@g1,@g2); +----------------------+ | mbrdisjoint(@g1,@g2) | +----------------------+ | 1 | +----------------------+ SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))'); SELECT mbrdisjoint(@g1,@g2); +----------------------+ | mbrdisjoint(@g1,@g2) | +----------------------+ | 0 | +----------------------+ URL: https://mariadb.com/kb/en/mbrdisjoint/https://mariadb.com/kb/en/mbrdisjoint/T #(MBRIntersectsSyntax ------ MBRIntersects(g1,g2) Description ----------- Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 intersect. Examples -------- SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))'); SELECT mbrintersects(@g1,@g2); +------------------------+ | mbrintersects(@g1,@g2) | +------------------------+ | 1 | +------------------------+ SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); SET @g2 = GeomFromText('Polygon((4 4,4 7,7 7,7 4,4 4))'); SELECT mbrintersects(@g1,@g2); +------------------------+ | mbrintersects(@g1,@g2) | +------------------------+ | 0 | +------------------------+ URL: https://mariadb.com/kb/en/mbrintersects/https://mariadb.com/kb/en/mbrintersects/U &MBROverlapsSyntax ------ MBROverlaps(g1,g2) Description ----------- Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 overlap. The term spatially overlaps is used if two geometries intersect and their intersection results in a geometry of the same dimension but not equal to either of the given geometries. Examples -------- SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); SET @g2 = GeomFromText('Polygon((4 4,4 7,7 7,7 4,4 4))'); SELECT mbroverlaps(@g1,@g2); +----------------------+ | mbroverlaps(@g1,@g2) | +----------------------+ | 0 | +----------------------+ SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))'); SELECT mbroverlaps(@g1,@g2); +----------------------+ | mbroverlaps(@g1,@g2) | +----------------------+ | 0 | +----------------------+ SET @g1 = GeomFromText('Polygon((0 0,0 4,4 4,4 0,0 0))'); SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))'); SELECT mbroverlaps(@g1,@g2); +----------------------+ | mbroverlaps(@g1,@g2) | +----------------------+ | 1 | +----------------------+ URL: https://mariadb.com/kb/en/mbroverlaps/https://mariadb.com/kb/en/mbroverlaps/V %MBRTouchesSyntax ------ MBRTouches(g1,g2) Description ----------- Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 touch. Two geometries spatially touch if the interiors of the geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interior of the other. Examples -------- SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); SET @g2 = GeomFromText('Polygon((4 4,4 7,7 7,7 4,4 4))'); SELECT mbrtouches(@g1,@g2); +---------------------+ | mbrtouches(@g1,@g2) | +---------------------+ | 0 | +---------------------+ SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))'); SELECT mbrtouches(@g1,@g2); +---------------------+ | mbrtouches(@g1,@g2) | +---------------------+ | 1 | +---------------------+ SET @g1 = GeomFromText('Polygon((0 0,0 4,4 4,4 0,0 0))'); SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))'); SELECT mbrtouches(@g1,@g2); +---------------------+ | mbrtouches(@g1,@g2) | +---------------------+ | 0 | +---------------------+ URL: https://mariadb.com/kb/en/mbrtouches/https://mariadb.com/kb/en/mbrtouches/W $MBRWithinSyntax ------ MBRWithin(g1,g2) Description ----------- Returns 1 or 0 to indicate whether the Minimum Bounding Rectangle of g1 is within the Minimum Bounding Rectangle of g2. This tests the opposite relationship as MBRContains(). Examples -------- SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); SET @g2 = GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))'); SELECT MBRWithin(@g1,@g2), MBRWithin(@g2,@g1); +--------------------+--------------------+ | MBRWithin(@g1,@g2) | MBRWithin(@g2,@g1) | +--------------------+--------------------+ | 1 | 0 | +--------------------+--------------------+ URL: https://mariadb.com/kb/en/mbrwithin/https://mariadb.com/kb/en/mbrwithin/[!NULLIFSyntax ------ NULLIF(expr1,expr2) Description ----------- Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END. Examples -------- SELECT NULLIF(1,1); +-------------+ | NULLIF(1,1) | +-------------+ | NULL | +-------------+ SELECT NULLIF(1,2); +-------------+ | NULLIF(1,2) | +-------------+ | 1 | +-------------+ URL: https://mariadb.com/kb/en/nullif/https://mariadb.com/kb/en/nullif/\NVLMariaDB starting with 10.3 -------------------------- From MariaDB 10.3, NVL is a synonym for IFNULL. URL: https://mariadb.com/kb/en/nvl/https://mariadb.com/kb/en/nvl/]NVL2MariaDB starting with 10.3 -------------------------- The NLV2 function was introduced in MariaDB 10.3.0. Syntax ------ NVL2(expr1,expr2,expr3) Description ----------- The NVL2 function returns a value based on whether a specified expression is NULL or not. If expr1 is not NULL, then NVL2 returns expr2. If expr1 is NULL, then NVL2 returns expr3. Examples -------- SELECT NVL2(NULL,1,2); +----------------+ | NVL2(NULL,1,2) | +----------------+ | 2 | +----------------+ SELECT NVL2('x',1,2); +---------------+ | NVL2('x',1,2) | +---------------+ | 1 | +---------------+ URL: https://mariadb.com/kb/en/nvl2/https://mariadb.com/kb/en/nvl2/f =5UNLOCK TABLESSyntax ------ UNLOCK TABLES Description ----------- UNLOCK TABLES explicitly releases any table locks held by the current session. See LOCK TABLES for more information. In addition to releasing table locks acquired by the LOCK TABLES statement, the UNLOCK TABLES statement also releases the global read lock acquired by the FLUSH TABLES WITH READ LOCK statement. The FLUSH TABLES WITH READ LOCK statement is very useful for performing backups. See FLUSH for more information about FLUSH TABLES WITH READ LOCK. URL: https://mariadb.com/kb/en/transactions-unlock-tables/https://mariadb.com/kb/en/transactions-unlock-tables/@ h"*XA Transactions@ i V& CREATE USER@ j ?% ALTER USER@ l GRANT#@ v ": Authentication from MariaDB 10.4Dk)=./3N  f 6~? VX h(CASE OPERATORSyntax ------ CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END Description ----------- The first version returns the result where value=compare_value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part. There is also a CASE statement, which differs from the CASE operator described here. Examples -------- SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; +------------------------------------------------------------+ | CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END | +------------------------------------------------------------+ | one | +------------------------------------------------------------+ SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END; +--------------------------------------------+ | CASE WHEN 1>0 THEN 'true' ELSE 'false' END | +--------------------------------------------+ | true | +--------------------------------------------+ SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END; +-----------------------------------------------------+ | CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END | +-----------------------------------------------------+ | NULL | +-----------------------------------------------------+ URL: https://mariadb.com/kb/en/case-operator/https://mariadb.com/kb/en/case-operator/Y &IF FunctionSyntax ------ IF(expr1,expr2,expr3) Description ----------- If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used. Note: There is also an IF statement which differs from the IF() function described here. Examples -------- SELECT IF(1>2,2,3); +-------------+ | IF(1>2,2,3) | +-------------+ | 3 | +-------------+ SELECT IF(1<2,'yes','no'); +--------------------+ | IF(1<2,'yes','no') | +--------------------+ | yes | +--------------------+ SELECT IF(STRCMP('test','test1'),'no','yes'); +---------------------------------------+ | IF(STRCMP('test','test1'),'no','yes') | +---------------------------------------+ | no | +---------------------------------------+ URL: https://mariadb.com/kb/en/if-function/https://mariadb.com/kb/en/if-function/ZC!IFNULLSyntax ------ IFNULL(expr1,expr2) NVL(expr1,expr2) Description ----------- If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used. From MariaDB 10.3, NVL() is an alias for IFNULL(). Examples -------- SELECT IFNULL(1,0); +-------------+ | IFNULL(1,0) | +-------------+ | 1 | +-------------+ SELECT IFNULL(NULL,10); +-----------------+ | IFNULL(NULL,10) | +-----------------+ | 10 | +-----------------+ SELECT IFNULL(1/0,10); +----------------+ | IFNULL(1/0,10) | +----------------+ | 10.0000 | +----------------+ SELECT IFNULL(1/0,'yes'); +-------------------+ | IFNULL(1/0,'yes') | +-------------------+ | yes | +-------------------+ URL: https://mariadb.com/kb/en/ifnull/https://mariadb.com/kb/en/ifnull/_!COMMITThe COMMIT statement ends a transaction, saving any changes to the data so that they become visible to subsequent transactions. Also, unlocks metadata changed by current transaction. If autocommit is set to 1, an implicit commit is performed after each statement. Otherwise, all transactions which don't end with an explicit COMMIT are implicitly rollbacked and the changes are lost. The ROLLBACK statement can be used to do this explicitly. The required syntax for the COMMIT statement is as follows: COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] COMMIT is the more important transaction terminator, as well as the more interesting one. The basic form of the COMMIT statement is simply the keyword COMMIT (the keyword WORK is simply noise and can be omitted without changing the effect). The optional AND CHAIN clause is a convenience for initiating a new transaction as soon as the old transaction terminates. If AND CHAIN is specified, then there is effectively nothing between the old and new transactions, although they remain separate. The characteristics of the new transaction will be the same as the characteristics of the old one — that is, the new transaction will have the same access mode, isolation level and diagnostics area size (we'll discuss all of these shortly) as the transaction just terminated. RELEASE tells the server to disconnect the client immediately after the current transaction. There are NO RELEASE and AND NO CHAIN options. By default, commits do not RELEASE or CHAIN, but it's possible to change this default behavior with the completion_type server system variable. In this case, the AND NO CHAIN and NO RELEASE options override the server default. URL: https://mariadb.com/kb/en/commit/https://mariadb.com/kb/en/commit/x ST_XSyntax ------ ST_X(p) X(p) Description ----------- Returns the X-coordinate value for the point p as a double-precision number. ST_X() and X() are synonyms. Examples -------- SET @pt = 'Point(56.7 53.34)'; SELECT X(GeomFromText(@pt)); +----------------------+ | X(GeomFromText(@pt)) | +----------------------+ | 56.7 | +----------------------+ URL: https://mariadb.com/kb/en/st_x/https://mariadb.com/kb/en/st_x/y ST_YSyntax ------ ST_Y(p) Y(p) Description ----------- Returns the Y-coordinate value for the point p as a double-precision number. ST_Y() and Y() are synonyms. Examples -------- SET @pt = 'Point(56.7 53.34)'; SELECT Y(GeomFromText(@pt)); +----------------------+ | Y(GeomFromText(@pt)) | +----------------------+ | 53.34 | +----------------------+ URL: https://mariadb.com/kb/en/st_y/https://mariadb.com/kb/en/st_y/zG- XA synonym for ST_X. URL: https://mariadb.com/kb/en/point-properties-x/https://mariadb.com/kb/en/point-properties-x/{G- YA synonym for ST_Y. URL: https://mariadb.com/kb/en/point-properties-y/https://mariadb.com/kb/en/point-properties-y/~ ( DECODE_ORACLEMariaDB starting with 10.3.2 ---------------------------- DECODE_ORACLE is a synonym for the Oracle mode version of the DECODE function, and is available in all modes. URL: https://mariadb.com/kb/en/decode_oracle/https://mariadb.com/kb/en/decode_oracle/ MD5Syntax ------ MD5(str) Description ----------- Calculates an MD5 128-bit checksum for the string. The return value is a 32-hex digit string, and as of MariaDB 5.5, is a nonbinary string in the connection character set and collation, determined by the values of the character_set_connection and collation_connection system variables. Before 5.5, the return value was a binary string. NULL is returned if the argument was NULL. Examples -------- SELECT MD5('testing'); +----------------------------------+ | MD5('testing') | +----------------------------------+ | ae2b1fca515949e5d54fb22b8ed95575 | +----------------------------------+ URL: https://mariadb.com/kb/en/md5/https://mariadb.com/kb/en/md5/y ~ x C^,START TRANSACTIONSyntax ------ START TRANSACTION [transaction_property [, transaction_property] ...] | BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET autocommit = {0 | 1} transaction_property: WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY Description ----------- The START TRANSACTION or BEGIN statement begins a new transaction. COMMIT commits the current transaction, making its changes permanent. ROLLBACK rolls back the current transaction, canceling its changes. The SET autocommit statement disables or enables the default autocommit mode for the current session. START TRANSACTION and SET autocommit = 1 implicitly commit the current transaction, if any. The optional WORK keyword is supported for COMMIT and ROLLBACK, as are the CHAIN and RELEASE clauses. CHAIN and RELEASE can be used for additional control over transaction completion. The value of the completion_type system variable determines the default completion behavior. The AND CHAIN clause causes a new transaction to begin as soon as the current one ends, and the new transaction has the same isolation level as the just-terminated transaction. The RELEASE clause causes the server to disconnect the current client session after terminating the current transaction. Including the NO keyword suppresses CHAIN or RELEASE completion, which can be useful if the completion_type system variable is set to cause chaining or release completion by default. Access Mode ----------- The access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in READ WRITE mode (see the tx_read_only system variable). READ ONLY mode allows the storage engine to apply optimizations that cannot be used for transactions which write data. Note that unlike the global read_only mode, READ_ONLY ADMIN (and SUPER before MariaDB 10.11.0) privilege doesn't allow writes and DDL statements on temporary tables are not allowed either. It is not permitted to specify both READ WRITE and READ ONLY in the same statement. READ WRITE and READ ONLY can also be specified in the SET TRANSACTION statement, in which case the specified mode is valid for all sessions, or for all subsequent transaction used by the current session. autocommit ---------- By default, MariaDB runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MariaDB stores the update on disk to make it permanent. To disable autocommit mode, use the following statement: SET autocommit=0; After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables (such as those for InnoDB or NDBCLUSTER) are not made permanent immediately. You must use COMMIT to store your changes to disk or ROLLBACK to ignore the changes. To disable autocommit mode for a single series of statements, use the START TRANSACTION statement. DDL Statements -------------- DDL statements (CREATE, ALTER, DROP) and administrative statements (FLUSH, RESET, OPTIMIZE, ANALYZE, CHECK, REPAIR, CACHE INDEX), transaction management statements (BEGIN, START TRANSACTION) and LOAD DATA INFILE, cause an implicit COMMIT and start a new transaction. An exception to this rule are the DDL that operate on temporary tables: you can CREATE, ALTER and DROP them without causing any COMMIT, but those actions cannot be rolled back. This means that if you call ROLLBACK, the temporary tables you created in the transaction will remain, while the rest of the transaction will be rolled back. Transactions cannot be used in Stored Functions or Triggers. In Stored Procedures and Events BEGIN is not allowed, so you should use START TRANSACTION instead. A transaction acquires a metadata lock on every table it accesses to prevent other connections from altering their structure. The lock is released at the end of the transaction. This happens even with non-transactional storage engines (like MEMORY or CONNECT), so it makes sense to use transactions with non-transactional tables. in_transaction -------------- The in_transaction system variable is a session-only, read-only variable that returns 1 inside a transaction, and 0 if not in a transaction. WITH CONSISTENT SNAPSHOT ------------------------ The WITH CONSISTENT SNAPSHOT option starts a consistent read for storage engines such as InnoDB that can do so, the same as if a START TRANSACTION followed by a SELECT from any InnoDB table was issued. See Enhancements for START TRANSACTION WITH CONSISTENT SNAPSHOT. Examples -------- START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT; URL: https://mariadb.com/kb/en/start-transaction/https://mariadb.com/kb/en/start-transaction/c $SAVEPOINTSyntax ------ SAVEPOINT identifier ROLLBACK [WORK] TO [SAVEPOINT] identifier RELEASE SAVEPOINT identifier Description ----------- InnoDB supports the SQL statements SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT and the optional WORK keyword for ROLLBACK. Each savepoint must have a legal MariaDB identifier. A savepoint is a named sub-transaction. Normally ROLLBACK undoes the changes performed by the whole transaction. When used with the TO clause, it undoes the changes performed after the specified savepoint, and erases all subsequent savepoints. However, all locks that have been acquired after the save point will survive. RELEASE SAVEPOINT does not rollback or commit any changes, but removes the specified savepoint. When the execution of a trigger or a stored function begins, it is not possible to use statements which reference a savepoint which was defined from out of that stored program. When a COMMIT (including implicit commits) or a ROLLBACK statement (with no TO clause) is performed, they act on the whole transaction, and all savepoints are removed. Errors ------ If COMMIT or ROLLBACK is issued and no transaction was started, no error is reported. If SAVEPOINT is issued and no transaction was started, no error is reported but no savepoint is created. When ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT is called for a savepoint that does not exist, an error like this is issued: ERROR 1305 (42000): SAVEPOINT svp_name does not exist URL: https://mariadb.com/kb/en/savepoint/https://mariadb.com/kb/en/savepoint/Z9 ENDPOINTA synonym for ST_ENDPOINT. URL: https://mariadb.com/kb/en/linestring-properties-endpoint/https://mariadb.com/kb/en/linestring-properties-endpoint/" GLENGTHSyntax ------ GLength(ls) Description ----------- Returns as a double-precision number the length of the LineString value ls in its associated spatial reference. Examples -------- SET @ls = 'LineString(1 1,2 2,3 3)'; SELECT GLength(GeomFromText(@ls)); +----------------------------+ | GLength(GeomFromText(@ls)) | +----------------------------+ | 2.82842712474619 | +----------------------------+ URL: https://mariadb.com/kb/en/glength/https://mariadb.com/kb/en/glength/ \: NumPointsA synonym for ST_NumPoints. URL: https://mariadb.com/kb/en/linestring-properties-numpoints/https://mariadb.com/kb/en/linestring-properties-numpoints/V7 PointNA synonym for ST_PointN. URL: https://mariadb.com/kb/en/linestring-properties-pointn/https://mariadb.com/kb/en/linestring-properties-pointn/`% `V #ROLLBACKThe ROLLBACK statement rolls back (ends) a transaction, destroying any changes to SQL-data so that they never become visible to subsequent transactions. The required syntax for the ROLLBACK statement is as follows. ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] [ TO [ SAVEPOINT ] { | } ] The ROLLBACK statement will either end a transaction, destroying all data changes that happened during any of the transaction, or it will just destroy any data changes that happened since you established a savepoint. The basic form of the ROLLBACK statement is just the keyword ROLLBACK (the keyword WORK is simply noise and can be omitted without changing the effect). The optional AND CHAIN clause is a convenience for initiating a new transaction as soon as the old transaction terminates. If AND CHAIN is specified, then there is effectively nothing between the old and new transactions, although they remain separate. The characteristics of the new transaction will be the same as the characteristics of the old one — that is, the new transaction will have the same access mode, isolation level and diagnostics area size (we'll discuss all of these shortly) as the transaction just terminated. The AND NO CHAIN option just tells your DBMS to end the transaction — that is, these four SQL statements are equivalent: ROLLBACK; ROLLBACK WORK; ROLLBACK AND NO CHAIN; ROLLBACK WORK AND NO CHAIN; All of them end a transaction without saving any transaction characteristics. The only other options, the equivalent statements: ROLLBACK AND CHAIN; ROLLBACK WORK AND CHAIN; both tell your DBMS to end a transaction, but to save that transaction's characteristics for the next transaction. ROLLBACK is much simpler than COMMIT: it may involve no more than a few deletions (of Cursors, locks, prepared SQL statements and log-file entries). It's usually assumed that ROLLBACK can't fail, although such a thing is conceivable (for example, an encompassing transaction might reject an attempt to ROLLBACK because it's lining up for a COMMIT). ROLLBACK cancels all effects of a transaction. It does not cancel effects on objects outside the DBMS's control (for example the values in host program variables or the settings made by some SQL/CLI function calls). But in general, it is a convenient statement for those situations when you say "oops, this isn't working" or when you simply don't care whether your temporary work becomes permanent or not. Here is a moot question. If all you've been doing is SELECTs, so that there have been no data changes, should you end the transaction with ROLLBACK or COMMIT? It shouldn't really matter because both ROLLBACK and COMMIT do the same transaction-terminating job. However, the popular conception is that ROLLBACK implies failure, so after a successful series of SELECT statements the convention is to end the transaction with COMMIT rather than ROLLBACK. MariaDB (and most other DBMSs) supports rollback of SQL-data change statements, but not of SQL-Schema statements. This means that if you use any of CREATE, ALTER, DROP, GRANT, REVOKE, you are implicitly committing at execution time. INSERT INTO Table_2 VALUES(5); DROP TABLE Table_3 CASCADE; ROLLBACK; The result will be that both the INSERT and the DROP will go through as separate transactions so the ROLLBACK will have no effect. URL: https://mariadb.com/kb/en/rollback/https://mariadb.com/kb/en/rollback/d+Metadata LockingMariaDB supports metadata locking. This means that when a transaction (including XA transactions) uses a table, it locks its metadata until the end of transaction. Non-transactional tables are also locked, as well as views and objects which are related to locked tables/views (stored functions, triggers, etc). When a connection tries to use a DDL statement (like an ALTER TABLE) which modifies a table that is locked, that connection is queued, and has to wait until it's unlocked. Using savepoints and performing a partial rollback does not release metadata locks. LOCK TABLES ... WRITE are also queued. Some wrong statements which produce an error may not need to wait for the lock to be freed. The metadata lock's timeout is determined by the value of the lock_wait_timeout server system variable (in seconds). However, note that its default value is 31536000 (1 year, MariaDB <= 10.2.3), or 86400 (1 day, MariaDB >= 10.2.4). If this timeout is exceeded, the following error is returned: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction If the metadata_lock_info plugin is installed, the Information Schema metadata_lock_info table stores information about existing metadata locks. MariaDB starting with 10.5.2 ---------------------------- From MariaDB 10.5, the Performance Schema metadata_locks table contains metadata lock information. Example ------- Let's use the following MEMORY (non-transactional) table: CREATE TABLE t (a INT) ENGINE = MEMORY; Connection 1 starts a transaction, and INSERTs a row into t: START TRANSACTION; INSERT INTO t SET a=1; t's metadata is now locked by connection 1. Connection 2 tries to alter t, but has to wait: ALTER TABLE t ADD COLUMN b INT; Connection 2's prompt is blocked now. Now connection 1 ends the transaction: COMMIT; ...and connection 2 finally gets the output of its command: Query OK, 1 row affected (35.23 sec) Records: 1 Duplicates: 0 Warnings: 0 URL: https://mariadb.com/kb/en/metadata-locking/https://mariadb.com/kb/en/metadata-locking/ ^; STARTPOINTA synonym for ST_STARTPOINT. URL: https://mariadb.com/kb/en/linestring-properties-startpoint/https://mariadb.com/kb/en/linestring-properties-startpoint/ & ST_ENDPOINTSyntax ------ ST_EndPoint(ls) EndPoint(ls) Description ----------- Returns the Point that is the endpoint of the LineString value ls. ST_EndPoint() and EndPoint() are synonyms. Examples -------- SET @ls = 'LineString(1 1,2 2,3 3)'; SELECT AsText(EndPoint(GeomFromText(@ls))); +-------------------------------------+ | AsText(EndPoint(GeomFromText(@ls))) | +-------------------------------------+ | POINT(3 3) | +-------------------------------------+ URL: https://mariadb.com/kb/en/st_endpoint/https://mariadb.com/kb/en/st_endpoint/ ' ST_NUMPOINTSSyntax ------ ST_NumPoints(ls) NumPoints(ls) Description ----------- Returns the number of Point objects in the LineString value ls. ST_NumPoints() and NumPoints() are synonyms. Examples -------- SET @ls = 'LineString(1 1,2 2,3 3)'; SELECT NumPoints(GeomFromText(@ls)); +------------------------------+ | NumPoints(GeomFromText(@ls)) | +------------------------------+ | 3 | +------------------------------+ URL: https://mariadb.com/kb/en/st_numpoints/https://mariadb.com/kb/en/st_numpoints/ $ ST_POINTNSyntax ------ ST_PointN(ls,N) PointN(ls,N) Description ----------- Returns the N-th Point in the LineString value ls. Points are numbered beginning with 1. ST_PointN() and PointN() are synonyms. Examples -------- SET @ls = 'LineString(1 1,2 2,3 3)'; SELECT AsText(PointN(GeomFromText(@ls),2)); +-------------------------------------+ | AsText(PointN(GeomFromText(@ls),2)) | +-------------------------------------+ | POINT(2 2) | +-------------------------------------+ URL: https://mariadb.com/kb/en/st_pointn/https://mariadb.com/kb/en/st_pointn/Z(cN  Wa?*SET TRANSACTIONSyntax ------ SET [GLOBAL | SESSION] TRANSACTION transaction_property [, transaction_property] ... transaction_property: ISOLATION LEVEL level | READ WRITE | READ ONLY level: REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE Description ----------- This statement sets the transaction isolation level or the transaction access mode globally, for the current session, or for the next transaction: * With the GLOBAL keyword, the statement sets the default transaction level globally for all subsequent sessions. Existing sessions are unaffected. * With the SESSION keyword, the statement sets the default transaction level for all subsequent transactions performed within the current session. * Without any SESSION or GLOBAL keyword, the statement sets the isolation level for the next (not started) transaction performed within the current session. A change to the global default isolation level requires the SUPER privilege. Any session is free to change its session isolation level (even in the middle of a transaction), or the isolation level for its next transaction. Isolation Level --------------- To set the global default isolation level at server startup, use the --transaction-isolation=level option on the command line or in an option file. Values of level for this option use dashes rather than spaces, so the allowable values are READ_UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE. For example, to set the default isolation level to REPEATABLE READ, use these lines in the [mysqld] section of an option file: [mysqld] transaction-isolation = REPEATABLE-READ To determine the global and session transaction isolation levels at runtime, check the value of the tx_isolation system variable (note that the variable has been renamed transaction_isolation from MariaDB 11.1.1, to match the option, and the old name deprecated). SELECT @@GLOBAL.tx_isolation, @@tx_isolation; From MariaDB 11.1.1: SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation; InnoDB supports each of the translation isolation levels described here using different locking strategies. The default level is REPEATABLE READ. For additional information about InnoDB record-level locks and how it uses them to execute various types of statements, see InnoDB Lock Modes, and http://dev.mysql.com/doc/refman/en/innodb-locks-set.html. Isolation Levels ---------------- The following sections describe how MariaDB supports the different transaction levels. READ UNCOMMITTED ---------------- SELECT statements are performed in a non-locking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a "dirty read." Otherwise, this isolation level works like READ COMMITTED. READ COMMITTED -------------- A somewhat Oracle-like isolation level with respect to consistent (non-locking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See http://dev.mysql.com/doc/refman/en/innodb-consistent-read.html. For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), InnoDB locks only index records, not the gaps before them, and thus allows the free insertion of new records next to locked records. For UPDATE and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition (such as WHERE id = 100), or a range-type search condition (such as WHERE id > 100). For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For range-type searches, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. This is necessary because "phantom rows" must be blocked for MySQL replication and recovery to work. Note: If the READ COMMITTED isolation level is used or the innodb_locks_unsafe_for_binlog system variable is enabled, there is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for non-matching rows are released after MariaDB has evaluated the WHERE condition.If you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must use row-based binary logging. REPEATABLE READ --------------- This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (non-locking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See http://dev.mysql.com/doc/refman/en/innodb-consistent-read.html. For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. This is the minimum isolation level for non-distributed XA transactions. SERIALIZABLE ------------ This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (non-locking) read and need not block for other transactions. (This means that to force a plain SELECT to block if other transactions have modified the selected rows, you should disable autocommit.) Distributed XA transactions should always use this isolation level. Access Mode ----------- The access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in READ WRITE mode (see the tx_read_only system variable). READ ONLY mode allows the storage engine to apply optimizations that cannot be used for transactions which write data. Note that unlike the global read_only mode, READ_ONLY ADMIN (and SUPER before MariaDB 10.11.0) privilege doesn't allow writes and DDL statements on temporary tables are not allowed either. It is not permitted to specify both READ WRITE and READ ONLY in the same statement. READ WRITE and READ ONLY can also be specified in the START TRANSACTION statement, in which case the specified mode is only valid for one transaction. Examples -------- SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; Attempting to set the isolation level within an existing transaction without specifying GLOBAL or SESSION. START TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress URL: https://mariadb.com/kb/en/set-transaction/https://mariadb.com/kb/en/set-transaction/ (( ST_STARTPOINTSyntax ------ ST_StartPoint(ls) StartPoint(ls) Description ----------- Returns the Point that is the start point of the LineString value ls. ST_StartPoint() and StartPoint() are synonyms. Examples -------- SET @ls = 'LineString(1 1,2 2,3 3)'; SELECT AsText(StartPoint(GeomFromText(@ls))); +---------------------------------------+ | AsText(StartPoint(GeomFromText(@ls))) | +---------------------------------------+ | POINT(1 1) | +---------------------------------------+ URL: https://mariadb.com/kb/en/st_startpoint/https://mariadb.com/kb/en/st_startpoint/k SF, b &LOCK TABLESSyntax ------ LOCK TABLE[S] tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... [WAIT n|NOWAIT] lock_type: READ [LOCAL] | [LOW_PRIORITY] WRITE | WRITE CONCURRENT UNLOCK TABLES Description ----------- The lock_type can be one of: +---------------------------+------------------------------------------------+ | Option | Description | +---------------------------+------------------------------------------------+ | READ | Read lock, no writes allowed | +---------------------------+------------------------------------------------+ | READ LOCAL | Read lock, but allow concurrent inserts | +---------------------------+------------------------------------------------+ | WRITE | Exclusive write lock. No other connections | | | can read or write to this table | +---------------------------+------------------------------------------------+ | LOW_PRIORITY WRITE | Exclusive write lock, but allow new read | | | locks on the table until we get the write | | | lock. | +---------------------------+------------------------------------------------+ | WRITE CONCURRENT | Exclusive write lock, but allow READ LOCAL | | | locks to the table. | +---------------------------+------------------------------------------------+ MariaDB enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session. Locks may be used to emulate transactions or to get more speed when updating tables. LOCK TABLES explicitly acquires table locks for the current client session. Table locks can be acquired for base tables or views. To use LOCK TABLES, you must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked. See GRANT For view locking, LOCK TABLES adds all base tables used in the view to the set of tables to be locked and locks them automatically. If you lock a table explicitly with LOCK TABLES, any tables used in triggers are also locked implicitly, as described in Triggers and Implicit Locks. UNLOCK TABLES explicitly releases any table locks held by the current session. MariaDB starting with 10.3.0 ---------------------------- WAIT/NOWAIT ----------- Set the lock wait timeout. See WAIT and NOWAIT. Limitations ----------- * LOCK TABLES doesn't work when using Galera cluster. You may experience crashes or locks when used with Galera. * LOCK TABLES works on XtraDB/InnoDB tables only if the innodb_table_locks system variable is set to 1 (the default) and autocommit is set to 0 (1 is default). Please note that no error message will be returned on LOCK TABLES with innodb_table_locks = 0. * LOCK TABLES implicitly commits the active transaction, if any. Also, starting a transaction always releases all table locks acquired with LOCK TABLES. This means that there is no way to have table locks and an active transaction at the same time. The only exceptions are the transactions in autocommit mode. To preserve the data integrity between transactional and non-transactional tables, the GET_LOCK() function can be used. * When using LOCK TABLES on a TEMPORARY table, it will always be locked with a WRITE lock. * While a connection holds an explicit read lock on a table, it cannot modify it. If you try, the following error will be produced: ERROR 1099 (HY000): Table 'tab_name' was locked with a READ lock and can't be updated * While a connection holds an explicit lock on a table, it cannot access a non-locked table. If you try, the following error will be produced: ERROR 1100 (HY000): Table 'tab_name' was not locked with LOCK TABLES * While a connection holds an explicit lock on a table, it cannot issue the following: INSERT DELAYED, CREATE TABLE, CREATE TABLE ... LIKE, and DDL statements involving stored programs and views (except for triggers). If you try, the following error will be produced: ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction * LOCK TABLES can not be used in stored routines - if you try, the following error will be produced on creation: ERROR 1314 (0A000): LOCK is not allowed in stored procedures URL: https://mariadb.com/kb/en/lock-tables/https://mariadb.com/kb/en/lock-tables/e/Transaction TimeoutsMariaDB has always had the wait_timeout and interactive_timeout settings, which close connections after a certain period of inactivity. However, these are by default set to a long wait period. In situations where transactions may be started, but not committed or rolled back, more granular control and a shorter timeout may be desirable so as to avoid locks being held for too long. MariaDB 10.3 introduced three new variables to handle this situation. * idle_transaction_timeout (all transactions) * idle_write_transaction_timeout (write transactions - called idle_readwrite_transaction_timeout until MariaDB 10.3.2) * idle_readonly_transaction_timeout (read transactions) These accept a time in seconds to time out, by closing the connection, transactions that are idle for longer than this period. By default all are set to zero, or no timeout. idle_transaction_timeout affects all transactions, idle_write_transaction_timeout affects write transactions only and idle_readonly_transaction_timeout affects read transactions only. The latter two variables work independently. However, if either is set along with idle_transaction_timeout, the settings for idle_write_transaction_timeout or idle_readonly_transaction_timeout will take precedence. Examples -------- SET SESSION idle_transaction_timeout=2; BEGIN; SELECT * FROM t; Empty set (0.000 sec) ## wait 3 seconds SELECT * FROM t; ERROR 2006 (HY000): MySQL server has gone away SET SESSION idle_write_transaction_timeout=2; BEGIN; SELECT * FROM t; Empty set (0.000 sec) ## wait 3 seconds SELECT * FROM t; Empty set (0.000 sec) INSERT INTO t VALUES(1); ## wait 3 seconds SELECT * FROM t; ERROR 2006 (HY000): MySQL server has gone away SET SESSION idle_transaction_timeout=2, SESSION idle_readonly_transaction_timeout=10; BEGIN; SELECT * FROM t; Empty set (0.000 sec) ## wait 3 seconds SELECT * FROM t; Empty set (0.000 sec) ## wait 11 seconds SELECT * FROM t; ERROR 2006 (HY000): MySQL server has gone away URL: https://mariadb.com/kb/en/transaction-timeouts/https://mariadb.com/kb/en/transaction-timeouts/ \$INET_ATONSyntax ------ INET_ATON(expr) Description ----------- 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. Examples -------- 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 URL: https://mariadb.com/kb/en/inet_aton/https://mariadb.com/kb/en/inet_aton/M9 gg*WAIT and NOWAITExtended syntax so that it is possible to set innodb_lock_wait_timeout and lock_wait_timeout for the following statements: Syntax ------ ALTER TABLE tbl_name [WAIT n|NOWAIT] ... CREATE ... INDEX ON tbl_name (index_col_name, ...) [WAIT n|NOWAIT] ... DROP INDEX ... [WAIT n|NOWAIT] DROP TABLE tbl_name [WAIT n|NOWAIT] ... LOCK TABLE ... [WAIT n|NOWAIT] OPTIMIZE TABLE tbl_name [WAIT n|NOWAIT] RENAME TABLE tbl_name [WAIT n|NOWAIT] ... SELECT ... FOR UPDATE [WAIT n|NOWAIT] SELECT ... LOCK IN SHARE MODE [WAIT n|NOWAIT] TRUNCATE TABLE tbl_name [WAIT n|NOWAIT] Description ----------- The lock wait timeout can be explicitly set in the statement by using either WAIT n (to set the wait in seconds) or NOWAIT, in which case the statement will immediately fail if the lock cannot be obtained. WAIT 0 is equivalent to NOWAIT. URL: https://mariadb.com/kb/en/wait-and-nowait/https://mariadb.com/kb/en/wait-and-nowait/k $ DROP USERSyntax ------ DROP USER [IF EXISTS] user_name [, user_name] ... Description ----------- The DROP USER statement removes one or more MariaDB accounts. It removes privilege rows for the account from all grant tables. To use this statement, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database. Each account is named using the same format as for the CREATE USER statement; for example, 'jeffrey'@'localhost'. If you specify only the user name part of the account name, a host name part of '%' is used. For additional information about specifying account names, see CREATE USER. Note that, if you specify an account that is currently connected, it will not be deleted until the connection is closed. The connection will not be automatically closed. If any of the specified user accounts do not exist, ERROR 1396 (HY000) results. If an error occurs, DROP USER will still drop the accounts that do not result in an error. Only one error is produced for all users which have not been dropped: ERROR 1396 (HY000): Operation DROP USER failed for 'u1'@'%','u2'@'%' Failed CREATE or DROP operations, for both users and roles, produce the same error code. IF EXISTS --------- If the IF EXISTS clause is used, MariaDB will return a note instead of an error if the user does not exist. Examples -------- DROP USER bob; DROP USER foo2@localhost,foo2@'127.%'; IF EXISTS: DROP USER bob; ERROR 1396 (HY000): Operation DROP USER failed for 'bob'@'%' DROP USER IF EXISTS bob; Query OK, 0 rows affected, 1 warning (0.00 sec) SHOW WARNINGS; +-------+------+---------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------+ | Note | 1974 | Can't drop user 'bob'@'%'; it doesn't exist | +-------+------+---------------------------------------------+ URL: https://mariadb.com/kb/en/drop-user/https://mariadb.com/kb/en/drop-user/m d& RENAME USERSyntax ------ RENAME USER old_user TO new_user [, old_user TO new_user] ... Description ----------- The RENAME USER statement renames existing MariaDB accounts. To use it, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. Each account is named using the same format as for the CREATE USER statement; for example, 'jeffrey'@'localhost'. If you specify only the user name part of the account name, a host name part of '%' is used. If any of the old user accounts do not exist or any of the new user accounts already exist, ERROR 1396 (HY000) results. If an error occurs, RENAME USER will still rename the accounts that do not result in an error. Examples -------- CREATE USER 'donald', 'mickey'; RENAME USER 'donald' TO 'duck'@'localhost', 'mickey' TO 'mouse'@'localhost'; URL: https://mariadb.com/kb/en/rename-user/https://mariadb.com/kb/en/rename-user/n! REVOKEPrivileges ---------- Syntax ------ REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ... REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... Description ----------- The REVOKE statement enables system administrators to revoke privileges (or roles - see section below) from MariaDB accounts. Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. If you specify only the user name part of the account name, a host name part of '%' is used. For details on the levels at which privileges exist, the allowable priv_type and priv_level values, and the syntax for specifying users and passwords, see GRANT. To use the first REVOKE syntax, you must have the GRANT OPTION privilege, and you must have the privileges that you are revoking. To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named user or users: REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... To use this REVOKE syntax, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. See GRANT. Examples -------- REVOKE SUPER ON *.* FROM 'alexander'@'localhost'; Roles ----- Syntax ------ REVOKE role [, role ...] FROM grantee [, grantee2 ... ] REVOKE ADMIN OPTION FOR role FROM grantee [, grantee2] Description ----------- REVOKE is also used to remove a role from a user or another role that it's previously been assigned to. If a role has previously been set as a default role, REVOKE does not remove the record of the default role from the mysql.user table. If the role is subsequently granted again, it will again be the user's default. Use SET DEFAULT ROLE NONE to explicitly remove this. Before MariaDB 10.1.13, the REVOKE role statement was not permitted in prepared statements. Example ------- REVOKE journalist FROM hulda URL: https://mariadb.com/kb/en/revoke/https://mariadb.com/kb/en/revoke/ $INET_NTOASyntax ------ INET_NTOA(expr) Description ----------- 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. Examples -------- 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 URL: https://mariadb.com/kb/en/inet_ntoa/https://mariadb.com/kb/en/inet_ntoa/ |'IS_FREE_LOCKSyntax ------ IS_FREE_LOCK(str) Description ----------- 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. URL: https://mariadb.com/kb/en/is_free_lock/https://mariadb.com/kb/en/is_free_lock/"IS_IPV6Syntax ------ IS_IPV6(expr) Description ----------- 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. Examples -------- 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 | +---------------------+ URL: https://mariadb.com/kb/en/is_ipv6/https://mariadb.com/kb/en/is_ipv6/0s3@%  Overview -------- The MariaDB XA implementation is based on the X/Open CAE document Distributed Transaction Processing: The XA Specification. This document is published by The Open Group and available at http://www.opengroup.org/public/pubs/catalog/c193.htm. XA transactions are designed to allow distributed transactions, where a transaction manager (the application) controls a transaction which involves multiple resources. Such resources are usually DBMSs, but could be resources of any type. The whole set of required transactional operations is called a global transaction. Each subset of operations which involve a single resource is called a local transaction. XA used a 2-phases commit (2PC). With the first commit, the transaction manager tells each resource to prepare an effective commit, and waits for a confirm message. The changes are not still made effective at this point. If any of the resources encountered an error, the transaction manager will rollback the global transaction. If all resources communicate that the first commit is successful, the transaction manager can require a second commit, which makes the changes effective. In MariaDB, XA transactions can only be used with storage engines that support them. At least InnoDB, TokuDB, SPIDER and MyRocks support them. For InnoDB, until MariaDB 10.2, XA transactions can be disabled by setting the innodb_support_xa server system variable to 0. From MariaDB 10.3, XA transactions are always supported. Like regular transactions, XA transactions create metadata locks on accessed tables. XA transactions require REPEATABLE READ as a minimum isolation level. However, distributed transactions should always use SERIALIZABLE. Trying to start more than one XA transaction at the same time produces a 1400 error (SQLSTATE 'XAE09'). The same error is produced when attempting to start an XA transaction while a regular transaction is in effect. Trying to start a regular transaction while an XA transaction is in effect produces a 1399 error (SQLSTATE 'XAE07'). The statements that cause an implicit COMMIT for regular transactions produce a 1400 error (SQLSTATE 'XAE09') if a XA transaction is in effect. Internal XA vs External XA -------------------------- XA transactions are an overloaded term in MariaDB. If a storage engine is XA-capable, it can mean one or both of these: * It supports MariaDB's internal two-phase commit API. This is transparent to the user. Sometimes this is called "internal XA", since MariaDB's internal transaction coordinator log can handle coordinating these transactions. * It supports XA transactions, with the XA START, XA PREPARE, XA COMMIT, etc. statements. Sometimes this is called "external XA", since it requires the use of an external transaction coordinator to use this feature properly. Transaction Coordinator Log --------------------------- If you have two or more XA-capable storage engines enabled, then a transaction coordinator log must be available. There are currently two implementations of the transaction coordinator log: * Binary log-based transaction coordinator log * Memory-mapped file-based transaction coordinator log If the binary log is enabled on a server, then the server will use the binary log-based transaction coordinator log. Otherwise, it will use the memory-mapped file-based transaction coordinator log. See Transaction Coordinator Log for more information. Syntax ------ XA {START|BEGIN} xid [JOIN|RESUME] XA END xid [SUSPEND [FOR MIGRATE]] XA PREPARE xid XA COMMIT xid [ONE PHASE] XA ROLLBACK xid XA RECOVER [FORMAT=['RAW'|'SQL']] xid: gtrid [, bqual [, formatID ]] The interface to XA transactions is a set of SQL statements starting with XA. Each statement changes a transaction's state, determining which actions it can perform. A transaction which does not exist is in the NON-EXISTING state. XA START (or BEGIN) starts a transaction and defines its xid (a transaction identifier). The JOIN or RESUME keywords have no effect. The new transaction will be in ACTIVE state. The xid can have 3 components, though only the first one is mandatory. gtrid is a quoted string representing a global transaction identifier. bqual is a quoted string representing a local transaction identifier. formatID is an unsigned integer indicating the format used for the first two components; if not specified, defaults to 1. MariaDB does not interpret in any way these components, and only uses them to identify a transaction. xids of transactions in effect must be unique. XA END declares that the specified ACTIVE transaction is finished and it changes its state to IDLE. SUSPEND [FOR MIGRATE] has no effect. XA PREPARE prepares an IDLE transaction for commit, changing its state to PREPARED. This is the first commit. XA COMMIT definitely commits and terminates a transaction which has already been PREPARED. If the ONE PHASE clause is specified, this statements performs a 1-phase commit on an IDLE transaction. XA ROLLBACK rolls back and terminates an IDLE or PREPARED transaction. XA RECOVER shows information about all PREPARED transactions. When trying to execute an operation which is not allowed for the transaction's current state, an error is produced: XA COMMIT 'test' ONE PHASE; ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state XA COMMIT 'test2'; ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the NON-EXISTING state XA RECOVER ---------- The XA RECOVER statement shows information about all transactions which are in the PREPARED state. It does not matter which connection created the transaction: if it has been PREPARED, it appears. But this does not mean that a connection can commit or rollback a transaction which was started by another connection. Note that transactions using a 1-phase commit are never in the PREPARED state, so they cannot be shown by XA RECOVER. XA RECOVER produces four columns: XA RECOVER; +----------+--------------+--------------+------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+------+ | 1 | 4 | 0 | test | +----------+--------------+--------------+------+ MariaDB starting with 10.3.3 ---------------------------- You can use XA RECOVER FORMAT='SQL' to get the data in a human readable form that can be directly copy-pasted into XA COMMIT or XA ROLLBACK. This is particularly useful for binary xid generated by some transaction coordinators. formatID is the formatID part of xid. data are the gtrid and bqual parts of xid, concatenated. gtrid_length and bqual_length are the lengths of gtrid and bqual, respectevely. Examples -------- 2-phases commit: XA START 'test'; INSERT INTO t VALUES (1,2); XA END 'test'; XA PREPARE 'test'; XA COMMIT 'test'; 1-phase commit: XA START 'test'; INSERT INTO t VALUES (1,2); XA END 'test'; XA COMMIT 'test' ONE PHASE; Human-readable: xa start '12\r34\t67\v78', 'abc\ndef', 3; insert t1 values (40); xa end '12\r34\t67\v78', 'abc\ndef', 3; xa prepare '12\r34\t67\v78', 'abc\ndef', 3; xa recover format='RAW'; +----------+--------------+--------------+--------------------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+--------------------+ 34 67v78abc 11 | 7 | 12 def | +----------+--------------+--------------+--------------------+ xa recover format='SQL'; +----------+--------------+--------------+------------------------------------- ---------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+------------------------------------- ---------+ | 3 | 11 | 7 | X'31320d3334093637763738',X'6162630a646566',3 | +----------+--------------+--------------+------------------------------------- ---------+ xa rollback X'31320d3334093637763738',X'6162630a646566',3; Known Issues ------------ MariaDB Galera Cluster ---------------------- MariaDB Galera Cluster does not support XA transactions. However, MariaDB Galera Cluster OO https://mariadb.com/kb/en/xa-transactions/:50:05 | | 3 | 2013-07-22 12:51:56 | | 4 | 2001-07-22 12:12:12 | +------+---------------------+ Converting to Unix epoch: SELECT ts, UNIX_TIMESTAMP(ts) FROM t; +---------------------+--------------------+ | ts | UNIX_TIMESTAMP(ts) | +---------------------+--------------------+ | 2013-07-22 12:50:05 | 1374490205 | | 2013-07-22 12:50:05 | 1374490205 | | 2013-07-22 12:51:56 | 1374490316 | | 2001-07-22 12:12:12 | 995796732 | +---------------------+--------------------+ Update also changes the timestamp: UPDATE t set id=5 WHERE id=1; SELECT * FROM t; +------+---------------------+ | id | ts | +------+---------------------+ | 5 | 2013-07-22 14:52:33 | | 2 | 2013-07-22 12:50:05 | | 3 | 2013-07-22 12:51:56 | | 4 | 2001-07-22 12:12:12 | +------+---------------------+ Default NULL: CREATE TABLE t2 (id INT, ts TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t(id) VALUES (1),(2); SELECT * FROM t2; INSERT INTO t2(id) VALUES (1),(2); SELECT * FROM t2; +------+------+ | id | ts | +------+------+ | 1 | NULL | | 2 | NULL | +------+------+ UPDATE t2 SET id=3 WHERE id=1; SELECT * FROM t2; +------+---------------------+ | id | ts | +------+---------------------+ | 3 | 2013-07-22 15:32:22 | | 2 | NULL | +------+---------------------+ Only the first timestamp is automatically inserted and updated: CREATE TABLE t3 (id INT, ts1 TIMESTAMP, ts2 TIMESTAMP); INSERT INTO t3(id) VALUES (1),(2); SELECT * FROM t3; +------+---------------------+---------------------+ | id | ts1 | ts2 | +------+---------------------+---------------------+ | 1 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 | | 2 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 | +------+---------------------+---------------------+ DESC t3; +-------+-----------+------+-----+---------------------+----------------------- -----+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------------------+----------------------- -----+ | id | int(11) | YES | | NULL | | | ts1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | ts2 | timestamp | NO | | 0000-00-00 00:00:00 | | +-------+-----------+------+-----+---------------------+----------------------- -----+ Explicitly setting a timestamp with the CURRENT_TIMESTAMP function: INSERT INTO t3(id,ts2) VALUES (3,CURRENT_TIMESTAMP()); SELECT * FROM t3; +------+---------------------+---------------------+ | id | ts1 | ts2 | +------+---------------------+---------------------+ | 1 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 | | 2 | 2013-07-22 15:35:07 | 0000-00-00 00:00:00 | | 3 | 2013-07-22 15:38:52 | 2013-07-22 15:38:52 | +------+---------------------+---------------------+ Specifying the timestamp as NOT NULL: CREATE TABLE t4 (id INT, ts TIMESTAMP NOT NULL); INSERT INTO t4(id) VALUES (1); SELECT SLEEP(1); INSERT INTO t4(id,ts) VALUES (2,NULL); SELECT * FROM t4; URL: https://mariadb.com/kb/en/timestamp/https://mariadb.com/kb/en/flush/https://mariadb.com/kb/en/show-replica-status/ 3 | 3 | 6 | | 4 | 4 | 8 | | 5 | 5 | 10 | | 6 | 8 | 14 | +------+------+------+ Another example, given the following data (the separator is a tab): 1 a 2 b The value of the first column is doubled before loading: LOAD DATA INFILE 'ld.txt' INTO TABLE ld (@i,v) SET i=@i*2; SELECT * FROM ld; +------+------+ | i | v | +------+------+ | 2 | a | | 4 | b | +------+------+ URL: https://mariadb.com/kb/en/load-data-infile/ AND t1.id=( SELECT MAX(id) FROM t tab WHERE tab.f1=t1.f1 ) ); Query OK, 2 rows affected (0.120 sec) SELECT * FROM t; +------+------+ | id | f1 | +------+------+ | 3 | b | | 4 | a | +------+------ URL: https://mariadb.com/kb/en/useful-mariadb-queries/https://mariadb.com/kb/en/explain/https://mariadb.com/kb/en/reserved-words/https://mariadb.com/kb/en/sql_modeoracle/https://mariadb.com/kb/en/date_format/ OS function to convert the data using the system time zone. At least on Linux, the corresponding function (localtime_r) uses a global mutex inside glibc that can cause contention under high concurrent load. Set your time zone to a named time zone to avoid this issue. See mysql time zone tables for details on how to do this. Examples -------- SELECT FROM_UNIXTIME(1196440219); +---------------------------+ | FROM_UNIXTIME(1196440219) | +---------------------------+ | 2007-11-30 11:30:19 | +---------------------------+ SELECT FROM_UNIXTIME(1196440219) + 0; +-------------------------------+ | FROM_UNIXTIME(1196440219) + 0 | +-------------------------------+ | 20071130113019.000000 | +-------------------------------+ SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x'); +---------------------------------------------------------+ | FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x') | +---------------------------------------------------------+ | 2010 27th March 01:03:47 2010 | +---------------------------------------------------------+ URL: https://mariadb.com/kb/en/from_unixtime/ | +---------------------------------------------------------+ SELECT STR_TO_DATE('Wednesday23423, June 2, 2014', '%W, %M %e, %Y'); +--------------------------------------------------------------+ | STR_TO_DATE('Wednesday23423, June 2, 2014', '%W, %M %e, %Y') | +--------------------------------------------------------------+ | NULL | +--------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) SHOW WARNINGS; +---------+------+------------------------------------------------------------- ---------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------- ---------------------+ | Warning | 1411 | Incorrect datetime value: 'Wednesday23423, June 2, 2014' for function str_to_date | +---------+------+------------------------------------------------------------- ---------------------+ SELECT STR_TO_DATE('Wednesday23423, June 2, 2014', '%W%#, %M %e, %Y'); +----------------------------------------------------------------+ | STR_TO_DATE('Wednesday23423, June 2, 2014', '%W%#, %M %e, %Y') | +----------------------------------------------------------------+ | 2014-06-02 | +----------------------------------------------------------------+ URL: https://mariadb.com/kb/en/str_to_date/J&{)R)0" ;  . 6 *vSyntax ------ CREATE [OR REPLACE] USER [IF NOT EXISTS] user_specification [,user_specification ...] [REQUIRE {NONE | tls_option [[AND] tls_option ...] }] [WITH resource_option [resource_option ...] ] [lock_option] [password_option] user_specification: username [authentication_option] authentication_option: IDENTIFIED BY 'password' | IDENTIFIED BY PASSWORD 'password_hash' | IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule ...] authentication_rule: authentication_plugin | authentication_plugin {USING|AS} 'authentication_string' | authentication_plugin {USING|AS} PASSWORD('password') tls_option: SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject' resource_option: MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count | MAX_STATEMENT_TIME time password_option: PASSWORD EXPIRE | PASSWORD EXPIRE DEFAULT | PASSWORD EXPIRE NEVER | PASSWORD EXPIRE INTERVAL N DAY lock_option: ACCOUNT LOCK | ACCOUNT UNLOCK } Description ----------- The CREATE USER statement creates new MariaDB accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new row in mysql.user (until MariaDB 10.3 this is a table, from MariaDB 10.4 it's a view) or mysql.global_priv_table (from MariaDB 10.4) that has no privileges. If any of the specified accounts, or any permissions for the specified accounts, already exist, then the server returns ERROR 1396 (HY000). If an error occurs, CREATE USER will still create the accounts that do not result in an error. Only one error is produced for all users which have not been created: ERROR 1396 (HY000): Operation CREATE USER failed for 'u1'@'%','u2'@'%' CREATE USER, DROP USER, CREATE ROLE, and DROP ROLE all produce the same error code when they fail. See Account Names below for details on how account names are specified. OR REPLACE ---------- If the optional OR REPLACE clause is used, it is basically a shortcut for: DROP USER IF EXISTS name; CREATE USER name ...; For example: CREATE USER foo2@test IDENTIFIED BY 'password'; ERROR 1396 (HY000): Operation CREATE USER failed for 'foo2'@'test' CREATE OR REPLACE USER foo2@test IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec) IF NOT EXISTS ------------- When the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the specified user already exists. For example: CREATE USER foo2@test IDENTIFIED BY 'password'; ERROR 1396 (HY000): Operation CREATE USER failed for 'foo2'@'test' CREATE USER IF NOT EXISTS foo2@test IDENTIFIED BY 'password'; Query OK, 0 rows affected, 1 warning (0.00 sec) SHOW WARNINGS; +-------+------+----------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------+ | Note | 1973 | Can't create user 'foo2'@'test'; it already exists | +-------+------+----------------------------------------------------+ Authentication Options ---------------------- IDENTIFIED BY 'password' ------------------------ The optional IDENTIFIED BY clause can be used to provide an account with a password. The password should be specified in plain text. It will be hashed by the PASSWORD function prior to being stored in the mysql.user/mysql.global_priv_table table. For example, if our password is mariadb, then we can create the user with: CREATE USER foo2@test IDENTIFIED BY 'mariadb'; If you do not specify a password with the IDENTIFIED BY clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set. The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password. IDENTIFIED BY PASSWORD 'password_hash' -------------------------------------- The optional IDENTIFIED BY PASSWORD clause can be used to provide an account with a password that has already been hashed. The password should be specified as a hash that was provided by the PASSWORD function. It will be stored in the mysql.user/mysql.global_priv_table table as-is. For example, if our password is mariadb, then we can find the hash with: SELECT PASSWORD('mariadb'); +-------------------------------------------+ | PASSWORD('mariadb') | +-------------------------------------------+ | *54958E764CE10E50764C2EECBB71D01F08549980 | +-------------------------------------------+ 1 row in set (0.00 sec) And then we can create a user with the hash: CREATE USER foo2@test IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980'; If you do not specify a password with the IDENTIFIED BY clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set. The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password. IDENTIFIED {VIA|WITH} authentication_plugin ------------------------------------------- The optional IDENTIFIED VIA authentication_plugin allows you to specify that the account should be authenticated by a specific authentication plugin. The plugin name must be an active authentication plugin as per SHOW PLUGINS. If it doesn't show up in that output, then you will need to install it with INSTALL PLUGIN or INSTALL SONAME. For example, this could be used with the PAM authentication plugin: CREATE USER foo2@test IDENTIFIED VIA pam; Some authentication plugins allow additional arguments to be specified after a USING or AS keyword. For example, the PAM authentication plugin accepts a service name: CREATE USER foo2@test IDENTIFIED VIA pam USING 'mariadb'; The exact meaning of the additional argument would depend on the specific authentication plugin. MariaDB starting with 10.4.0 ---------------------------- The USING or AS keyword can also be used to provide a plain-text password to a plugin if it's provided as an argument to the PASSWORD() function. This is only valid for authentication plugins that have implemented a hook for the PASSWORD() function. For example, the ed25519 authentication plugin supports this: CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret'); MariaDB starting with 10.4.3 ---------------------------- One can specify many authentication plugins, they all work as alternatives ways of authenticating a user: CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket; By default, when you create a user without specifying an authentication plugin, MariaDB uses the mysql_native_password plugin. TLS Options ----------- By default, MariaDB transmits data between the server and clients without encrypting it. This is generally acceptable when the server and client run on the same host or in networks where security is guaranteed through other means. However, in cases where the server and client exist on separate networks or they are in a high-risk network, the lack of encryption does introduce security concerns as a malicious actor could potentially eavesdrop on the traffic as it is sent over the network between them. To mitigate this concern, MariaDB allows you to encrypt data in transit between the server and clients using the Transport Layer Security (TLS) protocol. TLS was formerly known as Secure Socket Layer (SSL), but strictly speaking the SSL protocol is a predecessor to TLS and, that version of the protocol is now considered insecure. The documentation still uses the term SSL often and for compatibility reasons TLS-related server system and status variables still use the prefix ssl_, but internally, MariaDB only supports its secure successors. See Secure Connections Overview for more information about how to determine whether your MariaDB server has TLS support. You can set certain TLS-related restrictions for specific user accounts. For instance, you might use this with user accounts that require access to sensitive data while sending it across networks that you do not control. These restrictions can be enabled for a user account with the CREATE USER, ALTER USER, or GRANT statements. The following options are available: +---------------------------+------------------------------------------------+ | Option | Description | +---------------------------+------------------------------------------------+ | REQUIRE NONE | TLS is not required for this account, but can | | | still be used. | +---------------------------+------------------------------------------------+ | REQUIRE SSL | The account must use TLS, but no valid X509 | | | certificate is required. This option cannot | | | be combined with other TLS options. | +---------------------------+------------------------------------------------+ | REQUIRE X509 | The account must use TLS and must have a | | | valid X509 certificate. This option implies | | | REQUIRE SSL. This option cannot be combined | | | with other TLS options. | +---------------------------+------------------------------------------------+ | REQUIRE ISSUER 'issuer' | The account must use TLS and must have a | | | valid X509 certificate. Also, the Certificate | | | Authority must be the one specified via the | | | string issuer. This option implies REQUIRE | | | X509. This option can be combined with the | | | SUBJECT, and CIPHER options in any order. | +---------------------------+------------------------------------------------+ | REQUIRE SUBJECT 'subject' | The account must use TLS and must have a | | | valid X509 certificate. Also, the | | | certificate's Subject must be the one | | | specified via the string subject. This option | | | implies REQUIRE X509. This option can be | | | combined with the ISSUER, and CIPHER options | | | in any order. | +---------------------------+------------------------------------------------+ | REQUIRE CIPHER 'cipher' | The account must use TLS, but no valid X509 | | | certificate is required. Also, the encryption | | | used for the connection must use a specific | | | cipher method specified in the string cipher. | | | This option implies REQUIRE SSL. This option | | | can be combined with the ISSUER, and SUBJECT | | | options in any order. | +---------------------------+------------------------------------------------+ The REQUIRE keyword must be used only once for all specified options, and the AND keyword can be used to separate individual options, but it is not required. For example, you can create a user account that requires these TLS options with the following: CREATE USER 'alice'@'%' REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland' AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/emailAddress=p.parker@marvel.com' AND CIPHER 'SHA-DES-CBC3-EDH-RSA'; If any of these options are set for a specific user account, then any client who tries to connect with that user account will have to be configured to connect with TLS. See Securing Connections for Client and Server for information on how to enable TLS on the client and server. Resource Limit Options ---------------------- It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account: +--------------------------------------+--------------------------------------+ | Limit Type | Decription | +--------------------------------------+--------------------------------------+ | MAX_QUERIES_PER_HOUR | Number of statements that the | | | account can issue per hour | | | (including updates) | +--------------------------------------+--------------------------------------+ | MAX_UPDATES_PER_HOUR | Number of updates (not queries) | | | that the account can issue per hour | +--------------------------------------+--------------------------------------+ | MAX_CONNECTIONS_PER_HOUR | Number of connections that the | | | account can start per hour | +--------------------------------------+--------------------------------------+ | MAX_USER_CONNECTIONS | Number of simultaneous connections | | | that can be accepted from the same | | | account; if it is 0, | | | max_connections will be used | | | instead; if max_connections is 0, | | | there is no limit for this | | | account's simultaneous connections. | +--------------------------------------+--------------------------------------+ | MAX_STATEMENT_TIME | Timeout, in seconds, for statements | | | executed by the user. See also | | | Aborting Statements that Exceed a | | | Certain Time to Execute. | +--------------------------------------+--------------------------------------+ If any of these limits are set to 0, then there is no limit for that resource for that user. Here is an example showing how to create a user with resource limits: CREATE USER 'someone'@'localhost' WITH MAX_USER_CONNECTIONS 10 MAX_QUERIES_PER_HOUR 200; The resources are tracked per account, which means 'user'@'server'; not per user name or per connection. The count can be reset for all users using FLUSH USER_RESOURCES, FLUSH PRIVILEGES or mariadb-admin reload. Per account resource limits are stored in the user table, in the mysql database. Columns used for resources limits are named max_questions, max_updates, max_connections (for MAX_CONNECTIONS_PER_HOUR), and max_user_connections (for MAX_USER_CONNECTIONS). Account Names ------------- Account names have both a user name component and a host name component, and are specified as 'user_name'@'host_name'. The user name and host name may be unquoted, quoted as strings using double quotes (") or single quotes ('), or quoted as identifiers using backticks (`). You must use quotes when using special characters (such as a hyphen) or wildcard characters. If you quote, you must quote the user name and host name separately (for example 'user_name'@'host_name'). Host Name Component ------------------- If the host name is not provided, it is assumed to be '%'. Host names may contain the wildcard characters % and _. They are matched as if by the LIKE clause. If you need to use a wildcard character literally (for example, to match a domain name with an underscore), prefix the character with a backslash. See LIKE for more information on escaping wildcard characters. Host name matches are case-insensitive. Host names can match either domain names or IP addresses. Use 'localhost' as the host name to allow only local client connections. On Linux, the loopback interface (127.0.0.1) will not match 'localhost' as it isj 3 not considered a local connection: this means that only connections via UNIX-domain sockets will match 'localhost'. You can use a netmask to match a range of IP addresses using 'base_ip/netmask' as the host name. A user with an IP address ip_addr will be allowed to connect if the following condition is true: ip_addr & netmask = base_ip For example, given a user: CREATE USER 'maria'@'247.150.130.0/255.255.255.0'; the IP addresses satisfying this condition range from 247.150.130.0 to 247.150.130.255. Using 255.255.255.255 is equivalent to not using a netmask at all. Netmasks cannot be used for IPv6 addresses. Note that the credentials added when creating a user with the '%' wildcard host will not grant access in all cases. For example, some systems come with an anonymous localhost user, and when connecting from localhost this will take precedence. Before MariaDB 10.6, the host name component could be up to 60 characters in length. Starting from MariaDB 10.6, it can be up to 255 characters. User Name Component ------------------- User names must match exactly, including case. A user name that is empty is known as an anonymous account and is allowed to match a login attempt with any user name component. These are described more in the next section. For valid identifiers to use as user names, see Identifier Names. It is possible for more than one account to match when a user connects. MariaDB selects the first matching account after sorting according to the following criteria: * Accounts with an exact host name are sorted before accounts using a wildcard in the host name. Host names using a netmask are considered to be exact for sorting. * Accounts with a wildcard in the host name are sorted according to the position of the first wildcard character. Those with a wildcard character later in the host name sort before those with a wildcard character earlier in the host name. * Accounts with a non-empty user name sort before accounts with an empty user name. * Accounts with an empty user name are sorted last. As mentioned previously, these are known as anonymous accounts. These are described more in the next section. The following table shows a list of example account as sorted by these criteria: +---------+-------------+ | User | Host | +---------+-------------+ | joffrey | 192.168.0.3 | | | 192.168.0.% | | joffrey | 192.168.% | | | 192.168.% | +---------+-------------+ Once connected, you only have the privileges granted to the account that matched, not all accounts that could have matched. For example, consider the following commands: CREATE USER 'joffrey'@'192.168.0.3'; CREATE USER 'joffrey'@'%'; GRANT SELECT ON test.t1 to 'joffrey'@'192.168.0.3'; GRANT SELECT ON test.t2 to 'joffrey'@'%'; If you connect as joffrey from 192.168.0.3, you will have the SELECT privilege on the table test.t1, but not on the table test.t2. If you connect as joffrey from any other IP address, you will have the SELECT privilege on the table test.t2, but not on the table test.t1. Usernames can be up to 80 characters long before 10.6 and starting from 10.6 it can be 128 characters long. Anonymous Accounts ------------------ Anonymous accounts are accounts where the user name portion of the account name is empty. These accounts act as special catch-all accounts. If a user attempts to log into the system from a host, and an anonymous account exists with a host name portion that matches the user's host, then the user will log in as the anonymous account if there is no more specific account match for the user name that the user entered. For example, here are some anonymous accounts: CREATE USER ''@'localhost'; CREATE USER ''@'192.168.0.3'; Fixing a Legacy Default Anonymous Account ----------------------------------------- On some systems, the mysql.db table has some entries for the ''@'%' anonymous account by default. Unfortunately, there is no matching entry in the mysql.user/mysql.global_priv_table table, which means that this anonymous account doesn't exactly exist, but it does have privileges--usually on the default test database created by mariadb-install-db. These account-less privileges are a legacy that is leftover from a time when MySQL's privilege system was less advanced. This situation means that you will run into errors if you try to create a ''@'%' account. For example: CREATE USER ''@'%'; ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%' The fix is to DELETE the row in the mysql.db table and then execute FLUSH PRIVILEGES: DELETE FROM mysql.db WHERE User='' AND Host='%'; FLUSH PRIVILEGES; And then the account can be created: CREATE USER ''@'%'; Query OK, 0 rows affected (0.01 sec) See MDEV-13486 for more information. Password Expiry --------------- MariaDB starting with 10.4.3 ---------------------------- Besides automatic password expiry, as determined by default_password_lifetime, password expiry times can be set on an individual user basis, overriding the global setting, for example: CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY; See User Password Expiry for more details. Account Locking --------------- MariaDB starting with 10.4.2 ---------------------------- Account locking permits privileged administrators to lock/unlock user accounts. No new client connections will be permitted if an account is locked (existing connections are not affected). For example: CREATE USER 'marijn'@'localhost' ACCOUNT LOCK; See Account Locking for more details. From MariaDB 10.4.7 and MariaDB 10.5.8, the lock_option and password_option clauses can occur in either order. URL: https://mariadb.com/kb/en/create-user/om/kb/en/alter-user/kb/en/alter-https://mariadb.com/kb/en/grant/https://mariadb.com/kb/en/authentication-from-mariadb-104/https://mariadb.com/kb/en/load-data-infile/https://mariadb.com/kb/en/useful-mariadb-queries/https://mariadb.com/kb/en/from_unixtime/https://mariadb.com/kb/en/str_to_date/https://mariadb.com/kb/en/alter-table/ll not be created. There will not be any error for the client, just a warning. * How to create a PRIMARY KEY that is automatically generated. * How to specify a table-specific character set and another for a column. * How to create an index (name) that is only partly indexed (to save space). The following clauses will work from MariaDB 10.2.1 only. CREATE TABLE t1( a int DEFAULT (1+1), b int DEFAULT (a+1), expires DATETIME DEFAULT(NOW() + INTERVAL 1 YEAR), x BLOB DEFAULT USER() ); URL: https://mariadb.com/kb/en/create-table/oo2@test IDENTIFIED VIA pam USING 'mariadb'; The exact meaning of the additional argument would depend on the specific authentication plugin. MariaDB starting with 10.4.0 ---------------------------- The USING or AS keyword can also be used to provide a plain-text password to a plugin if it's provided as an argument to the PASSWORD() function. This is only valid for authentication plugins that have implemented a hook for the PASSWORD() function. For example, the ed25519 authentication plugin supports this: CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret'); MariaDB starting with 10.4.3 ---------------------------- One can specify many authentication plugins, they all work as alternatives ways of authenticating a user: a&;&(1+}:] = 1OVSyntax ------ ALTER USER [IF EXISTS] user_specification [,user_specification] ... [REQUIRE {NONE | tls_option [[AND] tls_option] ...}] [WITH resource_option [resource_option] ...] [lock_option] [password_option] user_specification: username [authentication_option] authentication_option: IDENTIFIED BY 'password' | IDENTIFIED BY PASSWORD 'password_hash' | IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule] ... authentication_rule: authentication_plugin | authentication_plugin {USING|AS} 'authentication_string' | authentication_plugin {USING|AS} PASSWORD('password') tls_option SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject' resource_option MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count | MAX_STATEMENT_TIME time password_option: PASSWORD EXPIRE | PASSWORD EXPIRE DEFAULT | PASSWORD EXPIRE NEVER | PASSWORD EXPIRE INTERVAL N DAY lock_option: ACCOUNT LOCK | ACCOUNT UNLOCK } Description ----------- The ALTER USER statement modifies existing MariaDB accounts. To use it, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. The global SUPER privilege is also required if the read_only system variable is enabled. If any of the specified user accounts do not yet exist, an error results. If an error occurs, ALTER USER will still modify the accounts that do not result in an error. Only one error is produced for all users which have not been modified. IF EXISTS --------- When the IF EXISTS clause is used, MariaDB will return a warning instead of an error for each specified user that does not exist. Account Names ------------- For ALTER USER statements, account names are specified as the username argument in the same way as they are for CREATE USER statements. See account names from the CREATE USER page for details on how account names are specified. CURRENT_USER or CURRENT_USER() can also be used to alter the account logged into the current session. For example, to change the current user's password to mariadb: ALTER USER CURRENT_USER() IDENTIFIED BY 'mariadb'; Authentication Options ---------------------- MariaDB starting with 10.4 -------------------------- From MariaDB 10.4, it is possible to use more than one authentication plugin for each user account. For example, this can be useful to slowly migrate users to the more secure ed25519 authentication plugin over time, while allowing the old mysql_native_password authentication plugin as an alternative for the transitional period. See Authentication from MariaDB 10.4 for more. When running ALTER USER, not specifying an authentication option in the IDENTIFIED VIA clause will remove that authentication method. (However this was not the case before MariaDB 10.4.13, see MDEV-21928) For example, a user is created with the ability to authenticate via both a password and unix_socket: CREATE USER 'bob'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('pwd') OR unix_socket; SHOW CREATE USER 'bob'@'localhost'\G *************************** 1. row *************************** CREATE USER for bob@localhost: CREATE USER `bob`@`localhost` IDENTIFIED VIA mysql_native_password USING '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD' OR unix_socket If the user's password is updated, but unix_socket authentication is not specified in the IDENTIFIED VIA clause, unix_socket authentication will no longer be permitted. ALTER USER 'bob'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('pwd2'); SHOW CREATE USER 'bob'@'localhost'\G *************************** 1. row *************************** CREATE USER for bob@localhost: CREATE USER `bob`@`localhost` IDENTIFIED BY PASSWORD '*38366FDA01695B6A5A9DD4E428D9FB8F7EB75512' IDENTIFIED BY 'password' ------------------------ The optional IDENTIFIED BY clause can be used to provide an account with a password. The password should be specified in plain text. It will be hashed by the PASSWORD function prior to being stored to the mysql.user table. For example, if our password is mariadb, then we can set the account's password with: ALTER USER foo2@test IDENTIFIED BY 'mariadb'; If you do not specify a password with the IDENTIFIED BY clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set. The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password. IDENTIFIED BY PASSWORD 'password_hash' -------------------------------------- The optional IDENTIFIED BY PASSWORD clause can be used to provide an account with a password that has already been hashed. The password should be specified as a hash that was provided by the PASSWORD#function. It will be stored to the mysql.user table as-is. For example, if our password is mariadb, then we can find the hash with: SELECT PASSWORD('mariadb'); +-------------------------------------------+ | PASSWORD('mariadb') | +-------------------------------------------+ | *54958E764CE10E50764C2EECBB71D01F08549980 | +-------------------------------------------+ And then we can set an account's password with the hash: ALTER USER foo2@test IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980'; If you do not specify a password with the IDENTIFIED BY clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set. The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password. IDENTIFIED {VIA|WITH} authentication_plugin ------------------------------------------- The optional IDENTIFIED VIA authentication_plugin allows you to specify that the account should be authenticated by a specific authentication plugin. The plugin name must be an active authentication plugin as per SHOW PLUGINS. If it doesn't show up in that output, then you will need to install it with INSTALL PLUGIN or INSTALL SONAME. For example, this could be used with the PAM authentication plugin: ALTER USER foo2@test IDENTIFIED VIA pam; Some authentication plugins allow additional arguments to be specified after a USING or AS keyword. For example, the PAM authentication plugin accepts a service name: ALTER USER foo2@test IDENTIFIED VIA pam USING 'mariadb'; The exact meaning of the additional argument would depend on the specific authentication plugin. In MariaDB 10.4 and later, the USING or AS keyword can also be used to provide a plain-text password to a plugin if it's provided as an argument to the PASSWORD() function. This is only valid for authentication plugins that have implemented a hook for the PASSWORD() function. For example, the ed25519 authentication plugin supports this: ALTER USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret'); TLS Options ----------- By default, MariaDB transmits data between the server and clients without encrypting it. This is generally acceptable when the server and client run on the same host or in networks where security is guaranteed through other means. However, in cases where the server and client exist on separate networks or they are in a high-risk network, the lack of encryption does introduce security concerns as a malicious actor could potentially eavesdrop on the traffic as it is sent over the network between them. To mitigate this concern, MariaDB allows you to encrypt data in transit between the server and clients using the Transport Layer Security (TLS) protocol. TLS was formerly known as Secure Socket Layer (SSL), but strictly speaking the SSL protocol is a predecessor to TLS and, that version of the protocol is now considered insecure. The documentation still uses the term SSL often and for compatibility reasons TLS-related server system and status variables still use the prefix ssl_, but internally, MariaDB only supports its secure successors. See Secure Connections Overview for more informationb about how to determine whether your MariaDB server has TLS support. You can set certain TLS-related restrictions for specific user accounts. For instance, you might use this with user accounts that require access to sensitive data while sending it across networks that you do not control. These restrictions can be enabled for a user account with the CREATE USER, ALTER USER, or GRANT statements. The following options are available: +---------------------------+------------------------------------------------+ | Option | Description | +---------------------------+------------------------------------------------+ | REQUIRE NONE | TLS is not required for this account, but can | | | still be used. | +---------------------------+------------------------------------------------+ | REQUIRE SSL | The account must use TLS, but no valid X509 | | | certificate is required. This option cannot | | | be combined with other TLS options. | +---------------------------+------------------------------------------------+ | REQUIRE X509 | The account must use TLS and must have a | | | valid X509 certificate. This option implies | | | REQUIRE SSL. This option cannot be combined | | | with other TLS options. | +---------------------------+------------------------------------------------+ | REQUIRE ISSUER 'issuer' | The account must use TLS and must have a | | | valid X509 certificate. Also, the Certificate | | | Authority must be the one specified via the | | | string issuer. This option implies REQUIRE | | | X509. This option can be combined with the | | | SUBJECT, and CIPHER options in any order. | +---------------------------+------------------------------------------------+ | REQUIRE SUBJECT 'subject' | The account must use TLS and must have a | | | valid X509 certificate. Also, the | | | certificate's Subject must be the one | | | specified via the string subject. This option | | | implies REQUIRE X509. This option can be | | | combined with the ISSUER, and CIPHER options | | | in any order. | +---------------------------+------------------------------------------------+ | REQUIRE CIPHER 'cipher' | The account must use TLS, but no valid X509 | | | certificate is required. Also, the encryption | | | used for the connection must use a specific | | | cipher method specified in the string cipher. | | | This option implies REQUIRE SSL. This option | | | can be combined with the ISSUER, and SUBJECT | | | options in any order. | +---------------------------+------------------------------------------------+ The REQUIRE keyword must be used only once for all specified options, and the AND keyword can be used to separate individual options, but it is not required. For example, you can alter a user account to require these TLS options with the following: ALTER USER 'alice'@'%' REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland' AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/emailAddress=p.parker@marvel.com' AND CIPHER 'SHA-DES-CBC3-EDH-RSA'; If any of these options are set for a specific user account, then any client who tries to connect with that user account will have to be configured to connect with TLS. See Securing Connections for Client and Server for information on how to enable TLS on the client and server. Resource Limit Options ---------------------- It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account: +------------------------------------+---------------------------------------+ | Limit Type | Description | +------------------------------------+---------------------------------------+ | MAX_QUERIES_PER_HOUR | Number of statements that the | | | account can issue per hour | | | (including updates) | +------------------------------------+---------------------------------------+ | MAX_UPDATES_PER_HOUR | Number of updates (not queries) that | | | the account can issue per hour | +------------------------------------+---------------------------------------+ | MAX_CONNECTIONS_PER_HOUR | Number of connections that the | | | account can start per hour | +------------------------------------+---------------------------------------+ | MAX_USER_CONNECTIONS | Number of simultaneous connections | | | that can be accepted from the same | | | account; if it is 0, max_connections | | | will be used instead; if | | | max_connections is 0, there is no | | | limit for this account's | | | simultaneous connections. | +------------------------------------+---------------------------------------+ | MAX_STATEMENT_TIME | Timeout, in seconds, for statements | | | executed by the user. See also | | | Aborting Statements that Exceed a | | | Certain Time to Execute. | +------------------------------------+---------------------------------------+ If any of these limits are set to 0, then there is no limit for that resource for that user. Here is an example showing how to set an account's resource limits: ALTER USER 'someone'@'localhost' WITH MAX_USER_CONNECTIONS 10 MAX_QUERIES_PER_HOUR 200; The resources are tracked per account, which means 'user'@'server'; not per user name or per connection. The count can be reset for all users using FLUSH USER_RESOURCES, FLUSH PRIVILEGES or mysqladmin reload. Per account resource limits are stored in the user table, in the mysql database. Columns used for resources limits are named max_questions, max_updates, max_connections (for MAX_CONNECTIONS_PER_HOUR), and max_user_connections (for MAX_USER_CONNECTIONS). Password Expiry --------------- MariaDB starting with 10.4.3 ---------------------------- Besides automatic password expiry, as determined by default_password_lifetime, password expiry times can be set on an individual user basis, overriding the global setting, for example: ALTER USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY; ALTER USER 'monty'@'localhost' PASSWORD EXPIRE NEVER; ALTER USER 'monty'@'localhost' PASSWORD EXPIRE DEFAULT; See User Password Expiry for more details. Account Locking --------------- MariaDB starting with 10.4.2 ---------------------------- Account locking permits privileged administrators to lock/unlock user accounts. No new client connections will be permitted if an account is locked (existing connections are not affected). For example: ALTER USER 'marijn'@'localhost' ACCOUNT LOCK; See Account Locking for more details. From MariaDB 10.4.7 and MariaDB 10.5.8, the lock_option and password_option clauses can occur in either order. URL: https://mariadb.c7Syntax ------ GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [ user_options ...] user_specification: username [authentication_option] | PUBLIC authentication_option: IDENTIFIED BY 'password' | IDENTIFIED BY PASSWORD 'password_hash' | IDENTIFIED {VIA|WITH} authentication_rule [OR authentication_rule ...] authentication_rule: authentication_plugin | authentication_plugin {USING|AS} 'authentication_string' | authentication_plugin {USING|AS} PASSWORD('password') GRANT PROXY ON username TO user_specification [, user_specification ...] [WITH GRANT OPTION] GRANT rolename TO grantee [, grantee ...] [WITH ADMIN OPTION] grantee: rolename username [authentication_option] user_options: [REQUIRE {NONE | tls_option [[AND] tls_option] ...}] [WITH with_option [with_option] ...] object_type: TABLE | FUNCTION | PROCEDURE | PACKAGE priv_level: * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name with_option: GRANT OPTION | resource_option resource_option: MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count | MAX_STATEMENT_TIME time tls_option: SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject' Description ----------- The GRANT statement allows you to grant privileges or roles to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. Use the REVOKE statement to revoke privileges granted with the GRANT statement. Use the SHOW GRANTS statement to determine what privileges an account has. Account Names ------------- For GRANT statements, account names are specified as the username argument in the same way as they are for CREATE USER statements. See account names from the CREATE USER page for details on how account names are specified. Implicit Account Creation ------------------------- The GRANT statement also allows you to implicitly create accounts in some cases. If the account does not yet exist, then GRANT can implicitly create it. To implicitly create an account with GRANT, a user is required to have the same privileges that would be required to explicitly create the account with the CREATE USER statement. If the NO_AUTO_CREATE_USER SQL_MODE is set, then accounts can only be created if authentication information is specified, or with a CREATE USER statement. If no authentication information is provided, GRANT will produce an error when the specified account does not exist, for example: show variables like '%sql_mode%' ; +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+--------------------------------------------+ GRANT USAGE ON *.* TO 'user123'@'%' IDENTIFIED BY ''; ERROR 1133 (28000): Can't find any matching row in the user table GRANT USAGE ON *.* TO 'user123'@'%' IDENTIFIED VIA PAM using 'mariadb' require ssl ; Query OK, 0 rows affected (0.00 sec) select host, user from mysql.user where user='user123' ; +------+----------+ | host | user | +------+----------+ | % | user123 | +------+----------+ Privilege Levels ---------------- Privileges can be set globally, for an entire database, for a table or routine, or for individual columns in a table. Certain privileges can only be set at certain levels. Global privileges do not take effect immediately and are only applied to connections created after the GRANT statement was executed. * Global privileges priv_type are granted using *.* for priv_level. Global privileges include privileges to administer the database and manage user accounts, as well as privileges for all tables, functions, and procedures. Global privileges are stored in the mysql.user table prior to MariaDB 10.4, and in mysql.global_priv table afterwards. * Database privileges priv_type are granted using db_name.* for priv_level, or using just * to use default database. Database privileges include privileges to create tables and functions, as well as privileges for all tables, functions, and procedures in the database. Database privileges are stored in the mysql.db table. * Table privileges priv_type are granted using db_name.tbl_name for priv_level, or using just tbl_name to specify a table in the default database. The TABLE keyword is optional. Table privileges include the ability to select and change data in the table. Certain table privileges can be granted for individual columns. * Column privileges priv_type are granted by specifying a table for priv_level and providing a column list after the privilege type. They allow you to control exactly which columns in a table users can select and change. * Function privileges priv_type are granted using FUNCTION db_name.routine_name for priv_level, or using just FUNCTION routine_name to specify a function in the default database. * Procedure privileges priv_type are granted using PROCEDURE db_name.routine_name for priv_level, or using just PROCEDURE routine_name to specify a procedure in the default database. The USAGE Privilege ------------------- The USAGE privilege grants no real privileges. The SHOW GRANTS statement will show a global USAGE privilege for a newly-created user. You can use USAGE with the GRANT statement to change options like GRANT OPTION and MAX_USER_CONNECTIONS without changing any account privileges. The ALL PRIVILEGES Privilege ---------------------------- The ALL PRIVILEGES privilege grants all available privileges. Granting all privileges only affects the given privilege level. For example, granting all privileges on a table does not grant any privileges on the database or globally. Using ALL PRIVILEGES does not grant the special GRANT OPTION privilege. You can use ALL instead of ALL PRIVILEGES. The GRANT OPTION Privilege -------------------------- Use the WITH GRANT OPTION clause to give users the ability to grant privileges to other users at the given privilege level. Users with the GRANT OPTION privilege can only grant privileges they have. They cannot grant privileges at a higher privilege level than they have the GRANT OPTION privilege. The GRANT OPTION privilege cannot be set for individual columns. If you use WITH GRANT OPTION when specifying column privileges, the GRANT OPTION privilege will be granted for the entire table. Using the WITH GRANT OPTION clause is equivalent to listing GRANT OPTION as a privilege. Global Privileges ----------------- The following table lists the privileges that can be granted globally. You can also grant all database, table, and function privileges globally. When granted globally, these privileges apply to all databases, tables, or functions, including those created later. To set a global privilege, use *.* for priv_level. BINLOG ADMIN ------------ Enables administration of the binary log, including the PURGE BINARY LOGS statement and setting the system variables: * binlog_annotate_row_events * binlog_cache_size * binlog_commit_wait_count * binlog_commit_wait_usec * binlog_direct_non_transactional_updates * binlog_expire_logs_seconds * binlog_file_cache_size * binlog_format * binlog_row_image * binlog_row_metadata * binlog_stmt_cache_size * expire_logs_days * log_bin_compress * log_bin_compress_min_len * log_bin_trust_function_creators * max_binlog_cache_size * max_binlog_size * max_binlog_stmt_cache_size * sql_log_bin and * sync_binlog. Added in MariaDB 10.5.2. BINLOG MONITOR -------------- New name for REPLICATION CLIENT from MariaDB 10.5.2, (REPLICATION CLIENT still supported as an alias for compatibility purposes). Permits running SHOW commands related to the binary log, in particular the SHOW BINLOG STATUS and SHOW BINARY LOGS statements. Unlike REPLICATION CLIENT prior to MariaDB 10.5, SHOW REPLICA STATUS isn't included in this privilege, and REPLICA MONITOR is required. BINLOG REPLAY ------------- Enables replaying the binary log with the BINLOG sta4tement (generated by mariadb-binlog), executing SET timestamp when secure_timestamp is set to replication, and setting the session values of system variables usually included in BINLOG output, in particular: * gtid_domain_id * gtid_seq_no * pseudo_thread_id * server_id. Added in MariaDB 10.5.2 CONNECTION ADMIN ---------------- Enables administering connection resource limit options. This includes ignoring the limits specified by: * max_connections * max_user_connections and * max_password_errors. The statements specified in init_connect are not executed, killing connections and queries owned by other users is permitted. The following connection-related system variables can be changed: * connect_timeout * disconnect_on_expired_password * extra_max_connections * init_connect * max_connections * max_connect_errors * max_password_errors * proxy_protocol_networks * secure_auth * slow_launch_time * thread_pool_exact_stats * thread_pool_dedicated_listener * thread_pool_idle_timeout * thread_pool_max_threads * thread_pool_min_threads * thread_pool_oversubscribe * thread_pool_prio_kickup_timer * thread_pool_priority * thread_pool_size, and * thread_pool_stall_limit. Added in MariaDB 10.5.2. CREATE USER ----------- Create a user using the CREATE USER statement, or implicitly create a user with the GRANT statement. FEDERATED ADMIN --------------- Execute CREATE SERVER, ALTER SERVER, and DROP SERVER statements. Added in MariaDB 10.5.2. FILE ---- Read and write files on the server, using statements like LOAD DATA INFILE or functions like LOAD_FILE(). Also needed to create CONNECT outward tables. MariaDB server must have the permissions to access those files. GRANT OPTION ------------ Grant global privileges. You can only grant privileges that you have. PROCESS ------- Show information about the active processes, for example via SHOW PROCESSLIST or mariadb-admin processlist. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MariaDB account that you are using). READ_ONLY ADMIN --------------- User can set the read_only system variable and allows the user to perform write operations, even when the read_only option is active. Added in MariaDB 10.5.2. From MariaDB 10.11.0, the READ_ONLY ADMIN privilege has been removed from SUPER. The benefit of this is that one can remove the READ_ONLY ADMIN privilege from all users and ensure that no one can make any changes on any non-temporary tables. This is useful on replicas when one wants to ensure that the replica is kept identical to the primary. RELOAD ------ Execute FLUSH statements or equivalent mariadb-admin commands. REPLICATION CLIENT ------------------ Execute SHOW MASTER STATUS and SHOW BINARY LOGS informative statements. Renamed to BINLOG MONITOR in MariaDB 10.5.2 (but still supported as an alias for compatibility reasons). SHOW SLAVE STATUS was part of REPLICATION CLIENT prior to MariaDB 10.5. REPLICATION MASTER ADMIN ------------------------ Permits administration of primary servers, including the SHOW REPLICA HOSTS statement, and setting the gtid_binlog_state, gtid_domain_id, master_verify_checksum and server_id system variables. Added in MariaDB 10.5.2. REPLICA MONITOR --------------- Permit SHOW REPLICA STATUS and SHOW RELAYLOG EVENTS. From MariaDB 10.5.9. When a user would upgrade from an older major release to a MariaDB 10.5 minor release prior to MariaDB 10.5.9, certain user accounts would lose capabilities. For example, a user account that had the REPLICATION CLIENT privilege in older major releases could run SHOW REPLICA STATUS, but after upgrading to a MariaDB 10.5 minor release prior to MariaDB 10.5.9, they could no longer run SHOW REPLICA STATUS, because that statement was changed to require the REPLICATION REPLICA ADMIN privilege. This issue is fixed in MariaDB 10.5.9 with this new privilege, which now grants the user the ability to execute SHOW [ALL] (SLAVE | REPLICA) STATUS. When a database is upgraded from an older major release to MariaDB Server 10.5.9 or later, any user accounts with the REPLICATION CLIENT or REPLICATION SLAVE privileges will automatically be granted the new REPLICA MONITOR privilege. The privilege fix occurs when the server is started up, not when mariadb-upgrade is performed. However, when a database is upgraded from an early 10.5 minor release to 10.5.9 and later, the user will have to fix any user account privileges manually. REPLICATION REPLICA ------------------- Synonym for REPLICATION SLAVE. From MariaDB 10.5.1. REPLICATION SLAVE ----------------- Accounts used by replica servers on the primary need this privilege. This is needed to get the updates made on the master. From MariaDB 10.5.1, REPLICATION REPLICA is an alias for REPLICATION SLAVE. REPLICATION SLAVE ADMIN ----------------------- Permits administering replica servers, including START REPLICA/SLAVE, STOP REPLICA/SLAVE, CHANGE MASTER, SHOW REPLICA/SLAVE STATUS, SHOW RELAYLOG EVENTS statements, replaying the binary log with the BINLOG statement (generated by mariadb-binlog), and setting the system variables: * gtid_cleanup_batch_size * gtid_ignore_duplicates * gtid_pos_auto_engines * gtid_slave_pos * gtid_strict_mode * init_slave * read_binlog_speed_limit * relay_log_purge * relay_log_recovery * replicate_do_db * replicate_do_table * replicate_events_marked_for_skip * replicate_ignore_db * replicate_ignore_table * replicate_wild_do_table * replicate_wild_ignore_table * slave_compressed_protocol * slave_ddl_exec_mode * slave_domain_parallel_threads * slave_exec_mode * slave_max_allowed_packet * slave_net_timeout * slave_parallel_max_queued * slave_parallel_mode * slave_parallel_threads * slave_parallel_workers * slave_run_triggers_for_rbr * slave_sql_verify_checksum * slave_transaction_retry_interval * slave_type_conversions * sync_master_info * sync_relay_log, and * sync_relay_log_info. Added in MariaDB 10.5.2. SET USER -------- Enables setting the DEFINER when creating triggers, views, stored functions and stored procedures. Added in MariaDB 10.5.2. SHOW DATABASES -------------- List all databases using the SHOW DATABASES statement. Without the SHOW DATABASES privilege, you can still issue the SHOW DATABASES statement, but it will only list databases containing tables on which you have privileges. SHUTDOWN -------- Shut down the server using SHUTDOWN or the mariadb-admin shutdown command. SUPER ----- Execute superuser statements: CHANGE MASTER TO, KILL (users who do not have this privilege can only KILL their own threads), PURGE LOGS, SET global system variables, or the mariadb-admin debug command. Also, this permission allows the user to write data even if the read_only startup option is set, enable or disable logging, enable or disable replication on replica, specify a DEFINER for statements that support that clause, connect once reaching the MAX_CONNECTIONS. If a statement has been specified for the init-connect mysqld option, that command will not be executed when a user with SUPER privileges connects to the server. The SUPER privilege has been split into multiple smaller privileges from MariaDB 10.5.2 to allow for more fine-grained privileges (MDEV-21743). The privileges are: * SET USER * FEDERATED ADMIN * CONNECTION ADMIN * REPLICATION SLAVE ADMIN * BINLOG ADMIN * BINLOG REPLAY * REPLICA MONITOR * BINLOG MONITOR * REPLICATION MASTER ADMIN * READ_ONLY ADMIN However, the smaller privileges are still a part of the SUPER grant in MariaDB 10.5.2. From MariaDB 11.0.1 onwards, these grants are no longer a part of SUPER and need to be granted separately (MDEV-29668). From MariaDB 10.11.0, the READ_ONLY ADMIN privilege has been removed from SUPER. The benefit of this is that one can remove the READ_ONLY ADMIN privilege from all users and ensure that no one can make any changes on any non-temporary tables. This is useful on replicas when one wants to ensure that the replica is kept identical to the primary (MDEV-29596). Database Privileges ------------------- The following table lists the privileges that can be granted at the database level. You can also grant all table and function privileges at the database level. Table and function privileges on a database apply to all tables or functions in that database, including those created later. To set a privilege for a database, specify the database using db_name.* for priv_level, or just use * to specify the default database. +----------------------------------+-----------------------------------------+ | Privilege | Description | +----------------------------------+-----------------------------------------+ | CREATE | Create a database using the CREATE | | | DATABASE statement, when the privilege | | | is granted for a database. You can | | | grant the CREATE privilege on | | | databases that do not yet exist. This | | | also grants the CREATE privilege on | | | all tables in the database. | +----------------------------------+-----------------------------------------+ | CREATE ROUTINE | Create Stored Programs using the | | | CREATE PROCEDURE and CREATE FUNCTION | | | statements. | +----------------------------------+-----------------------------------------+ | CREATE TEMPORARY TABLES | Create temporary tables with the | | | CREATE TEMPORARY TABLE statement. This | | | privilege enable writing and dropping | | | those temporary tables | +----------------------------------+-----------------------------------------+ | DROP | Drop a database using the DROP | | | DATABASE statement, when the privilege | | | is granted for a database. This also | | | grants the DROP privilege on all | | | tables in the database. | +----------------------------------+-----------------------------------------+ | EVENT | Create, drop and alter EVENTs. | +----------------------------------+-----------------------------------------+ | GRANT OPTION | Grant database privileges. You can | | | only grant privileges that you have. | +----------------------------------+-----------------------------------------+ | LOCK TABLES | Acquire explicit locks using the LOCK | | | TABLES statement; you also need to | | | have the SELECT privilege on a table, | | | in order to lock it. | +----------------------------------+-----------------------------------------+ Table Privileges ---------------- +----------------------------------+-----------------------------------------+ | Privilege | Description | +----------------------------------+-----------------------------------------+ | ALTER | Change the structure of an existing | | | table using the ALTER TABLE statement. | +----------------------------------+-----------------------------------------+ | CREATE | Create a table using the CREATE TABLE | | | statement. You can grant the CREATE | | | privilege on tables that do not yet | | | exist. | +----------------------------------+-----------------------------------------+ | CREATE VIEW | Create a view using the CREATE_VIEW | | | statement. | +----------------------------------+-----------------------------------------+ | DELETE | Remove rows from a table using the | | | DELETE statement. | +----------------------------------+-----------------------------------------+ | DELETE HISTORY | Remove historical rows from a table | | | using the DELETE HISTORY statement. | | | Displays as DELETE VERSIONING ROWS | | | when running SHOW GRANTS until MariaDB | | | 10.3.15 and until MariaDB 10.4.5 | | | (MDEV-17655), or when running SHOW | | | PRIVILEGES until MariaDB 10.5.2, | | | MariaDB 10.4.13 and MariaDB 10.3.23 | | | (MDEV-20382). From MariaDB 10.3.4. | | | From MariaDB 10.3.5, if a user has the | | | SUPER privilege but not this | | | privilege, running mariadb-upgrade | | | will grant this privilege as well. | +----------------------------------+-----------------------------------------+ | DROP | Drop a table using the DROP TABLE | | | statement or a view using the DROP | | | VIEW statement. Also required to | | | execute the TRUNCATE TABLE statement. | +----------------------------------+-----------------------------------------+ | GRANT OPTION | Grant table privileges. You can only | | | grant privileges that you have. | +----------------------------------+-----------------------------------------+ | INDEX | Create an index on a table using the | | | CREATE INDEX statement. Without the | | | INDEX privilege, you can still create | | | indexes when creating a table using | | | the CREATE TABLE statement if the you | | | have the CREATE privilege, and you can | | | create indexes using the ALTER TABLE | | | statement if you have the ALTER | | | privilege. | +----------------------------------+-----------------------------------------+ | INSERT | Add rows to a table using the INSERT | | | statement. The INSERT privilege can | | | also be set on individual columns; see | | | Column Privileges below for details. | +----------------------------------+-----------------------------------------+ | REFERENCES | Unused. | +----------------------------------+-----------------------------------------+ | SELECT | Read data from a table using the | | | SELECT statement. The SELECT privilege | | | can also be set on individual columns; | | | see Column Privileges below for | | | details. | +----------------------------------+-----------------------------------------+ | SHOW VIEW | Show the CREATE VIEW statement tHjo | | | create a view using the SHOW CREATE | | | VIEW statement. | +----------------------------------+-----------------------------------------+ | TRIGGER | Execute triggers associated to tables | | | you update, execute the CREATE | | | TRIGGER, DROP TRIGGER, and SHOW CREATE | | | TRIGGER statements. | +----------------------------------+-----------------------------------------+ | UPDATE | Update existing rows in a table using | | | the UPDATE statement. UPDATE | | | statements usually include a WHERE | | | clause to update only certain rows. | | | You must have SELECT privileges on the | | | table or the appropriate columns for | | | the WHERE clause. The UPDATE privilege | | | can also be set on individual columns; | | | see Column Privileges below for | | | details. | +----------------------------------+-----------------------------------------+ Column Privileges ----------------- Some table privileges can be set for individual columns of a table. To use column privileges, specify the table explicitly and provide a list of column names after the privilege type. For example, the following statement would allow the user to read the names and positions of employees, but not other information from the same table, such as salaries. GRANT SELECT (name, position) on Employee to 'jeffrey'@'localhost'; +----------------------------------+-----------------------------------------+ | Privilege | Description | +----------------------------------+-----------------------------------------+ | INSERT (column_list) | Add rows specifying values in columns | | | using the INSERT statement. If you | | | only have column-level INSERT | | | privileges, you must specify the | | | columns you are setting in the INSERT | | | statement. All other columns will be | | | set to their default values, or NULL. | +----------------------------------+-----------------------------------------+ | REFERENCES (column_list) | Unused. | +----------------------------------+-----------------------------------------+ | SELECT (column_list) | Read values in columns using the | | | SELECT statement. You cannot access or | | | query any columns for which you do not | | | have SELECT privileges, including in | | | WHERE, ON, GROUP BY, and ORDER BY | | | clauses. | +----------------------------------+-----------------------------------------+ | UPDATE (column_list) | Update values in columns of existing | | | rows using the UPDATE statement. | | | UPDATE statements usually include a | | | WHERE clause to update only certain | | | rows. You must have SELECT privileges | | | on the table or the appropriate | | | columns for the WHERE clause. | +----------------------------------+-----------------------------------------+ Function Privileges ------------------- +----------------------------------+-----------------------------------------+ | Privilege | Description | +----------------------------------+-----------------------------------------+ | ALTER ROUTINE | Change the characteristics of a stored | | | function using the ALTER FUNCTION | | | statement. | +----------------------------------+-----------------------------------------+ | EXECUTE | Use a stored function. You need SELECT | | | privileges for any tables or columns | | | accessed by the function. | +----------------------------------+-----------------------------------------+ | GRANT OPTION | Grant function privileges. You can | | | only grant privileges that you have. | +----------------------------------+-----------------------------------------+ Procedure Privileges -------------------- +----------------------------------+-----------------------------------------+ | Privilege | Description | +----------------------------------+-----------------------------------------+ | ALTER ROUTINE | Change the characteristics of a stored | | | procedure using the ALTER PROCEDURE | | | statement. | +----------------------------------+-----------------------------------------+ | EXECUTE | Execute a stored procedure using the | | | CALL statement. The privilege to call | | | a procedure may allow you to perform | | | actions you wouldn't otherwise be able | | | to do, such as insert rows into a | | | table. | +----------------------------------+-----------------------------------------+ | GRANT OPTION | Grant procedure privileges. You can | | | only grant privileges that you have. | +----------------------------------+-----------------------------------------+ GRANT EXECUTE ON PROCEDURE mysql.create_db TO maintainer; Proxy Privileges ---------------- +----------------------------------+-----------------------------------------+ | Privilege | Description | +----------------------------------+-----------------------------------------+ | PROXY | Permits one user to be a proxy for | | | another. | +----------------------------------+-----------------------------------------+ The PROXY privilege allows one user to proxy as another user, which means their privileges change to that of the proxy user, and the CURRENT_USER() function returns the user name of the proxy user. The PROXY privilege only works with authentication plugins that support it. The default mysql_native_password authentication plugin does not support proxy users. The pam authentication plugin is the only plugin included with MariaDB that currently supports proxy users. The PROXY privilege is commonly used with the pam authentication plugin to enable user and group mapping with PAM. For example, to grant the PROXY privilege to an anonymous account that authenticates with the pam authentication plugin, you could execute the following: CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword'; GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' ; CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb86Է'; GRANT PROXY ON 'dba'@'%' TO ''@'%'; A user account can only grant the PROXY privilege for a specific user account if the granter also has the PROXY privilege for that specific user account, and if that privilege is defined WITH GRANT OPTION. For example, the following example fails because the granter does not have the PROXY privilege for that specific user account at all: SELECT USER(), CURRENT_USER(); +-----------------+-----------------+ | USER() | CURRENT_USER() | +-----------------+-----------------+ | alice@localhost | alice@localhost | +-----------------+-----------------+ SHOW GRANTS; +------------------------------------------------------------------------------ ----------------------------------------+ | Grants for alice@localhost | +------------------------------------------------------------------------------ ----------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' | +------------------------------------------------------------------------------ ----------------------------------------+ GRANT PROXY ON 'dba'@'localhost' TO 'bob'@'localhost'; ERROR 1698 (28000): Access denied for user 'alice'@'localhost' And the following example fails because the granter does have the PROXY privilege for that specific user account, but it is not defined WITH GRANT OPTION: SELECT USER(), CURRENT_USER(); +-----------------+-----------------+ | USER() | CURRENT_USER() | +-----------------+-----------------+ | alice@localhost | alice@localhost | +-----------------+-----------------+ SHOW GRANTS; +------------------------------------------------------------------------------ ----------------------------------------+ | Grants for alice@localhost | +------------------------------------------------------------------------------ ----------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' | | GRANT PROXY ON 'dba'@'localhost' TO 'alice'@'localhost' | +------------------------------------------------------------------------------ ----------------------------------------+ GRANT PROXY ON 'dba'@'localhost' TO 'bob'@'localhost'; ERROR 1698 (28000): Access denied for user 'alice'@'localhost' But the following example succeeds because the granter does have the PROXY privilege for that specific user account, and it is defined WITH GRANT OPTION: SELECT USER(), CURRENT_USER(); +-----------------+-----------------+ | USER() | CURRENT_USER() | +-----------------+-----------------+ | alice@localhost | alice@localhost | +-----------------+-----------------+ SHOW GRANTS; +------------------------------------------------------------------------------ ----------------------------------------------------------+ | Grants for alice@localhost | +------------------------------------------------------------------------------ ----------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WITH GRANT OPTION | | GRANT PROXY ON 'dba'@'localhost' TO 'alice'@'localhost' WITH GRANT OPTION | +------------------------------------------------------------------------------ ----------------------------------------------------------+ GRANT PROXY ON 'dba'@'localhost' TO 'bob'@'localhost'; A user account can grant the PROXY privilege for any other user account if the granter has the PROXY privilege for the ''@'%' anonymous user account, like this: GRANT PROXY ON ''@'%' TO 'dba'@'localhost' WITH GRANT OPTION; For example, the following example succeeds because the user can grant the PROXY privilege for any other user account: SELECT USER(), CURRENT_USER(); +-----------------+-----------------+ | USER() | CURRENT_USER() | +-----------------+-----------------+ | alice@localhost | alice@localhost | +-----------------+-----------------+ SHOW GRANTS; +------------------------------------------------------------------------------ ----------------------------------------------------------+ | Grants for alice@localhost | +------------------------------------------------------------------------------ ----------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'alice'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WITH GRANT OPTION | | GRANT PROXY ON ''@'%' TO 'alice'@'localhost' WITH GRANT OPTION | +------------------------------------------------------------------------------ ----------------------------------------------------------+ GRANT PROXY ON 'app1_dba'@'localhost' TO 'bob'@'localhost'; Query OK, 0 rows affected (0.004 sec) GRANT PROXY ON 'app2_dba'@'localhost' TO 'carol'@'localhost'; Query OK, 0 rows affected (0.004 sec) The default root user accounts created by mariadb-install-db have this privilege. For example: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION; This allows the default root user accounts to grant the PROXY privilege for any other user account, and it also allows the default root user accounts to grant others the privilege to do the same. Authentication Options ---------------------- The authentication options for the GRANT statement are the same as those for the CREATE USER statement. IDENTIFIED BY 'password' ------------------------ The optional IDENTIFIED BY clause can be used to provide an account with a password. The password should be specified in plain text. It will be hashed by the PASSWORD function prior to being stored. For example, if our password is mariadb, then we can create the user with: GRANT USAGE ON *.* TO foo2@test IDENTIFIED BY 'mariadb'; If you do not specify a password with the IDENTIFIED BY clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set. If the user account already exists and if you provide the IDENTIFIED BY clause, then the user's password will be changed. You must have the privileges needed for the SET PASSWORD statement to change a user's password with GRANT. The only authentication plugins that this clause supports are mysql_native_password and mysql_old_password. IDENTIFIED BY PASSWORD 'password_hash' -------------------------------------- The optional IDENTIFIED BY PASSWORD clause can be used to provide an account with a password that has already been hashed. The password should be specified as a hash that was provided by the PASSWORD function. It will be stored as-is. For example, if our password is mariadb, then we can find the hash with: SELECT PASSWORD('mariadb'); +-------------------------------------------+ | PASSWORD('mariadb') | +-------------------------------------------+ | *54958E764CE10E50764C2EECBB71D01F08549980 | +-------------------------------------------+ 1 row in set (0.00 sec) And then we can create a user with the hash: GRANT USAGE ON *.* TO foo2@test IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980'; If you do not specify a password with the IDENTIFIED BY clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set. If the user account already exists and if you provide the IDENTIFIED BY clause, then the user's password will be changed. You must have the privileges needed for the SET PASSWORD statement to change a user's password with GRANT. The only authkx,entication plugins that this clause supports are mysql_native_password and mysql_old_password. IDENTIFIED {VIA|WITH} authentication_plugin ------------------------------------------- The optional IDENTIFIED VIA authentication_plugin allows you to specify that the account should be authenticated by a specific authentication plugin. The plugin name must be an active authentication plugin as per SHOW PLUGINS. If it doesn't show up in that output, then you will need to install it with INSTALL PLUGIN or INSTALL SONAME. For example, this could be used with the PAM authentication plugin: GRANT USAGE ON *.* TO foo2@test IDENTIFIED VIA pam; Some authentication plugins allow additional arguments to be specified after a USING or AS keyword. For example, the PAM authentication plugin accepts a service name: GRANT USAGE ON *.* TO foo2@test IDENTIFIED VIA pam USING 'mariadb'; The exact meaning of the additional argument would depend on the specific authentication plugin. MariaDB starting with 10.4.0 ---------------------------- The USING or AS keyword can also be used to provide a plain-text password to a plugin if it's provided as an argument to the PASSWORD() function. This is only valid for authentication plugins that have implemented a hook for the PASSWORD() function. For example, the ed25519 authentication plugin supports this: CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret'); MariaDB starting with 10.4.3 ---------------------------- One can specify many authentication plugins, they all work as alternatives ways of authenticating a user: CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket; By default, when you create a user without specifying an authentication plugin, MariaDB uses the mysql_native_password plugin. Resource Limit Options ---------------------- It is possible to set per-account limits for certain server resources. The following table shows the values that can be set per account: +--------------------------------------+--------------------------------------+ | Limit Type | Decription | +--------------------------------------+--------------------------------------+ | MAX_QUERIES_PER_HOUR | Number of statements that the | | | account can issue per hour | | | (including updates) | +--------------------------------------+--------------------------------------+ | MAX_UPDATES_PER_HOUR | Number of updates (not queries) | | | that the account can issue per hour | +--------------------------------------+--------------------------------------+ | MAX_CONNECTIONS_PER_HOUR | Number of connections that the | | | account can start per hour | +--------------------------------------+--------------------------------------+ | MAX_USER_CONNECTIONS | Number of simultaneous connections | | | that can be accepted from the same | | | account; if it is 0, | | | max_connections will be used | | | instead; if max_connections is 0, | | | there is no limit for this | | | account's simultaneous connections. | +--------------------------------------+--------------------------------------+ | MAX_STATEMENT_TIME | Timeout, in seconds, for statements | | | executed by the user. See also | | | Aborting Statements that Exceed a | | | Certain Time to Execute. | +--------------------------------------+--------------------------------------+ If any of these limits are set to 0, then there is no limit for that resource for that user. To set resource limits for an account, if you do not want to change that account's privileges, you can issue a GRANT statement with the USAGE privilege, which has no meaning. The statement can name some or all limit types, in any order. Here is an example showing how to set resource limits: GRANT USAGE ON *.* TO 'someone'@'localhost' WITH MAX_USER_CONNECTIONS 0 MAX_QUERIES_PER_HOUR 200; The resources are tracked per account, which means 'user'@'server'; not per user name or per connection. The count can be reset for all users using FLUSH USER_RESOURCES, FLUSH PRIVILEGES or mariadb-admin reload. Users with the CONNECTION ADMIN privilege (in MariaDB 10.5.2 and later) or the SUPER privilege are not restricted by max_user_connections, max_connections, or max_password_errors. Per account resource limits are stored in the user table, in the mysql database. Columns used for resources limits are named max_questions, max_updates, max_connections (for MAX_CONNECTIONS_PER_HOUR), and max_user_connections (for MAX_USER_CONNECTIONS). TLS Options ----------- By default, MariaDB transmits data between the server and clients without encrypting it. This is generally acceptable when the server and client run on the same host or in networks where security is guaranteed through other means. However, in cases where the server and client exist on separate networks or they are in a high-risk network, the lack of encryption does introduce security concerns as a malicious actor could potentially eavesdrop on the traffic as it is sent over the network between them. To mitigate this concern, MariaDB allows you to encrypt data in transit between the server and clients using the Transport Layer Security (TLS) protocol. TLS was formerly known as Secure Socket Layer (SSL), but strictly speaking the SSL protocol is a predecessor to TLS and, that version of the protocol is now considered insecure. The documentation still uses the term SSL often and for compatibility reasons TLS-related server system and status variables still use the prefix ssl_, but internally, MariaDB only supports its secure successors. See Secure Connections Overview for more information about how to determine whether your MariaDB server has TLS support. You can set certain TLS-related restrictions for specific user accounts. For instance, you might use this with user accounts that require access to sensitive data while sending it across networks that you do not control. These restrictions can be enabled for a user account with the CREATE USER, ALTER USER, or GRANT statements. The following options are available: +---------------------------+------------------------------------------------+ | Option | Description | +---------------------------+------------------------------------------------+ | REQUIRE NONE | TLS is not required for this account, but can | | | still be used. | +---------------------------+------------------------------------------------+ | REQUIRE SSL | The account must use TLS, but no valid X509 | | | certificate is required. This option cannot | | | be combined with other TLS options. | +---------------------------+------------------------------------------------+ | REQUIRE X509 | The account must use TLS and must have a | | | valid X509 certificate. This option implies | | | REQUIRE SSL. This option cannot be combined | | | with other TLS options. | +---------------------------+------------------------------------------------+ | REQUIRE ISSUER 'issuer' | The account must use TLS and must have a | | | valid X509 certificate. Also, the Certificate | | | Authority must be the one speeo "' SET PASSWORDSyntax ------ SET PASSWORD [FOR user] = { PASSWORD('some password') | OLD_PASSWORD('some password') | 'encrypted password' } Description ----------- The SET PASSWORD statement assigns a password to an existing MariaDB user account. If the password is specified using the PASSWORD() or OLD_PASSWORD() function, the literal text of the password should be given. If the password is specified without using either function, the password should be the already-encrypted password value as returned by PASSWORD(). OLD_PASSWORD() should only be used if your MariaDB/MySQL clients are very old (< 4.0.0). With no FOR clause, this statement sets the password for the current user. Any client that has connected to the server using a non-anonymous account can change the password for that account. With a FOR clause, this statement sets the password for a specific account on the current server host. Only clients that have the UPDATE privilege for the mysql database can do this. The user value should be given in user_name@host_name format, where user_name and host_name are exactly as they are listed in the User and Host columns of the mysql.user table (or view in MariaDB-10.4 onwards) entry. The argument to PASSWORD() and the password given to MariaDB clients can be of arbitrary length. Authentication Plugin Support ----------------------------- MariaDB starting with 10.4 -------------------------- In MariaDB 10.4 and later, SET PASSWORD (with or without PASSWORD()) works for accounts authenticated via any authentication plugin that supports passwords stored in the mysql.global_priv table. The ed25519, mysql_native_password, and mysql_old_password authentication plugins store passwords in the mysql.global_priv table. If you run SET PASSWORD on an account that authenticates with one of these authentication plugins that stores passwords in the mysql.global_priv table, then the PASSWORD() function is evaluated by the specific authentication plugin used by the account. The authentication plugin hashes the password with a method that is compatible with that specific authentication plugin. The unix_socket, named_pipe, gssapi, and pam authentication plugins do not store passwords in the mysql.global_priv table. These authentication plugins rely on other methods to authenticate the user. If you attempt to run SET PASSWORD on an account that authenticates with one of these authentication plugins that doesn't store a password in the mysql.global_priv table, then MariaDB Server will raise a warning like the following: SET PASSWORD is ignored for users authenticating via unix_socket plugin See Authentication from MariaDB 10.4 for an overview of authentication changes in MariaDB 10.4. MariaDB until 10.3 ------------------ In MariaDB 10.3 and before, SET PASSWORD (with or without PASSWORD()) only works for accounts authenticated via mysql_native_password or mysql_old_password authentication plugins Passwordless User Accounts -------------------------- User accounts do not always require passwords to login. The unix_socket , named_pipe and gssapi authentication plugins do not require a password to authenticate the user. The pam authentication plugin may or may not require a password to authenticate the user, depending on the specific configuration. The mysql_native_password and mysql_old_password authentication plugins require passwords for authentication, but the password can be blank. In that case, no password is required. If you provide a password while attempting to log into the server as an account that doesn't require a password, then MariaDB server will simply ignore the password. MariaDB starting with 10.4 -------------------------- In MariaDB 10.4 and later, a user account can be defined to use multiple authentication plugins in a specific order of preference. This specific scenario may be more noticeable in these versions, since an account could be associated with some authentication plugins that require a password, and some that do not. Example ------- For example, if you had an entry with User and Host column values of 'bob' and '%.loc.gov', you would write the statement like this: SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass'); If you want to delete a password for a user, you would do: SET PASSWORD FOR 'bob'@localhost = PASSWORD(""); URL: https://mariadb.com/kb/en/set-password/https://mariadb.com/kb/en/set-password/q $ DROP ROLESyntax ------ DROP ROLE [IF EXISTS] role_name [,role_name ...] Description ----------- The DROP ROLE statement removes one or more MariaDB roles. To use this statement, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database. DROP ROLE does not disable roles for connections which selected them with SET ROLE. If a role has previously been set as a default role, DROP ROLE does not remove the record of the default role from the mysql.user table. If the role is subsequently recreated and granted, it will again be the user's default. Use SET DEFAULT ROLE NONE to explicitly remove this. If any of the specified user accounts do not exist, ERROR 1396 (HY000) results. If an error occurs, DROP ROLE will still drop the roles that do not result in an error. Only one error is produced for all roles which have not been dropped: ERROR 1396 (HY000): Operation DROP ROLE failed for 'a','b','c' Failed CREATE or DROP operations, for both users and roles, produce the same error code. IF EXISTS --------- If the IF EXISTS clause is used, MariaDB will return a warning instead of an error if the role does not exist. Examples -------- DROP ROLE journalist; The same thing using the optional IF EXISTS clause: DROP ROLE journalist; ERROR 1396 (HY000): Operation DROP ROLE failed for 'journalist' DROP ROLE IF EXISTS journalist; Query OK, 0 rows affected, 1 warning (0.00 sec) Note (Code 1975): Can't drop role 'journalist'; it doesn't exist URL: https://mariadb.com/kb/en/drop-role/https://mariadb.com/kb/en/drop-role/ 0'IS_USED_LOCKSyntax ------ IS_USED_LOCK(str) Description ----------- 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. URL: https://mariadb.com/kb/en/is_used_lock/https://mariadb.com/kb/en/is_used_lock/ %NAME_CONSTSyntax ------ NAME_CONST(name,value) Description ----------- 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 | +--------+ URL: https://mariadb.com/kb/en/name_const/https://mariadb.com/kb/en/name_const/ SLEEPSyntax ------ SLEEP(duration) Description ----------- 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 replication. Example ------- SELECT SLEEP(5.5); +------------+ | SLEEP(5.5) | +------------+ | 0 | +------------+ 1 row in set (5.50 sec) URL: https://mariadb.com/kb/en/sleep/https://mariadb.com/kb/en/sleep/b!Pqo2 cMp & CREATE ROLESyntax ------ CREATE [OR REPLACE] ROLE [IF NOT EXISTS] role [WITH ADMIN {CURRENT_USER | CURRENT_ROLE | user | role}] Description ----------- The CREATE ROLE statement creates one or more MariaDB roles. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE ROLE creates a new row in the mysql.user table that has no privileges, and with the corresponding is_role field set to Y. It also creates a record in the mysql.roles_mapping table. If any of the specified roles already exist, ERROR 1396 (HY000) results. If an error occurs, CREATE ROLE will still create the roles that do not result in an error. The maximum length for a role is 128 characters. Role names can be quoted, as explained in the Identifier names page. Only one error is produced for all roles which have not been created: ERROR 1396 (HY000): Operation CREATE ROLE failed for 'a','b','c' Failed CREATE or DROP operations, for both users and roles, produce the same error code. PUBLIC and NONE are reserved, and cannot be used as role names. NONE is used to unset a role and PUBLIC has a special use in other systems, such as Oracle, so is reserved for compatibility purposes. For valid identifiers to use as role names, see Identifier Names. WITH ADMIN ---------- The optional WITH ADMIN clause determines whether the current user, the current role or another user or role has use of the newly created role. If the clause is omitted, WITH ADMIN CURRENT_USER is treated as the default, which means that the current user will be able to GRANT this role to users. OR REPLACE ---------- If the optional OR REPLACE clause is used, it acts as a shortcut for: DROP ROLE IF EXISTS name; CREATE ROLE name ...; IF NOT EXISTS ------------- When the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the specified role already exists. Cannot be used together with the OR REPLACE clause. Examples -------- CREATE ROLE journalist; CREATE ROLE developer WITH ADMIN lorinda@localhost; Granting the role to another user. Only user lorinda@localhost has permission to grant the developer role: SELECT USER(); +-------------------+ | USER() | +-------------------+ | henning@localhost | +-------------------+ ... GRANT developer TO ian@localhost; Access denied for user 'henning'@'localhost' SELECT USER(); +-------------------+ | USER() | +-------------------+ | lorinda@localhost | +-------------------+ GRANT m_role TO ian@localhost; The OR REPLACE and IF NOT EXISTS clauses. The journalist role already exists: CREATE ROLE journalist; ERROR 1396 (HY000): Operation CREATE ROLE failed for 'journalist' CREATE OR REPLACE ROLE journalist; Query OK, 0 rows affected (0.00 sec) CREATE ROLE IF NOT EXISTS journalist; Query OK, 0 rows affected, 1 warning (0.00 sec) SHOW WARNINGS; +-------+------+---------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------+ | Note | 1975 | Can't create role 'journalist'; it already exists | +-------+------+---------------------------------------------------+ URL: https://mariadb.com/kb/en/create-role/https://mariadb.com/kb/en/create-role/r# SET ROLESyntax ------ SET ROLE { role | NONE } Description ----------- The SET ROLE statement enables a role, along with all of its associated permissions, for the current session. To unset a role, use NONE . If a role that doesn't exist, or to which the user has not been assigned, is specified, an ERROR 1959 (OP000): Invalid role specification error occurs. An automatic SET ROLE is implicitly performed when a user connects if that user has been assigned a default role. See SET DEFAULT ROLE. Example ------- SELECT CURRENT_ROLE; +--------------+ | CURRENT_ROLE | +--------------+ | NULL | +--------------+ SET ROLE staff; SELECT CURRENT_ROLE; +--------------+ | CURRENT_ROLE | +--------------+ | staff | +--------------+ SET ROLE NONE; SELECT CURRENT_ROLE(); +----------------+ | CURRENT_ROLE() | +----------------+ | NULL | +----------------+ URL: https://mariadb.com/kb/en/set-role/https://mariadb.com/kb/en/set-role/s+ SET DEFAULT ROLESyntax ------ SET DEFAULT ROLE { role | NONE } [ FOR user@host ] Description ----------- The SET DEFAULT ROLE statement sets a default role for a specified (or current) user. A default role is automatically enabled when a user connects (an implicit SET ROLE statement is executed immediately after a connection is established). To be able to set a role as a default, the role must already have been granted to that user, and one needs the privileges to enable this role (if you cannot do SET ROLE X, you won't be able to do SET DEFAULT ROLE X). To set a default role for another user one needs to have write access to the mysql database. To remove a user's default role, use SET DEFAULT ROLE NONE [ FOR user@host ]. The record of the default role is not removed if the role is dropped or revoked, so if the role is subsequently re-created or granted, it will again be the user's default role. The default role is stored in the default_role column in the mysql.user table/view, as well as in the Information Schema APPLICABLE_ROLES table, so these can be viewed to see which role has been assigned to a user as the default. Examples -------- Setting a default role for the current user: SET DEFAULT ROLE journalist; Removing a default role from the current user: SET DEFAULT ROLE NONE; Setting a default role for another user. The role has to have been granted to the user before it can be set as default: CREATE ROLE journalist; CREATE USER taniel; SET DEFAULT ROLE journalist FOR taniel; ERROR 1959 (OP000): Invalid role specification `journalist` GRANT journalist TO taniel; SET DEFAULT ROLE journalist FOR taniel; Viewing mysql.user: select * from mysql.user where user='taniel'\G *************************** 1. row *************************** Host: % User: taniel ... is_role: N default_role: journalist ... Removing a default role for another user SET DEFAULT ROLE NONE FOR taniel; URL: https://mariadb.com/kb/en/set-default-role/https://mariadb.com/kb/en/set-default-role/|#SYS_GUIDMariaDB starting with 10.6.1 ---------------------------- The SYS_GUID function was introduced in MariaDB 10.6.1 to enhance Oracle compatibility. Similar functionality can be achieved with the UUID function. Syntax ------ SYS_GUID() Description ----------- Returns a 16-byte globally unique identifier (GUID), similar to the UUID function, but without the - character. Example ------- SELECT SYS_GUID(); +----------------------------------+ | SYS_GUID() | +----------------------------------+ | 2C574E45BA2811EBB265F859713E4BE4 | +----------------------------------+ URL: https://mariadb.com/kb/en/sys_guid/https://mariadb.com/kb/en/sys_guid/!Syntax ------ NOT, ! Description ----------- Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the operand is non-zero, and NOT NULL returns NULL. By default, the ! operator has a higher precedence. If the HIGH_NOT_PRECEDENCE SQL_MODE flag is set, NOT and ! have the same precedence. Examples -------- SELECT NOT 10; +--------+ | NOT 10 | +--------+ | 0 | +--------+ SELECT NOT 0; +-------+ | NOT 0 | +-------+ | 1 | +-------+ SELECT NOT NULL; +----------+ | NOT NULL | +----------+ | NULL | +----------+ SELECT ! (1+1); +---------+ | ! (1+1) | +---------+ | 0 | +---------+ SELECT ! 1+1; +-------+ | ! 1+1 | +-------+ | 1 | +-------+ URL: https://mariadb.com/kb/en/not/https://mariadb.com/kb/en/not/   őt) Roles OverviewDescription ----------- A role bundles a number of privileges together. It assists larger organizations where, typically, a number of users would have the same privileges, and, previously, the only way to change the privileges for a group of users was by changing each user's privileges individually. Alternatively, multiple external users could have been assigned the same user, and there would have been no way to see which actual user was responsible for which action. With roles, managing this is easy. For example, there could be a number of users assigned to a journalist role, with identical privileges. Changing the privileges for all the journalists is a matter of simply changing the role's privileges, while the individual user is still linked with any changes that take place. Roles are created with the CREATE ROLE statement, and dropped with the DROP ROLE statement. Roles are then assigned to a user with an extension to the GRANT statement, while privileges are assigned to a role in the regular way with GRANT. Similarly, the REVOKE statement can be used to both revoke a role from a user, or revoke a privilege from a role. Once a user has connected, he can obtain all privileges associated with a role by setting a role with the SET ROLE statement. The CURRENT_ROLE function returns the currently set role for the session, if any. Only roles granted directly to a user can be set, roles granted to other roles cannot. Instead the privileges granted to a role, which is, in turn, granted to another role (grantee), will be immediately available to any user who sets this second grantee role. The SET DEFAULT ROLE statement allows one to set a default role for a user. A default role is automatically enabled when a user connects (an implicit SET ROLE statement is executed immediately after a connection is established). Roles were implemented as a GSoC 2013 project by Vicentiu Ciorbaru. System Tables ------------- Information about roles and who they've been granted to can be found in the Information Schema APPLICABLE_ROLES table as well as the mysql.ROLES_MAPPING table. The Information Schema ENABLED_ROLES table shows the enabled roles for the current session. Examples -------- Creating a role and granting a privilege: CREATE ROLE journalist; GRANT SHOW DATABASES ON *.* TO journalist; GRANT journalist to hulda; Note, that hulda has no SHOW DATABASES privilege, even though she was granted the journalist role. She needs to set the role first: SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ SELECT CURRENT_ROLE; +--------------+ | CURRENT_ROLE | +--------------+ | NULL | +--------------+ SET ROLE journalist; SELECT CURRENT_ROLE; +--------------+ | CURRENT_ROLE | +--------------+ | journalist | +--------------+ SHOW DATABASES; +--------------------+ | Database | +--------------------+ | ... | | information_schema | | mysql | | performance_schema | | test | | ... | +--------------------+ SET ROLE NONE; Roles can be granted to roles: CREATE ROLE writer; GRANT SELECT ON data.* TO writer; GRANT writer TO journalist; But one does not need to set a role granted to a role. For example, hulda will automatically get all writer privileges when she sets the journalist role: SELECT CURRENT_ROLE; +--------------+ | CURRENT_ROLE | +--------------+ | NULL | +--------------+ SHOW TABLES FROM data; Empty set (0.01 sec) SET ROLE journalist; SELECT CURRENT_ROLE; +--------------+ | CURRENT_ROLE | +--------------+ | journalist | +--------------+ SHOW TABLES FROM data; +------------------------------+ | Tables_in_data | +------------------------------+ | set1 | | ... | +------------------------------+ Roles and Views (and Stored Routines) ------------------------------------- When a user sets a role, he, in a sense, has two identities with two associated sets of privileges. But a view (or a stored routine) can have only one definer. So, when a view (or a stored routine) is created with the SQL SECURITY DEFINER, one can specify whether the definer should be CURRENT_USER (and the view will have none of the privileges of the user's role) or CURRENT_ROLE (in this case, the view will use role's privileges, but none of the user's privileges). As a result, sometimes one can create a view that is impossible to use. CREATE ROLE r1; GRANT ALL ON db1.* TO r1; GRANT r1 TO foo@localhost; GRANT ALL ON db.* TO foo@localhost; SELECT CURRENT_USER +---------------+ | current_user | +---------------+ | foo@localhost | +---------------+ SET ROLE r1; CREATE TABLE db1.t1 (i int); CREATE VIEW db.v1 AS SELECT * FROM db1.t1; SHOW CREATE VIEW db.v1; +------+----------------------------------------------------------------------- ------------------------------------------------------------------+------------ ---------+----------------------+ | View | Create View | character_set_client | collation_connection | +------+----------------------------------------------------------------------- ------------------------------------------------------------------+------------ ---------+----------------------+ | v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`foo`@`localhost` SQL SECURITY DEFINER VIEW `db`.`v1` AS SELECT `db1`.`t1`.`i` AS `i` from `db1`.`t1` | utf8 | utf8_general_ci | +------+----------------------------------------------------------------------- ------------------------------------------------------------------+------------ ---------+----------------------+ CREATE DEFINER=CURRENT_ROLE VIEW db.v2 AS SELECT * FROM db1.t1; SHOW CREATE VIEW db.b2; +------+----------------------------------------------------------------------- -----------------------------------------------------+----------------------+-- -------------------+ | View | Create View | character_set_client | collation_connection | +------+----------------------------------------------------------------------- -----------------------------------------------------+----------------------+-- -------------------+ | v2 | CREATE ALGORITHM=UNDEFINED DEFINER=`r1` SQL SECURITY DEFINER VIEW `db`.`v2` AS select `db1`.`t1`.`a` AS `a` from `db1`.`t1` | utf8 | utf8_general_ci | +------+----------------------------------------------------------------------- -----------------------------------------------------+----------------------+-- -------------------+ Other Resources --------------- * Roles Review by Peter Gulutzan URL: https://mariadb.com/kb/en/roles_overview/https://mariadb.com/kb/en/roles_overview/&&Syntax ------ AND, && Description ----------- Logical AND. Evaluates to 1 if all operands are non-zero and not NULL, to 0 if one or more operands are 0, otherwise NULL is returned. For this operator, short-circuit evaluation can be used. Examples -------- SELECT 1 && 1; +--------+ | 1 && 1 | +--------+ | 1 | +--------+ SELECT 1 && 0; +--------+ | 1 && 0 | +--------+ | 0 | +--------+ SELECT 1 && NULL; +-----------+ | 1 && NULL | +-----------+ | NULL | +-----------+ SELECT 0 && NULL; +-----------+ | 0 && NULL | +-----------+ | 0 | +-----------+ SELECT NULL && 0; +-----------+ | NULL && 0 | +-----------+ | 0 | +-----------+ URL: https://mariadb.com/kb/en/and/https://mariadb.com/kb/en/and/ ~u* Account LockingMariaDB starting with 10.4.2 ---------------------------- Account locking was introduced in MariaDB 10.4.2. Description ----------- Account locking permits privileged administrators to lock/unlock user accounts. No new client connections will be permitted if an account is locked (existing connections are not affected). User accounts can be locked at creation, with the CREATE USER statement, or modified after creation with the ALTER USER statement. For example: CREATE USER 'lorin'@'localhost' ACCOUNT LOCK; or ALTER USER 'marijn'@'localhost' ACCOUNT LOCK; The server will return an ER_ACCOUNT_HAS_BEEN_LOCKED error when locked users attempt to connect: mysql -ulorin ERROR 4151 (HY000): Access denied, this account is locked The ALTER USER statement is also used to unlock a user: ALTER USER 'lorin'@'localhost' ACCOUNT UNLOCK; The SHOW CREATE USER statement will show whether the account is locked: SHOW CREATE USER 'marijn'@'localhost'; +-----------------------------------------------+ | CREATE USER for marijn@localhost | +-----------------------------------------------+ | CREATE USER 'marijn'@'localhost' ACCOUNT LOCK | +-----------------------------------------------+ as well as querying the mysql.global_priv table: SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv WHERE user='marijn'; +------------------------------------------------------------------------------ -------+ | CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) | +------------------------------------------------------------------------------ -------+ | marijn@localhost => { "access": 0, "plugin": "mysql_native_password", "authentication_string": "", "account_locked": true, "password_last_changed": 1558017158 } | +------------------------------------------------------------------------------ -------+ URL: https://mariadb.com/kb/en/account-locking/https://mariadb.com/kb/en/account-locking/wZ/ User Password ExpiryMariaDB starting with 10.4.3 ---------------------------- User password expiry was introduced in MariaDB 10.4.3. Password expiry permits administrators to expire user passwords, either manually or automatically. System Variables ---------------- There are two system variables which affect password expiry: default_password_lifetime, which determines the amount of time between requiring the user to change their password. 0, the default, means automatic password expiry is not active. The second variable, disconnect_on_expired_password determines whether a client is permitted to connect if their password has expired, or whether they are permitted to connect in sandbox mode, able to perform a limited subset of queries related to resetting the password, in particular SET PASSWORD and SET. Setting a Password Expiry Limit for a User ------------------------------------------ Besides automatic password expiry, as determined by default_password_lifetime, password expiry times can be set on an individual user basis, overriding the global using the CREATE USER or ALTER USER statements, for example: CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY; ALTER USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY; Limits can be disabled by use of the NEVER keyword, for example: CREATE USER 'monty'@'localhost' PASSWORD EXPIRE NEVER; ALTER USER 'monty'@'localhost' PASSWORD EXPIRE NEVER; A manually set limit can be restored the system default by use of DEFAULT, for example: CREATE USER 'monty'@'localhost' PASSWORD EXPIRE DEFAULT; ALTER USER 'monty'@'localhost' PASSWORD EXPIRE DEFAULT; SHOW CREATE USER ---------------- The SHOW CREATE USER statement will display information about the password expiry status of the user. Unlike MySQL, it will not display if the user is unlocked, or if the password expiry is set to default. CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY; CREATE USER 'konstantin'@'localhost' PASSWORD EXPIRE NEVER; CREATE USER 'amse'@'localhost' PASSWORD EXPIRE DEFAULT; SHOW CREATE USER 'monty'@'localhost'; +------------------------------------------------------------------+ | CREATE USER for monty@localhost | +------------------------------------------------------------------+ | CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY | +------------------------------------------------------------------+ SHOW CREATE USER 'konstantin'@'localhost'; +------------------------------------------------------------+ | CREATE USER for konstantin@localhost | +------------------------------------------------------------+ | CREATE USER 'konstantin'@'localhost' PASSWORD EXPIRE NEVER | +------------------------------------------------------------+ SHOW CREATE USER 'amse'@'localhost'; +--------------------------------+ | CREATE USER for amse@localhost | +--------------------------------+ | CREATE USER 'amse'@'localhost' | +--------------------------------+ Checking When Passwords Expire ------------------------------ The following query can be used to check when the current passwords expire for all users: WITH password_expiration_info AS ( SELECT User, Host, IF( IFNULL(JSON_EXTRACT(Priv, '$.password_lifetime'), -1) = -1, @@global.default_password_lifetime, JSON_EXTRACT(Priv, '$.password_lifetime') ) AS password_lifetime, JSON_EXTRACT(Priv, '$.password_last_changed') AS password_last_changed FROM mysql.global_priv ) SELECT pei.User, pei.Host, pei.password_lifetime, FROM_UNIXTIME(pei.password_last_changed) AS password_last_changed_datetime, FROM_UNIXTIME( pei.password_last_changed + (pei.password_lifetime * 60 * 60 * 24) ) AS password_expiration_datetime FROM password_expiration_info pei WHERE pei.password_lifetime != 0 AND pei.password_last_changed IS NOT NULL UNION SELECT pei.User, pei.Host, pei.password_lifetime, FROM_UNIXTIME(pei.password_last_changed) AS password_last_changed_datetime, 0 AS password_expiration_datetime FROM password_expiration_info pei WHERE pei.password_lifetime = 0 OR pei.password_last_changed IS NULL; --connect-expired-password Client Option ---------------------------------------- The mariadb client --connect-expired-password option notifies the server that the client is prepared to handle expired password sandbox mode (even if the --batch option was specified). URL: https://mariadb.com/kb/en/user-password-expiry/https://mariadb.com/kb/en/user-password-expiry/XORSyntax ------ XOR Description ----------- XOR stands for eXclusive OR. Returns NULL if either operand is NULL. For non-NULL operands, evaluates to 1 if an odd number of operands is non-zero, otherwise 0 is returned. Examples -------- SELECT 1 XOR 1; +---------+ | 1 XOR 1 | +---------+ | 0 | +---------+ SELECT 1 XOR 0; +---------+ | 1 XOR 0 | +---------+ | 1 | +---------+ SELECT 1 XOR NULL; +------------+ | 1 XOR NULL | +------------+ | NULL | +------------+ In the following example, the right 1 XOR 1 is evaluated first, and returns 0. Then, 1 XOR 0 is evaluated, and 1 is returned. SELECT 1 XOR 1 XOR 1; +---------------+ | 1 XOR 1 XOR 1 | +---------------+ | 1 | +---------------+ URL: https://mariadb.com/kb/en/xor/https://mariadb.com/kb/en/xor/ n&STDDEV_SAMPSyntax ------ STDDEV_SAMP(expr) Description ----------- Returns the sample standard deviation of expr (the square root of VAR_SAMP()). It is an aggregate function, and so can be used with the GROUP BY clause. STDDEV_SAMP() can be used as a window function. STDDEV_SAMP() returns NULL if there were no matching rows. URL: https://mariadb.com/kb/en/stddev_samp/https://mariadb.com/kb/en/stddev_samp/* *MariaDB starting with 10.4 -------------------------- MariaDB 10.4 introduced a number of changes to the authentication process, intended to make things easier and more intuitive. Overview -------- There are four new main features in 10.4 relating to authentication: * It is possible to use more than one authentication plugin for each user account. For example, this can be useful to slowly migrate users to the more secure ed25519 authentication plugin over time, while allowing the old mysql_native_password authentication plugin as an alternative for the transitional period. * The root@localhost user account created by mariadb-install-db is created with the ability to use two authentication plugins. First, it is configured to try to use the unix_socket authentication plugin. This allows the root@localhost user to login without a password via the local Unix socket file defined by the socket system variable, as long as the login is attempted from a process owned by the operating system root user account. Second, if authentication fails with the unix_socket authentication plugin, then it is configured to try to use the mysql_native_password authentication plugin. However, an invalid password is initially set, so in order to authenticate this way, a password must be set with SET PASSWORD. However, just using the unix_socket authentication plugin may be fine for many users, and it is very secure. You may want to try going without password authentication to see how well it works for you. Remember, the best way to keep your password safe is not to have one! * All user accounts, passwords, and global privileges are now stored in the mysql.global_priv table. The mysql.user table still exists and has exactly the same set of columns as before, but it’s now a view that references the mysql.global_priv table. Tools that analyze the mysql.user table should continue to work as before. From MariaDB 10.4.13, the dedicated mariadb.sys user is created as the definer of this view. Previously root was the definer, which resulted in privilege problems when this username was changed. * MariaDB 10.4 adds supports for User Password Expiry, which is not active by default. Description ----------- As a result of the above changes, the open-for-everyone all-powerful root account is finally gone. And installation scripts will no longer demand that you "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !", because the root account is securely created automatically. Two all-powerful accounts are created by default — root and the OS user that owns the data directory, typically mysql. They are created as: CREATE USER root@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid' CREATE USER mysql@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid' Using unix_socket means that if you are the system root user, you can login as root@locahost without a password. This technique was pioneered by Otto Kekäläinen in Debian MariaDB packages and has been successfully used in Debian since as early as MariaDB 10.0. It is based on a simple fact that asking the system root for a password adds no extra security — root has full access to all the data files and all process memory anyway. But not asking for a password means, there is no root password to forget (no need for the numerous tutorials on "how to reset MariaDB root password"). And if you want to script some tedious database work, there is no need to store the root password in plain text for the script to use (no need for debian-sys-maint user). Still, some users may wish to log in as MariaDB root without using sudo. Hence the old authentication method — conventional MariaDB password — is still available. By default it is disabled ("invalid" is not a valid password hash), but one can set the password with a usual SET PASSWORD statement. And still retain the password-less access via sudo. If you install MariaDB locally (say from a tarball), you would not want to use sudo to be able to login. This is why MariaDB creates a second all-powerful user with the same name as a system user that owns the data directory. In local (not system-wide) installations, this will be the user who installed MariaDB — they automatically get convenient password-less root-like access, because they can access all the data files anyway. Even if MariaDB is installed system-wide, you may not want to run your database maintenance scripts as system root — now you can run them as system mysql user. And you will know that they will never destroy your entire system, even if you make a typo in a shell script. However, seasoned MariaDB DBAs who are used to the old ways do need to make some changes. See the examples below for common tasks. Cookbook -------- After installing MariaDB system-wide the first thing you’ve got used to doing is logging in into the unprotected root account and protecting it, that is, setting the root password: $ sudo dnf install MariaDB-server $ mysql -uroot ... MariaDB> set password = password("XH4VmT3_jt"); This is not only unnecessary now, it will simply not work — there is no unprotected root account. To login as root use $ sudo dnf install MariaDB-server $ sudo mysql Note that it implies you are connecting via the unix socket, not tcp. If you happen to have protocol=tcp in a system-wide /etc/my.cnf file, use sudo mysql --protocol=socket. After installing MariaDB locally you’ve also used to connect to the unprotected root account using mysql -uroot. This will not work either, simply use mysql without specifying a username. If you've forgotten your root password, no problem — you can still connect using sudo and change the password. And if you've also removed unix_socket authentication, to restore access do as follows: * restart MariaDB with --skip-grant-tables * login into the unprotected server * run FLUSH PRIVILEGES (note, before 10.4 this would’ve been the last step, not anymore). This disables --skip-grant-tables and allows you to change the stored authentication method * run SET PASSWORD FOR root@localhost to change the root password. To view inside privilege tables, the old mysql.user table still exists. You can select from it as before, although you cannot update it anymore. It doesn’t show alternative authentication plugins and this was one of the reasons for switching to the mysql.global_priv table — complex authentication rules did not fit into rigid structure of a relational table. You can select from the new table, for example: select concat(user, '@', host, ' => ', json_detailed(priv)) from mysql.global_priv; Reverting to the Previous Authentication Method for root@localhost ------------------------------------------------------------------ If you don't want the root@localhost user account created by mariadb-install-db to use unix_socket authentication by default, then there are a few ways to revert to the previous mysql_native_password authentication method for this user account. Configuring mariadb-install-db to Revert to the Previous Authentication Method ------------------------------------------------------------------------------ One way to revert to the previous mysql_native_password authentication method for the root@localhost user account is to execute mariadb-install-db with a special option. If mariadb-install-db is executed while --auth-root-authentication-method=normal is specified, then it will create the default user accounts using the default behavior of MariaDB 10.3 and before. This means that the root@localhost user account will use mysql_native_password authentication by default. There are some other differences as well. See mariadb-install-db: User Accounts Created by Default for more information. For example, the option can be set on the command-line while running mariadb-install-db: mariadb-install-db --user=mysql --datadir=/var/lib/mysql --auth-root-authentication-method=normal The option can also be set in an option file in an option group supported by mariadb-install-db. For example: [mysql_install_db] auth_root_authentication_method=normal If the option is set in an opt1| u% UNCOMPRESSSyntax ------ UNCOMPRESS(string_to_uncompress) Description ----------- Uncompresses a string compressed by the COMPRESS() function. If the argument is not a compressed value, the result is NULL. This function requires MariaDB to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL. The have_compress server system variable indicates whether a compression library is present. Examples -------- SELECT UNCOMPRESS(COMPRESS('a string')); +----------------------------------+ | UNCOMPRESS(COMPRESS('a string')) | +----------------------------------+ | a string | +----------------------------------+ SELECT UNCOMPRESS('a string'); +------------------------+ | UNCOMPRESS('a string') | +------------------------+ | NULL | +------------------------+ URL: https://mariadb.com/kb/en/uncompress/https://mariadb.com/kb/en/uncompress/}D ! DECODESyntax ------ DECODE(crypt_str,pass_str) In Oracle mode from MariaDB 10.3.2: DECODE(expr, search_expr, result_expr [, search_expr2, result_expr2 ...] [default_expr]) In all modes from MariaDB 10.3.2: DECODE_ORACLE(expr, search_expr, result_expr [, search_expr2, result_expr2 ...] [default_expr]) Description ----------- In the default mode, DECODE decrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE(). The resulting string will be the original string only if pass_str is the same. In Oracle mode from MariaDB 10.3.2, DECODE compares expr to the search expressions, in order. If it finds a match, the corresponding result expression is returned. If no matches are found, the default expression is returned, or NULL if no default is provided. NULLs are treated as equivalent. DECODE_ORACLE is a synonym for the Oracle-mode version of the function, and is available in all modes. Examples -------- From MariaDB 10.3.2: SELECT DECODE_ORACLE(2+1,3*1,'found1',3*2,'found2','default'); +--------------------------------------------------------+ | DECODE_ORACLE(2+1,3*1,'found1',3*2,'found2','default') | +--------------------------------------------------------+ | found1 | +--------------------------------------------------------+ SELECT DECODE_ORACLE(2+4,3*1,'found1',3*2,'found2','default'); +--------------------------------------------------------+ | DECODE_ORACLE(2+4,3*1,'found1',3*2,'found2','default') | +--------------------------------------------------------+ | found2 | +--------------------------------------------------------+ SELECT DECODE_ORACLE(2+2,3*1,'found1',3*2,'found2','default'); +--------------------------------------------------------+ | DECODE_ORACLE(2+2,3*1,'found1',3*2,'found2','default') | +--------------------------------------------------------+ | default | +--------------------------------------------------------+ Nulls are treated as equivalent: SELECT DECODE_ORACLE(NULL,NULL,'Nulls are equivalent','Nulls are not equivalent'); +----------------------------------------------------------------------------+ | DECODE_ORACLE(NULL,NULL,'Nulls are equivalent','Nulls are not equivalent') | +----------------------------------------------------------------------------+ | Nulls are equivalent | +----------------------------------------------------------------------------+ URL: https://mariadb.com/kb/en/decode/https://mariadb.com/kb/en/decode/ P& AES_DECRYPTSyntax ------ AES_DECRYPT(crypt_str,key_str) From MariaDB 11.2.0 AES_ENCRYPT(crypt_str, key_str, [, iv [, mode]]) Description ----------- This function allows decryption of data using the official AES (Advanced Encryption Standard) algorithm. For more information, see the description of AES_ENCRYPT(). MariaDB starting with 11.2 -------------------------- From MariaDB 11.2, the function supports an initialization vector, and control of the block encryption mode. The default mode is specified by the block_encryption_mode system variable, which can be changed when calling the function with a mode. mode is aes-{128,192,256}-{ecb,cbc,ctr} for example: "AES-128-cbc". For modes that require it, the initialization_vector iv should be 16 bytes (it can be longer, but the extra bytes are ignored). A shorter iv, where one is required, results in the function returning NULL. Calling RANDOM_BYTES(16) will generate a random series of bytes that can be used for the iv. Examples -------- From MariaDB 11.2.0: SELECT HEX(AES_ENCRYPT('foo', 'bar', '0123456789abcdef', 'aes-128-ctr')) AS x; +--------+ | x | +--------+ | C57C4B | +--------+ SELECT AES_DECRYPT(x'C57C4B', 'bar', '0123456789abcdef', 'aes-128-ctr'); +------------------------------------------------------------------+ | AES_DECRYPT(x'C57C4B', 'bar', '0123456789abcdef', 'aes-128-ctr') | +------------------------------------------------------------------+ | foo | +------------------------------------------------------------------+ URL: https://mariadb.com/kb/en/aes_decrypt/https://mariadb.com/kb/en/aes_decrypt/ $BENCHMARKSyntax ------ BENCHMARK(count,expr) Description ----------- The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how quickly MariaDB processes the expression. The result value is always 0. The intended use is from within the mariadb client, which reports query execution times. Examples -------- SELECT BENCHMARK(1000000,ENCODE('hello','goodbye')); +----------------------------------------------+ | BENCHMARK(1000000,ENCODE('hello','goodbye')) | +----------------------------------------------+ | 0 | +----------------------------------------------+ 1 row in set (0.21 sec) URL: https://mariadb.com/kb/en/benchmark/https://mariadb.com/kb/en/benchmark/ *BINLOG_GTID_POSSyntax ------ BINLOG_GTID_POS(binlog_filename,binlog_offset) Description ----------- The BINLOG_GTID_POS() function takes as input an old-style binary log position in the form of a file name and a file offset. It looks up the position in the current binlog, and returns a string representation of the corresponding GTID position. If the position is not found in the current binlog, NULL is returned. Examples -------- SELECT BINLOG_GTID_POS("master-bin.000001", 600); URL: https://mariadb.com/kb/en/binlog_gtid_pos/https://mariadb.com/kb/en/binlog_gtid_pos/ $COLLATIONSyntax ------ COLLATION(str) Description ----------- Returns the collation of the string argument. If str is not a string, it is considered as a binary string (so the function returns 'binary'). This applies to NULL, too. The return value is a string in the utf8 character set. See Character Sets and Collations. Examples -------- SELECT COLLATION('abc'); +-------------------+ | COLLATION('abc') | +-------------------+ | latin1_swedish_ci | +-------------------+ SELECT COLLATION(_utf8'abc'); +-----------------------+ | COLLATION(_utf8'abc') | +-----------------------+ | utf8_general_ci | +-----------------------+ URL: https://mariadb.com/kb/en/collation/https://mariadb.com/kb/en/collation/P7y T >n & AES_ENCRYPTSyntax ------ AES_ENCRYPT(str,key_str) From MariaDB 11.2.0 AES_ENCRYPT(str, key, [, iv [, mode]]) Description ----------- AES_ENCRYPT() and AES_DECRYPT() allow encryption and decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as "Rijndael." Encoding with a 128-bit key length is used (from MariaDB 11.2.0, this is the default, and can be changed). 128 bits is much faster and is secure enough for most purposes. AES_ENCRYPT() encrypts a string str using the key key_str, and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string. The input arguments may be any length. If either argument is NULL, the result of this function is also NULL. Because AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated using this formula: 16 x (trunc(string_length / 16) + 1) If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid. MariaDB starting with 11.2 -------------------------- From MariaDB 11.2, the function supports an initialization vector, and control of the block encryption mode. The default mode is specified by the block_encryption_mode system variable, which can be changed when calling the function with a mode. mode is aes-{128,192,256}-{ecb,cbc,ctr} for example: "AES-128-cbc". AES_ENCRYPT(str, key) can no longer be used in persistent virtual columns (and the like). Examples -------- INSERT INTO t VALUES (AES_ENCRYPT('text',SHA2('password',512))); From MariaDB 11.2.0: SELECT HEX(AES_ENCRYPT('foo', 'bar', '0123456789abcdef', 'aes-256-cbc')) AS x; +----------------------------------+ | x | +----------------------------------+ | 42A3EB91E6DFC40A900D278F99E0726E | +----------------------------------+ URL: https://mariadb.com/kb/en/aes_encrypt/https://mariadb.com/kb/en/aes_encrypt/# COMPRESSSyntax ------ COMPRESS(string_to_compress) Description ----------- Compresses a string and returns the result as a binary string. This function requires MariaDB to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL. The compressed string can be uncompressed with UNCOMPRESS(). The have_compress server system variable indicates whether a compression library is present. Examples -------- SELECT LENGTH(COMPRESS(REPEAT('a',1000))); +------------------------------------+ | LENGTH(COMPRESS(REPEAT('a',1000))) | +------------------------------------+ | 21 | +------------------------------------+ SELECT LENGTH(COMPRESS('')); +----------------------+ | LENGTH(COMPRESS('')) | +----------------------+ | 0 | +----------------------+ SELECT LENGTH(COMPRESS('a')); +-----------------------+ | LENGTH(COMPRESS('a')) | +-----------------------+ | 13 | +-----------------------+ SELECT LENGTH(COMPRESS(REPEAT('a',16))); +----------------------------------+ | LENGTH(COMPRESS(REPEAT('a',16))) | +----------------------------------+ | 15 | +----------------------------------+ URL: https://mariadb.com/kb/en/compress/https://mariadb.com/kb/en/compress/ & DES_DECRYPTDES_DECRYPT has been deprecated from MariaDB 10.10.0, and will be removed in a future release. Syntax ------ DES_DECRYPT(crypt_str[,key_str]) Description ----------- Decrypts a string encrypted with DES_ENCRYPT(). If an error occurs, this function returns NULL. This function works only if MariaDB has been configured with TLS support. If no key_str argument is given, DES_DECRYPT() examines the first byte of the encrypted string to determine the DES key number that was used to encrypt the original string, and then reads the key from the DES key file to decrypt the message. For this to work, the user must have the SUPER privilege. The key file can be specified with the --des-key-file server option. If you pass this function a key_str argument, that string is used as the key for decrypting the message. If the crypt_str argument does not appear to be an encrypted string, MariaDB returns the given crypt_str. URL: https://mariadb.com/kb/en/des_decrypt/https://mariadb.com/kb/en/des_decrypt/ & DES_ENCRYPTDES_ENCRYPT has been deprecated from MariaDB 10.10.0, and will be removed in a future release. Syntax ------ DES_ENCRYPT(str[,{key_num|key_str}]) Description ----------- Encrypts the string with the given key using the Triple-DES algorithm. This function works only if MariaDB has been configured with TLS support. The encryption key to use is chosen based on the second argument to DES_ENCRYPT(), if one was given. With no argument, the first key from the DES key file is used. With a key_num argument, the given key number (0-9) from the DES key file is used. With a key_str argument, the given key string is used to encrypt str. The key file can be specified with the --des-key-file server option. The return string is a binary string where the first character is CHAR(128 | key_num). If an error occurs, DES_ENCRYPT() returns NULL. The 128 is added to make it easier to recognize an encrypted key. If you use a string key, key_num is 127. The string length for the result is given by this formula: new_len = orig_len + (8 - (orig_len % 8)) + 1 Each line in the DES key file has the following format: key_num des_key_str Each key_num value must be a number in the range from 0 to 9. Lines in the file may be in any order. des_key_str is the string that is used to encrypt the message. There should be at least one space between the number and the key. The first key is the default key that is used if you do not specify any key argument to DES_ENCRYPT(). You can tell MariaDB to read new key values from the key file with the FLUSH DES_KEY_FILE statement. This requires the RELOAD privilege. One benefit of having a set of default keys is that it gives applications a way to check for the existence of encrypted column values, without giving the end user the right to decrypt those values. Examples -------- SELECT customer_address FROM customer_table WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number'); URL: https://mariadb.com/kb/en/des_encrypt/https://mariadb.com/kb/en/des_encrypt/ E(CONNECTION_IDSyntax ------ CONNECTION_ID() Description ----------- Returns the connection ID for the connection. Every connection (including events) has an ID that is unique among the set of currently connected clients. Until MariaDB 10.3.1, returns MYSQL_TYPE_LONGLONG, or bigint(10), in all cases. From MariaDB 10.3.1, returns MYSQL_TYPE_LONG, or int(10), when the result would fit within 32-bits. Examples -------- SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ | 3 | +-----------------+ URL: https://mariadb.com/kb/en/connection_id/https://mariadb.com/kb/en/connection_id/!SCHEMASyntax ------ SCHEMA() Description ----------- This function is a synonym for DATABASE(). URL: https://mariadb.com/kb/en/schema/https://mariadb.com/kb/en/schema/ 'SESSION_USERSyntax ------ SESSION_USER() Description ----------- SESSION_USER() is a synonym for USER(). URL: https://mariadb.com/kb/en/session_user/https://mariadb.com/kb/en/session_user/cf_ 2- &F! ENCODESyntax ------ ENCODE(str,pass_str) Description ----------- ENCODE is not considered cryptographically secure, and should not be used for password encryption. Encrypt str using pass_str as the password. To decrypt the result, use DECODE(). The result is a binary string of the same length as str. The strength of the encryption is based on how good the random generator is. It is not recommended to rely on the encryption performed by the ENCODE function. Using a salt value (changed when a password is updated) will improve matters somewhat, but for storing passwords, consider a more cryptographically secure function, such as SHA2(). Examples -------- ENCODE('not so secret text', CONCAT('random_salt','password')) URL: https://mariadb.com/kb/en/encode/https://mariadb.com/kb/en/encode/" ENCRYPTSyntax ------ ENCRYPT(str[,salt]) Description ----------- Encrypts a string using the Unix crypt() system call, returning an encrypted binary string. The salt argument should be a string with at least two characters or the returned result will be NULL. If no salt argument is given, a random value of sufficient length is used. It is not recommended to use ENCRYPT() with utf16, utf32 or ucs2 multi-byte character sets because the crypt() system call expects a string terminated with a zero byte. Note that the underlying crypt() system call may have some limitations, such as ignoring all but the first eight characters. If the have_crypt system variable is set to NO (because the crypt() system call is not available), the ENCRYPT function will always return NULL. Examples -------- SELECT ENCRYPT('encrypt me'); +-----------------------+ | ENCRYPT('encrypt me') | +-----------------------+ | 4I5BsEx0lqTDk | +-----------------------+ URL: https://mariadb.com/kb/en/encrypt/https://mariadb.com/kb/en/encrypt/ <' OLD_PASSWORDSyntax ------ OLD_PASSWORD(str) Description ----------- OLD_PASSWORD() was added to MySQL when the implementation of PASSWORD() was changed to improve security. OLD_PASSWORD() returns the value of the old (pre-MySQL 4.1) implementation of PASSWORD() as a string, and is intended to permit you to reset passwords for any pre-4.1 clients that need to connect to a more recent MySQL server version, or any version of MariaDB, without locking them out. As of MariaDB 5.5, the return value is a nonbinary string in the connection character set and collation, determined by the values of the character_set_connection and collation_connection system variables. Before 5.5, the return value was a binary string. The return value is 16 bytes in length, or NULL if the argument was NULL. URL: https://mariadb.com/kb/en/old_password/https://mariadb.com/kb/en/old_password/ # PASSWORDSyntax ------ PASSWORD(str) Description ----------- The PASSWORD() function is used for hashing passwords for use in authentication by the MariaDB server. It is not intended for use in other applications. Calculates and returns a hashed password string from the plaintext password str. Returns an empty string (>= MariaDB 10.0.4) if the argument was NULL. The return value is a nonbinary string in the connection character set and collation, determined by the values of the character_set_connection and collation_connection system variables. This is the function that is used for hashing MariaDB passwords for storage in the Password column of the user table (see privileges), usually used with the SET PASSWORD statement. It is not intended for use in other applications. Until MariaDB 10.3, the return value is 41-bytes in length, and the first character is always '*'. From MariaDB 10.4, the function takes into account the authentication plugin where applicable (A CREATE USER or SET PASSWORD statement). For example, when used in conjunction with a user authenticated by the ed25519 plugin, the statement will create a longer hash: CREATE USER edtest@localhost IDENTIFIED VIA ed25519 USING PASSWORD('secret'); CREATE USER edtest2@localhost IDENTIFIED BY 'secret'; SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv WHERE user LIKE 'edtest%'\G *************************** 1. row *************************** CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)): edtest@localhost => { ... "plugin": "ed25519", "authentication_string": "ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY", ... } *************************** 2. row *************************** CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)): edtest2@localhost => { ... "plugin": "mysql_native_password", "authentication_string": "*14E65567ABDB5135D0CFD9A70B3032C179A49EE7", ... } The behavior of this function is affected by the value of the old_passwords system variable. If this is set to 1 (0 is default), MariaDB reverts to using the mysql_old_password authentication plugin by default for newly created users and passwords. Examples -------- SELECT PASSWORD('notagoodpwd'); +-------------------------------------------+ | PASSWORD('notagoodpwd') | +-------------------------------------------+ | *3A70EE9FC6594F88CE9E959CD51C5A1C002DC937 | +-------------------------------------------+ SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass'); URL: https://mariadb.com/kb/en/password/https://mariadb.com/kb/en/password/ &SYSTEM_USERSyntax ------ SYSTEM_USER() Description ----------- SYSTEM_USER() is a synonym for USER(). URL: https://mariadb.com/kb/en/system_user/https://mariadb.com/kb/en/system_user/.Assignment Operator (:=)Syntax ------ var_name := expr Description ----------- Assignment operator for assigning a value. The value on the right is assigned to the variable on left. Unlike the = operator, := can always be used to assign a value to a variable. This operator works with both user-defined variables and local variables. When assigning the same value to several variables, LAST_VALUE() can be useful. Examples -------- SELECT @x := 10; +----------+ | @x := 10 | +----------+ | 10 | +----------+ SELECT @x, @y := @x; +------+----------+ | @x | @y := @x | +------+----------+ | 10 | 10 | +------+----------+ URL: https://mariadb.com/kb/en/assignment-operator/https://mariadb.com/kb/en/assignment-operator/#GREATESTSyntax ------ GREATEST(value1,value2,...) Description ----------- With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST(). Examples -------- SELECT GREATEST(2,0); +---------------+ | GREATEST(2,0) | +---------------+ | 2 | +---------------+ SELECT GREATEST(34.0,3.0,5.0,767.0); +------------------------------+ | GREATEST(34.0,3.0,5.0,767.0) | +------------------------------+ | 767.0 | +------------------------------+ SELECT GREATEST('B','A','C'); +-----------------------+ | GREATEST('B','A','C') | +-----------------------+ | C | +-----------------------+ URL: https://mariadb.com/kb/en/greatest/https://mariadb.com/kb/en/greatest/ &IS NOT NULLSyntax ------ IS NOT NULL Description ----------- Tests whether a value is not NULL. See also NULL Values in MariaDB. Examples -------- SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL; +---------------+---------------+------------------+ | 1 IS NOT NULL | 0 IS NOT NULL | NULL IS NOT NULL | +---------------+---------------+------------------+ | 1 | 1 | 0 | +---------------+---------------+------------------+ URL: https://mariadb.com/kb/en/is-not-null/https://mariadb.com/kb/en/is-not-null/8"  V}> 2G m ' RANDOM_BYTESMariaDB starting with 10.10.0 ----------------------------- The RANDOM_BYTES function generates a binary string of random bytes. It was added in MariaDB 10.10.0. Syntax ------ RANDOM_BYTES(length) Description ----------- Given a length from 1 to 1024, generates a binary string of length consisting of random bytes generated by the SSL library's random number generator. See the RAND_bytes() function documentation of your SSL library for information on the random number generator. In the case of OpenSSL, a cryptographically secure pseudo random generator (CSPRNG) is used. Statements containing the RANDOM_BYTES function are unsafe for statement-based replication. An error occurs if length is outside the range 1 to 1024. URL: https://mariadb.com/kb/en/random_bytes/https://mariadb.com/kb/en/random_bytes/9 SHA1Syntax ------ SHA1(str), SHA(str) Description ----------- Calculates an SHA-1 160-bit checksum for the string str, as described in RFC 3174 (Secure Hash Algorithm). The value is returned as a string of 40 hex digits, or NULL if the argument was NULL. As of MariaDB 5.5, the return value is a nonbinary string in the connection character set and collation, determined by the values of the character_set_connection and collation_connection system variables. Before 5.5, the return value was a binary string. Examples -------- SELECT SHA1('some boring text'); +------------------------------------------+ | SHA1('some boring text') | +------------------------------------------+ | af969fc2085b1bb6d31e517d5c456def5cdd7093 | +------------------------------------------+ URL: https://mariadb.com/kb/en/sha1/https://mariadb.com/kb/en/sha1/ SHA2Syntax ------ SHA2(str,hash_len) Description ----------- Given a string str, calculates an SHA-2 checksum, which is considered more cryptographically secure than its SHA-1 equivalent. The SHA-2 family includes SHA-224, SHA-256, SHA-384, and SHA-512, and the hash_len must correspond to one of these, i.e. 224, 256, 384 or 512. 0 is equivalent to 256. The return value is a nonbinary string in the connection character set and collation, determined by the values of the character_set_connection and collation_connection system variables. NULL is returned if the hash length is not valid, or the string str is NULL. SHA2 will only work if MariaDB was has been configured with TLS support. Examples -------- SELECT SHA2('Maria',224); +----------------------------------------------------------+ | SHA2('Maria',224) | +----------------------------------------------------------+ | 6cc67add32286412efcab9d0e1675a43a5c2ef3cec8879f81516ff83 | +----------------------------------------------------------+ SELECT SHA2('Maria',256); +------------------------------------------------------------------+ | SHA2('Maria',256) | +------------------------------------------------------------------+ | 9ff18ebe7449349f358e3af0b57cf7a032c1c6b2272cb2656ff85eb112232f16 | +------------------------------------------------------------------+ SELECT SHA2('Maria',0); +------------------------------------------------------------------+ | SHA2('Maria',0) | +------------------------------------------------------------------+ | 9ff18ebe7449349f358e3af0b57cf7a032c1c6b2272cb2656ff85eb112232f16 | +------------------------------------------------------------------+ URL: https://mariadb.com/kb/en/sha2/https://mariadb.com/kb/en/sha2/ %INET6_ATONSyntax ------ INET6_ATON(expr) Description ----------- 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 ---------------------------- From MariaDB 10.5.0, INET6_ATON can take INET6 as an argument. Examples -------- 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 | +----------------------------------------------+ URL: https://mariadb.com/kb/en/inet6_aton/https://mariadb.com/kb/en/inet6_aton/!ISNULLSyntax ------ ISNULL(expr) Description ----------- If expr is NULL, ISNULL() returns 1, otherwise it returns 0. See also NULL Values in MariaDB. Examples -------- SELECT ISNULL(1+1); +-------------+ | ISNULL(1+1) | +-------------+ | 0 | +-------------+ SELECT ISNULL(1/0); +-------------+ | ISNULL(1/0) | +-------------+ | 1 | +-------------+ URL: https://mariadb.com/kb/en/isnull/https://mariadb.com/kb/en/isnull/&&Syntax ------ & Description ----------- Bitwise AND. Converts the values to binary and compares bits. Only if both the corresponding bits are 1 is the resulting bit also 1. See also bitwise OR. Examples -------- SELECT 2&1; +-----+ | 2&1 | +-----+ | 0 | +-----+ SELECT 3&1; +-----+ | 3&1 | +-----+ | 1 | +-----+ SELECT 29 & 15; +---------+ | 29 & 15 | +---------+ | 13 | +---------+ URL: https://mariadb.com/kb/en/bitwise_and/https://mariadb.com/kb/en/bitwise_and/%<>Syntax ------ value1 >> value2 Description ----------- Converts a longlong (BIGINT) number (value1) to binary and shifts value2 units to the right. Examples -------- SELECT 4 >> 2; +--------+ | 4 >> 2 | +--------+ | 1 | +--------+ URL: https://mariadb.com/kb/en/shift-right/https://mariadb.com/kb/en/shift-right/ $BIT_COUNTSyntax ------ BIT_COUNT(N) Description ----------- Returns the number of bits that are set in the argument N. Examples -------- SELECT BIT_COUNT(29), BIT_COUNT(b'101010'); +---------------+----------------------+ | BIT_COUNT(29) | BIT_COUNT(b'101010') | +---------------+----------------------+ | 4 | 3 | +---------------+----------------------+ URL: https://mariadb.com/kb/en/bit_count/https://mariadb.com/kb/en/bit_count/&^Syntax ------ ^ Description ----------- Bitwise XOR. Converts the values to binary and compares bits. If one (and only one) of the corresponding bits is 1 is the resulting bit also 1. Examples -------- SELECT 1 ^ 1; +-------+ | 1 ^ 1 | +-------+ | 0 | +-------+ SELECT 1 ^ 0; +-------+ | 1 ^ 0 | +-------+ | 1 | +-------+ SELECT 11 ^ 3; +--------+ | 11 ^ 3 | +--------+ | 8 | +--------+ URL: https://mariadb.com/kb/en/bitwise-xor/https://mariadb.com/kb/en/bitwise-xor/o9SP*PWj K\N#GET_LOCKSyntax ------ GET_LOCK(str,timeout) Description ----------- 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). Examples -------- 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 transaction URL: https://mariadb.com/kb/en/get_lock/https://mariadb.com/kb/en/get_lock/%|Syntax ------ | Description ----------- Bitwise OR. Converts the values to binary and compares bits. If either of the corresponding bits has a value of 1, the resulting bit is also 1. See also bitwise AND. Examples -------- SELECT 2|1; +-----+ | 2|1 | +-----+ | 3 | +-----+ SELECT 29 | 15; +---------+ | 29 | 15 | +---------+ | 31 | +---------+ URL: https://mariadb.com/kb/en/bitwise-or/https://mariadb.com/kb/en/bitwise-or/ :- %INET6_NTOASyntax ------ INET6_NTOA(expr) Description ----------- 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. Examples -------- 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 | +-------------------------------------------------------+ URL: https://mariadb.com/kb/en/inet6_ntoa/https://mariadb.com/kb/en/inet6_ntoa/r"IS_IPV4Syntax ------ IS_IPV4(expr) Description ----------- 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. Examples -------- 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 | +--------------------------------------+ URL: https://mariadb.com/kb/en/is_ipv4/https://mariadb.com/kb/en/is_ipv4/)IS_IPV4_COMPATSyntax ------ IS_IPV4_COMPAT(expr) Description ----------- Returns 1 if a given numeric binary string IPv6 address, such as returned by INET6_ATON(), is IPv4-compatible, otherwise returns 0. MariaDB starting with 10.5.0 ---------------------------- From MariaDB 10.5.0, when the argument is not INET6, automatic implicit CAST to INET6 is applied. As a consequence, IS_IPV4_COMPAT now understands arguments in both text representation and binary(16) representation. Before MariaDB 10.5.0, the function understood only binary(16) representation. Examples -------- 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 | +-----------------------------------------------------------+ URL: https://mariadb.com/kb/en/is_ipv4_compat/https://mariadb.com/kb/en/is_ipv4_compat/)IS_IPV4_MAPPEDSyntax ------ IS_IPV4_MAPPED(expr) Description ----------- 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. MariaDB starting with 10.5.0 ---------------------------- From MariaDB 10.5.0, when the argument is not INET6, automatic implicit CAST to INET6 is applied. As a consequence, IS_IPV4_MAPPED now understands arguments in both text representation and binary(16) representation. Before MariaDB 10.5.0, the function understood only binary(16) representation. Examples -------- 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 | +-----------------------------------------------+ URL: https://mariadb.com/kb/en/is_ipv4_mapped/https://mariadb.com/kb/en/is_ipv4_mapped/*MASTER_POS_WAITSyntax ------ MASTER_POS_WAIT(log_name,log_pos[,timeout,["connection_name"]]) Description ----------- 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 while MASTER_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 replication. URL: https://mariadb.com/kb/en/master_pos_wait/https://mariadb.com/kb/en/master_pos_wait/@&~Syntax ------ ~ Description ----------- Bitwise NOT. Converts the value to 4 bytes binary and inverts all bits. Examples -------- SELECT 3 & ~1; +--------+ | 3 & ~1 | +--------+ | 2 | +--------+ SELECT 5 & ~1; +--------+ | 5 & ~1 | +--------+ | 4 | +--------+ URL: https://mariadb.com/kb/en/bitwise-not/https://mariadb.com/kb/en/bitwise-not/ %TRUE FALSEDescription ----------- The constants TRUE and FALSE evaluate to 1 and 0, respectively. The constant names can be written in any lettercase. Examples -------- SELECT TRUE, true, FALSE, false; +------+------+-------+-------+ | TRUE | TRUE | FALSE | FALSE | +------+------+-------+-------+ | 1 | 1 | 0 | 0 | +------+------+-------+-------+ URL: https://mariadb.com/kb/en/true-false/https://mariadb.com/kb/en/true-false/ %CHECK VIEWSyntax ------ CHECK VIEW view_name Description ----------- The CHECK VIEW statement was introduced in MariaDB 10.0.18 to assist with fixing MDEV-6916, an issue introduced in MariaDB 5.2 where the view algorithms were swapped. It checks whether the view algorithm is correct. It is run as part of mariadb-upgrade, and should not normally be required in regular use. URL: https://mariadb.com/kb/en/check-view/https://mariadb.com/kb/en/check-view/ u8_; 7[ O1 Y+MASTER_GTID_WAITSyntax ------ MASTER_GTID_WAIT(gtid-list[, timeout) Description ----------- 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 slave 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 gtid_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 slave (or until timed out or killed). MASTER_GTID_WAIT() can be useful to ensure that a slave 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 slave; when the call completes, the slave 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 slaves 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 slave, and if the slave 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 slave, do a MASTER_GTID_WAIT() on the value obtained from the master; this will ensure that the read is not performed until the slave has replicated sufficiently far for the update to have become visible. Note that MASTER_GTID_WAIT() can be used even if the slave 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 slave servers. Differences to MASTER_POS_WAIT() -------------------------------- * 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 SLAVE or due to an error, MASTER_GTID_WAIT() continues to wait while slaves 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 slaves 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 slave 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 slave 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 slave. URL: https://mariadb.com/kb/en/master_gtid_wait/https://mariadb.com/kb/en/master_gtid_wait/v,RELEASE_ALL_LOCKSMariaDB until 10.5.2 -------------------- RELEASE_ALL_LOCKS was added in MariaDB 10.5.2. Syntax ------ RELEASE_ALL_LOCKS() Description ----------- 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. Examples -------- 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 | +---------------------+ URL: https://mariadb.com/kb/en/release_all_locks/https://mariadb.com/kb/en/release_all_locks/3BLOB and TEXT Data TypesDescription ----------- A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are * TINYBLOB, * BLOB, * MEDIUMBLOB, and * LONGBLOB. These differ only in the maximum length of the values they can hold. The TEXT types are * TINYTEXT, * TEXT, * MEDIUMTEXT, and * LONGTEXT. * JSON (alias for LONGTEXT) These correspond to the four BLOB types and have the same maximum lengths and storage requirements. BLOB and TEXT columns can have a DEFAULT value. MariaDB starting with 10.4.3 ---------------------------- From MariaDB 10.4, it is possible to set a unique index on columns that use the BLOB or TEXT data types. URL: https://mariadb.com/kb/en/blob-and-text-data-types/https://mariadb.com/kb/en/blob-and-text-data-types/ $CHAR BYTEDescription ----------- The CHAR BYTE data type is an alias for the BINARY data type. This is a compatibility feature. URL: https://mariadb.com/kb/en/char-byte/https://mariadb.com/kb/en/char-byte/L@ ; INET6 %MEDIUMBLOBSyntax ------ MEDIUMBLOB Description ----------- A BLOB column with a maximum length of 16,777,215 (224 - 1) bytes. Each MEDIUMBLOB value is stored using a three-byte length prefix that indicates the number of bytes in the value. URL: https://mariadb.com/kb/en/mediumblob/https://mariadb.com/kb/en/mediumblob/ %MEDIUMTEXTSyntax ------ MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name] Description ----------- A TEXT column with a maximum length of 16,777,215 (224 - 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each MEDIUMTEXT value is stored using a three-byte length prefix that indicates the number of bytes in the value. URL: https://mariadb.com/kb/en/mediumtext/https://mariadb.com/kb/en/mediumtext/Qo"o# , 5 'RELEASE_LOCKSyntax ------ RELEASE_LOCK(str) Description ----------- 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 replication. The DO statement is convenient to use with RELEASE_LOCK(). Examples -------- 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 | +-----------------------+-----------------------+-----------------------+ From MariaDB 10.0.2, 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) URL: https://mariadb.com/kb/en/release_lock/https://mariadb.com/kb/en/release_lock/UUIDSyntax ------ UUID() Description ----------- Returns a Universally Unique Identifier (UUID). 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 unpredictable and unguessable, so should not be relied upon for these purposes. 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 replication. 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). Examples -------- SELECT UUID(); +--------------------------------------+ | UUID() | +--------------------------------------+ | cd41294a-afb0-11df-bc9b-00241dd75637 | +--------------------------------------+ URL: https://mariadb.com/kb/en/uuid/https://mariadb.com/kb/en/uuid/<#LONGBLOBSyntax ------ LONGBLOB Description ----------- A BLOB column with a maximum length of 4,294,967,295 bytes or 4GB (232 - 1). The effective maximum length of LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGBLOB value is stored using a four-byte length prefix that indicates the number of bytes in the value. Oracle Mode ----------- MariaDB starting with 10.3 -------------------------- In Oracle mode from MariaDB 10.3, BLOB is a synonym for LONGBLOB. URL: https://mariadb.com/kb/en/longblob/https://mariadb.com/kb/en/longblob/#TINYBLOBSyntax ------ TINYBLOB Description ----------- A BLOB column with a maximum length of 255 (28 - 1) bytes. Each TINYBLOB value is stored using a one-byte length prefix that indicates the number of bytes in the value. URL: https://mariadb.com/kb/en/tinyblob/https://mariadb.com/kb/en/tinyblob/#TINYTEXTSyntax ------ TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name] Description ----------- A TEXT column with a maximum length of 255 (28 - 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TINYTEXT value is stored using a one-byte length prefix that indicates the number of bytes in the value. URL: https://mariadb.com/kb/en/tinytext/https://mariadb.com/kb/en/tinytext/! :(SET Data TypeSyntax ------ SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name] Description ----------- A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET column can have a maximum of 64 members. SET values are represented internally as integers. SET values cannot contain commas. If a SET contains duplicate values, an error will be returned if strict mode is enabled, or a warning if strict mode is not enabled. URL: https://mariadb.com/kb/en/set-data-type/https://mariadb.com/kb/en/set-data-type/}=gu  v my, %UUID_SHORTSyntax ------ UUID_SHORT() Description ----------- 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 slave servers * server_id is between 0 and 255 * You don't set back your system time for your server between mysqld restarts * You do not invoke UUID_SHORT() on average more than 16 million times per second between mysqld 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. Examples -------- 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 | +-------------------+ URL: https://mariadb.com/kb/en/uuid_short/https://mariadb.com/kb/en/uuid_short/U'VALUES / VALUESyntax ------ MariaDB starting with 10.3.3 ---------------------------- VALUE(col_name) MariaDB until 10.3.2 -------------------- VALUES(col_name) Description ----------- 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. In MariaDB 10.3.3 this function was renamed to VALUE(), because it's incompatible with the standard Table Value Constructors syntax, implemented in MariaDB 10.3.3. The VALUES() function can still be used even from MariaDB 10.3.3, but only in INSERT ... ON DUPLICATE KEY UPDATE statements; it's a syntax error otherwise. Examples -------- MariaDB starting with 10.3.3 ---------------------------- INSERT INTO t (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUE(a)+VALUE(b); MariaDB until 10.3.2 -------------------- INSERT INTO t (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); URL: https://mariadb.com/kb/en/values-value/https://mariadb.com/kb/en/values-value/||Syntax ------ OR, || Description ----------- Logical OR. When both operands are non-NULL, the result is 1 if any operand is non-zero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is non-zero, and NULL otherwise. If both operands are NULL, the result is NULL. For this operator, short-circuit evaluation can be used. Note that, if the PIPES_AS_CONCAT SQL_MODE is set, || is used as a string concatenation operator. This means that a || b is the same as CONCAT(a,b). See CONCAT() for details. Oracle Mode ----------- MariaDB starting with 10.3 -------------------------- In Oracle mode from MariaDB 10.3, || ignores NULL. Examples -------- SELECT 1 || 1; +--------+ | 1 || 1 | +--------+ | 1 | +--------+ SELECT 1 || 0; +--------+ | 1 || 0 | +--------+ | 1 | +--------+ SELECT 0 || 0; +--------+ | 0 || 0 | +--------+ | 0 | +--------+ SELECT 0 || NULL; +-----------+ | 0 || NULL | +-----------+ | NULL | +-----------+ SELECT 1 || NULL; +-----------+ | 1 || NULL | +-----------+ | 1 | +-----------+ In Oracle mode, from MariaDB 10.3: SELECT 0 || NULL; +-----------+ | 0 || NULL | +-----------+ | 0 | +-----------+ URL: https://mariadb.com/kb/en/or/https://mariadb.com/kb/en/or/ AVGSyntax ------ AVG([DISTINCT] expr) Description ----------- Returns the average value of expr. The DISTINCT option can be used to return the average of the distinct values of expr. NULL values are ignored. It is an aggregate function, and so can be used with the GROUP BY clause. AVG() returns NULL if there were no matching rows. AVG() can be used as a window function. Examples -------- CREATE TABLE sales (sales_value INT); INSERT INTO sales VALUES(10),(20),(20),(40); SELECT AVG(sales_value) FROM sales; +------------------+ | AVG(sales_value) | +------------------+ | 22.5000 | +------------------+ SELECT AVG(DISTINCT(sales_value)) FROM sales; +----------------------------+ | AVG(DISTINCT(sales_value)) | +----------------------------+ | 23.3333 | +----------------------------+ Commonly, AVG() is used with a GROUP BY clause: CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); INSERT INTO student VALUES ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83); SELECT name, AVG(score) FROM student GROUP BY name; +---------+------------+ | name | AVG(score) | +---------+------------+ | Chun | 74.0000 | | Esben | 37.0000 | | Kaolin | 72.0000 | | Tatiana | 85.0000 | +---------+------------+ Be careful to avoid this common mistake, not grouping correctly and returning mismatched data: SELECT name,test,AVG(score) FROM student; +------+------+------------+ | name | test | MIN(score) | +------+------+------------+ | Chun | SQL | 31 | +------+------+------------+ As a window function: CREATE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT); INSERT INTO student_test VALUES ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83); SELECT name, test, score, AVG(score) OVER (PARTITION BY test) AS average_by_test FROM student_test; +---------+--------+-------+-----------------+ | name | test | score | average_by_test | +---------+--------+-------+-----------------+ | Chun | SQL | 75 | 65.2500 | | Chun | Tuning | 73 | 68.7500 | | Esben | SQL | 43 | 65.2500 | | Esben | Tuning | 31 | 68.7500 | | Kaolin | SQL | 56 | 65.2500 | | Kaolin | Tuning | 88 | 68.7500 | | Tatiana | SQL | 87 | 65.2500 | | Tatiana | Tuning | 83 | 68.7500 | +---------+--------+-------+-----------------+ URL: https://mariadb.com/kb/en/avg/https://mariadb.com/kb/en/avg/T@ & ,$TIMESTAMP0GOTOMariaDB starting with 10.3 -------------------------- The GOTO statement was introduced in MariaDB 10.3 for Oracle compatibility. Syntax ------ GOTO label Description ----------- The GOTO statement causes the code to jump to the specified label, and continue operating from there. It is only accepted when in Oracle mode. Example ------- SET sql_mode=ORACLE; DELIMITER // CREATE OR REPLACE PROCEDURE p1 AS BEGIN SELECT 1; GOTO label; SELECT 2; <