MySQL and MariaDB

MySQL and MariaDB share one planner and renderer family, but they are separate dialects with different capability sets. Pass an explicit --dialect mysql or --dialect mariadb in examples and CI jobs, and treat a plan reviewed for one variant as unreviewed for the other. Differences that show up in generated SQL:

• Enums are inline ENUM column types, not standalone type objects.
• MariaDB guards constraint and index drops with IF EXISTS; MySQL rejects that guard, so the mysql renderer strips it.
• The DROP CHECK spelling exists only on MySQL 8.0.16+; MariaDB uses the generic DROP CONSTRAINT clause.
• Portable foreign keys require InnoDB tables and compatible column types, signedness, character sets, and collations. MariaDB generated FK columns and MySQL virtual generated FK columns fail before rendering. MySQL stored generated columns reject referential actions the engine cannot apply. When an FK-participating table has no declared engine, Ptah emits ENGINE=InnoDB explicitly instead of trusting the session default.
• SET NULL requires nullable local columns. Explicit foreign-key names are limited to 64 characters; generated names are shortened deterministically.
• A nonunique referenced key must be a complete leftmost BTREE prefix. FULLTEXT, SPATIAL, HASH, parser-backed, expression, and prefix indexes do not qualify.
• A modified SQL SECURITY DEFINER routine is refused before migration SQL is planned when its catalog DEFINER differs from the connected CURRENT_USER(). Connect as that definer, change the desired routine to SQL SECURITY INVOKER, or leave the foreign routine unchanged. Missing ownership facts fail closed too.
• Routines have no overloads. A schema that declares two functions, or two procedures, of one name with different parameters is refused before any statement is planned. Applied, the second CREATE fails with Error 1304 and the first stays, measured on MySQL 8.4 and MariaDB 11.8. A function and a procedure of one name are two routines.
• An index declaring SPATIAL or FULLTEXT is compared against the access method the server reports, so a plain index of the same name over the same column is a difference rather than a match. An index declaring no type accepts whatever the engine chose, and the asymmetry is the engines’: CREATE INDEX over a POINT column leaves INDEX_TYPE=BTREE on MariaDB 11.8 and SPATIAL on MySQL 8.4, so comparing an undeclared type would plan a rebuild on MySQL that MySQL immediately undoes.
• USING BTREE and USING HASH are read on a KEY, INDEX or UNIQUE KEY and rendered back after the column list. A UNIQUE KEY asking for a method is read as a unique index, which is what the server builds and where a method has somewhere to live. BTREE is not carried: INDEX_TYPE reports it for a declared USING BTREE and for an index that asked for nothing alike, so the two are one index to every reader Ptah has, and emitting it would put the clause into the DDL of every index read back from a server. HASH is carried, and whether the server honors it belongs to the storage engine rather than to the dialect: on InnoDB, MySQL 8.4 records BTREE and drops the clause from SHOW CREATE TABLE while MariaDB 11.8 records HASH and prints it back, and on MEMORY both record HASH. MariaDB records it on every engine that takes an index – InnoDB, MEMORY, MyISAM and Aria all report HASH, and ARCHIVE refuses an index at all – so there a desired HASH against a server reporting otherwise is a real difference and is reported. MySQL records it only on MEMORY, so the comparison stays quiet there for the same reason the undeclared type above is not compared: on the default engine a desired HASH reads back as BTREE, and reporting it would plan a rebuild MySQL immediately undoes. Deciding the MySQL case properly needs the table’s storage engine, which the index comparison does not have.
• A column-level UNIQUE is compared by the name the server gives it: the column’s name, with _2 and on where another key of the table holds it. When the database holds the key over the column under another name, the plan drops that key and adds the column’s, as Atlas CE plans it. For a migration that names the key c_x_uq and a schema file that writes x INT UNIQUE, the plan drops c_x_uq and adds x. Where a key the plan drops holds the column’s name, the plan drops it first, so the server gives the column’s key that name. Both engines compare index names without case, so a key named X over x is the column’s key; Atlas CE renames it to x, and Ptah keeps it, because the server does not tell the two names apart.
• A column-level UNIQUE accounts for one key over its column alone. Every other UNIQUE of the table is compared by its name: a second key over the column, and a key over more columns that the column leads. So a INT UNIQUE, b INT, UNIQUE (a, b) matches the keys a and a_2 it builds, and a key the file no longer declares is dropped, as Atlas CE plans it. A key over the column that the file declares by name, as a constraint or as a unique index, stays that declaration’s, and the column’s own key is compared beside it. Both engines build a INT UNIQUE and CONSTRAINT uq_a UNIQUE (a) as two keys, so a database with uq_a alone is planned the key a, as Atlas CE plans it.
• Two constraints on one table may share a name, and both engines accept CONSTRAINT same UNIQUE (a) beside CONSTRAINT same FOREIGN KEY (a). Ptah identifies a named constraint by its type as well as its table and name, so both survive the read, the desired model, and the comparison. MySQL also lets a CHECK share a name with a UNIQUE, where MariaDB answers ERROR 1826.
• A key part’s direction is read back from the catalog, so KEY (a DESC) and KEY (a) are told apart rather than both arriving ascending. It also decides which index a foreign key owns: MySQL will not back one with a descending leading part and builds its own index instead, while MariaDB reuses whatever covers the columns. So a same-named index beside a covering one is the author’s on MariaDB and the engine’s on MySQL, and only the first is planned for removal.
• A key part may be an expression on MySQL – KEY ((a + 1)), a functional key part – and an unnamed one takes the name the server gives it, functional_index, then functional_index_2 and functional_index_3. Two refusals go with it, and they are different facts rather than one rule: MariaDB has no functional key parts at all and answers ERROR 1064 to every spelling, so the dialect decides; MySQL accepts them in an index and refuses one in a PRIMARY KEY with ERROR 3756, so that refusal holds on both engines. A functional part in a table-body UNIQUE KEY is read as a unique index rather than as a constraint, which is what the server builds: MySQL reports one index with NON_UNIQUE=0, a null column and the expression, and a constraint has nowhere to keep an expression.
• An inline KEY, INDEX or UNIQUE KEY the author did not name is read with the name its server would assign: the first key part’s column, then _2, _3 for a name already taken. A prefix length and a DESC direction stay out of the name. Names are taken in the order the body writes the keys, and a column’s own UNIQUE, or the index its REFERENCES builds on MariaDB, takes its name at the column’s place: KEY (a), a INT UNIQUE names the index a and the column’s key a_2, and a INT UNIQUE, KEY (a) the other way round. The name is decided when the SQL is read rather than when it is written, because the catalog reports what the server chose and a desired schema that guessed differently would never converge with it.
• An unnamed UNIQUE, KEY or INDEX that ALTER TABLE ... ADD adds is named by the same rule, against every index the table holds at that point of the schema, the earlier files of a directory included. The index a foreign key built holds a name too: the key’s own name, or its first column’s for an unnamed key. The server drops that index when the added one covers the key, and the added one can then take its name, so FOREIGN KEY (a) REFERENCES p(id) followed by ALTER TABLE c ADD UNIQUE (a) leaves one index, a. MySQL does not let a descending leading part cover a foreign key and MariaDB does, so ADD KEY (a DESC) is a_2 on MySQL and a on MariaDB.
• A foreign key the author did not name is read with the name the server gives it: <table>_ibfk_<n>. In CREATE TABLE the unnamed keys are numbered from 1 in the order they are written, and a named key does not move the count. A key that ALTER TABLE adds takes one more than the highest <table>_ibfk_<n> the table held before the statement: a key the same statement drops still counts, and a key the statement adds under a name does not. The server names the index it builds for such a key after the key’s first column, then _2 and on, and that index is read as the key’s, so a schema file compares equal to the database its own SQL built. A derived name that another foreign key of the database already holds is refused, because the server refuses it: MySQL answers ERROR 1826 and MariaDB ERROR 1005. So is a derived name the server does not keep as it is. MySQL answers ERROR 1059 to a name over 64 characters. MariaDB through 12.0 answers it to a name of 64 characters in CREATE TABLE, and in ALTER TABLE cuts a longer name to 64 without a warning. Ptah refuses the cut name too, because the key would not have the name the file implies.
• On MariaDB a column-level a INT REFERENCES p(id) builds a key, numbered with the table’s other unnamed keys in the order the body writes them: a table-level key written before the column takes the lower number. MySQL 8.4 builds nothing from the clause, and Ptah refuses it under --dialect mysql.
• The name in FOREIGN KEY idx (a) REFERENCES p(id) names the key itself on MariaDB, and the key’s index takes it. On MySQL it names only the key’s index, and the key is <table>_ibfk_<n>.
• The index the server builds for a foreign key follows its own rule, on both engines, and Ptah reads a schema file by it. The server builds one only where no other index begins with the key’s columns: not where the body declares such an index, and not for a key whose columns begin a longer key’s, or match a later key’s. The index outlives its key: ALTER TABLE ... DROP FOREIGN KEY and DROP CONSTRAINT leave it, and it keeps an index of the key’s name that the author declared. And it gives way to any index added later that begins with its columns, which then takes its place and can take its name. The index a MySQL FOREIGN KEY idx (a) clause names is such an index, under that name. An index the author declared never gives way. So a file that drops a key, or adds a UNIQUE over a key’s column, compares equal to the database it built.
• MariaDB 12.1 and later name an unnamed key <n> rather than <table>_ibfk_<n>, and the name belongs to the table rather than to the database. A schema file does not say which line it is for, so Ptah reads the older name and compares a key the server numbered with the key of the same definition. Two effects of that choice stay on the newer lines (stokaro/ptah#3762). A document whose older name collides with another key, or is too long for the older lines, is refused on every line, although 12.1 and later accept it. And a key Ptah creates is written with the older name, so the database holds c_ibfk_1 where the file run by hand would hold 1; a later comparison matches either.
• A CHECK the author did not name is read with the name its server gives it. MySQL names it <table>_chk_<n>: in CREATE TABLE the unnamed checks are numbered from 1 in the order they are written, on a column or on the table, and a named check does not move the count. A check that ALTER TABLE adds takes one more than the highest <table>_chk_<n> the table holds once the statement’s drops are made; a check the statement adds under a name does not count. A derived name that another check of the database already holds is refused, because MySQL refuses it with ERROR 3822, and so is a name past 64 characters, which MySQL refuses with ERROR 1059. MariaDB names a check written on a column after the column, and one written on the table CONSTRAINT_<n>, with the smallest number no check of the table holds, the names the same statement writes included. MariaDB takes one check on a column and answers ERROR 1064 to a second, and Ptah refuses it the same way.
• A column check declared in YAML or a Go annotation without check_name is written without a name, and the comparison looks for the name the server gives it: <table>_chk_<n> on MySQL, numbered in column order among the unnamed column checks, and the column’s own name on MariaDB. Measured on MySQL 8.4.11 and MariaDB 11.8.9, a table whose columns a and b each carry one holds e_chk_1 and e_chk_2 on MySQL, and a and b on MariaDB.
• A CHECK written on a column may name only that column on MySQL. CREATE TABLE c (a int, b int CHECK (b > a)) is ERROR 3813 (HY000): Column check constraint ... references other column on MySQL 8.4 and 26.7, and so is the same check on a column that ALTER TABLE adds or modifies. Ptah refuses such a file when it reads it for MySQL, and the message says to write the check at table level. MariaDB accepts it.
• Both engines accept CONSTRAINT without a name before PRIMARY KEY, UNIQUE, FOREIGN KEY and CHECK, as in CONSTRAINT FOREIGN KEY (p_id) REFERENCES p(id). Ptah reads such a clause as the same clause written without the keyword, which is how the server builds it: it takes the name that clause takes, so an unnamed foreign key counts in the same <table>_ibfk_<n> sequence as the other unnamed keys of its table, and an unnamed CHECK in the same sequence as the table’s other checks. The server answers ERROR 1064 to every other symbol-less spelling and to a CONSTRAINT in front of KEY, INDEX, FULLTEXT or SPATIAL, with a name or without one, and Ptah refuses each of them by name.
• On a column, MySQL accepts CONSTRAINT only before CHECK and MariaDB only before REFERENCES, with a name or without one, as in a INT CONSTRAINT ck CHECK (a > 0) on MySQL. Measured on MySQL 8.4, MySQL 26.7 and MariaDB 11.8, the server answers ERROR 1064 to every other kind, such as a INT CONSTRAINT uq UNIQUE, CONSTRAINT pk PRIMARY KEY or CONSTRAINT nn NOT NULL, and Ptah refuses each of them by name. Declared at table level, a unique key keeps its name on both engines, and a check keeps its name on MariaDB. In the column definition of ALTER TABLE ... MODIFY, MariaDB takes CONSTRAINT before nothing, and neither engine takes REFERENCES, with CONSTRAINT in front or without it; Ptah refuses those too.
• A bare column REFERENCES, as in a INT REFERENCES p(id), means something different on each MySQL line: MySQL 8.4 builds nothing from it, while MySQL 9.7 and 26.7 build the foreign key <table>_ibfk_<n> and an index on the column. A SQL file is read without the server version, so --dialect mysql refuses the clause and asks for a table-level FOREIGN KEY, which every line builds. MariaDB builds the key, and --dialect mariadb reads it.
• A non-ASCII index name is refused, rather than compared. The two engines fold such names differently and not in a way one rule covers: measured on MySQL 8.4.11 and MariaDB 11.8.9 over a utf8mb4 connection, I beside dotless ı and Σ beside final ς are accepted by MySQL and answer ERROR 1061 on MariaDB, while dotted İ beside i and the Kelvin sign beside K do the opposite. A lone prımary is accepted by MySQL and answers ERROR 1280 on MariaDB, which is why a solitary non-ASCII name is not a safe exception either – it is still an unresolved comparison against the reserved PRIMARY. ASCII folding is shared and deterministic and is unchanged; a name derived from a non-ASCII column is refused for the same reason an explicit one is.
• Migration planning refuses a plan whose index names may collide, rather than emitting one the server rejects halfway. A name carrying a non-ASCII rune has an equivalence class Ptah cannot compute offline, so it is treated as a possible conflict with every other index name on that table, ASCII ones included – İ collides with plain i on MySQL, so grouping only the non-ASCII names together would still miss it. Measured, the alternative was a half-applied migration: rendering CREATE INDEX İ and CREATE INDEX i against MySQL 8.4.11 creates the first and answers ERROR 1061 on the second.
• A comparison that reaches the target asks it instead of guessing. The conservative rule above is what an offline run still does, and it was the only answer until the names could be resolved against the server: a beside ä is accepted by both engines and was reported as a possible conflict for want of anything better. A comparison holding a connection now asks, and gets the engine’s own answer for each pair. What is asked is the collision rather than a fold to imitate, because neither engine exposes the fold its identifier comparison uses – measured, none of LOWER(), utf8mb4_general_ci, utf8mb4_unicode_ci, utf8mb4_uca1400_ai_ci or utf8mb4_bin reproduces MariaDB’s. A temporary table carrying the names as keys either creates or answers ERROR 1061, which is exactly the question, and a temporary table is this session’s alone and gives the per-table namespace an index name actually lives in. Only names Ptah cannot fold are asked about, so a schema whose index names are all ASCII reaches no server and pays nothing. Sixty-four keys is the per-table ceiling on both engines, so a larger set is asked in several statements, each carrying every non-ASCII name so no pairing is missed. A refusal that is not the duplicate-name answer – a lost connection, or an account without CREATE TEMPORARY TABLES, which answers ERROR 1044 – is reported rather than read as an equivalence.
• Column names are compared ASCII-case-insensitively, and a non-ASCII one is treated as a possible conflict with every column in its table. Both engines fold ASCII case: a table declaring A and a answers ERROR 1060, and a foreign key written a binds to a column declared A and reuses its key – so modeling these names as exact reported drift on every run and missed a duplicate the server refuses. Beyond ASCII the two disagree, and the same fold decides both questions: MySQL calls İ/i and the Kelvin sign/K one column and MariaDB calls I/ı and σ/ς one column, each engine accepting a foreign key written with either spelling of a pair it folds and reporting a missing key column for a pair it does not. The ASCII half of a pair is why the conflict is table-wide rather than per name: İ collides with plain i on MySQL.
• A non-ASCII column named by a key, a constraint, or its own UNIQUE is refused for the same reason, and the disagreement runs deeper there. Asked whether two columns differing only by the pair are one name, MySQL folds dotted İ/i and the Kelvin sign/K while MariaDB folds I/ı and σ/ς – and MariaDB folds the Kelvin pair for that question while treating the two as different columns when it resolves a foreign key, so the rule is not one per engine either. A column nothing keys takes part in no comparison and is kept.
• Two indexes on one table claiming one name are refused. Both engines answer ERROR 1061 Duplicate key name, so accepting it would describe a table neither can create. KEY (a), KEY a (b) is that shape: the unnamed index takes a as soon as it is read, and the later explicit a collides with it.
• A table body declares a spatial or full-text index as {SPATIAL|FULLTEXT} [INDEX|KEY] [name] (columns), and every optional part of that is optional here too — FULLTEXT (bio) is as readable as FULLTEXT INDEX ft_bio (bio). The WITH PARSER <name> clause travels with it. KEY matters as much as INDEX: both dump tools normalize to it, so a table written with FULLTEXT INDEX comes back out of mysqldump as FULLTEXT KEY. An index left unnamed takes the name its server would give it, by the rule above.
• KEY, SPATIAL, FULLTEXT and INDEX open a table-level index here, and both engines reserve all four, so no column carries those names bare. Ptah reads the word as an index on this family and in dialect-neutral documents, which have no family to ask. A .sql source read for another dialect gets a column wherever that engine has no table element opening with the word. key, spatial and fulltext are columns on PostgreSQL, YugabyteDB, CockroachDB, Spanner, ClickHouse, SQLite and Oracle. SQL Server reserves key, so only spatial and fulltext are columns there. index is a column on PostgreSQL, YugabyteDB and Spanner, and on CockroachDB unless a column list follows it, directly or after a name. SQL Server reads index as its own inline index and ClickHouse as a data-skipping index, and SQLite and Oracle reserve it. Read as an index on the other dialects, key text would be refused, and spatial nvarchar(32) has the shape of an index named after its type over a column named 32 (stokaro/ptah#3089, stokaro/ptah#3299). An index names columns, so a column list that is a number is refused, and the refusal names the keyword. MySQL and MariaDB answer the same statement with Error 1064.
• A column carrying both a primary key and a UNIQUE is written back the way it was read, because the two spellings do not mean the same thing. a INT UNIQUE, PRIMARY KEY (a) builds the primary key and a secondary unique index named a on both engines, and it is rendered as the table-level key it was; a INT PRIMARY KEY UNIQUE builds both on MySQL and the primary key alone on MariaDB, and it is rendered inline so each engine gives its own answer. Folding the first into the second would lose MariaDB’s second index.
• DDL commits implicitly on both engines, so a failed migration cannot be rolled back by the surrounding transaction.

Dev-database cleanup privileges

Database-realm cleanup requires global SELECT, DROP, ALTER, ALTER ROUTINE, EVENT, LOCK TABLES, and PROCESS. MySQL also requires global TRIGGER and, on MySQL 8.0.20 and newer, SHOW_ROUTINE; MariaDB requires global SHOW VIEW. Ptah verifies this privilege set before destructive DDL. Cleanup fails closed when another user database contains a routine, event, or trigger because its body can reference the cleanup realm without a catalog dependency. Grant these privileges only to credentials used with a dedicated disposable dev database.

Making a column NOT NULL

A plan that makes an existing column NOT NULL writes MODIFY COLUMN with the whole new definition. What the server does with a row that holds NULL depends on the session’s SQL mode:

• Under strict SQL mode, the default on both servers, the statement fails: MySQL answers error 1138 and MariaDB error 1265, and the row keeps its NULL.
• Without STRICT_TRANS_TABLES, both servers apply the statement and rewrite the NULL to the type’s zero value, such as 0 or the empty string, with a warning. A DEFAULT in the same MODIFY is not what they write.

The safety report lists the statement as a warning for both reasons. Update the NULL rows in a migration of their own first. A MODIFY that repeats NOT NULL for a column that already has it, because its type or default changed, is judged by that change instead.

Online DDL for large tables

For large tables, ptah migrations up and down can route ALTER TABLE statements through gh-ost or pt-online-schema-change, either per migration with a -- +ptah online_ddl_tool=ghost directive or automatically above a configured row-count threshold:

online_ddl:
  tool: ghost
  threshold_rows: 1000000

A tool-routed migration runs on the tool’s own connections and is not atomic: keep online-DDL migrations minimal, ideally one ALTER per file. The online_ddl keys, including fallback and args, are listed in Configuration.

Next steps

• Which release lines are declared and at what support level: Database support matrix.
• Capability keys per dialect: Capabilities.
• The online_ddl keys and every other configuration key: Configuration.