MariaDB-Claude-Skill-Package

Use when a user provides a slow query plus its EXPLAIN output and wants a concrete optimization, or asks "make this query faster", or wants an index recommendation validated against MariaDB optimizer behavior. Prevents the common mistake of suggesting an index without checking selectivity, proposing FORCE INDEX as a fix, copying MySQL 8 optimizer hints, or recommending an index that duplicates an existing one. Covers a deterministic query-optimization procedure : read EXPLAIN, identify the bottleneck (type=ALL, filesort, temporary), propose index or rewrite, validate against composite leftmost-prefix rule, check covering-index opportunity, verify with ANALYZE FORMAT=JSON, cross-references mariadb-impl-query-optimization, mariadb-syntax-indexing, mariadb-errors-slow-queries. Keywords: optimize my query, make this query faster, query optimization, EXPLAIN analysis, index recommendation, why is this slow, slow query fix, covering index, index suggestion, query rewrite, optimize SQL, performance fix, EXPLAIN, ANA

Use when reviewing a proposed MariaDB schema before it ships, auditing an existing schema for engine / indexing / naming / normalization problems, or validating a migration DDL. Prevents the common mistake of shipping a schema with MyISAM tables, UUID-text PKs, missing tenant indexes, utf8 charset, or composite indexes in the wrong column order. Covers a deterministic schema-review checklist : storage-engine choice, primary-key type, indexing strategy and column-order, charset / collation, normalization fitness, multi-tenant pattern detection, naming-convention adherence, with severity grading and cross-references to mariadb-core-storage-engines, mariadb-syntax-indexing, mariadb-impl-schema-design. Keywords: schema review, schema audit, review my schema, is this schema correct, schema checklist, design review, storage engine audit, index audit, primary key audit, normalization check, multi-tenant check, naming convention, DDL review, before I ship this schema, ENGINE=InnoDB, MyISAM, BIGINT AUTO_INCREMENT, UUI

Use when a Galera cluster reports certification failures, ER_LOCK_DEADLOCK fires at COMMIT in a multi-master setup, wsrep_local_cert_failures keeps climbing, a hot-row is contended across nodes, or the cluster appears to be in split-brain. Prevents the common mistake of treating Galera ER_LOCK_DEADLOCK as a bug, retrying without backoff, ignoring hot-row design, running a 2-node Galera without an arbitrator, or "fixing" symptoms by disabling wsrep_on. Covers certification-based replication semantics, ER_LOCK_DEADLOCK at COMMIT (Galera-specific path), wsrep_local_cert_failures and wsrep_local_bf_aborts status variables, hot-row detection plus sharding redesign, split-brain prevention with pc.weight and garbd, application retry strategy with exponential backoff, gcache exhaust leading to forced SST. Keywords: Galera, certification failure, wsrep_local_cert_failures, wsrep_local_bf_aborts, ER_LOCK_DEADLOCK at commit, ER_QUERY_INTERRUPTED, hot row, multi-master conflict, split brain, pc.weight, pc.ignore_sb, garb

Use when a query is slow, when reading EXPLAIN output, when deciding on index hints, when tuning optimizer_switch flags, when using optimizer_trace, or when comparing MariaDB and MySQL optimizer behavior. Prevents the common mistake of trusting type=ALL queries in production, blindly applying USE INDEX without checking selectivity, leaving outdated statistics, or copying MySQL 8 optimizer assumptions to MariaDB. Covers EXPLAIN reading column-by-column, EXPLAIN FORMAT=JSON, ANALYZE FORMAT=JSON (actual execution stats), index hints USE/FORCE/IGNORE INDEX, optimizer_switch flags (MariaDB-specific), optimizer_trace, ANALYZE TABLE for statistics, persistent vs in-memory statistics. Keywords: EXPLAIN, EXPLAIN FORMAT JSON, ANALYZE FORMAT JSON, query plan, optimizer, optimizer_switch, optimizer_trace, USE INDEX, FORCE INDEX, IGNORE INDEX, type ALL, type ref, type range, key_len, rows, filtered, Using filesort, Using temporary, Using index, ANALYZE TABLE, persistent statistics, why is my query slow, slow query, query

Use when writing INSERT, UPDATE, DELETE, REPLACE, or upsert statements, debugging "why was this row not updated", or migrating MySQL DML patterns to MariaDB. Prevents the INSERT IGNORE silent-corruption trap, REPLACE INTO FK cascade, ON DUPLICATE KEY auto-increment burn, multi-table UPDATE/DELETE ordering pitfalls, and the "UPDATE RETURNING does not exist in LTS" gotcha. Covers INSERT single-row + multi-row + INSERT SET + INSERT ... SELECT, INSERT ... ON DUPLICATE KEY UPDATE, INSERT IGNORE pitfalls, REPLACE INTO, UPDATE ... ORDER BY ... LIMIT, multi-table UPDATE/DELETE with JOIN, INSERT/DELETE RETURNING, DELETE HISTORY for system-versioned tables. Keywords: INSERT, UPDATE, DELETE, REPLACE, ON DUPLICATE KEY UPDATE, INSERT IGNORE, RETURNING, INSERT RETURNING, DELETE RETURNING, multi-table UPDATE, multi-table DELETE, upsert, why was my row not updated, my insert ignored the error, auto increment burn, FK cascade on REPLACE, mariadb upsert pattern, UPDATE RETURNING not supported, DELETE HISTORY, system-versioned

Use when investigating "my query worked yesterday and now doesn't", upgrading between LTS releases, migrating from MySQL, or setting up a new MariaDB instance with explicit sql_mode and charset. Prevents the common mistake of relying on implicit defaults that change between versions, mixing sql_mode-strict with legacy data, or assuming utf8 means utf8mb4. Covers sql_mode per version (STRICT_TRANS_TABLES, ANSI_QUOTES, NO_ZERO_DATE, etc.), default server charset_set_server shift from latin1 to utf8mb4 in 11.6, default utf8mb4 collation shift to utf8mb4_uca1400_ai_ci in 11.5, default authentication plug-in evolution, default storage engine (InnoDB), default binlog format MIXED since 10.2.3, my.cnf defaults that break apps on upgrade. Keywords: mariadb defaults, sql_mode, STRICT_TRANS_TABLES, ANSI_QUOTES, default charset, utf8mb4, utf8mb4_uca1400, default authentication, default storage engine, my.cnf defaults, why does this query fail now, upgrade broke my app, sql mode change between versions, NO_ZERO_DATE, ONL

Use when choosing which MariaDB version to install, upgrading between LTS releases, reasoning about feature availability, or interpreting EOL dates. Prevents the common mistake of assuming a feature works in 10.6 when it landed in 10.11, or running production on a non-LTS interim release. Covers LTS cadence (10.6 / 10.11 / 11.4 / 11.8 / next LTS), interim releases, EOL dates, breaking changes 10.6 to 10.11 to 11.x, feature-introduction matrix for top-30 features, upgrade-path with mariadb-upgrade. Keywords: mariadb version, LTS, 10.6, 10.11, 11.4, 11.8, 12, end of life, EOL, breaking change, feature matrix, when was X added, upgrade mariadb, mariadb-upgrade, mariadb_upgrade, version compatibility, jump version, which mariadb should I use, is feature X available, my version is old, support expired, production version

Use when validating a MySQL SQL dump or schema before importing into MariaDB, checking a codebase for MySQL-specific syntax that breaks on MariaDB, or producing a migration remediation checklist. Prevents the common mistake of importing a MySQL 8 dump with JSON-binary assumptions, caching_sha2_password users, INVISIBLE INDEX syntax, or GTID-continuity expectations. Covers a deterministic migration-validation procedure : scan SQL dump or schema for MySQL-specific divergences, flag JSON-storage / GTID-format / auth-plugin / INVISIBLE-vs-IGNORED / sequence-syntax / sql_mode issues, grade severity, produce a remediation list with skill-references to mariadb-impl-migration-mysql-to-mariadb and others. Keywords: migration validation, validate MySQL dump, check schema before import, MySQL to MariaDB compatibility check, will this dump import, migration remediation, MySQL-specific syntax, caching_sha2_password, INVISIBLE INDEX, JSON binary, GTID, pre-migration check, is my dump compatible

Use when emoji or Unicode characters fail to store, "Illegal mix of collations" errors appear in JOINs, ALTER TABLE CONVERT TO is risky, or migrating charset from latin1/utf8mb3 to utf8mb4. Prevents the common mistake of using utf8 (3-byte alias for utf8mb3) instead of utf8mb4, mixed-collation JOIN failures, or in-place CONVERT TO breaking index-prefix limits. Covers utf8 vs utf8mb4 (3-byte trap), collation suffixes (_ci / _cs / _bin), utf8mb4_uca1400 collation family (default 11.5+), Illegal mix of collations error, ALTER TABLE CONVERT TO CHARACTER SET risks, connection charset, index-prefix byte limits. Keywords: encoding, collation, charset, utf8, utf8mb3, utf8mb4, utf8mb4_uca1400, utf8mb4_general_ci, _ci _cs _bin, Illegal mix of collations, emoji not stored, character set, CONVERT TO CHARACTER SET, connection charset, SET NAMES, index prefix limit, why does my emoji disappear, question marks instead of characters, latin1 migration, old_mode, UTF8_IS_UTF8MB3, Incorrect string value, mojibake

Use when a replica is reporting Seconds_Behind_Master > 0 and growing, replication appears stuck, slave_parallel_threads is not helping, or you need to measure actual replication lag. Prevents the common mistake of trusting Seconds_Behind_Master as a real-time metric, leaving a single-threaded applier on a legacy setup, long master-transactions creating cascading lag, or binlog_row_image=MINIMAL causing replay or fix-on-replica failures. Covers measuring lag accurately with pt-heartbeat (NEVER with Seconds_Behind_Master alone), common root causes (single-threaded applier, long master transactions, replica IO bottleneck, large binlog events, GTID position mismatch with gtid_strict_mode), fixing with parallel-applier modes and threads, GTID position checks, and semi-sync timeout misunderstanding. Keywords: replication lag, Seconds_Behind_Master, replica behind, slave lag, pt-heartbeat, slave_parallel_threads, slave_parallel_mode optimistic, binlog_row_image MINIMAL, GTID position mismatch, IO bottleneck on repl

Use when a query is slow and you need to diagnose, enabling the slow query log, analyzing aggregated samples with mariadb-dumpslow or pt-query-digest, profiling via performance_schema, detecting missing indexes from EXPLAIN, or interpreting Using filesort and Using temporary signals. Prevents the common mistake of trusting random one-off timings, leaving the slow query log disabled in production, setting long_query_time too high (10s) and missing sub-second issues, treating Using filesort as harmless, ignoring file-sort vs index-sort distinction, or using deprecated SHOW PROFILE instead of performance_schema. Covers slow_query_log + long_query_time + log_slow_verbosity configuration, mariadb-dumpslow vs pt-query-digest aggregation, performance_schema events_statements_summary_by_digest and events_statements_history_long, the 10.11 variable-rename to log_slow_query_*, missing-index detection patterns, file-sort vs index-sort with descending indexes (10.8+), Using temporary signal, EXPLAIN combined with ANALYZE

Use when ER_LOCK_DEADLOCK or ER_LOCK_WAIT_TIMEOUT errors appear, designing lock-discipline for concurrent transactions, reading SHOW ENGINE INNODB STATUS deadlock section, tuning innodb_deadlock_detect, or handling Galera certification-COMMIT deadlocks. Prevents the common mistake of expecting deadlocks to vanish via retry-without-design-fix, missing gap-lock dynamics, or treating Galera ER_LOCK_DEADLOCK as a bug instead of expected outcome. Covers InnoDB deadlock detection (innodb_deadlock_detect ON default), reading deadlock section of SHOW ENGINE INNODB STATUS, lock-order discipline (always-same-table-order), gap locks vs record locks vs next-key locks, transaction isolation levels, application-layer retry strategy, Galera COMMIT-time deadlock semantics. Keywords: deadlock, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, 1213, 1205, SHOW ENGINE INNODB STATUS, innodb_lock_wait_timeout, innodb_deadlock_detect, gap lock, next-key lock, record lock, lock order, retry transaction, REPEATABLE READ, READ COMMITTED, Galer

Use when migrating a MySQL instance to MariaDB, comparing compatibility per MySQL version, identifying syntax divergences, planning replication bridging, re-validating JSON data after the binary swap, or running mariadb-upgrade to finalise the migration. Prevents the common mistake of expecting GTID continuity (incompatible per L-004), assuming JSON storage is the same (LONGTEXT-alias per L-005 and D-010), copying MySQL 8 INVISIBLE INDEX syntax (MariaDB uses IGNORED per L-006), keeping caching_sha2_password users (no MariaDB plug-in), skipping mariadb-upgrade after the binary swap, or copying my.cnf verbatim across the divide. Covers per-MySQL-version compatibility matrix (5.6 / 5.7 / 8.0 / 8.4), JSON storage divergence and CHECK (JSON_VALID(col)) remediation, GTID-format divergence (one-way dump-and-load only), authentication-plugin shift, mysql.user vs mysql.global_priv (10.4+), sql_mode default differences, role syntax differences, MariaDB sequence syntax (10.3+), INVISIBLE vs IGNORED INDEX rename, the mar

Use when tuning a new MariaDB instance, diagnosing slow server performance, sizing innodb_buffer_pool, choosing between one-thread-per-connection and the thread pool, deciding on the durability vs throughput trade-off, or migrating tuning settings from MySQL to MariaDB. Prevents the common mistake of setting innodb_buffer_pool_size to 50% of RAM (too low for a dedicated DB host), disabling innodb_flush_log_at_trx_commit on financial data, copying a MySQL 8 my.cnf verbatim (different defaults and removed variables), and inflating per-connection buffers without multiplying by max_connections (OOM). Covers innodb_buffer_pool_size (60-80% RAM rule), innodb_buffer_pool_instances (removed 10.6.0+), innodb_buffer_pool_chunk_size (deprecated 10.11.12+ / 11.4.6+ / 11.8.2+), innodb_buffer_pool_size_max, innodb_log_file_size, innodb_log_buffer_size, innodb_flush_log_at_trx_commit (0/1/2/3 modes), innodb_flush_method (deprecated 11.0+), innodb_io_capacity for SSD vs HDD vs NVMe, innodb_doublewrite (fast 11.0.6+), thread_

Use when planning backup strategy, performing logical or physical backup, restoring from backup, doing point-in-time recovery, or running incremental backup chains. Prevents the common mistake of mysqldump locking production, missing PITR coverage with only logical backups, restoring incremental chain in wrong order, or backing up a Galera donor mid-transfer. Covers mysqldump / mariadb-dump 10.5+ logical backup, mariabackup / mariadb-backup physical hot-backup (incremental chains, --prepare, --copy-back), point-in-time recovery via mariadb-binlog --start-datetime / --start-position, single-table restore via --export + DISCARD/IMPORT TABLESPACE, encrypted backup via --stream piped to gpg/openssl, backup verification, RPO/RTO planning matrix, and Galera-specific donor-node selection. Keywords: backup, restore, mysqldump, mariadb-dump, mariabackup, mariadb-backup, physical backup, logical backup, hot backup, incremental backup, point in time recovery, PITR, RPO, RTO, mysqlbinlog, mariadb-binlog, --single-transac

Use when setting up a Galera multi-master cluster, sizing the cluster, choosing an SST method, debugging split-brain or certification failures, or scaling reads off Galera. Prevents the common mistake of running 2-node Galera (split-brain risk), using mysqldump SST in production (slow and blocking), running heterogeneous MariaDB versions in one cluster, or treating COMMIT-time deadlocks as application bugs instead of retrying. Covers 3-node minimum quorum, wsrep_provider (galera-4 since 10.4+), gcomm:// addresses, SST via mariabackup, IST incremental recovery, weighted quorum via pc.weight, garbd arbitrator, certification-based replication semantics (deadlock only at COMMIT), and wsrep_local_cert_failures monitoring. Keywords: Galera cluster, wsrep, wsrep_provider, gcomm, mariabackup SST, IST, multi master, 3 node cluster, split brain, quorum, pc.weight, certification failure, ER_LOCK_DEADLOCK at COMMIT, hot row, wsrep_cluster_status, wsrep_cluster_size, wsrep_local_state, Synced state, galera_new_cluster, bo

Use when setting up primary-replica replication, enabling semi-sync, configuring parallel applier, setting up GTID, configuring multi-source channels with named connections, or bridging replication from MySQL. Prevents the common mistake of expecting MariaDB GTID to interop with MySQL GTID (incompatible per L-004), using STATEMENT binlog with non-deterministic functions, copying MySQL `FOR CHANNEL` syntax, or running parallel_threads without a disk-IO benchmark. Covers server-id assignment, log_bin enable, CHANGE MASTER setup (positional and GTID), START SLAVE, semi-sync enable (built-in 10.3+), slave_parallel_threads and slave_parallel_mode optimistic (default 10.5.1+), MariaDB GTID (domain-server-sequence format), multi-source replication with NAMED CONNECTION syntax, and SHOW SLAVE STATUS monitoring. Keywords: replication setup, CHANGE MASTER, START SLAVE, server-id, log_bin, semi-sync, rpl_semi_sync_master_enabled, parallel applier, slave_parallel_threads, slave_parallel_mode optimistic, GTID, gtid_strict

Use when designing a new MariaDB schema, choosing primary-key type, structuring multi-tenant tables, picking ROW_FORMAT or CHARSET, or applying Frappe/ERPNext table-naming conventions. Prevents the common mistake of UUID-text PK on InnoDB (insert fragmentation), schema-per-tenant scaling without quota, utf8 instead of utf8mb4 (3-byte trap), or composite-key in wrong column-order. Covers PK type choice (BIGINT AUTO_INCREMENT vs ULID/UUIDv7 vs UUID-string), multi-tenant patterns (schema-per-tenant vs row-tenant-id), ROW_FORMAT=DYNAMIC default, CHARSET utf8mb4_uca1400 default 10.6+, foreign-key constraints, generated columns, Frappe naming convention (tab<DoctypeName>, parent/parentfield/parenttype/idx columns), required InnoDB ROW_FORMAT=DYNAMIC + large_prefix for utf8mb4 indexes. Keywords: schema design, primary key, BIGINT AUTO_INCREMENT, UUID, ULID, UUIDv7, multi-tenant, schema per tenant, row tenant id, ROW_FORMAT, DYNAMIC, COMPRESSED, utf8mb4, utf8mb4_uca1400, foreign key, generated column, virtual column,

Use when defining triggers for BEFORE / AFTER INSERT / UPDATE / DELETE, scheduling recurring tasks via events, creating views including the materialized-view workaround, or controlling DEFINER vs INVOKER security context. Prevents the common mistake of overusing triggers for business logic, forgetting event_scheduler is OFF by default, expecting MariaDB to support materialized views (it does not), or choosing TEMPTABLE algorithm on huge views. Covers CREATE TRIGGER with FOLLOWS / PRECEDES, multi-trigger per event (10.2.3+), CREATE EVENT scheduler, ON COMPLETION PRESERVE / NOT PRESERVE, CREATE VIEW with ALGORITHM (UNDEFINED / MERGE / TEMPTABLE), DEFINER vs INVOKER, WITH CHECK OPTION LOCAL / CASCADED, materialized-view workaround via CREATE TABLE AS SELECT + EVENT. Keywords: CREATE TRIGGER, BEFORE INSERT, AFTER UPDATE, BEFORE DELETE, AFTER DELETE, FOR EACH ROW, FOLLOWS, PRECEDES, NEW.col, OLD.col, NEW pseudo-row, OLD pseudo-row, CREATE EVENT, ON SCHEDULE EVERY, ON SCHEDULE AT, event_scheduler, SET GLOBAL event_

Use when writing stored procedures or stored functions, designing error handling with DECLARE HANDLER, using SIGNAL and RESIGNAL for custom errors, choosing DETERMINISTIC vs NOT DETERMINISTIC, or porting MySQL procedures to MariaDB. Prevents the common mistake of marking a function DETERMINISTIC when it reads NOW() or session variables, putting heavy stored-function logic in WHERE clauses (defeats index usage), missing EXIT or CONTINUE handlers leading to silent loop-abort, and forgetting to raise max_sp_recursion_depth before calling a recursive procedure. Covers CREATE PROCEDURE syntax with IN / OUT / INOUT parameters, CREATE FUNCTION with RETURNS, DELIMITER usage, local DECLARE variables, control flow (IF, CASE, LOOP, WHILE, REPEAT, ITERATE, LEAVE), cursor lifecycle (DECLARE / OPEN / FETCH / CLOSE with NOT FOUND handler), error handling via DECLARE HANDLER for CONTINUE / EXIT on SQLSTATE / SQLWARNING / NOT FOUND / SQLEXCEPTION / numeric error codes, SIGNAL with SQLSTATE 45000 and MESSAGE_TEXT, RESIGNAL ins

Use when building audit trails, GDPR-style history retention, time-travel queries, bitemporal data models, or migrating manual history-table patterns to native temporal tables. Prevents the common mistake of system-versioned tables without HISTORY PARTITION (unbounded growth), confusing system-time with application-time, or missing DELETE HISTORY for GDPR compliance. Covers WITH SYSTEM VERSIONING (10.3+), generated row_start/row_end columns, FOR SYSTEM_TIME AS OF/BETWEEN/FROM TO/ALL, partition-by-system-time, INTERVAL AUTO (10.9.1+), application-time periods (10.4+), bitemporal combining (10.5+), DELETE HISTORY BEFORE SYSTEM_TIME. Keywords: system-versioned, system versioning, temporal table, time travel, AS OF, audit trail, GDPR, history retention, bitemporal, application-time period, PERIOD FOR, row_start, row_end, DELETE HISTORY, MariaDB only, FOR SYSTEM_TIME, FOR PORTION OF, history partition, transaction precision, row level precision, point in time query, how do I track row history, why is my history ta

Use when writing window functions like ROW_NUMBER, RANK, LAG, LEAD, computing running totals, top-N per group, percentile bucketing, gap-and-island queries, tree walks with recursive CTEs, hierarchy traversal, or migrating PostgreSQL or MySQL window-queries to MariaDB. Prevents the common mistake of using the GROUPS frame which is NOT supported in MariaDB, forgetting to terminate a recursive CTE, calling LAST_VALUE with the default frame and getting the current row instead of the partition tail, or assuming a CTE is always materialized. Covers window function OVER syntax with PARTITION BY, ORDER BY, and ROWS or RANGE frames (NEVER GROUPS), ranking functions (ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE), value functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE), aggregates used as window functions, named windows via the WINDOW clause, non-recursive CTEs with WITH AS, recursive CTEs since 10.2.2, the CYCLE col_list RESTRICT clause since 10.5.2, and the max_recursive_iterations cap. Keywor

Use when enforcing column or table invariants, validating JSON structure, replacing trigger-based validation, or migrating CHECK constraints from MySQL 8 or PostgreSQL. Prevents the common mistake of relying on app-layer validation, forgetting that NULL passes CHECK, conflating CHECK with NOT NULL, or expecting CHECK to fire on every UPDATE. Covers column-level CHECK, table-level CHECK, CHECK with JSON_VALID, NULL semantics (CHECK passes on NULL), interaction with INSERT IGNORE, performance impact, and when to choose CHECK vs trigger vs app-validation. Keywords: CHECK constraint, ALTER TABLE ADD CHECK, table constraint, column constraint, JSON_VALID CHECK, NULL semantics in CHECK, constraint vs trigger, why does my CHECK allow NULL, ER_CHECK_CONSTRAINT_VIOLATED, data integrity, schema invariant, drop constraint, check_constraint_checks, three-valued logic, SQL 3VL, INSERT IGNORE check, named constraint, how do I validate JSON, getting started with constraints

Use when storing sparse or heterogeneous attributes inside a single column, comparing dynamic columns with JSON, migrating legacy MariaDB schemas that use dynamic columns, or porting data in or out of MariaDB. Prevents the common mistake of using dynamic columns where JSON would be simpler, the assumption that the feature is portable to MySQL (it is MariaDB-only), and the silent NULL return on a type-mismatched COLUMN_GET. Covers COLUMN_CREATE, COLUMN_ADD, COLUMN_GET, COLUMN_DELETE, COLUMN_EXISTS, COLUMN_LIST, COLUMN_CHECK, COLUMN_JSON, blob storage layout, named vs numbered keys, type tags (CHAR, BINARY, INTEGER, UNSIGNED, DECIMAL, DOUBLE, DATE, TIME, DATETIME), nesting up to 10 levels, when to choose dynamic columns vs JSON, and migration patterns to JSON. Keywords: dynamic columns, COLUMN_CREATE, COLUMN_GET, COLUMN_LIST, COLUMN_ADD, COLUMN_DELETE, COLUMN_EXISTS, COLUMN_JSON, COLUMN_CHECK, MariaDB only, MySQL incompatible, not in MySQL, sparse columns, EAV pattern, blob storage, key-value in column, BLOB co

Use when working with JSON columns in MariaDB, comparing MariaDB JSON to MySQL JSON behaviour, indexing JSON paths, validating JSON structure, or extracting / setting / merging JSON values. Prevents the common mistake of assuming MariaDB JSON is binary like MySQL 5.7.8+, forgetting CHECK (JSON_VALID(col)) so the column accepts arbitrary text, using deprecated JSON_MERGE instead of the explicit PATCH or PRESERVE variants, indexing a JSON path with a direct expression instead of a generated column, and confusing JSON_VALUE (scalar) with JSON_EXTRACT or JSON_QUERY (JSON sub-tree). Covers the JSON-as-LONGTEXT alias (D-010), JSON_VALID, JSON_EXTRACT, JSON_VALUE, JSON_QUERY, JSON_TABLE (10.6+), JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, JSON_MERGE_PATCH (RFC 7396) versus JSON_MERGE_PRESERVE, JSON path expressions, CHECK (JSON_VALID(col)) validation, and functional indexing on JSON via virtual or persistent generated columns. Keywords: JSON, JSON_VALID, JSON_EXTRACT, JSON_VALUE, JSON_QUERY, JSON_TABLE, JSON_S

Use when adding indexes, choosing an index type, debugging slow WHERE / ORDER BY / JOIN, indexing JSON paths, or auditing composite-index column-order. Prevents the missing-index full scan, the composite-index wrong column-order trap, redundant indexes wasting writes, and indexing without considering selectivity. Covers B-tree (default), FULLTEXT (InnoDB + Aria + MyISAM), SPATIAL R-tree, hash indexes (MEMORY engine only), composite indexes with the leftmost-prefix rule, prefix indexes for long VARCHAR / TEXT / BLOB, descending indexes (10.8+), IGNORED indexes (10.6+), functional indexes on JSON paths via virtual / persistent columns, and EXPLAIN signals (type=ALL, Using filesort, Using temporary, Using index). Keywords: CREATE INDEX, ADD INDEX, PRIMARY KEY, UNIQUE, B-tree, BTREE, full-text, FULLTEXT, MATCH AGAINST, IN BOOLEAN MODE, spatial, SPATIAL INDEX, R-tree, RTREE, hash, HASH index, MEMORY engine, composite index, leftmost prefix, descending index, DESC index, IGNORED index, invisible index, functional i

Use when creating or altering tables, choosing an ALTER algorithm, dropping objects safely, working with sequences, virtual/persistent columns, invisible columns, or partitioning. Prevents the costly COPY-algorithm ALTER on production, dropping a table referenced by FK without cascade, picking PERSISTENT when VIRTUAL is enough, or misuse of INSTANT-DROP-COLUMN locking semantics. Covers CREATE TABLE patterns, ALTER algorithms INPLACE/COPY/INSTANT/NOCOPY, atomic DDL (10.6+), sequences (10.3+), generated columns (VIRTUAL vs PERSISTENT), invisible columns (10.3+), partitioning (RANGE/LIST/HASH/KEY/COLUMNS), RENAME TABLE atomic-rename. Keywords: CREATE TABLE, ALTER TABLE, DROP TABLE, ALGORITHM=INPLACE, ALGORITHM=COPY, ALGORITHM=INSTANT, ALGORITHM=NOCOPY, sequences, AUTO_INCREMENT, virtual columns, persistent columns, stored columns, invisible columns, partitioning, RENAME TABLE, atomic DDL, online DDL, instant ADD COLUMN, instant DROP COLUMN, how do I add a column without locking, schema migration, blue-green depl

Use when creating users, granting privileges, choosing an authentication plug-in, enabling roles, setting up encryption at rest or TLS, or migrating user schema from MySQL. Prevents the common mistake of using mysql_native_password instead of ed25519, forgetting to enable encryption on binlog / tmp / redo as well as tables, or losing access after upgrading mysql.user to mysql.global_priv. Covers authentication plug-ins (mysql_native_password, ed25519, gssapi, pam, unix_socket), CREATE USER and GRANT model, roles since 10.0.5, encryption at rest via file-key-management plug-in, TLS for client and replication, mysql.user vs mysql.global_priv shift in 10.4+. Keywords: mariadb security, CREATE USER, GRANT, REVOKE, roles, SET DEFAULT ROLE, ed25519, mysql_native_password, gssapi, pam, unix_socket, encryption at rest, file-key-management, TLS, SSL, REQUIRE SSL, mysql.user, mysql.global_priv, access denied, authentication failure, how do I add a user, secure mariadb, file_key_management_use_pbkdf2, file_key_managemen

Use when explaining MariaDB process model, threading, the query execution pipeline, plug-in architecture, in-memory caches, or when reasoning about why a query behaves as it does at the engine layer. Prevents the mistake of treating MariaDB as a black box ; surfaces the buffer-pool, log-buffer, thread-pool, and storage-engine boundaries that drive performance. Covers mysqld process model, connection threads vs thread-pool plug-in, parser-optimizer-executor stages, storage-engine plug-in API, buffer pool, redo log, undo log, binlog, query cache history, MariaDB vs MySQL optimizer divergence. Keywords: mariadb architecture, mysqld process, thread pool, buffer pool, query execution pipeline, storage engine plug-in, why is my query slow, what does mariadb do internally, how does mariadb work, redo log, undo log, binlog, mariadb internals, mariadb architecture diagram, innodb buffer pool, optimizer switch, handler interface, connection lifecycle

Use when designing a MariaDB replication topology, choosing between async, semi-sync, or parallel-applier, configuring GTID, migrating replication from MySQL, or debugging replication lag at the model level. Prevents the common mistake of mixing MariaDB GTID with MySQL GTID, choosing STATEMENT binlog with non-deterministic functions, or running parallel-applier without understanding conflict semantics. Covers asynchronous replication (default), semi-sync built-in 10.3+, parallel-applier with slave_parallel_mode optimistic/conservative/aggressive/minimal/none, MariaDB GTID format domain-server-sequence, binlog formats ROW/STATEMENT/MIXED, multi-source named-connection replication. Keywords: mariadb replication, master slave, primary replica, async replication, semi-sync, semi-synchronous, parallel applier, slave_parallel_threads, slave_parallel_mode, GTID, domain-server-sequence, gtid_strict_mode, binlog format, ROW STATEMENT MIXED, binlog_row_image, NOBLOB, multi-source, replication lag, Seconds_Behind_Master

Use when choosing a storage engine for a new table, migrating from MyISAM to InnoDB, understanding engine-specific behaviors, or reasoning about ACID guarantees on MariaDB 10.6+. Prevents the common mistake of using MyISAM for transactional data, ColumnStore for OLTP, MEMORY for persistent caches, or mixing engines without understanding cross-engine query limitations. Covers InnoDB (default, ACID), Aria (crash-safe MyISAM replacement, system tables), MyISAM (legacy), ColumnStore (analytical), Spider (sharding), CONNECT (federated), MEMORY (in-memory), and a deterministic decision tree per workload. Keywords: storage engine, InnoDB, Aria, MyISAM, ColumnStore, Spider, CONNECT, MEMORY, ENGINE=, which engine should I use, no transactions, table locking, row locking, ACID, MVCC, full-text search, columnar analytics, federation, sharding, in-memory cache, foreign keys missing, table corrupted on restart, slow concurrent inserts, why is my table locking, can I run analytics on InnoDB, federated table, CSV table, my