MySQL Guide

PormG runs on MySQL 8.4 or newer, through MySQL.jl 2.2.2 or newer. An older MySQL.jl still loads (Julia does not check an extension's [compat], so a manifest resolved before PormG raised the floor keeps it), and PormG warns once at using MySQL, naming the floor; Pkg.update() fixes it. The same models, the same fluent query API and the same migration engine run on it as on PostgreSQL and SQLite, and the integration suite runs on all three. This page is the entry point for what is particular to MySQL:

1. What MySQL lacks. A feature MySQL does not have raises BackendCapabilityError naming it, rather than being emulated. The capability table lists them, and the guarantees table says how each promise PormG makes holds on MySQL.
2. What a query means on MySQL. Where MySQL's own operator or function computes something other than what PormG's expression means, PormG renders it differently (DIV for an integer division, CONCAT_WS for Concat), or refuses the query before any SQL is sent.
3. Migrations and inspectdb. MySQL's DDL is not transactional, and its catalog re-prints what it stores. PormG plans and reads the schema around both.

The deep-dive pages own the full reference for each feature. This page states what holds on MySQL.

Server versions

PormG needs MySQL 8.4 or newer. It asks the server its version when a connection opens, and an older server is refused there with BackendCapabilityError naming both versions, before any statement is sent. Reading the schema and a migration plan's DDL need nothing newer than that floor. A MariaDB server is refused with InvalidConfigurationError: it is a different engine, planned as a separate backend.

Connecting

Set adapter: MySQL in connection.yml, and load the extension with using MySQL. The keys a MySQL connection reads, and the ones it refuses, are in Using MySQL.

Each connection runs in UTC (time_zone = '+00:00'), with utf8mb4, the utf8mb4_0900_bin connection collation, and MySQL 8.4's default sql_mode plus NO_AUTO_VALUE_ON_ZERO (so a 0 written into an AUTO_INCREMENT key is stored as 0, as on the other engines, not replaced by a generated id), so neither the server's global settings nor a DBA's change to them alters what a statement means.

What runs on a MySQL pool is what runs on the other engines: the connection pool, raw fetch with ? placeholders, run_in_transaction / atomic, and with_advisory_lock, which takes a named lock (GET_LOCK). Server errors arrive as IntegrityError, OperationalError or StatementError, classified by MySQL's error number.

Writes

• Single-row writes and deletes: create, update (it returns the rows it matched, a row it left unchanged included), save, and delete with its cascades. MySQL refuses a subquery that reads the table an UPDATE or DELETE writes (error 1093), so a joined filter's selection and a membership subquery ("id__@in" => queryset, and each cascade step's subquery over its parents) are read through a derived table. A correlated one cannot be, and MySQL's error 1093 still reaches you for it, for a scalar Subquery over the table being written, and for a joined filter whose joins lead back into that table. An update whose SET reads a joined table renders MySQL's multi-table UPDATE t AS a, … SET a.col = …. MySQL assigns an UPDATE's SET items in order, so an item that reads a column an earlier item writes (update("laps" => F("grid"), "grid" => F("laps"))) is refused with QueryBuildError, and in the multi-table form any item that reads another written column, since MySQL leaves that order unspecified. An item reading its own column (F("laps") + 1), or, in a single-table UPDATE, a column a later item writes, builds.
• Upserts: update_or_create and get_or_create (Creating rows). update_or_create is INSERT … ON DUPLICATE KEY UPDATE, which updates whichever row a unique key matched, so it runs only when the lookup is the one unique key the row can conflict on and raises BackendCapabilityError (upsert_conflict_target) otherwise. get_or_create is a plain INSERT whose duplicate-key error re-reads the winner. For both, the lookup must be a unique key declared on the model.
• Bulk inserts: bulk_insert with on_conflict and returning (Bulk operations). One ? per cell, and each statement is split under MySQL.jl's 4,096 parameters. A targeted on_conflict or a DO UPDATE needs its target to be the one unique key the rows can conflict on (upsert_conflict_target), read from the frame: a column takes part when it is inserted and non-NULL in at least one row. The skipped rows of on_conflict = :nothing are counted from MySQL's report of each multi-row INSERT (Records: N Duplicates: D, in whatever language the server writes it); a one-row chunk is sent without the clause, and its duplicate-key error is the skip. A DO UPDATE counts every row of the chunk. MySQL's DO NOTHING is an update that writes nothing, so it locks each existing row it meets until the transaction ends and can fire the table's UPDATE triggers, where PostgreSQL's skips without either. returning reads the rows back on the transaction's connection, by a key the table carries or the on_conflict target; for an AUTO_INCREMENT key the table leaves out it raises BackendCapabilityError (preallocated_keys), as allocate_primary_keys does. resync_sequences has nothing to repair: an explicit id moves AUTO_INCREMENT past it.
• Bulk updates: bulk_update is a multi-table UPDATE t AS Tb, (VALUES ROW(?, …), …) AS source (…) SET Tb.col = source.col WHERE …, each SET target qualified (a bare one is ambiguous there). It counts matched rows, a row whose values did not change included, as update() does. A text match key compares by the column's collation, as a filter does: on a case- and accent-insensitive one (utf8mb4_0900_ai_ci, MySQL 8's default) "LOTUS" matches lotus, and two keys of the frame that the collation equates both match that row, which ends with one of their values. A filters= subquery over the table being written is read through a derived table (error 1093 otherwise), as in update().
• The many-to-many manager: add is an idempotent INSERT … SELECT … FROM (VALUES ROW(?, ?), …) … WHERE NOT EXISTS (…), one statement per group of 2048 targets (4096 parameters), so it needs no unique key on an explicit through table, and the groups of a larger add run in one transaction; remove, clear and set are the same statements on every engine, and a remove of more targets than one statement binds (4096 parameters) is split, in one transaction.

Reads

Every read the query API builds renders on MySQL, from filter, joins along relation paths, values, aggregates and window functions to Exists, Subquery, CTEs, JSON path lookups and explain() (with no analyze, buffers or verbose), except the features the capability table marks no for MySQL: the regular-expression lookups (@regex, …), the JSONB operators (@jcontains, @has_key, …), the accent-insensitive lookups (@iunaccent_*), full-text search and a FULL OUTER JOIN (join_type = "FULL", full_joins; MySQL has RIGHT JOIN), each refused with BackendCapabilityError. What follows is where MySQL needs its own rendering to keep a read's meaning, or where PormG refuses one.

• Text compares code points. Every text column PormG creates or adds declares utf8mb4_0900_bin on the column itself, and each connection's collation is the same, so filter("surname" => "senna") does not match Senna, "Senna " (a trailing space) is not "Senna", and @contains, @startswith and @endswith respect case and accents, as on PostgreSQL (#1265). The case-insensitive lookups (@icontains, @iexact, …) fold case with LOWER and stay accent-sensitive. That holds for a column PormG adds to a table it did not create, too (#1434): the table's default collation reaches only a column added by hand. A column PormG did not create keeps its own collation, through a retype too, and MySQL compares by it: under the server default utf8mb4_0900_ai_ci, = ignores case and accents there.
• order_by on text sorts by code point, not by a locale (a representation difference: PostgreSQL sorts by its collation's locale).
• NULL placement is PormG's default on every engine (last ascending, first descending). MySQL has no NULLS FIRST/NULLS LAST, so PormG sorts on an (expr IS NULL) key first. An ordering expression that binds a value is refused with BackendCapabilityError; project it and order by the name. An ordering on a projection named after a column of a table the query reads, such as values("laps" => "position").order_by("laps"), puts the projection's own SQL in the key, because MySQL would read laps there as the column. Under distinct() or a grouping, MySQL can refuse such a key over a projection computed from a column it does not project or group (position + 1, a Case, Lag("position")) with its own error 3065 or 1055; name the projection after no column to order it. When that column's table is joined only by a later term through a nested query's OuterRef, PormG cannot tell in time and refuses the ordering with QueryBuildError; rename the projection or order by that term first.
• A boolean is a TINYINT(1) and reads as a Bool, from its declared field type, since MySQL has none on the wire.
• A DateTimeField is a DATETIME(3) (#1270), the precision a Julia DateTime holds, read in the connection's UTC as a ZonedDateTime. A value with digits below the millisecond (a DATETIME(6) column PormG did not create, NOW(6)) raises InvalidValueError instead of losing them.
• Sum over integers reads as an integer, and a sum past the 64-bit range raises, as on PostgreSQL; MySQL itself computes a DECIMAL, which PormG turns back with DIV 1. Avg over integers reads as a Float64 (each connection sets div_precision_increment = 30, so the mean is not cut to 4 decimals).
• select_for_update(no_key = true) is refused with BackendCapabilityError: MySQL has no lock weaker than FOR UPDATE that leaves foreign-key checks free.
• Division means what it means on every engine: F("grid") / 2 divides integers as integers (truncating toward zero, -9 / 4 is -2). MySQL's own / keeps the fraction (7 / 2 is 3.5), so PormG writes DIV between two sides it types as integers and / beside a float or a decimal side. A side whose type PormG cannot name (a text column) is refused with QueryBuildError, naming the casts that say which division you mean. A quotient over a Sum PormG cannot type is refused too, since MySQL's SUM of integers is a DECIMAL. Division by zero reads NULL (the server records a warning), as on SQLite; PostgreSQL raises.
• Cast maps each type name to the MySQL CAST target that holds its meaning: text and the character types to CHAR (a size kept), every integer to SIGNED under a range guard (below), a sized numeric(p, s) to DECIMAL(65, s) with p applied as the row is read, a bare numeric to DECIMAL(65, 30) (MySQL's own bare DECIMAL has no fraction), real to FLOAT (a Float32), the doubles to DOUBLE, date to DATE, a timestamp to DATETIME(3), time to TIME(6), json to JSON and uuid to CHAR(36). A boolean cast over a boolean or an integer is ((x) <> 0), as PostgreSQL's integer::boolean reads it, and over any other value it raises QueryBuildError, since MySQL casts to no boolean type. A type with no such target (interval, bytea, a type of your own) raises UnsupportedConnectionError before any SQL; an array, a network type or a full-text type (tsvector, tsquery) raises BackendCapabilityError. Where MySQL computes another value than the cast means, the cast is refused with QueryBuildError: a boolean's text ('1', not 'true'), a date, a timestamp, a time or an interval cast to a number (MySQL reads the digits, 2009-03-29 → 20090329), and a literal a numeric(p, s) cannot hold (MySQL clamps it to the largest value the type holds). A column value too large for its cast would be clamped the same way, with only a server warning, where PostgreSQL raises "numeric field overflow". That depends on the row, so PormG computes the cast at DECIMAL(65, s), which rounds to the same scale, and raises StatementError with sqlstate == "22003" as it reads a row the declared p cannot hold, as on SQLite (#1110). A cast used only in a filter, an ORDER BY or a GROUP BY is never read back, and one composed into arithmetic or an aggregate reads as the computed number it means, so neither is checked: there it is the value as it is. The same read check runs on a DecimalField column, so a column made wider than its field by hand raises 22003 for a value the field cannot hold, where PostgreSQL returns it. A bare numeric (DECIMAL(65, 30)) holds 35 digits before the point. An unsized char keeps one character, as on PostgreSQL (CHAR(1)). MySQL's SIGNED is 64-bit and returns a value outside smallint or integer as it is, and past 64 bits wraps or clamps it, so Cast(x, "smallint" | "integer" | "bigint") renders a guard: CAST(IF(<the 64-bit cast> BETWEEN <the type's range>, <the 64-bit cast>, ~0 + ABS(…)) AS SIGNED) (a bigint's range compared as DECIMAL(65,0)). It keeps SIGNED's rounding and an integer result, and a value outside the type raises StatementError with sqlstate == "22003" on the row, in a projection, a filter, an ORDER BY or an aggregate alike, as PostgreSQL's cast does (#1356). A declared output_field of an integer type on Coalesce, Greatest, Least or Case renders the same guard over the whole function, and raises the same (#1405).
• Concat skips a NULL operand, as on every engine, and renders CONCAT_WS('', …). PormG never writes || for MySQL, where it is a logical OR ('a' || 'b' is 0).
• Date arithmetic renders MySQL's date functions, since MySQL's own + reads a date as the number its digits spell (DATE '2009-03-29' + 1 is 20090330). F("date") + Day(3) and every other duration is ± INTERVAL ? <unit> per component, with years and months as one month count, as PostgreSQL adds them (2008-02-29 plus one year and one month is 2009-03-29); a whole number of days, a literal or an integer column, is ± INTERVAL (n) DAY; F("date") - F("date") is the whole days between them (DATEDIFF). A DATE shifted by whole days stays a Date, and by hours becomes a timestamp. A value PormG cannot type beside a date (F("date") + Sum("year")) is refused with QueryBuildError. A shift past MySQL's last date, 9999-12-31, reads NULL (MySQL records a warning).
• A DurationField is a BIGINT of microseconds, Django's choice for MySQL: MySQL has no interval type, and its TIME stops at 838:59:59. It reads back as the Dates.CompoundPeriod it reads as on every engine. A filter binds the duration as its microseconds, an equality and @in included; ordering, Sum/Avg/Max/Min, arithmetic (F("time") * 2, F("time") + F("time")) and a timestamp difference (F("start_at") - F("other"), TIMESTAMPDIFF(MICROSECOND, …)) compute in microseconds, exactly; a date shifted by one is ± INTERVAL (d) MICROSECOND. A product, a quotient and a mean are rounded to the microsecond, half away from zero (beside a FloatField, a tie of half a microsecond rounds as MySQL's DOUBLE does). Cast(d, "text") reads the HH:MM:SS text PormG writes (00:01:20.5), as on SQLite, where MySQL's own cast would print the count; Cast(d, "date"), "time" or "timestamp" is refused with QueryBuildError, since the cast would read the count's digits. What has no meaning is refused with QueryBuildError before any SQL, as on SQLite: a duration plus a number, times another duration, or a number compared with one, through any spelling (an expression, a filter either way round, a Subquery, a queryset for @in, an alias).
• Conditionals (Case/When, Coalesce, Greatest/Least, NullIf) render MySQL's own functions. Greatest and Least skip a NULL argument through the same COALESCE rotations as on SQLite, since MySQL's GREATEST is NULL when any argument is. A Decimal literal inside an expression (a Case branch, a Coalesce or Value operand) binds as CAST(? AS DECIMAL(p,s)) at its own precision, so it reads back, and orders, as a number rather than text; one past MySQL's DECIMAL (65 digits, 30 after the point) is refused with QueryBuildError. A duration beside a value of another type (Coalesce("fastestlaptime", 0)) is refused with QueryBuildError, declared or not, since one value cannot be both a count and a duration there. A conditional over text and a number (Greatest("positiontext", "grid"), Coalesce("positiontext", 0)) is refused with QueryBuildError too, on every engine, since MySQL would compare the two as strings (GREATEST('10', 9) is '9'): cast the operand that differs (Mixed date, time, text and number operands, #1327). A declared output_field is CAST(… AS T), except that a boolean declaration over booleans or integers reads ((x) <> 0) (MySQL casts to no boolean type; over any other value it is refused), and over durations a text declaration reads the HH:MM:SS text and a date, time, timestamp or boolean declaration is refused.
• Math functions (Abs, Round, Floor, Ceil, Sqrt, Power, Ln, Exp, Mod) render MySQL's own functions, and over an integer Abs, Floor, Ceil and Round keep it. MySQL rounds a double half to even (ROUND(2.5E0) is 2), so Round over a float rounds it as DECIMAL(65, 30): half away from zero, as on every engine, from the double's shortest digits; a float of 1e35 or more is clamped by that cast. A negative precision rounds to tens, hundreds, … (Round(125, -1) is 130). Over a decimal MySQL computes Sqrt, Exp, Ln and Power as a double and Floor/Ceil as a BIGINT; each reads back as the Decimal it means, as on SQLite. Sqrt of a negative number, Ln of 0 and Mod(x, 0) read NULL (the server records a warning), as on SQLite; PostgreSQL raises. An Exp or Power past the double range raises, as on PostgreSQL.
• Date parts (Extract, ToChar and the @year, @month, @week, @week_day, @quarter, @date, … transforms) render MySQL's own date functions, numbered as PostgreSQL numbers them: the ISO week is WEEK(x, 3) (MySQL's default WEEK mode is not the ISO week) and @week_day is DAYOFWEEK, 1 = Sunday. Extract takes SQLite's eleven parts plus QUARTER, EPOCH, MILLISECONDS and MICROSECONDS. EPOCH is computed from 1970 with TIMESTAMPDIFF, exact before 1970 too, where MySQL's UNIX_TIMESTAMP answers 0; EPOCH of a time raises BackendCapabilityError, and so do CENTURY, DECADE, MILLENNIUM, JULIAN and the TIMEZONE fields. ToChar takes the formats of the shared table through DATE_FORMAT; any other format raises BackendCapabilityError, and a DurationField raises QueryBuildError (use Cast(x, "text")). A date part of a time (Extract(Cast("start_at", "time"), "year"), ToChar of a time with a year, month or day in its format) raises QueryBuildError, since MySQL reads a time's date as today's. The Extract parts and the @ transforms over a TimeField column are refused on every engine. EPOCH is a decimal (#1457), so a quotient over it (Extract(d, "epoch") / 2, or a sum that holds one) keeps the fraction, as on PostgreSQL.
• Text functions render MySQL's own, with Length as CHAR_LENGTH (MySQL's LENGTH counts bytes). LPad/RPad with an empty literal fill render the cut alone, as PostgreSQL answers it; a fill column that is empty on a row gives "" on MySQL. Substr renders SUBSTR and refuses a position below 1 or a negative length, where MySQL counts from the end or answers "".
• Text and binary values read back by their column's character set: text as a String, a binary type as a Vector{UInt8}. MySQL marks every column of a binary collation (PormG's utf8mb4_0900_bin) with its binary flag, and MySQL.jl decides by that flag, so PormG prepares every statement that returns rows, even one without parameters, to read the character set from its column definitions. A read with no parameters costs one more round trip for it.
• Window functions render MySQL's own (Rank, DenseRank and RowNumber cast to a signed integer, so they read back as an Int64). ROWS and RANGE frames render, a RANGE interval in MySQL's INTERVAL 7 DAY spelling; a GROUPS frame or an EXCLUDE clause raises BackendCapabilityError. A window's ORDER BY without nulls sorts NULLs first ascending, as SQLite does.

Migrations

• Migrations (#1268): makemigrations, migrate, status, dry_run, check, run_once and ensure_table!. A model's tables are created with backtick-quoted names, ENGINE=InnoDB, utf8mb4 and the utf8mb4_0900_bin collation. The column types are BIGINT AUTO_INCREMENT for an IDField, BIGINT for a foreign key, a BigIntegerField and a DurationField (its microseconds), INT and SMALLINT (the positive fields with a >= 0 CHECK, not UNSIGNED, which turns a subtraction below zero into an error), TINYINT(1) for a boolean, DOUBLE, DECIMAL(p,s), VARCHAR(n) for the fields with a length, LONGTEXT for the text fields without one (TEXT holds 64 KB), DATE, DATETIME(3), TIME(6), CHAR(36) for a UUIDField, JSON and LONGBLOB. A literal default on a LONGTEXT, LONGBLOB or JSON column is written as an expression (DEFAULT ('…')), the only default MySQL takes there. A db_default is the portable CURRENT_TIMESTAMP or CURRENT_DATE, or the mysql entry of a per-engine one (db_default = (postgres = "gen_random_uuid()", mysql = "uuid()")), written DEFAULT (<expression>). MySQL re-prints such an expression (lower('X') reads back as lower(_utf8mb4\'X\')), so PormG stamps the column with PostgreSQL's ownership marker in its comment, in the same plan step: MySQL has no anonymous block, so one SET reads the default the server printed and builds the MODIFY that restates the column with the marker, and PREPARE/EXECUTE run it. The column converges after one migrate, a comment already on it is kept, and a default changed by hand is planned again (Column defaults). A server that logs statements refuses a non-deterministic default (uuid()) added to a table that has rows (error 1674). A quoted string is a literal there too, refused as db_default, while a number is an expression. A declared CheckConstraint carries PormG's marker in its clause, CHECK ((<condition>) AND 'pormg:check:<hash>' IS NOT NULL), a term that is always true, since MySQL keeps no comment on a constraint; a renamed one is re-created in one ALTER TABLE, and a CHECK name another table of the database holds, another model declares, or that starts with <table>_chk_ (MySQL's own prefix, which it renames with the table) is refused by makemigrations (MySQL scopes the names per database). A functional index (Index(expressions = …), or a UniqueConstraint of expressions) writes each expression in parentheses of its own and the hashed marker of its text as the index's COMMENT; MySQL refuses one whose expression is LONGTEXT (error 3757: write the cast, CAST(notes AS CHAR(64))), and nothing counts the rows a functional UniqueConstraint rejects before the plan runs. MySQL cannot change an index's comment in place, so PormG adopts no index there, as on SQLite. A double-quoted name in a CheckConstraint condition or in an Index or UniqueConstraint expression is refused by makemigrations with InvalidMigrationError, naming the bare spelling: PormG's session sql_mode has no ANSI_QUOTES, so MySQL reads "laps" >= 0 as a comparison of the text 'laps', and the constraint or index would be over a constant. Write names bare (laps >= 0, LOWER(name)), which is the column on every engine; a name with no bare spelling (a reserved word such as user, or one not in lower case) is refused with a pointer to db_column (#1368). A FULLTEXT or SPATIAL index, one over a column prefix (KEY (note(10))) and one mixing columns and expressions are never read: no declaration states one, so makemigrations leaves a hand-made one alone and inspectdb leaves it out of the model with a warning naming it (#1369). A foreign key is checked at each statement (deferred_foreign_keys). MySQL accepts ON DELETE SET DEFAULT but enforces it as RESTRICT, so a SET_DEFAULT key is applied by PormG's delete and refused by MySQL on a delete PormG does not make. An index has no partial form (partial_indexes), and method, opclasses and include are refused (index_methods). A key or index over a field written as LONGTEXT, LONGBLOB or JSON (such as a TextField, EmailField, ImageField, FileField, PasswordField, BinaryField or JSONField) is refused by makemigrations (unbounded_text_indexes): MySQL indexes those columns only by a prefix; declare a CharField. A key over CharFields longer than InnoDB's 3072 bytes (4 bytes a character in utf8mb4: one max_length above 768, or a composite whose lengths add up past it) is refused by MySQL at migrate (error 1071), after the statements before it committed. A name a model writes longer than 64 characters is refused by MySQL (error 1059); an index name PormG derives (<table>_<cols>_uniq, a many-to-many join table's) is cut to 55 characters and 8 hex digits of its hash there. PormG keeps index names unique across a database's tables on every backend, though MySQL scopes them per table.
• Changes to a table that exists. A column change is one ALTER TABLE statement whose clauses carry it whole (so it applies or does not): MODIFY COLUMN restating the column's type, nullability, default and AUTO_INCREMENT, which MySQL replaces together, PormG's CHECKs dropped and added by name, the column's own unique index, the primary key. What the column holds that no model states is read from the catalog and restated, so the change touches only what the model changed, as PostgreSQL's per-column ALTER does: a COMMENT, INVISIBLE, ON UPDATE CURRENT_TIMESTAMP on a column still a timestamp, a collation other than the table's on a column still text, and an expression default the model does not declare. A retype keeps the column's collation (a column of the table's collation keeps it by inheriting it); a column retyped into text from a type that holds none is declared utf8mb4_0900_bin, as an added one is. A retype drops that default (it may not suit the new type), which takes destructive = true, as a PostgreSQL retype with USING does. A retype MySQL would apply by a rule that changes every value, or that fails on rows nothing counted, is refused, as PostgreSQL refuses it for want of a cast: a date or a time into a number, JSON or the other of the two (a time takes today's date), a number or a boolean into a date or a time, anything but text into a UUID, a boolean into JSON (1), binary to or from anything but text, and JSON or a UUID into anything but text. A generated column made by hand reads as a plain one, and a change to it restates its generation (a MODIFY that left it out would end it, or fail on a VIRTUAL one); a default declared for one is refused, since it holds none. An index over a column the plan drops is dropped first, after the table's foreign-key drops, since MySQL would keep it over the columns that remain (UNIQUE (a, b) becoming UNIQUE (a)). MySQL keeps every foreign key indexed, so a plan that drops a column from under the only index a key the table keeps has (the plan's other index drops counted) is refused by makemigrations: index the key column on its own first (db_index = true, applied by a migration of its own), then drop the column. A plan that takes a key's only index away otherwise (a db_index turned off, an undeclared index dropped) is refused by MySQL at migrate (error 1553). A renamed column drops and re-adds the CHECKs MySQL will not let it take along (error 3959), and renames its unique index; the drop makes such a plan destructive (destructive = true), as any constraint drop is. A dropped foreign key takes the index InnoDB made for it. A dropped table is dropped with the session's foreign-key checks off, as PostgreSQL's CASCADE would remove the keys of the children the same plan alters; a foreign key to it from a table the plan does not manage (an ignored table, an unmanaged model's, one in another database) is refused by makemigrations, since it would be left pointing at a missing table. A db_index turned off on a column that keeps its foreign key is refused by MySQL (error 1553: InnoDB keeps every foreign key indexed), and retyping a column a foreign key joins is too (error 3780).
• The row pre-checks count, before any statement runs, the rows a change would fail: MySQL's strict sql_mode fails the ALTER on a value that does not convert or fit, part-way through a plan. A shorter VARCHAR counts trailing spaces too (MySQL refuses them, where PostgreSQL trims them); a narrower integer or decimal, a NULL under NOT NULL, a duplicate under a new key, a key's orphans, the rows a new CheckConstraint rejects, and text into a number, a date, a time, JSON or a UUID are each counted, and migrate refuses the plan with MigrationPrecheckError naming the counts. Text MySQL would convert by rounding ('12.5' into an integer) is counted as failing, as PostgreSQL refuses it. A NOT NULL column added without a default is refused over existing rows: MySQL would fill them with 0 or ''. What MySQL applies and changes — a lower decimal scale, a timestamp's time of day dropped — takes destructive = true, as on PostgreSQL.
• migrate runs a plan's statements on one connection, renewed afterwards. lock_timeout sets that connection's lock_wait_timeout (whole seconds); statement_timeout is refused (statement_timeouts), since MySQL bounds no DDL statement's run time. A second instance waits on the GET_LOCK, and its schema check runs inside the lock (before it, a half-applied plan reads as changed). An interrupt that reaches a plan statement's await sends KILL QUERY for that statement before the run is recorded failed and the connection is renewed, asking the server to stop it rather than run it to its end; the statements before it stay applied. ensure_table! sends it too, before it drops the table it had begun to create, or before it rethrows when the interrupted statement was its CREATE TABLE (#1642). The cancel is best effort, as everywhere on MySQL: it needs a second connection, and a statement already committing finishes anyway. A Ctrl+C reaches the await under the thread layout Cancelling a statement below describes.
• run_once takes the migration lock and commits the step and its record together, as on PostgreSQL. A DDL statement inside a MySQL step commits the transaction implicitly, so keep a step to writes. ensure_table! runs its statements on one connection, and drops the table again when a statement after its CREATE TABLE fails.

inspectdb

• inspectdb is import_models_from_mysql(db; include_table, include_views, file), and check reports a column's expression default as on the other engines. A column reads as the field whose values it holds: what PormG writes reads as itself, and so does a type MySQL has that holds the same values (an integer's display width, any datetime(n) or time(n), a timestamp, which PormG's UTC session reads as a datetime, and text, mediumtext, tinytext). A type whose values differ (an unsigned integer, tinyint(4), mediumint, char(n), enum, set, float, year, bit, a binary type but longblob) is written as a TextField with a warning, and the next makemigrations retypes it, as PostgreSQL's money is. MySQL spells some fields alike, so they read back as one: a BIGINT is a BigIntegerField (a DurationField reads back as one), a CHAR(36) a UUIDField, a TINYINT(1) a BooleanField, and a key that is not BIGINT AUTO_INCREMENT an IDField, with a warning that the next plan changes it (MySQL refuses that on a key a foreign key references, error 3780). MySQL re-prints a CHECK's condition, a functional index's expressions and a column default with every name in backticks and every literal with its charset (_utf8mb4'x'), which no declaration can hold, so PormG reads that text with the backticks and introducers taken off. Text that cannot be written that way for every engine (a column named by a word some engine reserves, such as order, or user, which PostgreSQL reads as current_user; a name MySQL reads bare as a charset introducer, such as _latin1; a backslash escape in a literal) is left out of the model, with a warning naming it; when it is PormG's own CHECK or index, the warning says the next makemigrations drops it unless it is declared by hand. What PormG made is held only to MySQL's own reserved words, since it was declared in that bare text: a CHECK or index of PormG's over a column named user imports. A default PormG stamped keeps MySQL's own text even with a backslash, since it is the mysql entry of a per-engine db_default. A MODIFY for any other change (null, a default) restates the column's own type rather than PormG's, so a datetime(6) keeps its microseconds, and a TextField over a tinytext keeps its 255-byte bound and a DateTimeField over a timestamp its 2038 one (each refused loudly under PormG's strict session). A CHECK made by hand is declared, and the first makemigrations adopts it: MySQL can neither rename nor comment a CHECK, so the plan re-creates it with PormG's marker, one statement per CHECK. A generated column made by hand is declared as a plain field, with a warning, and a view over a table dropped since is skipped with one.

Production notes

• Retries. A statement is retried once, on a renewed connection, only when MySQL.jl says it never ran. That is error 2006, "MySQL server has gone away", which since MySQL.jl 2.2.0 means the statement's write failed or the connection was already known dead (#1502), and error 4031, "The client was disconnected by the server because of inactivity" (#1629). An idle connection the server killed (an administrator's KILL) shows up as 2006 over TLS, MySQL 8's default, so the statement runs on a fresh connection and returns. A connection closed for its wait_timeout shows up as 4031: the server sends that farewell before it closes the connection, and MySQL.jl reads it only after writing the next statement, which the server had stopped reading. That statement never ran either, so it too runs on a fresh connection and returns (see below for how a checkout usually renews such a connection first). A statement whose connection is lost after it was sent (error 2013, "Lost connection to MySQL server during query") is never retried, because it may have committed: PormG raises OperationalError and renews the connection after it. Over a plaintext connection, a killed idle connection may be noticed only after the statement was written, in which case it reports 2013 and is not retried. No retry happens inside a transaction or on a connection you pinned. A connection the server closed while it sat idle in the pool past its wait_timeout (8 hours by default, often less on a hosted server) does not reach a statement: a checkout pings a connection idle longer than validate_after_idle (60 seconds by default) and renews it before your statement is sent (#1548; Advanced Configuration). Keep validate_after_idle below the server's wait_timeout, which saves the failed round trip. With validate_after_idle: 0, the first statement after an idle gap longer than wait_timeout meets error 4031 and is retried on a renewed connection, with a warning in the log, and returns.

• Cancelling a statement. A Ctrl+C during a statement raises, as on the other engines (Async), and PormG sends KILL QUERY for the statement on a second connection, since MySQL has no out-of-band cancel and closing the socket leaves the statement running on the server. That needs MySQL.jl 2.2.2 or newer, PormG's floor. Before it, MySQL.jl's background reaper task could take the interrupt and drop it, so the query ran to its end and raised nothing (#1520, MySQL.jl#254). A KILL QUERY that fails (the second connection cannot be opened, or the server refuses it) is logged as a warning that the statement may still be running on the server; one that finds the session already gone is not, since the statement has ended with it. Run Julia with an interactive thread, which plain julia, -t N, -t N,1 and -t auto all give: the reaper then runs on the default threadpool and is never the task the interrupt goes to. Two cases remain that neither PormG nor MySQL.jl can change. With no interactive thread (julia -t 1, -t N,0), the reaper or one of Julia's own timer tasks can be the task resident on the main thread, so the first Ctrl+C ends that task instead of reaching your query, and the query runs to its end. On macOS, MySQL.jl's maintainers saw about 1 in 3 interrupts dropped inside Julia's runtime even with no MySQL code loaded. An interrupt that lands on the task awaiting the result leaves the connection that ran the statement usable. One that lands inside MySQL.jl's own read closes that connection, and the pool replaces it. Over TLS, which MySQL 8 negotiates by default, PormG recognises that second kind only with Reseau 1.6.2 or later, the socket layer under MySQL.jl (MySQL.jl#247). MySQL.jl's own compat still admits older Reseau versions, and PormG sets no bound on it. Check the version with Pkg.status("Reseau"; mode = Pkg.PKGMODE_MANIFEST), and run Pkg.update("Reseau") if it is older. With an older Reseau, that interrupt is lost inside the TLS error: it arrives as a lost connection (OperationalError), the connection is renewed, and the statement keeps running on the server until it ends.

• register_connection does not take adapter = "MySQL": a MySQL connection is configured by its keys in connection.yml, through load.