MySQL and MariaDB
MySQL and MariaDB in Ptah - the dialect differences that reach generated SQL, the foreign-key and routine rules, TLS with a private CA, cleanup privileges, and online-DDL routing.
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
ENUMcolumn types, not standalone type objects. - MariaDB guards constraint and index drops with
IF EXISTS; MySQL rejects that guard, so themysqlrenderer strips it. - The
DROP CHECKspelling exists only on MySQL 8.0.16+; MariaDB uses the genericDROP CONSTRAINTclause. - 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=InnoDBexplicitly instead of trusting the session default. SET NULLrequires nullable local columns. Explicit foreign-key names are limited to 64 characters; generated names are shortened deterministically.- A foreign key may reference a table in another database, such as
REFERENCES crm.customers (id)on a table ofshop. A read ofshopkeeps the namecrm:db readand SQL output writeREFERENCES `crm`.`customers`(`id`), HCL output writestable.crm.customers, and a schema file declaring the key compares equal to the database. The description does not holdcrm.customers, so Ptah cannot check its columns or its key, and the server checks them when the statement runs. An unqualified reference, or one to a database the description holds a table of, must still name a table the description holds. Where the read a command compares against coverscrmtoo, as a URL naming no database does without--schemas, a desired schema that declares nothing ofcrmasks for its tables to be dropped; Ptah refuses a key into it by name rather than plan the drop. - A nonunique referenced key must be a complete leftmost BTREE prefix. FULLTEXT, SPATIAL, HASH, parser-backed, expression, and prefix indexes do not qualify.
- A routine that states no security mode runs with its definer’s rights, as
the server applies to one created by hand: a declaration that omits
securityisSQL SECURITY DEFINER. Declaresecurity="INVOKER"for a routine that runs with its caller’s rights. - A modified
SQL SECURITY DEFINERroutine is refused before migration SQL is planned when its catalogDEFINERdiffers from the connectedCURRENT_USER(). Connect as that definer, change the desired routine toSQL SECURITY INVOKER, or leave the foreign routine unchanged. Missing ownership facts fail closed too. An unrelated--excludeselector does not change the connected account used for this check. A view of another definer is refused the same way; see View security. - 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
CREATEfails 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
SPATIALorFULLTEXTis 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 INDEXover aPOINTcolumn leavesINDEX_TYPE=BTREEon MariaDB 11.8 andSPATIALon MySQL 8.4, so comparing an undeclared type would plan a rebuild on MySQL that MySQL immediately undoes. Spatial indexes report a bounding-box size inSTATISTICS.SUB_PART. Ptah keeps that size out of column prefixes, so reading and planning an unchanged spatial index produces no migration. USING BTREEandUSING HASHare read on aPRIMARY KEY,KEY,INDEXorUNIQUE KEYand rendered back after the column list. AUNIQUE KEYasking for a method is read as a unique index, which is what the server builds and where a method has somewhere to live.BTREEis not carried:INDEX_TYPEreports it for a declaredUSING BTREEand 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.HASHis carried, and whether the server honors it belongs to the storage engine rather than to the dialect: on InnoDB, MySQL 8.4 recordsBTREEand drops the clause fromSHOW CREATE TABLEwhile MariaDB 11.8 recordsHASHand prints it back, and onMEMORYboth recordHASH. MariaDB records it on every engine that takes an index –InnoDB,MEMORY,MyISAMandAriaall reportHASH, andARCHIVErefuses an index at all – so there a desiredHASHagainst a server reporting otherwise is a real difference and is reported. MySQL records it only onMEMORY, so the comparison stays quiet there for the same reason the undeclared type above is not compared: on the default engine a desiredHASHreads back asBTREE, 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 primary key is compared the same way; where its method differs, the plan drops the key and adds it with the method. Other dialects have no clause for a primary key’s method, so a schema asking for one is refused there.- A column-level
UNIQUEis compared by the name the server gives it: the column’s name, with_2and 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 keyc_x_uqand a schema file that writesx INT UNIQUE, the plan dropsc_x_uqand addsx. 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. Where a foreign key stands on that key, the drop goes in the statement that gives the column its key,ALTER TABLE t DROP INDEX x, MODIFY COLUMN x INT UNIQUE, becauseDROP INDEXalone answersERROR 1553, and the foreign key then stands on the column’s key. A foreign key the column’s key cannot serve, one over more columns, gets an index of its own in the statement that drops the old one, ahead of the column change. A plan that turns aUNIQUEinto an index under the same name, as the rollback of such a column does, writes one statement as well:ALTER TABLE t DROP INDEX x, ADD INDEX x (x). Both engines compare index names without case, so a key namedXoverxis the column’s key; Atlas CE renames it tox, and Ptah keeps it, because the server does not tell the two names apart. - A column-level
UNIQUEaccounts for one key over its column alone. Every otherUNIQUEof the table is compared by its name: a second key over the column, and a key over more columns that the column leads. Soa INT UNIQUE, b INT, UNIQUE (a, b)matches the keysaanda_2it 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 builda INT UNIQUEandCONSTRAINT uq_a UNIQUE (a)as two keys, so a database withuq_aalone is planned the keya, as Atlas CE plans it. - A plan that drops a column with its own
UNIQUEwrites noDROP INDEXfor that key. Both engines drop an index with the last of its columns, and MySQL 26.10 answers a laterDROP INDEX xwithCan't DROP 'x'; check that column/key exists. The rollback of a plan that gives a column itsUNIQUEdrops the key again, and a key the rollback adds back is added once, as a constraint, not through the column’sUNIQUEas well. - A plan that changes a column writes
MODIFY COLUMNwith the whole new definition, and writesUNIQUEin it only when the change gives the column itsUNIQUE. Both engines read the clause as a request for a new key, soMODIFY COLUMN x BIGINT UNIQUEon a column that already has its key builds a second one,x_2. A column that keeps itsUNIQUEwhile its type, nullability or default changes is written without the clause and keeps its one key, as Atlas CE writes it. - An index’s
COMMENT, including a primary key’s, is kept inSTATISTICS.INDEX_COMMENTon both engines. SQL inspection preserves primary-key comments on both CLI surfaces. Applying the export to the same database keeps the key and comment. A changed secondary-index comment rebuilds the index in one statement:ALTER TABLE t DROP INDEX k, ADD INDEX k (...) COMMENT '...'. MySQL 8.4.11 refuses aDROP INDEXalone on an index a foreign key needs (ERROR 1553) and takes the pair. Atlas CE writes the drop and the add as two statements. Primary-key replacements also use oneALTER TABLEsoAUTO_INCREMENTand incoming foreign keys keep their supporting index. - A column’s
AUTO_INCREMENTis compared, and a change is written asMODIFY COLUMN. A key column is restatedNOT NULLwithoutPRIMARY KEY, because both engines refuse aMODIFY COLUMN ... PRIMARY KEYon a table that has a key (ERROR 1068); the same holds for a key column whose type changes. - An expression default is compared as the operations its operators group,
by the precedence both engines share, and read back as an expression on
every column type. MySQL 26.10 stores
(2 * 3 + 1)as((2 * 3) + 1),(a + b * 2)as(`a` + (`b` * 2)),(CURRENT_TIMESTAMP + INTERVAL 1 DAY)as(now() + interval 1 day)and(concat('a', 'b'))asconcat(_utf8mb4'a',_utf8mb4'b'). MariaDB 12.3 writes7 % 4as7 MOD 4andNOW()ascurrent_timestamp(). Each pair is one default, so a second apply plans nothing, anddb readwrites the default in parentheses. Only a current-time function on aDATETIMEorTIMESTAMPcolumn stays bare: MySQL 8.4 and 26.10 refuseDEFAULT uuid()withERROR 1064andDEFAULT now()on aVARCHARwithERROR 1067. Adefault_exprin a Go struct or a YAML schema is written by the same rule, sodefault_expr="1 + 2"rendersDEFAULT (1 + 2). A parenthesis that changes the grouping is part of the default:((1 + 2) * 3)and(1 + 2 * 3)differ.NOT,IS,IN,BETWEEN,LIKEandREGEXPare read the way the server writes them back: MySQL stores(NOT 1 + 1)as(0 = (1 + 1)), and the two are one default. An expression that holdsCASE,LIKE ... ESCAPE,IS UNKNOWNor a qualified name is compared as written. - A string default is not an expression default, even where its characters
spell the expression.
DEFAULT '1 + 2'stores five characters andDEFAULT (1 + 2)stores 3, so a plan between the two changes the column in either direction, and the same holds for'now()'and(now()). MySQL reports both of those asnow()and tells them apart byDEFAULT_GENERATEDinEXTRA, anddb readwrites the string in quotes. Adefaultin a Go struct or a YAML schema is such a string, sodefault="1 + 2"against a column that holds(1 + 2)changes it too. - A date or time default of
'now','today','tomorrow'or'yesterday'is sent as written. Ptah refuses one only on the PostgreSQL family, where the server stores the moment it creates the table (see PostgreSQL). MySQL 26.10 and MariaDB 12.3 refuseDATETIME DEFAULT 'now'themselves, withERROR 1067. DEFAULT ''and no default are different defaults, and so areDEFAULT 'NULL'andDEFAULT NULL. MySQL reports the stringNULLwithout quotes and SQL NULL as no value, and the empty string as an empty value.db readwrites each as the string it is.db readwrites a backslash in a MySQL string default doubled, soDEFAULT 'a\\b'replays as the three characters it stores, and a control character as its escape, soDEFAULT 'a\nb'replays with its line feed. A quote escaped with a backslash and a doubled quote compare as one character. The catalog reports a default the same way under everysql_mode, so a read in a session whosesql_modenamesNO_BACKSLASH_ESCAPESwrites the same file.- A schema file may write a default as a bit string (
b'101',0b101), a hexadecimal string (X'6A',0x6a), a string with a character set introducer (_utf8mb4'x') orN, adjacent strings ('a' 'b') or a double-quoted string ("a"). Each is read as the literal the server stores. On aBINARY,VARBINARYorBLOBcolumn a string and the hexadecimal string of its bytes are one default, since MySQL reportsDEFAULT 'xy'as0x7879and MariaDBDEFAULT ('xy')asx'7879'. On a text column a hexadecimal or bit string is the string of its bytes, so0x41,b'1000001'and'A'are one default. On an integer column0x1Fand the31the server reports are one default, and so areb'101'and5; on aBITcolumn0b110andb'110'are one default. Both engines takeucaseandlcaseforupperandlower, and MariaDB printsupperandlowerasucaseandlcase. The comparison reads a call to either name as a call toupperorlower, anddb readwritesupperandlower. SERIAL,BIGSERIALandSMALLSERIALare written asINT,BIGINTandSMALLINTwithAUTO_INCREMENT, unless the field gives its own MySQL or MariaDB type. Both engines take one auto-increment column per table, and only as the first column of a key (ERROR 1075), so a serial column that breaks either rule is refused by name before anything runs.- A primary key declared on its column and again as a
PRIMARY KEYconstraint over the same columns is one key, and the table is created with it once. Both engines refuse aCREATE TABLEthat holds two primary keys (ERROR 1068). A new table holds itsPRIMARY KEYconstraint inside itsCREATE TABLE, because both engines refuse anAUTO_INCREMENTcolumn that no key of the same statement covers (ERROR 1075). - A plain
KEYthat is the only key of anAUTO_INCREMENTcolumn shares a statement with the column for the same reason. A new table holds it inside itsCREATE TABLE. A column that comes or starts counting adds it in the sameALTER TABLE, and one that goes or stops counting drops it there. KEY_BLOCK_SIZE [=] non a secondary, unique or primary index is retained by MariaDB. MySQL retains it only on a table withROW_FORMAT=COMPRESSED. Ptah reads the hint fromSHOW CREATE TABLEand compares it where the server keeps it.ptah db readpreserves compression so replay retains the hint. Zero selects the engine default. Values above 65,535 on MariaDB or 4,294,967,295 on MySQL are refused because the servers truncate them. Changing or removing only a stored secondary-index hint on MySQL requiresALGORITHM=COPY: an in-place drop and add of an unchanged key keeps the old hint. This can copy the whole table, and the safety classification says so. Withonline_alter,LOCK=NONEmakes the server refuse that blocking operation. When the key, the uniqueness or the comment changes too, the one rebuild writes the new hint without a copy: measured on MySQL 8.4.11, an in-place rebuild that changes the definition stores it. MariaDB 11.8.9 stores a new hint in place in every case. A primary key behaves the same way: replacing it for its hint alone asks MySQL forALGORITHM=COPY, and a replacement that also changes the columns or the comment does not. The key is compared by its table, so a key declared asCONSTRAINT pk_t PRIMARY KEYmatches the one MySQL namesPRIMARY. An index or a key a YAML or HCL source declares has no hint, so applying one removes a stored hint. Other dialects refuse the hint.- Go index annotations use
key_block_size="8". A table annotation can useprimary_key="id" primary_key_block_size="8" primary_key_comment="lookup"; the hint needs the key declared withprimary_key, since a field’sprimarywrites a column key with no place for it, and is refused without one. APRIMARY KEYconstraint annotation useskey_block_sizeandcomment. AUNIQUEor any other constraint annotation that writeskey_block_sizeis refused when the source is read, as rendering refuses it: MySQL keeps aUNIQUEkey’s hint on the unique index it is, so declare a unique index withunique="true"instead. These attributes belong to the MySQL owner: a parse that does not select it refuses them as unknown. Atlas HCL output reports the block-size hint it cannot represent. - An index the optimizer does not use is
INVISIBLEon MySQL andIGNOREDon MariaDB, and each engine answersERROR 1064to the other’s word. Ptah reads it fromSTATISTICS.IS_VISIBLEorSTATISTICS.IGNORED, writes it in the target’s word, and changes it in place withALTER TABLE t ALTER INDEX k INVISIBLEorIGNORED. Neither engine hides a primary key (ERROR 3522,ERROR 4174), and the reader refuses one. CockroachDB has such an index, spelledNOT VISIBLE; see Invisible indexes. A target without one, PostgreSQL among them, refuses an invisible index rather than build it visible. Atlas HCL has no attribute for it, soschema inspect --format hclwrites the index and reports on stderr that the document makes it visible. - Two constraints on one table may share a name, and both engines accept
CONSTRAINT same UNIQUE (a)besideCONSTRAINT 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 aCHECKshare a name with aUNIQUE, where MariaDB answersERROR 1826. NOT ENFORCEDafter aCHECKis kept on MySQL 8.0.16+ and read back frominformation_schema.TABLE_CONSTRAINTS; MariaDB has no such clause. A foreign key’sMATCH FULLorMATCH PARTIALis kept on MySQL, which records it inREFERENTIAL_CONSTRAINTS.MATCH_OPTIONand leaves it out ofSHOW CREATE TABLE. MySQL 8.4.11 still checks such a key asMATCH SIMPLE. MariaDB 11.8.9 accepts the clause and recordsNONE, so Ptah refuses it there rather than read a key the server does not build. A constraint whose enforcement or MATCH type changes is dropped and added again.- A key part’s direction is read back from the catalog, so
KEY (a DESC)andKEY (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. - Each key part is compared whole: its column or expression, its prefix length
and its direction, read from
information_schema.STATISTICS. Neither engine changes a key part in place, so a changed part rebuilds the index in oneALTER TABLE, as a changed comment does. - 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, thenfunctional_index_2andfunctional_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 answersERROR 1064to every spelling, so the dialect decides, and Ptah refuses the index by name before the plan reaches the server; MySQL accepts them in an index and refuses one in aPRIMARY KEYwithERROR 3756, so that refusal holds on both engines. A functional part in a table-bodyUNIQUE KEYis read as a unique index rather than as a constraint, which is what the server builds: MySQL reports one index withNON_UNIQUE=0, a null column and the expression, and a constraint has nowhere to keep an expression. - An inline
KEY,INDEXorUNIQUE KEYthe author did not name is read with the name its server would assign: the first key part’s column, then_2,_3for a name already taken. A prefix length and aDESCdirection stay out of the name. Names are taken in the order the body writes the keys, and a column’s ownUNIQUE, or the index itsREFERENCESbuilds on MariaDB, takes its name at the column’s place:KEY (a), a INT UNIQUEnames the indexaand the column’s keya_2, anda 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,KEYorINDEXthatALTER TABLE ... ADDadds 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, soFOREIGN KEY (a) REFERENCES p(id)followed byALTER TABLE c ADD UNIQUE (a)leaves one index,a. MySQL does not let a descending leading part cover a foreign key and MariaDB does, soADD KEY (a DESC)isa_2on MySQL andaon MariaDB. - A foreign key the author did not name is read with the name the server gives
it:
<table>_ibfk_<n>. InCREATE TABLEthe unnamed keys are numbered from 1 in the order they are written, and a named key does not move the count. A key thatALTER TABLEadds 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_2and 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 answersERROR 1826and MariaDBERROR 1005. So is a derived name the server does not keep as it is. MySQL answersERROR 1059to a name over 64 characters. MariaDB through 12.0 answers it to a name of 64 characters inCREATE TABLE, and inALTER TABLEcuts 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 KEYandDROP CONSTRAINTleave 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 MySQLFOREIGN 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 aUNIQUEover a key’s column, compares equal to the database it built. - A plan that removes the last index a foreign key it keeps can stand on, whoever
built it, gives the key an index of its own in the statement that drops the
old one:
ALTER TABLE t DROP INDEX cover, ADD INDEX k (a).DROP INDEXalone answersERROR 1553, and an index added first can make the server drop one it built by itself, so that a laterDROP INDEXanswersERROR 1091. The new index takes the name the server gives the index it builds for that key. A key an exclude selector keeps out of scope gets its index the same way, with a comment above the statement that names the key; the selector leaves the new index out with the key, so the next run plans nothing. - A plan that adds an index or a key the foreign key can stand on drops the
index the server built for the key in the statement that adds it:
ALTER TABLE t DROP INDEX k, ADD INDEX i (a), and in the same way withADD CONSTRAINT u UNIQUE (a), withMODIFY COLUMN a INT UNIQUEand withADD PRIMARY KEY (a, id). Once the addition is there, the server drops the index it built by itself, also afterDROP FOREIGN KEY, so a laterDROP INDEX kanswersERROR 1091, and the rollback’s removal of the addition answersERROR 1553while a key the plan kept stands on it alone. An index the author namedkstays, and the catalog does not tell the two apart, so the plan drops it either way. Where the plan makes several such additions, the first statement dropskand the others leave it out. For a key the plan removes, a new index comes after the key’s drop and a new key comes before it; the rollback addskback before it removes the addition. A descending index takes nothing over on MySQL, which stands no key on it, and takes the index over on MariaDB, which does. - A foreign key that moves to another column under its name takes that name for
its new index, so a plan drops the key, then the old index, then adds the key.
With the old index in place
ADD CONSTRAINTanswersERROR 1061: Duplicate key name. The same order serves a new key named after an index that leaves. Every other key the plan moves or removes on that table is dropped first as well, since the server answers the index’s drop withERROR 1553while one of them stands on it. Two keys moved onto one column end on one index under the second key’s name, as the server builds a key an index of its own in place of one it built for another key over the same columns. A rollback drops both keys and rebuilds that index under its name in one statement. Where a key the plan keeps stands on that index, the statement that gives the kept key an index of its own takes the old one out at that point, and a rollback drops the moved key first, then rebuilds the index under its name in one statement. Where that statement takes out several indexes, each under the name of a key the plan moves or adds, it goes once, at the first of those keys. - 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 holdsc_ibfk_1where the file run by hand would hold1; a later comparison matches either. - A
CHECKthe author did not name is read with the name its server gives it. MySQL names it<table>_chk_<n>: inCREATE TABLEthe 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 thatALTER TABLEadds 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 withERROR 3822, and so is a name past 64 characters, which MySQL refuses withERROR 1059. MariaDB names a check written on a column after the column, and one written on the tableCONSTRAINT_<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 answersERROR 1064to a second, and Ptah refuses it the same way. - A
CHECKname the declaration writes is held where the server holds it. MySQL holds it once per schema, across its tables, and compares it without case but with accents:ckandCKare one name,ekandéktwo. MariaDB holds it once per table. Ptah folds ASCII letters on both, and on MySQL also the letters the server was measured to fold: Latin-1, Latin Extended-A and part of Latin Extended-B, basic Greek, Cyrillic and Armenian capitals, the polytonic Greek block, the Kelvin and Ångström signs, Roman numerals, and the circled and fullwidth capitals. It never refuses a pair the server accepts, such asẞandß. A pair the server refuses that Ptah does not recognize, such asḀandḁon MySQL oréandÉon MariaDB, reaches the server, which refuses it; MySQL DDL is not transactional, so the statements before it stay applied. TwoCHECKconstraints of one name are refused before any statement runs, and the refusal ends with the server’s own error, such asError 3822 (HY000): Duplicate check constraint name 'CK'.on MySQL andError 1826 (HY000): Duplicate CHECK constraint name 'DD'on MariaDB. Against a live server the scope is that of the server that answers, so amysql://URL that reaches MariaDB holds a name per table. Measured on MySQL 26.10 and MariaDB 12.3. - A column
checkdeclared in YAML or a Go annotation withoutcheck_nameis 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 columnsaandbeach carry one holdse_chk_1ande_chk_2on MySQL, andaandbon MariaDB. - A
CHECKcondition and the expression of a functional index are read as SQL. MySQL’sinformation_schemareports both escaped once more, as the body of a string: forCHECK (c <> 'it''s')it holds(`c` <> _utf8mb4\'it\\\'s\'). Ptah takes that escaping off and reads(`c` <> _utf8mb4'it\'s'), the conditionSHOW CREATE TABLEprints. MariaDB reports the condition as SQL,`c` <> 'it\'s', and Ptah reads it as reported. The comparison reads each string by the value it stands for, so'it''s','it\'s'and"it's"are one string, and so are'x\ty'and the same string holding a tab. A generated column’s expression and an expression default are read the same way. - MySQL’s
information_schemaalso reports a character beyond ASCII in these expressions encoded once more: each byte of its UTF-8 form arrives as a character of its own, soCHECK (c <> 'é')holds(`c` <> _utf8mb4\'é\'). Ptah takes that layer off where what is left is UTF-8, and keeps the text as reported where it is not. A string written in another character set, such as_latin1 x'e9', is reported with one layer and read as reported. MariaDB reports each character as it is, apart from theCHECK_CLAUSEcase below. - A
CHECKcondition and a generated column’s expression are compared as written first, and where the text differs, as the operations their operators group, by the precedence an expression default is read with. MySQL 26.10 storesCHECK (id between 1 and 10 and c is not null)as((`id` between 1 and 10) and (`c` is not null)),CHECK (NOT a OR id > 0)as((0 = `a`) or (`id` > 0)),CHECK (c NOT LIKE 'x%')as(not((`c` like _utf8mb4'x%')))andCHECK (b <> X'41')as(`b` <> 0x41). MariaDB 12.3 writesNOT aas`a` = 0and keepsX'41'compared with avarbinarycolumn. Each pair is one condition, so a second apply plans nothing, and a changed value is still a change. A condition holdingCASEorLIKE ... ESCAPEis compared as written, and so is one the server rewrites further: MariaDB storesCHECK (NOT TRUE)as0. Each of these plans the condition again on every run. - MariaDB converts a hexadecimal, bit, national or other character set string
in a
CHECKto the character set of the column it is compared with, and stores the string that results. Measured on MariaDB 12.3 with autf8mb4varcharcolumn,x <> x'41',x <> 0x41andx <> b'1000001'are stored as`x` <> 'A', andx <> N'abc'as`x` <> 'abc'. The stored condition compares by the column’s collation rather than by bytes: underutf8mb4_uca1400_ai_ciit refuses the value'a', which'a' <> x'41'in a query accepts. The declaration and the condition the server enforces differ, and Ptah cannot tell a declaredx'41'from a declared'A'in what the server reports. It compares such aCHECKas written, so theCHECKis planned again on every run. Write the string the server stores,x <> 'A', and theCHECKconverges and states the condition the server enforces. - MariaDB reports
CHECK_CLAUSEinutf8mb3, so a character beyond U+FFFF reads as one?per byte of its UTF-8 form, whileSHOW CREATE TABLEprints it whole. ACHECKholding such a character, such as U+1F600, is compared as reported and planned again on every run. - A
CHECKwritten on a column may name only that column on MySQL.CREATE TABLE c (a int, b int CHECK (b > a))isERROR 3813 (HY000): Column check constraint ... references other columnon MySQL 8.4 and 26.7, and so is the same check on a column thatALTER TABLEadds 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
CONSTRAINTwithout a name beforePRIMARY KEY,UNIQUE,FOREIGN KEYandCHECK, as inCONSTRAINT 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 unnamedCHECKin the same sequence as the table’s other checks. The server answersERROR 1064to every other symbol-less spelling and to aCONSTRAINTin front ofKEY,INDEX,FULLTEXTorSPATIAL, with a name or without one, and Ptah refuses each of them by name. - On a column, MySQL accepts
CONSTRAINTonly beforeCHECKand MariaDB only beforeREFERENCES, with a name or without one, as ina INT CONSTRAINT ck CHECK (a > 0)on MySQL. Measured on MySQL 8.4, MySQL 26.7 and MariaDB 11.8, the server answersERROR 1064to every other kind, such asa INT CONSTRAINT uq UNIQUE,CONSTRAINT pk PRIMARY KEYorCONSTRAINT 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 ofALTER TABLE ... MODIFY, MariaDB takesCONSTRAINTbefore nothing, and neither engine takesREFERENCES, withCONSTRAINTin front or without it; Ptah refuses those too. - A bare column
REFERENCES, as ina 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 mysqlrefuses the clause and asks for a table-levelFOREIGN KEY, which every line builds. MariaDB builds the key, and--dialect mariadbreads 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
utf8mb4connection,Ibeside dotlessıandΣbeside finalςare accepted by MySQL and answerERROR 1061on MariaDB, while dottedİbesideiand the Kelvin sign besideKdo the opposite. A loneprımaryis accepted by MySQL and answersERROR 1280on MariaDB, which is why a solitary non-ASCII name is not a safe exception either – it is still an unresolved comparison against the reservedPRIMARY. 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 plainion MySQL, so grouping only the non-ASCII names together would still miss it. Measured, the alternative was a half-applied migration: renderingCREATE INDEX İandCREATE INDEX iagainst MySQL 8.4.11 creates the first and answersERROR 1061on 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:
abesideä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 ofLOWER(),utf8mb4_general_ci,utf8mb4_unicode_ci,utf8mb4_uca1400_ai_ciorutf8mb4_binreproduces MariaDB’s. A temporary table carrying the names as keys either creates or answersERROR 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 withoutCREATE TEMPORARY TABLES, which answersERROR 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
AandaanswersERROR 1060, and a foreign key writtenabinds to a column declaredAand 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İ/iand the Kelvin sign/Kone column and MariaDB callsI/ı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 plainion MySQL. - A non-ASCII column named by a key, a constraint, or its own
UNIQUEis 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İ/iand the Kelvin sign/Kwhile MariaDB foldsI/ı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 takesaas soon as it is read, and the later explicitacollides 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 asFULLTEXT INDEX ft_bio (bio). TheWITH PARSER <name>clause travels with it, as the index’sparserplatform property of MySQL and MariaDB.KEYmatters as much asINDEX: both dump tools normalize to it, so a table written withFULLTEXT INDEXcomes back out ofmysqldumpasFULLTEXT KEY. An index left unnamed takes the name its server would give it, by the rule above. KEY,SPATIAL,FULLTEXTandINDEXopen 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.sqlsource read for another dialect gets a column wherever that engine has no table element opening with the word.key,spatialandfulltextare columns on PostgreSQL, YugabyteDB, CockroachDB, ClickHouse, SQLite and Oracle. SQL Server reserveskey, so onlyspatialandfulltextare columns there.indexis a column on PostgreSQL and YugabyteDB, and on CockroachDB unless a column list follows it, directly or after a name. SQL Server readsindexas its own inline index and ClickHouse as a data-skipping index, and SQLite and Oracle reserve it. All four are columns on an engine a provider serves, since no provider states a word that opens an index insideCREATE TABLE; the Spanner emulator and DuckDB take them so. Read as an index on the other dialects,key textwould be refused, andspatial nvarchar(32)has the shape of an index named after its type over a column named32(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 withError 1064.- Under
--dialect mysql, anALTER TABLEin a schema file carryingIF EXISTSorIF NOT EXISTSis refused, because MySQL refuses the guard on every operation. MariaDB takes it onDROP INDEX,DROP KEY,DROP FOREIGN KEY,DROP CONSTRAINT,DROP COLUMNandADD COLUMN, and Ptah reads it there. MariaDB has noDROP CHECKat all, so under--dialect mariadba schema file drops a table-level check withDROP CONSTRAINT. A column-level check is changed withMODIFY COLUMN: MariaDB refusesDROP CONSTRAINTfor that check. - A schema file’s
USE appselects the database the table names after it are in, as the server reads it, so a file for a whole server can writeUSE app;and then unqualified tables. The table aREFERENCESclause names without a database is looked up in the database of the table holding the key, which is where the server looks, whateverUSEselected. - A column carrying both a primary key and a
UNIQUEis 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 namedaon both engines, and it is rendered as the table-level key it was;a INT PRIMARY KEY UNIQUEbuilds 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. - A role is a name and nothing else.
CREATE ROLEtakes no attribute on either engine, so a declaredlogin,password,superuser,createdb,createroleorreplicationis refused before any SQL is written: what it asks for is a user, and Ptah does not manage users. A role always passes on the privileges of the roles granted to it, and there is noNOINHERIT(Error 1064on both engines). So every role reads back as inheriting, which is what a declaration defaults to, and a declaredinherit="false"is refused rather than created as a role that inherits anyway. - DDL commits implicitly on both engines, so a failed migration cannot be rolled back by the surrounding transaction.
- A schema file’s column
COMMENT 'text'and tableCOMMENT = 'text', with or without the=, hold the text the server stores. A doubled quote and a backslash escape are read as the server reads them, and of twoCOMMENTclauses on one column the last is kept. - A Go or YAML literal default, a column, table or index comment, an enum
value and a declared row’s value are written with each quote doubled. Under
the default
sql_modeeach backslash is doubled too, soa\bstores those three characters. A text default is compared by the string it stores, so'it\'s'and'it''s'are one default. A literalNULLon a text column is the string'NULL'; a schema file writes SQLNULLwithout quotes. - A session whose
sql_modenamesNO_BACKSLASH_ESCAPESreads a backslash in a string as the character, so'a\\b'stores two backslashes there. Ptah reads the mode once, when it connects, and writes the literals above for the session that runs them, with each backslash single on such a session:schema apply, its declared rows,--dry-run, and what a dev database builds forschema diffand for the--dev-urlrehearsal. Text Ptah writes for later is written for the defaultsql_modewhatever the session’s: a migration file frommigrations generate,migrations data,migrations checkpointorptah-compat migrate diff, a saved plan file, and the SQL thatschema diff,migrations plan,schema compare,db readandschema inspectprint. - SQL a user wrote is sent byte for byte: a view or routine body from a schema
file, the plan’s SQL after
--edit, and a migration file.schema applycuts the--edittext into statements the way the session reads it, so the prompt rates each statement the session receives.migrations upsends a migration file as written and cuts it the way the defaultsql_modereads it, so a file Ptah generated stores each such backslash twice on aNO_BACKSLASH_ESCAPESsession.migrations upon such a session also needs--tx-mode none; see Apply migrations. - Two refusals keep text off a session that reads it differently, before
anything runs.
schema apply --planrefuses a saved plan that holds a backslash on aNO_BACKSLASH_ESCAPESsession; apply the desired schema without--planthere. The--dev-urlrehearsal refuses a plan that holds a backslash when the dev session and the target session disagree onNO_BACKSLASH_ESCAPES; give the dev database the target’ssql_mode. A plan without a backslash runs on either session. With--json, both refusals end the run asrefusedwith the codebackslash-mode. ptah-compatwrites these literals the same way, so on aNO_BACKSLASH_ESCAPESsession it stores the declared value. The community binary stores four backslashes where the declaration holds one (measured with the community binary v1.3.0 and v1.4.0). We are stricter here, deliberately: that spelling changes the data the user declared; see Compatibility differences.
Table options
Section titled “Table options”A table’s storage engine, the first value of its auto-increment column and its
default character set are the options CREATE TABLE takes as ENGINE=,
AUTO_INCREMENT= and CHARSET=. A Go annotation declares the engine with the
table’s engine attribute and the others as platform properties of the
target:
//ptah:schema:table name="orders" engine="InnoDB" platform.mysql.auto_increment="1000" platform.mysql.charset="utf8mb4" platform.mariadb.charset="utf8mb4"type Order struct{}YAML places auto_increment and charset in the target’s platform group, and
an HCL table block takes engine, auto_increment and charset as
attributes, for MySQL and MariaDB alike. An engine or a character set that is
not a name of letters, digits and underscores, and an auto-increment value
that is not a whole number, is refused where it is written.
The engine and the auto-increment value apply when the table is created. A read reports neither, so a plan never changes them on a table that exists. A read reports the table’s default collation, which names its character set, so a plan changes the default character set of a table that exists through its collation; see Column collations. Another target leaves the auto-increment value and the character set out, as it leaves out any other target’s properties.
A description of a read keeps the table’s character set and collation, so SQL written from it creates the table as the server holds it. In HCL a table leaves out the ones its schema block states, as the Atlas community binary writes them; SQL writes both on every table, and JSON lists both on every table.
Table partitioning
Section titled “Table partitioning”Ptah does not model MySQL or MariaDB partitioning (stokaro/ptah#6357): nothing renders or compares partitions. A declaration that partitions a table is refused by name rather than created as one ordinary table, or compared as synced with a table that has no partitions:
PARTITION BYin aCREATE TABLEof a SQL schema file read for MySQL or MariaDB, and anALTER TABLEthat opens with a partition operation, such asPARTITION BY,ADD PARTITIONorREMOVE PARTITIONING;- the
partition_byplatform property of a Go annotation or a YAML table; - an HCL table’s
partitionblock.
unsupported feature: PARTITION BY on table sales: Ptah does not model MySQL or MariaDB partitioning (stokaro/ptah#6357); leave it out of the desired schema and manage the partitions outside PtahA table partitioned outside Ptah is read and compared without its partitions,
so a schema that declares it without them reports synced. The read names the
table rather than let that read as covering the partitions, in the same places
it names an event: schema compare lists it under the report, and
db read, schema apply and schema diff warn on standard error:
warning: table partitioning "sales" in the database is left out: Ptah does not model MySQL or MariaDB partitioning (stokaro/ptah#6357), so no comparison sees the partitions, and SQL written from this read creates the table without themThe read does not refuse the table. A change Ptah plans for it, such as
ADD COLUMN, keeps its partitions, because the server keeps them through the
change. SQL that db read writes from the read creates the table without
partitions. A run whose --exclude or --include leaves the table out does
not name it, since the run is not about that table.
ptah-compat refuses the same declarations. The community binary writes a SQL
file’s clause as an option the server refuses with ERROR 1064, reports a table
that exists without partitions as synced, and fails to parse a MySQL HCL
table’s partition block. We are stricter here, deliberately: a declaration Ptah cannot
carry out is refused rather than lost.
A column default with an operator
Section titled “A column default with an operator”MySQL and MariaDB take an expression default only in parentheses. MySQL 26.10
and MariaDB 12.3 answer ERROR 1064 (42000) to DEFAULT 1 + 2, DEFAULT 5 - 2 NOT NULL and DEFAULT (1) + 2, and take DEFAULT (1 + 2). A SQL schema file
read with --dialect mysql or --dialect mariadb is refused at the operator,
before anything is planned, and the refusal names the parenthesized form,
write DEFAULT (1 + 2). A version gate after the default, such as
/*!80023 INVISIBLE */, stays the column’s.
Both engines read || as OR under their default SQL mode and as a
concatenation only under PIPES_AS_CONCAT. MySQL 8.4 to 26.10 store
DEFAULT ('a' || 'b') as ((0 <> 'a') or (0 <> 'b')) and answer
ERROR 1292 (22007) to the INSERT that takes it; MariaDB 11.8 to 12.3 store
('a' or 'b') and insert 0. A schema file read with either dialect is
refused at the first ||, in parentheses or without, and the refusal writes
the default with a call that concatenates under every mode where the
operands stand: write DEFAULT (CONCAT('a', 'b')), and
write DEFAULT (coalesce(CONCAT('a', 'b'), 'c')) for
(coalesce('a' || 'b', 'c')). Write OR where a logical or is meant.
Column character set and ON UPDATE
Section titled “Column character set and ON UPDATE”A column’s CHARACTER SET and its ON UPDATE clause, such as
ON UPDATE CURRENT_TIMESTAMP(6), exist only in MySQL and MariaDB, so the MySQL
owner holds them rather than the common column. Each source binds them to that
family:
- SQL:
CHARACTER SETorCHARSET, andON UPDATE, in a column definition. - Atlas HCL: the
charsetandon_updateattributes of acolumnblock. - Go annotations and YAML: the platform properties
charsetandon_update, asplatform.mysql.charset="latin1"on a//ptah:schema:fielddirective or under a column’splatform: {mysql: {...}}group. The key is read for the target it names, so a MariaDB target needsplatform.mariadb.
Another target leaves the settings out without a report, because the binding says they are not meant for it. A value bound to no target is refused there.
The comparison does not compare these settings. A column whose only difference
is its character set or its ON UPDATE clause is not changed. A column that
changes for another reason is rewritten with MODIFY COLUMN, and that
statement writes the declared settings. A database read reports a character
set for every text column, inherited ones included, and an export writes what
it read.
Column collations
Section titled “Column collations”A column’s COLLATE is written on the column, in CREATE TABLE and in
MODIFY COLUMN, and compared with the collation the column holds. A read
reports that collation whether the column declares it or takes it from its
table, so COLLATE utf8mb4_unicode_ci on a column of a table whose default is
utf8mb4_unicode_ci is no change. Names compare ignoring case, and
COLLATE 'utf8mb4_bin' is the same name as COLLATE utf8mb4_bin. A changed
collation is planned as MODIFY COLUMN, which restates the column with the
declared collation and its declared character set.
A name the server stores under another name is compared as the stored name.
MySQL stores a collation of the utf8 character set under utf8mb3, so
COLLATE utf8_bin holds as utf8mb3_bin. MariaDB does the same while its
old_mode holds UTF8_IS_UTF8MB3, which is the default, and stores it under
utf8mb4 when old_mode leaves that flag out. Ptah asks the server what
utf8 names in the session it reads with, so a URL that sets old_mode, such
as mariadb://user:pass@host:3306/app?old_mode=%27%27, is compared the way the
server stores what that session writes.
MariaDB also takes a collation without its character set, so
COLLATE uca1400_ai_ci on a utf8mb4 column holds as utf8mb4_uca1400_ai_ci,
and COLLATE DEFAULT, which selects the
default collation of the column’s character set and is compared as no
collation. Both servers accept a collation on a type that holds none, such as
int COLLATE utf8mb4_bin, and keep none, so such a collation is not compared.
A column that declares no collation takes one from the server: the default
collation of a character set the column names, or else its table’s default
collation. CHARACTER SET utf8mb4 in a table whose default is
utf8mb4_unicode_ci gives utf8mb4_0900_ai_ci on MySQL 8.4 and
utf8mb4_uca1400_ai_ci on MariaDB 12.3, and CHARACTER SET latin1 gives
latin1_swedish_ci on both. Ptah reads each character set’s default from the
server it compares with, in the session it reads with: MySQL’s
default_collation_for_utf8mb4 and MariaDB’s character_set_collations are
honored.
The column is compared with the collation it takes, and one that holds
another is reset with a MODIFY COLUMN that names no collation, so the server
resolves it the same way. A column that names no character set is not compared
where the declaration names neither a character set nor a collation for its
table, since the database’s default decides it. Nor is a column whose type
carries an attribute that selects a collation of its own, such as
varchar(20) BINARY.
A table’s default collation is compared too, where the declaration names it or
names the table’s character set. A changed one is planned in one statement
with the reset of each column that takes it,
ALTER TABLE `t` COLLATE utf8mb4_general_ci, MODIFY COLUMN `n` varchar(20),
which names the character set too where it changes:
ALTER TABLE `t` CHARSET latin1, COLLATE latin1_bin, MODIFY COLUMN `n` varchar(20).
The table option alone leaves the collation of every existing column as it
is. In the same statement the server gives a reset column the new default, so
one apply converges. A change of a table’s default collation is rated a
warning, because a column added or restated without a collation takes it.
The pinned Atlas community binary plans the same statement when it is given a
dev database, and the table option alone without one; see
A changed MySQL table collation.
MariaDB lists its UCA 14.0.0 collations, such as utf8mb4_uca1400_ai_ci, with
no character set, and the binary reads no character set for a table that
holds one. ptah-compat writes the change between two of them as the binary
does, COLLATE utf8mb4_uca1400_ai_ci without CHARSET utf8mb4. The collation
names its character set, so the server makes the same change.
Where the other side is not a UCA 14.0.0 collation, ptah-compat writes the
character set the collation names, and a column that takes the table’s new
collation is restated without a character set, so it takes the collation the
table declares. The binary writes the dev database’s character set on the
table and CHARSET utf8mb3 on the column, which MariaDB can refuse, or which
gives the column the default collation of utf8mb3; see
A MariaDB table changed to a UCA 14.0.0 collation.
A foreign key stands on the collation of both its ends, so the server refuses a change of either end’s collation while the key stands: MySQL with Error 3780 and MariaDB with Error 1832. Ptah drops the key, changes both ends, and adds the key back, as it does for a change of the columns’ type, so one apply changes a key and the column it references together. A change of the character set is a change of the collation. The statements are not one transaction: where the referenced table fails with Error 1366 after the referencing one converted, the key stays dropped, and the next plan finishes the change and adds it back, as it does after a failed change of type.
A change of a column’s character set converts every value the column holds,
and is rated destructive: a character the new character set cannot hold fails
the statement with Error 1366, or is lost where the session is not strict.
ptah schema drift reports it as column_charsets_converted. The table’s
change and the column resets are one statement, so a value that fails the
conversion leaves the table’s default and its columns as they were.
A diff of two schema files predicts what the server gives the tables of the
--from file, as a read of the tables built from it would report: each
table’s default collation, and the collation of each column that names none.
The character set defaults come from the dev database’s session where
--dev-url names one, so a dev URL that sets default_collation_for_utf8mb4
is honored, and otherwise from the defaults of the newest release line Ptah
tests: utf8mb4_0900_ai_ci on MySQL and utf8mb4_uca1400_ai_ci on MariaDB
for utf8mb4. The --to file is resolved from the same defaults, so a file
compared with itself plans nothing, and a changed table default or column
character set is planned as it is against a server.
A character type with CHARACTER SET binary is stored as the binary type of
the same length: varchar(10) CHARACTER SET binary as varbinary(10),
char(4) CHARACTER SET binary as binary(4), and text CHARACTER SET binary
as blob. The comparison accepts the stored type, so such a column is not
planned again, and a MODIFY COLUMN that changes it restates it as declared.
A --from schema file holds its columns at the stored type too, so two files
that spell one column both ways diff as synced in either direction.
A mysql://, mariadb:// or maria:// URL cannot say whether the server is
MySQL or MariaDB, and the server can. A schema diff or schema inspect that
creates a schema file on the dev database reads the file again for the dev
server’s dialect where the URL named the other, so the table options the file
states, such as COLLATE=utf8mb4_unicode_ci, reach the tables it creates, as
schema apply and migrations generate read a file from the start. The second
read resolves the source again: a desired schema that a data "external_schema"
program writes runs that program a second time, so the program has to print the
same schema on each run and change nothing else.
FULLTEXT parser
Section titled “FULLTEXT parser”A FULLTEXT index’s parser plugin, such as ngram, is an option of the MySQL
family, declared as the index’s parser platform property. A Go annotation
and YAML state it for each target that should write it:
//ptah:schema:index name="ft_bio" fields="bio" type="FULLTEXT" platform.mysql.parser="ngram" platform.mariadb.parser="ngram"An HCL index block takes parser, as Atlas writes it, for MySQL and MariaDB
alike, and a schema file’s WITH PARSER reads the same way. A parser that is
not a name of letters, digits and underscores is refused where it is written.
The parser applies when the index is created. A read does not report it, so a
plan never changes it on an index that exists, and another target leaves it
out.
TLS with a private certificate authority
Section titled “TLS with a private certificate authority”A server whose certificate a private certificate authority signed is verified
against that authority with the ssl-ca URL parameter. It names a PEM file of
one or more CA certificates:
mysql://app:secret@db.internal:3306/shop?ssl-ca=/etc/ptah/mysql-ca.pemssl-ca requires TLS and verifies two properties: the server’s certificate
chains to a certificate in the bundle, and the certificate names the URL’s
host. That is what the MySQL client’s --ssl-mode=VERIFY_IDENTITY checks. The
bundle replaces the system roots for this connection only. The process trust
store is neither read nor changed, so two URLs naming different bundles connect
side by side. mariadb://, maria:// and the Go-driver form
mysql://app:secret@tcp(db.internal:3306)/shop?ssl-ca=... take the parameter
the same way, and tls=true beside it changes nothing. The name is read
without regard to case, and ssl_ca is the same parameter, as in MySQL option
files.
The name is the MySQL and MariaDB client option for the same file, and the
name the Atlas URL documentation gives a custom CA. ptah-compat reads it in
the same URLs. The pinned Atlas CLI community binary does not apply it.
Without tls, it sends ssl-ca to the server as a session variable, and the
server answers Error 1064. With tls=true, the handshake fails with
x509: certificate signed by unknown authority whichever file ssl-ca names.
PTAH_ATLAS_STRICT_COMPAT=1 refuses the parameter for that reason (see Strict Community Edition mode).
Try it locally
Section titled “Try it locally”These commands create a certificate authority and a server certificate for
127.0.0.1 and localhost, start MySQL with them, and read the database
through the authority. They need openssl, Docker and a free port 3306.
mkdir tlsopenssl req -x509 -newkey rsa:2048 -nodes -days 30 -subj "/CN=Local MySQL CA" \ -addext "basicConstraints=critical,CA:TRUE" \ -addext "keyUsage=critical,keyCertSign,cRLSign" \ -keyout tls/ca-key.pem -out tls/ca.pemopenssl req -newkey rsa:2048 -nodes -subj "/CN=127.0.0.1" \ -keyout tls/server-key.pem -out tls/server.csrprintf 'subjectAltName=IP:127.0.0.1,DNS:localhost\n' > tls/server.extopenssl x509 -req -in tls/server.csr -CA tls/ca.pem -CAkey tls/ca-key.pem \ -CAcreateserial -days 30 -extfile tls/server.ext -out tls/server-cert.pemchmod 0644 tls/*.pemThe server runs as another user inside the container, so the files are made readable to it. These keys exist for this example only.
docker run -d --name mysql-tls -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=app \ -v "$PWD/tls:/etc/mysql-tls:ro" \ mysql:8.4 \ --ssl-ca=/etc/mysql-tls/ca.pem \ --ssl-cert=/etc/mysql-tls/server-cert.pem \ --ssl-key=/etc/mysql-tls/server-key.pemuntil docker exec mysql-tls mysql -uroot -psecret -h127.0.0.1 -e 'SELECT 1' >/dev/null 2>&1; do sleep 2; donedocker exec mysql-tls mysql -uroot -psecret -h127.0.0.1 app -e 'CREATE TABLE notes (id int PRIMARY KEY)'ptah db read --db-url 'mysql://root:secret@127.0.0.1:3306/app?ssl-ca=tls/ca.pem'The read prints the notes table. A bundle that did not sign the server’s
certificate stops the command at exit 2:
openssl req -x509 -newkey rsa:2048 -nodes -days 30 -subj "/CN=Another CA" \ -addext "basicConstraints=critical,CA:TRUE" -keyout other-key.pem -out other-ca.pemptah db read --db-url 'mysql://root:secret@127.0.0.1:3306/app?ssl-ca=other-ca.pem'Error connecting to database: failed to ping database: tls: failed to verify certificate: x509: certificate signed by unknown authorityRemove the server with docker rm -f mysql-tls.
A bundle Ptah cannot use
Section titled “A bundle Ptah cannot use”The bundle is read when the connection opens, before the server is contacted. Every PEM block in it must be a certificate that parses; text between blocks, such as the comments a distribution bundle carries, is ignored. A missing or unreadable file, a file holding no PEM certificate, a damaged certificate, and a block that is not a certificate, such as a private key, each stop the command at exit 2 and name the file by its absolute path:
the ssl-ca parameter names a CA bundle Ptah cannot use: CA bundle /work/tls/server-key.pem holds a PEM block that is not a certificate: block 1 is "PRIVATE KEY", and the bundle takes CA certificates onlyA server certificate issued for another host name fails the handshake with
x509: certificate is valid for ..., not .... No failure falls back to an
unverified or an unencrypted connection, and no message repeats the password.
Ptah refuses a URL that contradicts ssl-ca, before it connects:
tls=false,tls=skip-verify,tls=preferredandallowFallbackToPlaintext=true, each of which leaves the connection unverified or unencrypted;- a socket address,
mysql+unix://orunix(...), which names no host to verify; - a port with no host, as in
mysql://app@:3306/shop. A URL that names no address at all dials127.0.0.1:3306, and the certificate must name127.0.0.1; - an empty
ssl-ca, orssl-cagiven twice, in any spelling.
Every URL parameter whose name starts with ssl- or ssl_, in any case,
belongs to Ptah, whether or not the URL names ssl-ca. Ptah reads ssl-ca and
refuses each of the others, such as ssl-mode, ssl-cert and ssl-key,
before it connects. The driver would send such a parameter to the server as a
session variable, which the server refuses with an error that carries the
value. So the URL cannot set a session variable whose name starts with ssl_.
Where the path is read
Section titled “Where the path is read”A relative path is read from the directory the command runs in, not from the
directory of a configuration file that holds the URL. Percent-encode a %,
&, + or # in the path; a / may be written as is or as %2F.
The Ptah process reads the file, so when Ptah runs in a container the bundle has to be inside that container, and the URL names the path there:
docker run --rm -v "$PWD/tls/ca.pem:/tls/ca.pem:ro" ghcr.io/stokaro/ptah:latest \ db read --db-url 'mysql://app:secret@db.internal:3306/shop?ssl-ca=/tls/ca.pem'In Kubernetes, mount the CA certificate from a ConfigMap or a Secret and name the mounted file. A CA certificate is public, and a ConfigMap is enough for it.
A dev database or shadow database URL takes ssl-ca the same way, and a
scratch database Ptah creates on the server a URL names is reached with that
URL’s ssl-ca.
A CA certificate is not a client certificate
Section titled “A CA certificate is not a client certificate”ssl-ca decides which servers Ptah trusts. It sends nothing to the server, and
the account still logs in with its password. An account created with
REQUIRE SSL needs TLS alone, and ssl-ca meets it. An account created with
REQUIRE X509, REQUIRE ISSUER or REQUIRE SUBJECT needs a client
certificate, which is mutual TLS. Ptah does not support mutual TLS: ssl-cert
and ssl-key are refused rather than ignored. The bundle holds CA certificates
only, and a private key in it is refused.
A MySQL server started without certificates generates its own, and the
generated server certificate names no host. Host name verification cannot pass
against it, so give the server a certificate whose subjectAltName holds the
host name or address the URL uses. Ptah does not read a host name from the
certificate’s common name.
Tools Ptah starts
Section titled “Tools Ptah starts”The mysqldump pre-flight hook and the online-DDL tools, gh-ost and
pt-online-schema-change, open their own connections, and Ptah gives them no TLS
settings. A URL with ssl-ca is refused for them, because the tool would
connect without verifying the server.
A provider manifest can claim a MySQL-family engine, such as mysql, and a
command can name it with --provider-engine. The provider is then handed the URL as
text, and Ptah’s connector does not take part. A URL with ssl-ca is refused
before the provider receives it, because nothing says the provider verifies
the server against the bundle.
What the connecting account needs
Section titled “What the connecting account needs”information_schema lists only what the connecting account holds a privilege on,
and MySQL and MariaDB have no other catalog an ordinary account can read. A
comparison reads a missing row as a missing object, so a read that cannot prove it
saw a kind of object is refused rather than answered. The refusal comes before any
result, at exit 2, and names what is missing:
the account reader@% cannot see everything in database "app": information_schema lists only what an account holds a privilege on, and a comparison reads a hidden object as absent. The account needs: - triggers: TRIGGER on `app`.* - procedures and functions: SHOW_ROUTINE on *.*Every command that reads a schema gives this answer: schema diff, schema compare, schema drift, schema inspect, schema plan, schema apply,
migrations generate and db read. A plan needs it as much as a report does: an
apply that believes an object is absent plans from that belief. ptah-compat gives
the same answer at exit 1, the one failure code its surface has.
The documents of schema plan, schema apply and schema drift report a refused
read of the target with the kind permission_denied and a missing_privileges
list, so a caller need not read the sentence.
Why a command failed gives the
fields.
Ptah reads SHOW GRANTS for the connecting account before it decides which
objects the account can see. If the server or proxy rejects this statement, the
read fails. The privilege visibility described below was measured on direct
MySQL and MariaDB servers. It has not been verified on AWS RDS, Aurora, Google
Cloud SQL, Azure Database for MySQL, ProxySQL, or Vitess. If one of these
services gives a different result, open an issue with the product and version,
account grants, and a reproducible read failure.
What each kind of object takes, measured on MySQL 8.4 and 26.10 and on MariaDB 11.8 and 12.3:
| Objects | MySQL | MariaDB |
|---|---|---|
| Tables and views, with their columns and indexes | SELECT, INSERT, UPDATE or REFERENCES on the database or globally |
The same |
Primary keys, unique keys, foreign keys, CHECK constraints |
Nothing more | A privilege beyond SELECT on the table or above it, such as SHOW VIEW |
| View definitions | SELECT and SHOW VIEW on the view or above it |
The same |
| Triggers | TRIGGER on each table or above it |
The same |
| Procedures and functions | SHOW_ROUTINE globally, or SELECT globally |
SHOW CREATE ROUTINE on the database or globally, SELECT globally, or SELECT on mysql.proc |
| Events, which no comparison reads | EVENT on the database or globally, for a report to name them |
The same |
So this account gets the same comparison as root, roles and grants aside:
-- MySQLGRANT SELECT, SHOW VIEW, TRIGGER ON app.* TO reader;GRANT SHOW_ROUTINE ON *.* TO reader;
-- MariaDBGRANT SELECT, SHOW VIEW, TRIGGER, SHOW CREATE ROUTINE ON app.* TO reader;TRIGGER also lets the account create and drop triggers. Neither engine has a
privilege that only shows them.
Where a grant applies decides what it proves:
- A grant on one table says nothing about the others. The list of tables needs
a privilege on the database or a global one, however many tables the account holds
a grant on.
TRIGGERand the MariaDB constraint privilege are read table by table once the list is complete, so a grant on every table counts. - A database-level grant is a pattern, and the most specific one applies.
GRANT TRIGGER ON `a%`.*besideGRANT SELECT ON `app`.*leaves the account withSELECTonappand noTRIGGER. An unescaped_is a wildcard, so a grant onmy_appranks below a grant onmy\_app. partial_revokeschanges both of those on MySQL. With it on, a name in a grant is literal, so%and_match only themselves, andREVOKE TRIGGER ON app.*takes a globalTRIGGERaway onapp.- A role counts while it is active, through
SET DEFAULT ROLEoractivate_all_roles_on_login. Connections do not runSET ROLE, so a role that is granted and not set holds nothing here. ALL PRIVILEGESon the database shows everything on MariaDB 11.4 and newer. On MySQL and on MariaDB 10.11 it lists every routine and shows the body only of the account’s own, asEXECUTE,ALTER ROUTINEandCREATE ROUTINEon the database do: a routine of another definer is refused by name until the account holds the routine privilege.- A URL that names no database covers the databases the account can see, and each
of them is held to this table. A database the account holds nothing on is outside the
comparison.
schema diffnames this visible-scope limit instead of claiming that two servers are fully synced.SHOW DATABASESlists every database, so an account that holds it has to see them all or name the ones it reads with--schemas.
A run that does not depend on a kind
Section titled “A run that does not depend on a kind”A selector that leaves out a whole kind relieves the read of answering for it, so the account needs no privilege for it:
| Selector | The account does not need |
|---|---|
--exclude '*[type=trigger]' |
TRIGGER |
--exclude '*[type=view]' |
SHOW VIEW |
--exclude '*[type=function]' |
The routine privilege |
--exclude '*[type=constraint]' |
The MariaDB constraint privilege |
--exclude '*[type=table]' |
TRIGGER and the MariaDB constraint privilege |
--include '*[type=table]' |
SHOW VIEW and the routine privilege |
MariaDB can hide constraint rows from an account with only SELECT. The read
still gets each primary and unique key from its visible index, and each foreign
key’s columns and referenced table from KEY_COLUMN_USAGE, which MariaDB shows
that account. The selector leaves each key out as it does for an account that
sees the row, so both accounts plan the same. The index the server built for a
foreign key leaves the comparison with its key, and a plan that drops an index
the author declared under the key adds one for the key in the same statement,
so no plan drops an index the server refuses to drop while the key stands.
A selector that names objects does not count, because a read that cannot see a
trigger cannot say what it is called: --exclude 'audit_*[type=trigger]' still needs
TRIGGER. Neither does --exclude '*[type=foreign_key]' or --exclude '*[type=check]',
since the catalog view that hides a foreign key or a CHECK constraint from a MariaDB
account hides a primary key with it.
diff.skip relieves nothing. It leaves destructive changes out of a plan, and the
comparison still reads the kind to find what to add.
schema apply refuses a selector that matched nothing, and an account that cannot
see a trigger matches none with --exclude '*[type=trigger]'. Set
PTAH_ATLAS_ALLOW_UNMATCHED_EXCLUDE=1 to run it.
A dev database
Section titled “A dev database”A dev or shadow database holds what the same run created there, and the account that created a trigger or a routine is shown it. A read of one needs a privilege that lists the columns and nothing else from the table above, on top of what emptying a dev database takes. A view or routine that came back without a body is still refused, because it has another definer.
schema diff compares a migration directory’s views with views in the target
database even when the dev and target databases have different names. A view
whose body is the same reports no change; a changed body still produces a plan.
View security
Section titled “View security”A MySQL or MariaDB view runs with its definer’s privileges under SQL SECURITY DEFINER, and with its caller’s under SQL SECURITY INVOKER. Set
security="INVOKER" on a //ptah:schema:view annotation, or security: INVOKER on a YAML view, to run it with its caller’s privileges. A SQL schema
file can declare CREATE [OR REPLACE] SQL SECURITY DEFINER VIEW or CREATE [OR REPLACE] SQL SECURITY INVOKER VIEW; reading the file preserves that mode.
The read keeps information_schema.VIEWS.SECURITY_TYPE. A declaration that
omits security states the server default, DEFINER, because a CREATE OR REPLACE VIEW without the clause selects it. A new view declared without
security takes DEFINER, and so does a view held as DEFINER. A plan that
creates a view writes no DEFINER clause. A plan that replaces a view held as
DEFINER names the view’s definer when a database read answers for the plan,
as the definer paragraphs below describe. A plan made from a replay or from
two descriptions names no definer.
When a view holds SQL SECURITY INVOKER and the declaration names no mode, the
plan refuses before the server changes, whatever else changes on the view.
Without the clause the view would run with its definer’s privileges, not its
caller’s, and no statement would fail. A view altered by hand, or a declaration
written before the mode was set, is the usual case:
unsupported feature: view my_docs holds SQL SECURITY INVOKER and the schema does not state it. Without it the view runs with its definer's privileges, not its caller's. Declare SQL SECURITY INVOKER to keep it, or SQL SECURITY DEFINER to change it. A Go annotation states the mode as security="INVOKER", a YAML view as security: INVOKER, and an HCL view as security = INVOKERDeclare INVOKER to keep the mode: security="INVOKER" on the annotation,
security: INVOKER in YAML, CREATE SQL SECURITY INVOKER VIEW in a SQL file, or
security = INVOKER in HCL. The plan then writes the clause with any other
change. Declare DEFINER to change it, and the plan runs as usual. Keeping the
mode in silence would ignore an author who removed it on purpose. schema drift
plans nothing, so it reports the difference under views_modified and does not
refuse, and a rollback migration that restores the earlier mode is not refused
either. Under --json, schema plan and schema apply report the refusal as
outcome: refused with the code view-security-option, and view_options
names the view with the option sql_security. The rule matches the one for
PostgreSQL view options.
A CREATE OR REPLACE VIEW that names no DEFINER makes the account that runs
it the view’s definer, whatever the mode. The read keeps
information_schema.VIEWS.DEFINER and the connected account,
CURRENT_USER() with its host, and compares them as it does for a routine.
When a view runs under SQL SECURITY DEFINER before and after the change and
its definer is another account, the plan refuses before the server changes.
The replacement would run the view with the connected account’s privileges,
and no statement would fail:
unsupported feature: view my_docs runs with the privileges of its definer owner@% under SQL SECURITY DEFINER, and replacing it as app@% would make app@% its definer. Connect as owner@% to replace it, declare SQL SECURITY INVOKER if it should run with its caller's privileges, or leave the view unchangedA read that did not record the definer or the connected account is refused
too, since nothing says the account stays the same. A view held as INVOKER
runs with its caller’s privileges until the change, and a view declared
INVOKER runs with them after it, so the plan replaces either one and the
connected account becomes the definer. Under --json the refusal is outcome: refused with the code view-definer, and view_definers names each view
with its definer and the account the plan would make its definer.
schema drift reports the difference and does not refuse.
When the definer is the connected account, the replacement names it, so the view keeps its definer whichever account applies the plan later:
CREATE OR REPLACE DEFINER=`app`@`%` SQL SECURITY DEFINER VIEW `my_docs` AS ...A rollback migration is checked on the same terms. It names the definer the up
migration’s read recorded whenever it writes the view under DEFINER, since
that is the view it restores. That includes a view the up migration moves from
DEFINER to INVOKER: the rollback restores the DEFINER view for its
definer, whichever account that is, and an account that cannot name the
definer is refused when the rollback runs.
A plan made from a replay
Section titled “A plan made from a replay”migrations generate --replay, ptah-compat migrate diff and ptah-compat schema diff with a migration directory as --from plan from a dev database
that a replay built. Its account and its definers are the dev server’s, so
the plan names no definer and refuses no change of definer. The replayed
state still records each view’s mode, so a replacement of a view that runs as
its definer warns on standard error, once per view, and the exit code stays
the same:
warning: view my_docs runs with its definer's privileges under SQL SECURITY DEFINER, and the migration replaces it without a DEFINER clause, so the account that applies the migration becomes its definer. To keep a definer, write DEFINER= into the file, or apply it as that definerThe warning is not written into the file or to standard output.
A history that names definers needs a dev account that may name them: on
MySQL SET_ANY_DEFINER, with ALLOW_NONEXISTENT_DEFINER for an account the
dev server lacks, and on MariaDB SET USER. An administrative account holds
them. Without them the replay fails with error 1227.
Naming another account as the definer
Section titled “Naming another account as the definer”A statement that writes DEFINER= for another account fails with error 1227
unless the account that runs it holds a global privilege. MySQL and MariaDB
run DDL outside a transaction, so a failure in the middle of a run would
leave the statements before it applied. schema apply, with a computed plan
or with --plan, ptah-compat schema apply and migrations up and down
therefore ask before the first statement, and --dry-run refuses what the run
would refuse. They scan the statements, and for migrations every pending
file, for a CREATE or ALTER of a view, routine, trigger, event or MariaDB
package that names another account, and refuse when the connected account
cannot name it.
A plan that also holds a provider-native operation is scanned over its SQL
statements. A clause inside an executable comment the connected server runs is
read as part of the statement: /*!50013 DEFINER=... */ as mysqldump writes
it, a comment gated on a version no later than the server’s, and on MariaDB
/*M! ... */. The message names the object, the definer, the account and the
privilege. Grant the privilege, or apply as the definer:
-- MySQLGRANT SET_ANY_DEFINER ON *.* TO 'deploy'@'%';-- MySQL, when the definer account does not exist on the serverGRANT ALLOW_NONEXISTENT_DEFINER ON *.* TO 'deploy'@'%';-- MySQL 8.0 and 8.1, which have neither of the twoGRANT SET_USER_ID ON *.* TO 'deploy'@'%';-- MariaDBGRANT SET USER ON *.* TO 'deploy'@'%';SUPER covers both on MySQL. Under --json the refusal is outcome: refused with the code definer-privilege, and definer_privileges names
each object with its definer, the account and the privilege. A
statement that names the connected account or CURRENT_USER needs no
privilege, and neither does CURRENT_ROLE on MariaDB, where it is the current
role. MySQL reads CURRENT_ROLE as the account CURRENT_ROLE@%, and the check
treats it as one.
The check reads plain SHOW GRANTS, which on both servers includes the
privileges of an active role and leaves out a role that is granted but not
active, as the server does. The grants are read on a session of their own,
so a role that a file activates with SET ROLE is not counted. This is the one
case where the check refuses a statement the server would accept: an account
that holds the privilege only through such a role is refused, although the
server would accept the statement after the SET ROLE.
Where the check cannot decide, it refuses nothing, and the server answers with error 1227 as it would without the check:
SHOW GRANTSfails;- on MySQL before 8.0, the account lacks
SUPER, the only privilege that names another account there; - on MariaDB, the account holds
SUPERwithoutSET USER: MariaDB 10.11 accepts it, 11.4 and later refuse it; - on MariaDB, the definer is written without a host, so it may name a role;
- on MySQL, the account holds
SET_ANY_DEFINERwithoutALLOW_NONEXISTENT_DEFINER, and it cannot readmysql.userto learn whether the definer exists, or the run creates or renames an account, since the server is read before the run starts; - a quoted name holds a backslash, whose meaning depends on
sql_mode.
The plan records the mode before and after each statement on a view, as the
sql_security effect.
Roles and grants
Section titled “Roles and grants”Reading roles and the grants on them needs SELECT on the mysql database, which
ordinary schema work does not. An account without it records them as not described
and is not refused, so a comparison decides nothing about them in either direction.
When a SQL diff finds no changes in the readable objects, it names roles and
grants as a comparison limit instead of claiming the schemas are fully synced.
Native schema diff --format json includes these limits in comparison_limits.
An account that compares grants needs SELECT on mysql.*.
Events
Section titled “Events”A read reports an event rather than refuses over one, because Ptah does not model
it. No schema source can declare an event: a SQL file that says CREATE EVENT fails
with unsupported CREATE target: EVENT, and a dev database replay refuses it, since
what an event does on its schedule cannot be confined to that database. So no
comparison reads events and no plan creates, changes or drops one. schema apply
leaves them where they are.
An event made by hand is therefore not a difference, and a report that found nothing
else says so rather than claiming the database matches. drift, failed, the
findings and the exit code stay what they would be without it:
No schema drift found, but 1 event was not compared.Failure threshold: all. Failing: false.Database: mysql://root:***@db/app
Not compared:- event "nightly_cleanup": Ptah does not model events, so nothing compares or plans this oneThe JSON report carries the same objects under not_compared, each with a kind, a
name and a reason, and the schema it is in when the URL names no database.
github-actions annotates each one as a warning. schema compare opens with
No schema differences detected, but 1 event was not compared: over the same list,
and schema serve lists them under Not compared. schema diff, schema inspect,
db read, schema apply, schema plan and migrations plan keep their output as it
is and name them in a warning on standard error. migrations generate writes files
for what differs and does not name them.
What a report says depends on what the read can list. schema drift, schema compare
and schema serve claim to have compared, so they say when the read could not list
events. The verbs that warn on standard error claim nothing about them, so they warn
only about an event the read saw, and an account without EVENT runs them without
this warning:
| Read | schema drift |
The verbs that warn on standard error |
|---|---|---|
The account holds EVENT and the database has none |
No schema drift detected. |
Nothing |
The account holds EVENT and the database has some |
Names each one under Not compared |
Names each one |
The account holds no EVENT on the database |
An entry with no name, whose reason asks for the privilege |
Nothing |
| The server refuses the catalog | An entry with no name, whose reason gives the server’s answer |
Nothing |
information_schema.EVENTS lists a database’s events only to an account holding
EVENT on it, at the database level, on a pattern or globally. Measured on MySQL
8.4 and 26.10 and on MariaDB 11.8 and 12.3, every other privilege answers no rows
and no error, including SELECT on mysql.*, so an empty answer alone says nothing.
Nothing the comparison does depends on the answer, so that account is not refused.
MariaDB with --event-scheduler=DISABLED refuses the query for every account with
error 1577, and MySQL answers it.
To have a report name the events, add GRANT EVENT ON app.* TO reader. EVENT also
lets the account create and drop events, and neither engine has a privilege that
only shows them. A run that does not depend on them can say so with
--exclude '*[type=event]', as for any other kind, and nothing reads or reports
events then. This whole-kind selector does not warn that it matched no objects,
even when the account cannot list events. A selector naming one event, such as
--exclude 'e_tick[type=event]', counts as a match and takes that event out of
the report, since the run is not about it. An --include selection leaves out
of the report every event it does not name. An event is not something to
compare, so an --include that names only events matched no objects, and
schema apply refuses it as it refuses any empty selection.
Dev-database cleanup privileges
Section titled “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.
When MariaDB runs with event_scheduler=DISABLED, db drop-all --dry-run
reads event names from mysql.event if information_schema.EVENTS refuses the
query. Existing events remain in the plan. MariaDB also refuses DROP EVENT
while the scheduler is disabled, so executing that plan requires a server that
can drop the events.
A dev server, a --dev-url that names no database, is emptied database by
database and needs none of these. The run found no user database there before
it replayed, so it drops only the databases the session sees, which are the
ones it created; see
A whole dev server.
Views that read other views
Section titled “Views that read other views”The server checks a view’s query when the view is created, so a view has to be
created after every view it reads. Ptah reads the body of each view the plan
adds or replaces, and creates a view after the views of the plan it reads,
whatever order the schema file or the names give. A name is a read where a
relation can appear, as after FROM or a JOIN; a column alias, a table alias
or a common table expression that has the name of another view is not.
depends_on adds an order the body does not show. A name inside a string is
not a reference, and a string is read as the server reads it: "x\"y" is one
string.
Views the plan adds that read one another in a ring are refused before anything is applied, because no order creates them:
error: generate schema apply operations: the views a_v, b_v read one another in a cycle, so none of them can be created first: a_v reads b_v reads a_vDropping a table read by a view
Section titled “Dropping a table read by a view”MySQL accepts DROP TABLE when a view still reads the table. The view stays in
the catalog, but querying it fails. When a plan drops a table that a declared
view still reads, the generated SQL warns and names the view. The warning does
not block apply. Remove or replace the view, or keep the table, before applying
the plan. A view the plan removes is dropped before the table. The warning
cannot identify a view absent from the declaration and hidden from the read.
A dev database can hold such a view after a replay, for example when
migrate lint replays a migration that drops a table a view reads. Both
servers refuse to lock that view with error 1356, so cleanup drops the views
the server cannot open first, then locks and drops the others. The checks for
views and stored programs in other databases run again before that first drop.
A SQL SECURITY DEFINER view whose definer account does not exist on the dev
server cannot be opened either. MySQL refuses the lock with error 1449 and
MariaDB with error 1446. A replayed file that writes DEFINER= for an account
of the target server leaves such a view, because an account with the
privileges to name another definer creates it with a note. Cleanup drops it
with the other views the server cannot open. A SQL SECURITY INVOKER view
naming the same account opens and goes through the usual lock.
Replacing a trigger
Section titled “Replacing a trigger”A plan runs every trigger change in one block, after the columns a new trigger
body uses are added and before the columns an old body reads are dropped. Both
servers accept a trigger whose body writes a column that does not exist yet,
and a column dropped while a trigger reads it through NEW; in both cases
every write to the table fails with error 1054 until the plan moves on.
MySQL has no CREATE OR REPLACE TRIGGER, so a changed trigger is replaced with
DROP TRIGGER and CREATE TRIGGER. A renamed trigger is created before the
old one is dropped, so a body the server refuses leaves the old trigger in
place. A write between two such statements sees no trigger or both of them, so
when the block both removes or replaces a trigger and creates one on a table,
it runs under LOCK TABLES ... WRITE and the write waits instead:
ALTER TABLE `secondtable` ADD COLUMN `NewColumn` VARCHAR(100);-- Writes to these tables wait until their triggers are replacedLOCK TABLES `mytable` WRITE;CREATE TRIGGER `mytable_after_insert_v2` AFTER INSERT ON `mytable` FOR EACH ROW INSERT INTO secondtable (tbl_time, NewColumn) VALUES (NOW(), NEW.name);DROP TRIGGER IF EXISTS `mytable_after_insert_v1`;UNLOCK TABLES;MariaDB replaces a trigger that keeps its name with one
CREATE OR REPLACE TRIGGER, which keeps the old trigger when the server
refuses the new body and takes no lock. A rename takes the lock there too.
The plan check rates the DROP TRIGGER of a trigger the block creates again as
the change it is, a warning, so --check-destructive passes a changed
trigger. The DROP TRIGGER of a renamed trigger’s old name removes that
trigger, and is destructive.
- The statements run in order on one session, as
migrations up,schema applyandptah-compat migrate applyrun them. A program that runs planned statements on a connection pool can leave a statement waiting behind the lock another connection holds. - The migration account needs the
LOCK TABLESprivilege. LOCK TABLESwaits for a metadata lock while another transaction still holds the table, and later writes queue behind it. A migration’slock_timeoutbounds that wait, as it bounds every DDL statement’s, and the migration then stops before it touches a trigger. Without one the wait lasts as long as the server’slock_wait_timeout: a year on MySQL and a day on MariaDB.- The migrator refuses
CREATE TRIGGERandLOCK TABLESinside a migration transaction.migrations generatewrites such a file with-- +ptah no_transactionandptah-compat migrate diffwith-- atlas:txmode none, so it applies with the default--tx-mode. A file written by hand needs one of them, or--tx-mode none. - When MySQL refuses the new body of a trigger it replaces by name, the old
trigger is already gone, the migration is recorded dirty, and the lock ends
with the session.
migrations up --allow-dirtytakes the lock again before it retries the failed statement. - A dev-database replay runs the lock too, and refuses a lock on a table in another database unless the dev server belongs to the run.
schema apply --dev-urlrehearses a view or trigger body whose columns are qualified with a table or alias the body reads, as inSELECT u.id FROM users u, and a trigger body’sNEW.colandOLD.col. Where a table name is read, as inINSERT INTO new.audit, a qualifier names a database, and one other than the target’s refuses the rehearsal. So do a routine call such asother.f(), a sequence such asNEXTVAL(other.s)and a three-part path such asother.t.v.
A column’s fractional seconds
Section titled “A column’s fractional seconds”timestamp, datetime and time hold no fractional-second digits where a column
declares none, on both servers. So datetime and datetime(0) are one type and plan
nothing, and datetime(6) declared datetime plans a MODIFY COLUMN that rounds the
digits away. The plan rates that destructive. See
how a column change is rated.
A column’s size
Section titled “A column’s size”decimal and numeric are decimal(10,0) where a column declares no precision, on
both servers. So decimal and decimal(10,0) are one type and plan nothing, and
decimal(10,2) declared decimal plans a MODIFY COLUMN that rounds 1.50 to 2,
rated destructive. char, binary and bit are the same types with a length of one.
float(p) is float up to a p of 24 and double above it, and real is double
unless REAL_AS_FLOAT is set.
text and blob hold 65535 bytes, the tiny types 255, the medium types 16 MB and the
long types 4 GB, so longtext declared text is rated destructive, and in strict mode
the server refuses a value that does not fit. text(n) and blob(n) read as the type the
server builds for them, so blob(100) and tinyblob are one type, and text(60) and
tinytext are one type. text(n) and a varchar(n) under a text type are read at four
bytes a character, which is utf8mb4. double declared float reads
1.23456789012 back as 1.23457, without a word, and is rated destructive. So are a lost
sign (int declared int unsigned), half a range lost (int unsigned declared int),
and a member an enum or a set stops naming.
On MariaDB a json column is a longtext with CHECK (json_valid(c)) under the column’s
own name, and a column declared json plans nothing against that shape. A longtext with
no such CHECK plans the ADD CONSTRAINT, and any other type plans a MODIFY COLUMN that
writes the CHECK with the type. Declaring a checked int as plain int, or a json
as plain longtext, removes the column’s check with MODIFY COLUMN.
The plan rates removal or replacement of a column check as destructive because
the new definition may stop enforcing the old condition.
Making a column NOT NULL
Section titled “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 theNULLto the type’s zero value, such as0or the empty string, with a warning. ADEFAULTin the sameMODIFYis 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
Section titled “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: 1000000A 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
Section titled “Next steps”- Which release lines are declared and at what support level: Database support matrix.
- Capability keys per dialect: Capabilities.
- The
online_ddlkeys and every other configuration key: Configuration.