mariadb-docs

MariaDB-specific syntax and behavior for ALTER USER and SET PASSWORD — IF EXISTS warning-not-error semantics, IDENTIFIED BY vs IDENTIFIED BY PASSWORD vs IDENTIFIED VIA/WITH multi-plugin auth, PASSWORD EXPIRE variants and default_password_lifetime interaction, ACCOUNT LOCK/UNLOCK, resource limits, TLS/REQUIRE options, and SET PASSWORD's three literal forms (PASSWORD(), OLD_PASSWORD(), pre-hashed string — there is no cleartext-string form). Use when writing, generating, or reviewing statements that create/change account credentials, expire or lock accounts, or set passwords on MariaDB.

MariaDB-specific syntax and behavior for CREATE USER — multiple authentication methods chained with OR, IDENTIFIED BY / IDENTIFIED BY PASSWORD / IDENTIFIED VIA|WITH plugin USING|AS 'string'|PASSWORD(...), the default authentication plugin, REQUIRE TLS options, WITH resource-limit options, PASSWORD EXPIRE variants, ACCOUNT LOCK/UNLOCK, OR REPLACE, IF NOT EXISTS, and account-name/host defaulting. Covers CREATE USER only (not GRANT's implicit user-creation path). Use when writing, generating, or reviewing CREATE USER statements that target MariaDB.

MariaDB-specific behavior for DROP USER and RENAME USER — multiple accounts per statement, IF EXISTS turning a missing-user error into a note, the active-connections warning (dropped users keep working until their session ends; new connections are blocked), no cascade onto objects owned or privileges granted by the dropped user, dangling DEFINER references left behind on views/routines/triggers, and RENAME USER preserving privileges across the rename with no active-session check. Use when writing, generating, or reviewing DROP USER or RENAME USER statements, or account-lifecycle cleanup scripts, that target MariaDB.

MariaDB-specific GRANT behavior — privilege scope levels (global/db/table/column/routine), implicit account creation interacting with the NO_AUTO_CREATE_USER sql_mode (on by default), MariaDB-only privileges LLMs won't know (BINLOG MONITOR, DELETE HISTORY, SET USER, FEDERATED ADMIN, READ_ONLY ADMIN, etc.) and the SUPER-privilege split, GRANT PROXY, resource limits, REQUIRE TLS options, and MariaDB roles (GRANT role TO user/role, WITH ADMIN OPTION, single-active-role semantics). Use when writing, generating, or reviewing GRANT statements or privilege/role designs that target MariaDB.

MariaDB-specific syntax and behavior for REVOKE — the mirrored five-form grammar (privilege ON object, the special ALL PRIVILEGES/GRANT OPTION form with no ON clause, PROXY, role revocation, ADMIN OPTION FOR role), the USAGE-privilege gotcha, ER_NONEXISTING_GRANT on mismatched revoke level, and the absence of an IF EXISTS clause. Use when writing, generating, or reviewing REVOKE statements (privileges, roles, or proxy) against MariaDB.

MariaDB control-flow functions and operators — IF(), IFNULL()/NVL(), NULLIF(), COALESCE(), the CASE operator (simple and searched forms), NVL2(), and DECODE/DECODE_ORACLE. Use when writing SQL that branches on a condition, substitutes for NULL, or matches a value against a list of alternatives in MariaDB.

MariaDB information functions — session and server metadata: LAST_INSERT_ID, ROW_COUNT, FOUND_ROWS, DATABASE/SCHEMA, USER/CURRENT_USER/SESSION_USER/SYSTEM_USER, CURRENT_ROLE, VERSION, CONNECTION_ID, CHARSET, COLLATION, COERCIBILITY, BENCHMARK, DEFAULT, LAST_VALUE, ROWNUM, BINLOG_GTID_POS, DECODE_HISTOGRAM. Use when writing SQL that reads auto-increment IDs, affected/found row counts, the current database or authenticated user, the server version, or connection/session metadata in MariaDB.

MariaDB window functions — ranking (ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE), value navigation (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE), and ordered-set/inverse-distribution functions (MEDIAN, PERCENTILE_CONT, PERCENTILE_DISC), plus the OVER (PARTITION BY ... ORDER BY ... frame) clause, named windows (WINDOW w AS (...)), and ROWS/RANGE window frames. Use when writing SQL that ranks or paginates rows, computes running totals or moving aggregates, compares a row to its neighbors, or computes percentiles/medians in MariaDB.

MariaDB explicit table locking (LOCK TABLES / UNLOCK TABLES: READ, READ LOCAL, WRITE, LOW_PRIORITY WRITE, WRITE CONCURRENT, WAIT n/NOWAIT) and the named user-level lock functions GET_LOCK, RELEASE_LOCK, RELEASE_ALL_LOCKS, IS_FREE_LOCK, IS_USED_LOCK — including the alias-matching trap, the implicit-commit interaction with transactions, and multi-lock-per-connection semantics since 10.0.2. Use when writing or reviewing code that takes explicit table locks, implements application-level mutexes/advisory locks, or debugs 'was not locked with LOCK TABLES' / lock-wait-timeout errors.

MariaDB-specific behavior for SET TRANSACTION and transaction isolation — the GLOBAL/SESSION/next-transaction scope rule, the transaction_isolation system variable (successor to deprecated tx_isolation), READ WRITE/READ ONLY access mode, and innodb_snapshot_isolation write-write conflict detection (ON by default under REPEATABLE READ since 11.6.2, raising ER_CHECKREAD/1020 and rolling back the whole transaction). Use when writing, generating, or reviewing SET TRANSACTION statements, isolation-level configuration, or transaction retry logic that must handle MariaDB's snapshot-isolation error path.

MariaDB-specific syntax and behavior for transaction control — START TRANSACTION / BEGIN [WORK], WITH CONSISTENT SNAPSHOT, READ ONLY / READ WRITE, COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE], ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE], ROLLBACK TO SAVEPOINT, SAVEPOINT, RELEASE SAVEPOINT, autocommit, implicit commit, completion_type, in_transaction, and idle transaction timeouts. Covers START TRANSACTION, BEGIN, COMMIT, ROLLBACK, and SAVEPOINT. Use when writing, generating, or reviewing transaction-control statements that target MariaDB.

MariaDB JSON functions — complete catalog of JSON_*, JSON_ARRAY/OBJECT constructors and aggregates (JSON_ARRAYAGG, JSON_OBJECTAGG), extraction (JSON_EXTRACT, JSON_VALUE, JSON_QUERY, JSON_TABLE, ->, ->>), modification (JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT), merge variants (JSON_MERGE, JSON_MERGE_PATCH, JSON_MERGE_PRESERVE), validation (JSON_VALID, JSON_SCHEMA_VALID, IS JSON), introspection (JSON_TYPE, JSON_DEPTH, JSON_LENGTH, JSON_KEYS, JSON_CONTAINS, JSON_EQUALS), formatting (JSON_DETAILED/PRETTY, JSON_COMPACT, JSON_LOOSE, JSON_QUOTE, JSON_UNQUOTE), and JSON_TABLE for relational projection. Use when writing SQL that constructs, queries, or modifies JSON data in MariaDB.

MariaDB numeric and math functions — rounding/truncation (ROUND, the TRUNCATE function vs the TRUNCATE TABLE statement, FLOOR, CEILING, SIGN, ABS), integer-vs-decimal division (the DIV operator vs /, MOD / %), powers and logs (POW/POWER, SQRT, EXP, LN, LOG, LOG2, LOG10), trigonometry (SIN, COS, TAN, ASIN, ACOS, ATAN, ATAN2, COT, PI, DEGREES, RADIANS), randomness (RAND, seeded RAND(N)), base conversion (CONV, OCT, BIN, HEX), checksums (CRC32, CRC32C), and number formatting (FORMAT). Use when writing SQL that does arithmetic, rounding, base conversion, or random sampling in MariaDB — especially where exact-vs-approximate types or division-by-zero behavior matter.

Build a GitBook redirect CSV when mariadb-docs pages are renamed, moved, split, or retired. Use when the user asks to "create a redirects CSV", "make GitBook redirects", "set up redirects for moved/retired pages", or when a page-restructuring task removes or relocates published URLs. Produces the CSV; a GitBook site admin imports it manually (no API/MCP).

Work with the MariaDB DOCS Jira project. Shared plumbing for the /jira-start, /jira-create, /jira-resolve, /jira-close, /jira-comment, /jira-mine, and /jira-chase slash commands — fetch/transition/create DOCS tickets, manage the feature-branch workflow, and chase reviewers of tickets waiting in Review. Use when asked to start work on a DOCS ticket, file one, move one through the workflow, comment, list assigned tickets, or remind reviewers.

Turn a MariaDB DOCS Jira ticket into a concrete, source-verified documentation edit for mariadb-docs. Fetches the ticket, follows it to the upstream MDEV/source change, VERIFIES the claims against local MariaDB source code, locates the right .md page, and drafts the GitBook edit. Use when asked to "document DOCS-XXXX", "write the docs for this ticket", or "draft the page for <DOCS ticket>".

Drive a repo-wide, multi-file documentation pass across mariadb-docs — batched, progress-tracked, and verified per file. Use for campaigns like "add railroad diagrams to all SQL pages", a terminology rename across a space, a frontmatter backfill, or any change that repeats over many .md files. Scopes to one space/path, ties to a DOCS ticket, and never fans out across the whole repo unprompted.

Analyze a MariaDB source change for its documentation impact — the "explain" step before drafting. Given an MDEV ticket, a DOCS ticket, or a MariaDB/server PR/commit, it reads the actual code change, determines what is user-facing, whether docs are needed and which pages, and lists the claims to verify. Produces an analysis report; it does NOT edit docs (hand off to doc-from-ticket / /doc-ticket for that). Use when asked to "explain the doc impact of X", "what docs does MDEV-XXXXX need", "analyze this PR for documentation", or to triage whether a change needs docs.

Validate changed MariaDB documentation pages the way CI does, before committing or opening a PR. Use when the user asks to check docs, validate a page, run pre-PR checks, "check links", "run codespell", or before creating a docs PR. Runs codespell + lychee link-check on changed files and checks frontmatter, GitBook block balance, alias usage, and SUMMARY.md consistency, then reports pass/fail by check.

Re-verify a fact-check report against MariaDB source in a later session — confirm each documented claim is still source-proven. Use when asked to "verify the claims", "re-check the fact-check report", "audit DOCS-XXXX's claims", or before approving/merging a doc PR a different writer drafted. Reads the local report, re-checks each claim against source at the recorded SHA (and the current ref, to surface drift), and reports per-claim status. Read-only — never edits docs, source, or the report.

MariaDB aggregate functions — calculations across a set of rows (typically with GROUP BY or as window functions via OVER()). Covers counting (COUNT, COUNT(DISTINCT)), totals/averages (SUM, AVG), extrema (MIN, MAX), string aggregation (GROUP_CONCAT with DISTINCT/ORDER BY/SEPARATOR/LIMIT), bitwise aggregation (BIT_AND, BIT_OR, BIT_XOR), and the statistical family (STD/STDDEV/STDDEV_POP, STDDEV_SAMP, VARIANCE/VAR_POP, VAR_SAMP). Use when summarizing or rolling up rows in MariaDB. Note aggregates skip NULLs, GROUP_CONCAT silently truncates at group_concat_max_len, and ONLY_FULL_GROUP_BY is not in the default sql_mode.

MariaDB date and time functions — current time (NOW, CURRENT_TIMESTAMP, SYSDATE, CURDATE, CURTIME, UTC_TIMESTAMP/UTC_DATE/UTC_TIME), arithmetic (DATE_ADD, DATE_SUB, ADDDATE, SUBDATE, INTERVAL units), differences (TIMESTAMPDIFF, TIMESTAMPADD, DATEDIFF, TIMEDIFF), extraction (EXTRACT, YEAR/MONTH/DAY/HOUR/MINUTE/SECOND/MICROSECOND, QUARTER, WEEK, DAYOFWEEK, WEEKDAY, LAST_DAY), parsing/formatting (STR_TO_DATE, DATE_FORMAT, GET_FORMAT), Unix time (UNIX_TIMESTAMP, FROM_UNIXTIME), time-zone conversion (CONVERT_TZ), construction (MAKEDATE, MAKETIME, SEC_TO_TIME), and Oracle-compat helpers (ADD_MONTHS, MONTHS_BETWEEN, TO_DATE). Use when computing, parsing, formatting, or converting dates, times, and timestamps in MariaDB.

MariaDB string functions — extraction (SUBSTRING/SUBSTR, LEFT, RIGHT, MID, SUBSTRING_INDEX); length in bytes (LENGTH/OCTET_LENGTH/LENGTHB) vs characters (CHAR_LENGTH/CHARACTER_LENGTH); search (LOCATE, INSTR, POSITION, FIND_IN_SET, FIELD); concatenation (CONCAT, CONCAT_WS); modification (REPLACE, INSERT, REVERSE, REPEAT, SPACE, LPAD/RPAD, TRIM/LTRIM/RTRIM); case (LOWER/UPPER); PCRE2 regular expressions (REGEXP/RLIKE, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_SUBSTR); formatting (FORMAT, SFORMAT); encoding (HEX/UNHEX, TO_BASE64/FROM_BASE64, ASCII, CHAR, ORD); plus the LIKE, REGEXP, SOUNDS LIKE, and BINARY operators. Use when writing SQL that searches, slices, pads, concatenates, case-folds, or pattern-matches string data in MariaDB.

MariaDB-specific syntax and behavior for LOAD DATA [LOCAL] INFILE (and LOAD XML) — the LOCAL vs server-side fork and its security/privilege requirements (local_infile, FILE privilege, secure_file_priv), tab/newline (not CSV) defaults, how LOCAL silently downgrades duplicate handling to IGNORE and disables strict-mode aborts, IGNORE n LINES, user-variable + SET transformation, the CHARACTER SET clause, and priority/concurrency. Use when writing, generating, or reviewing LOAD DATA / LOAD XML statements, or bulk-loading CSV/TSV/XML into MariaDB.

MariaDB-specific behavior of the mariadb-dump client for dev workflows (seeding, fixtures, schema snapshots, consistent backups) — why --single-transaction is the live-InnoDB flag and its limits, that --routines/--events are off by default while --triggers is on, --extended-insert vs diffable dumps, schema-only/data-only, --replace/--insert-ignore for idempotent seeding, that plain dbname omits CREATE DATABASE, restoring by piping into the mariadb client, and that the replication-coordinate flags are --master-data/--dump-slave (not --source-data/--dump-replica). Use when invoking or reviewing mariadb-dump commands.

MariaDB-specific behavior of the mariadb-import client — a command-line wrapper around LOAD DATA INFILE for bulk-loading text files. Covers the table-name-from-filename rule, TAB (not CSV) defaults, the --local vs server-side fork, --replace/--ignore, --columns vs --ignore-lines, --parallel and its --lock-tables interaction, and the locale-autodetected charset. Use when invoking or reviewing mariadb-import commands, or scripting bulk CSV/TSV imports into MariaDB.

MariaDB-specific syntax and behavior for databases — CREATE DATABASE / CREATE SCHEMA (exact synonyms), the OR REPLACE data-loss footgun vs IF NOT EXISTS, CHARACTER SET / COLLATE / COMMENT specifications and the utf8mb4 default-charset change, ALTER DATABASE (charset/collate/comment + UPGRADE DATA DIRECTORY NAME), DROP DATABASE, and the absence of a RENAME DATABASE statement. Use when writing, generating, or reviewing CREATE DATABASE / ALTER DATABASE / DROP DATABASE statements that target MariaDB.

MariaDB-specific syntax and behavior for CREATE INDEX / DROP INDEX — that it maps to ALTER TABLE (batch multiple adds), can't create a PRIMARY KEY, needs a prefix length on TEXT/BLOB columns, has no expression/functional indexes (index a generated column instead), real descending indexes (since 10.8), IGNORED indexes for safe optimizer testing, UNIQUE/FULLTEXT/SPATIAL/VECTOR kinds, USING BTREE/HASH/RTREE, CREATE OR REPLACE / IF NOT EXISTS, KEY_BLOCK_SIZE being ignored here, and dropping a PRIMARY KEY via DROP INDEX `PRIMARY`. Use when writing, generating, or reviewing CREATE INDEX / DROP INDEX statements that target MariaDB.

MariaDB-specific syntax and behavior for views — CREATE OR REPLACE VIEW vs CREATE VIEW IF NOT EXISTS (mutually exclusive), ALGORITHM = UNDEFINED/MERGE/TEMPTABLE and its effect on updatability and performance, the SQL SECURITY DEFINER default, WITH CASCADED/LOCAL CHECK OPTION, what makes a view updatable/insertable, frozen-at-creation definitions, and ALTER VIEW / DROP VIEW. Use when writing, generating, or reviewing CREATE VIEW / ALTER VIEW / DROP VIEW statements that target MariaDB.

MariaDB-specific syntax and behavior for DROP TABLE — RESTRICT/CASCADE being no-ops, the DROP TEMPORARY TABLE guard against dropping a same-named base table, partial drops in a multi-table statement, IF EXISTS warning behavior, failure when a table is referenced by a foreign key, DROP TABLE on a view, automatic trigger removal, implicit-commit (and the DROP TEMPORARY exception), WAIT/NOWAIT, and CREATE OR REPLACE TABLE as the atomic replace. Use when writing, generating, or reviewing DROP TABLE statements that target MariaDB.

MariaDB-specific syntax and behavior for ALTER TABLE — online DDL with ALGORITHM (DEFAULT/COPY/INPLACE/NOCOPY/INSTANT) and LOCK clauses, ALTER ONLINE TABLE shorthand, WAIT/NOWAIT lock timeouts, IF EXISTS/IF NOT EXISTS on subclauses, ADD/DROP SYSTEM VERSIONING, ADD PERIOD FOR, DROP CONSTRAINT, FORCE rebuild, CONVERT PARTITION/TABLE, DISCARD/IMPORT TABLESPACE, atomic ALTER, two-phase replication. Use when writing, generating, or reviewing ALTER TABLE statements that target MariaDB, or planning schema migrations against MariaDB.

MariaDB-specific syntax and behavior for CREATE TABLE — atomic CREATE OR REPLACE, INVISIBLE / COMPRESSED columns, system-versioned and application-time PERIOD clauses, IGNORED indexes, table-level encryption, page compression, sequence and Aria table options, and the utf8/utf8mb4 trap. Use when writing, generating, or reviewing CREATE TABLE statements that target MariaDB.

MariaDB-specific syntax and behavior for SELECT — optimizer hints (/*+ ... */, JOIN_PREFIX/JOIN_ORDER/JOIN_FIXED_ORDER/JOIN_SUFFIX, MAX_EXECUTION_TIME) and legacy query-level hints, LIMIT extensions (comma-form, ROWS EXAMINED), OFFSET … FETCH … ONLY/WITH TIES, FOR UPDATE / LOCK IN SHARE MODE with WAIT/NOWAIT/SKIP LOCKED, PARTITION pruning, FOR SYSTEM_TIME temporal queries, SELECT INTO OUTFILE/DUMPFILE/variable, WITH ROLLUP, max_statement_time, FROM DUAL. Covers SELECT, SELECT INTO OUTFILE, SELECT INTO DUMPFILE, SELECT … OFFSET … FETCH, and SELECT WITH ROLLUP. Use when writing, generating, or reviewing SELECT statements that target MariaDB.

MariaDB-specific syntax and behavior for DELETE — RETURNING deleted rows in one statement, both multi-table forms (DELETE … FROM and DELETE FROM … USING) with ORDER BY/LIMIT (since 11.8), single-table ORDER BY/LIMIT/PARTITION/alias/index hints, DELETE HISTORY for system-versioned tables, FOR PORTION OF for application-time periods, QUICK/LOW_PRIORITY/IGNORE modifiers and their engine limits, deleting from a table referenced in its own subquery, and DELETE-vs-TRUNCATE differences. Use when writing, generating, or reviewing DELETE statements that target MariaDB.

MariaDB-specific syntax and behavior for INSERT — RETURNING to get inserted rows in one round trip, INSERT … ON DUPLICATE KEY UPDATE with VALUE()/VALUES(), INSERT IGNORE warning and error-coercion behavior, INSERT … SELECT, the SET form, PARTITION targeting, multi-row VALUES, priority/DELAYED modifiers and their engine limits, and LAST_INSERT_ID() semantics after multi-row inserts. Use when writing, generating, or reviewing INSERT statements that target MariaDB, or when an upsert is needed.

MariaDB-specific syntax and behavior for REPLACE — why REPLACE is DELETE-then-INSERT (not an upsert/UPDATE), when it triggers (PRIMARY/UNIQUE conflicts only), its data-loss footguns (unlisted columns reset to DEFAULT, AUTO_INCREMENT burned, ON DELETE cascades and DELETE+INSERT triggers fired), the INSERT … ON DUPLICATE KEY UPDATE alternative, REPLACE … RETURNING, the SET and SELECT forms, PARTITION targeting, and the INSERT+DELETE privilege requirement. Use when writing, generating, or reviewing REPLACE statements, or when an upsert is being considered.

MariaDB-specific syntax and behavior for UPDATE — single-table vs multi-table forms and their different rules, UPDATE … ORDER BY … LIMIT, PARTITION pruning, RETURNING with OLD_VALUE() (since 13.0), left-to-right assignment evaluation and SIMULTANEOUS_ASSIGNMENT sql_mode, IGNORE / LOW_PRIORITY modifiers, rows-matched-vs-changed reporting, updating system-versioned and application-time-period (FOR PORTION OF) tables, and CTEs before UPDATE (since 12.3). Use when writing, generating, or reviewing UPDATE statements that target MariaDB.

MariaDB-specific features and capabilities that go beyond standard MySQL. Use when evaluating MariaDB, optimizing an existing MariaDB application, reviewing code or schema for MariaDB improvements, asking what MariaDB can do that other databases cannot, or migrating from Oracle to MariaDB. Also use when the user asks what could be improved in how a codebase uses MariaDB, or asks about MariaDB advantages over MySQL or PostgreSQL.

Best practices for query optimization in MariaDB — indexing strategies, EXPLAIN analysis, pagination, histogram statistics, and MariaDB-specific optimizer settings. Use when diagnosing slow queries, designing indexes, reviewing schema or query performance, or when queries involve large tables, pagination, GROUP BY, or ORDER BY. Also use when the user asks about MariaDB query performance, EXPLAIN output, or optimizer behavior.

Best practices for MariaDB system-versioned (temporal) tables — automatic row history built into the database. Use when tracking data changes over time, implementing audit trails, meeting GDPR or compliance requirements, doing point-in-time queries, or when the user asks about row history, data versioning, temporal tables, or querying past data states in MariaDB. This feature is unique to MariaDB among MySQL-compatible databases.