Everything about sqlshape that is MySQL's: the version and server settings the schema declares,
the Go types and constraint names the rules use, the runtime on database/sql, the migration
commands, and what the checker embeds and how its verdicts are verified. The rules themselves are in
checks.md and are the same for every database; this page is where the names,
numbers and types in those rules come from when the schema declares mysql.
Read it when you start on MySQL, and when you need the Go type a column takes, how to connect, or the name a failure mode goes by. What a MySQL error number in a diagnostic means is in mysql-errors.md.
- The schema declares the version and the server's settings
- The Go type table
- The runtime: database/sql: connecting, running statements, checking the server's settings
- Constraint names and failure modes, including what a trigger or routine raises
- What the rules use: the
Oneproof, grouping - Not on MySQL
- Migrations
- Errors the checker predicts
- What the checker embeds
-- sqlshape: mysql 8.4
-- sqlshape: server sql_mode = 'ANSI,STRICT_ALL_TABLES'
-- sqlshape: server lower_case_table_names = 1
CREATE TABLE ...schema.sql names the MySQL version it is written for; 8.4 is the one embedded. MySQL's own
grammar then parses the schema and every statement, and MySQL's rules type the expressions. Two
declarations that disagree, or one that also names postgres, are rejected.
The declaration chooses the rules statements are judged by; it does not restrict the server. The program runs on whatever server go-sql-driver/mysql connects to, another MySQL version, MariaDB or another MySQL-compatible database included, but the verdicts still follow 8.4's rules, and where that server behaves differently they are not guaranteed to match it (README: Limitations).
A server variable that changes how a statement is judged is declared next to the version, one per line, so the checker and the production connection agree on it (checks.md). MySQL reads three:
-
sql_mode: the names in any case, comma-separated, the empty string, and the combination modesANSIandTRADITIONAL, expanded as the server expands them. The modes that change a judgment:- the lexer bits (
ANSI_QUOTES,PIPES_AS_CONCAT,IGNORE_SPACE,NO_BACKSLASH_ESCAPES,HIGH_NOT_PRECEDENCE,REAL_AS_FLOAT): how the parser reads the text; ONLY_FULL_GROUP_BY: the group check on or off;- strict mode (
STRICT_TRANS_TABLESorSTRICT_ALL_TABLES): whether a string function is nullable, and whether aNULLinto aNOT NULLcolumn is a failure mode; NO_UNSIGNED_SUBTRACTION: the difference of unsigned operands is signed.
The rest act at run time only and are accepted as written.
- the lexer bits (
-
lower_case_table_names: 0 compares table and view names case-sensitively (the Linux default), 1 stores them lower-cased, 2 keeps the spelling and compares without case. 2 is only for a case-insensitive filesystem (macOS / Windows); on a case-sensitive onemysqldwarns and starts at 0 instead, out of step with a schema that declares 2 (mysql.Verifyreturns that drift). -
max_sp_recursion_depth: 0 to 255. At 0 (the default) a PROCEDURE thatCALLs itself is 1456 on every invocation; above 0 the depth a call reaches is not static, so the checker predicts nothing for it.
Without a declaration the checker assumes a freshly initialized 8.4 server: the default
sql_mode (ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION),
lower_case_table_names = 0 and max_sp_recursion_depth = 0.
What go-sql-driver/mysql delivers with parseTime=true, verified against a running server. An
integer arrives as int64 (uint64 for BIGINT UNSIGNED), a DECIMAL as its text, temporal
types as time.Time or a string, binary strings and JSON as []byte. A comparison or a logical
operator is a bigint(1), which bool may receive; so may a TINYINT(1).
| MySQL | Go |
|---|---|
TINYINT / SMALLINT / MEDIUMINT / INT / YEAR |
int64 / int32 / int (uint64 / uint32 / uint too when UNSIGNED); TINYINT(1) also bool |
BIGINT |
int64 / int (uint64 / int64 when UNSIGNED); a bigint(1) also bool |
DECIMAL |
string |
FLOAT |
float32 / float64 |
DOUBLE |
float64 |
BIT |
[]byte |
CHAR / VARCHAR / TEXT / ENUM / SET |
string / []byte |
BINARY / VARBINARY / BLOB |
[]byte |
JSON |
[]byte / string |
DATE / DATETIME / TIMESTAMP |
time.Time / string |
TIME |
string |
ENUM columns, key identities |
a Go named type (Giving types a meaning); an ENUM is a value set like a CHECK (col IN (...)) |
There is no // sqlshape: type binding on MySQL: it has no named types to bind a Go type to.
A few results are not the type they are written over:
- a window function's integer (
MIN(id) OVER (),FIRST_VALUE,NTH_VALUE,LAG,LEAD...) reaches the client through the window's temporary table, which widens it:INTandBIGINTbecomeBIGINT,TINYINT/SMALLINT/MEDIUMINTbecomeINT, the signedness kept, andYEARbecomesINT UNSIGNED. A plain aggregate keeps the column's type (MIN(small)over aSMALLINTis aSMALLINT), and so does aROLLUPgroup column unless the plan happens to materialize the grouping, which the checker does not predict; - under
ROLLUP, the columns that read the grouped columns are nullable (the super-aggregate rows hold NULL there); a constant in the select list is not; DATE'...'/TIME'...'/TIMESTAMP'...'literals carry their type (the fractional digits written) and are never NULL; one the server cannot read as exactly that type (aDATEwith a time part, aTIMESTAMPwithout one, a zero month underNO_ZERO_IN_DATE, a displacement outside-14:00to+14:00) is the statement's error 1525;<=>is never NULL, whatever its operands;USER(),CURRENT_USER(),DATABASE(),SCHEMA(),VERSION()andCURRENT_ROLE()are character strings (utf8mb3), not binary strings.
$ go get github.com/kr9ly/sqlshape/v2 # Query / One: the declarations the checker reads
$ go get github.com/kr9ly/sqlshape/mysql/v2 # running them on database/sql with go-sql-driver/mysql
github.com/kr9ly/sqlshape/mysql/v2 runs a declared statement through database/sql, with
go-sql-driver/mysql as the driver. mysql.DB is *sql.DB, *sql.Tx or *sql.Conn. The
template's {{.X}} become MySQL's positional ? on the wire, the arguments lined up in the
order the placeholders appear (a parameter used twice is sent twice).
The DSN must set parseTime=true. The Go type table above assumes it: without it the driver
returns DATE, DATETIME and TIMESTAMP columns as bytes, and a time.Time field cannot
receive them.
db, err := sql.Open("mysql", "app:secret@tcp(localhost:3306)/app?parseTime=true")Set clientFoundRows=true as well if an ExecOne UPDATE that writes the values a row already
has should count that row (below).
for o, err := range mysql.Run(ctx, db, ListOrders, p) { ... } // iter.Seq2[Order, error], streamed
orders, err := mysql.Collect(ctx, db, ListOrders, p) // []Order
first, err := mysql.First(ctx, db, ListOrders, p) // ErrNoRows (sql.ErrNoRows) when none
res, err := mysql.Exec(ctx, db, MarkPaid, p) // sql.Result
u, err := mysql.Get(ctx, db, UserByEmail, p) // One: ErrNoRows when absent
u, ok, err := mysql.Find(ctx, db, UserByEmail, p) // One: ok reports presence
res, err := mysql.ExecOne(ctx, db, MarkOrderPaid, p) // One: ErrNoRows when no row was touchedWhat every runtime does the same way (row mapping, One, the guarantee that only checked SQL
runs, errors under the expect line's names) is in runtime.md. What is MySQL's:
- The receive types are the driver's, as in the table above; a
DECIMALarrives as its text, so a money type of your own can wrap it. - A constraint violation comes back as a
*mysql.ConstraintErrorwhoseKeyis the schema's name for it, as below;mysql.Violates(err, key)tests for it. A trigger's or a routine's own SIGNAL comes back the same way, keyed the way below describes. ExecOnejudgesRowsAffected, which MySQL counts as changed rows: anUPDATEto the values a row already has reportsErrNoRowsunless the DSN setsclientFoundRows=true. For anINSERT ... ON DUPLICATE KEY UPDATEor aREPLACEthe 0, 1 or 2 rows MySQL reports for the one row are all one row.
mysql.Verify(ctx, db, schemaSQL) asks the connection for its session @@sql_mode and the
server's lower_case_table_names and returns an error when they differ from what the schema
declares (a freshly initialized 8.4's defaults when it declares none): a DSN's sql_mode=..., a
pool's session setup or a server configured otherwise would run the statements under rules the
checker did not judge them by. A mode name 8.4 does not define (another MySQL version or
MariaDB may run with some) is left out of the comparison rather than reported; only a
difference in the modes 8.4 knows is an error, and its message names the modes it left out.
Call it once at start-up, after opening the pool.
if err := mysql.Verify(ctx, db, schemaSQL); err != nil { ... }A failure mode is named as MySQL names the constraint, and numbered as MySQL numbers the error:
| constraint | name | error |
|---|---|---|
| primary key | PRIMARY |
1062 |
UNIQUE key |
the key's name | 1062 (a key the server numbers itself, or one a NULL leaves alone, cannot be violated; a prefix key UNIQUE (c(10)) or an expression key UNIQUE ((n * 2)) is violated through the columns it reads) |
| foreign key | the CONSTRAINT name, or <table>_ibfk_<n> when it has none |
1452 on the child side, 1451 on the parent side (following ON DELETE / ON UPDATE CASCADE) |
CHECK |
the CONSTRAINT name, or <table>_chk_<n> |
3819 |
NOT NULL |
<table>.<column> |
1048 |
a view's WITH CHECK OPTION |
the view's own name (MySQL's own message names it, unlike PostgreSQL's unnamed 44000) | 1369 |
| a base column with no default an INSERT through a view leaves unassigned | the view's own name (the server's message names the view, not the column -- whether or not the view exposes it) | 1423 |
What a statement's own form does to the list:
INSERT IGNOREviolates nothing;ON DUPLICATE KEY UPDATEabsorbs the insert's key violations;REPLACEviolates no key and may violate a referencing foreign key (1451);UPDATE IGNOREabsorbs aWITH CHECK OPTIONview's 1369 the same way it absorbs a key orNOT NULLviolation, unlike a trigger's ownSIGNAL, which noIGNOREabsorbs;- without strict mode only a single-row
INSERTorREPLACE(itsON DUPLICATE KEY UPDATEincluded) rejects aNULLfor aNOT NULLcolumn; more rows,INSERT ... SELECTandUPDATEstore the type's implicit default with a warning, so no 1048 is listed for them.
mysql.Violates(err, key) tests the run-time error by the same names; mysql.WrapError(err)
gives an error from a statement run outside Run / Exec the same wrapping first.
A literal the column can never store is not a failure mode but the statement's own error (mysql-errors.md).
Not a failure mode here: a length, range or ENUM value truncation (1265 / 1406 / 1366 /
1264). It is a property of the type (a parameter's Go type, a literal's own value set), caught
on that side.
A trigger or a stored routine brings its failure modes to the statements that fire or call it.
A statement on a table takes the failure modes of that table's triggers for its event:
INSERT / UPDATE / DELETE; REPLACE fires the INSERT and DELETE triggers, ON DUPLICATE KEY UPDATE the INSERT and UPDATE ones. A trigger's or routine's own writes bring their own
failure modes into the body's: the schema's constraints, and what those writes' own triggers
raise in turn (a cycle is cut).
A SIGNAL is keyed the way the runtime reads the error back:
- a
SIGNALthat setsMYSQL_ERRNOis keyed by that number as decimal text, a builtin's number too:SET MYSQL_ERRNO = 1062under SQLSTATE'23000'is keyed1062, not by a key name the message never carried; - a
SIGNALthat sets noMYSQL_ERRNOis keyed by its SQLSTATE; - SQLSTATE class
01is a warning and no failure mode; an unhandled class02is 1643, and anything else unhandled is 1644.
A named CONDITION resolves to its value; a bare RESIGNAL re-raises whatever the innermost
HANDLER is itself handling. A DECLARE ... HANDLER FOR absorbs the matching failure modes of
its own block (SQLEXCEPTION everything but classes 01 and 02, SQLWARNING / NOT FOUND
their class, a SQLSTATE or number itself); INSERT / UPDATE IGNORE absorbs none of a
trigger's SIGNALs (the statement still fails). SELECT ... INTO carries 1172 unless the query
is provably at most one row, the same proof One uses (a query with no row is NOT FOUND, a
warning, never a failure).
-- sqlshape: error <key> = <Name> above a CREATE TRIGGER / FUNCTION / PROCEDURE, the
same annotation checks.md documents for
PostgreSQL, gives <key> a Name that a program mirrors with sqlshape.Error(<key>); vet checks
the two against the schema both ways. On the expect line the Name and the key are
interchangeable. At run time mysql.Violates compares the code (30001), since a
sqlshape.Failure from sqlshape.Error("30001") carries the code and nothing else.
What the server refuses in a body at CREATE, and the numbers a call to a routine can carry,
are in mysql-errors.md.
A write through a view declared WITH CHECK OPTION discharges require pinned(<col>) on the
base table the way checks.md describes (sqlshape check
prints it as ok(view)), and the 1369 above is what makes the pin genuine: a view without the
clause never discharges it (a write through it moves the row out of the view's WHERE
silently). What is MySQL's about it:
- a plain
WITH CHECK OPTIONis CASCADED, so every underlying view'sWHEREcounts, behind a join as well;WITH LOCAL CHECK OPTIONstops at the view itself except for an underlying view that declares a check option of its own, which the server keeps enforcing; - it holds for
UPDATE,INSERTandREPLACEalike; aDELETEnever checks the option; - an underlying view's own check option is enforced whatever the view written through says, and the 1369 still names the view written through.
Every rule in checks.md applies to a MySQL schema the same way, judged by the MySQL
analyzer: result columns and parameters against the Go types, the NULL handling, the meaning of
types, the failure modes, the One proof, and every declaration of Part 2 (visible where,
pinned, via view, EXISTS, aggregate, transitions, never, paired, single,
sensitive, context). Diagnostics carry MySQL's error numbers and message texts
(Unknown column 'nope' in 'field list' (MySQL error 1054)). {{.X}} becomes a ? on the wire.
Proved from PRIMARY KEY and UNIQUE keys over whole columns, LIMIT 1, and an aggregate
without GROUP BY (wherever it sits: COALESCE(SUM(total), 0) is one row as much as SUM(total);
an aggregate a subquery owns is the subquery's); MySQL has no partial indexes.
MySQL runs the checks of sql_mode ONLY_FULL_GROUP_BY, and so does the checker, with the
server's numbers: in a grouped or aggregated query every select-list, HAVING, ORDER BY and
window PARTITION BY / ORDER BY expression is a GROUP BY expression, an aggregate, or made of
columns functionally dependent on the group columns (1055; 1140 without GROUP BY). The
dependencies the server recognizes are the ones the checker recognizes:
- a table's columns once its
PRIMARYorUNIQUEkey is known (a nullable key column only where a conjunct rejects its NULL); col = colandcol = literalinWHEREand inner joins;- an outer join's
ON, into its nullable side; - a derived table's or view's body, through its outputs;
- under
ROLLUP, the group expressions only.
The neighbouring checks come with it:
| rule | error |
|---|---|
a column named outside an aggregate in HAVING must be a select-list column or alias, or a GROUP BY column |
1054 |
with DISTINCT, an ORDER BY expression not in the select list may read only select-list columns |
3065 |
an aggregate in the ORDER BY of a query that aggregates nowhere else |
3029 |
an aggregate in the ORDER BY of a set operation |
3028 |
SELECT email, count(*) FROM users GROUP BY name
-- Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
-- 'users.email' which is not functionally dependent on columns in GROUP BY clause; this is
-- incompatible with sql_mode=only_full_group_by (MySQL error 1055)
SELECT name, count(*) FROM users GROUP BY id -- OK: id is the primary keyTwo statement forms are two writes, not one, when the schema's rules (require, visible where) are judged:
| statement | writes recorded | why |
|---|---|---|
INSERT ... ON DUPLICATE KEY UPDATE |
an INSERT, and an UPDATE of the columns the branch assigns | the branch moves an existing row |
REPLACE |
an INSERT and a DELETE | a colliding key deletes the old row first (its AFTER DELETE trigger fires) |
What does not exist on MySQL is not checked there: Batch, Copy and MatView, PL/pgSQL,
domains, composite types and arrays, -schemas (a MySQL schema is one database),
// sqlshape: type, and seeded tables in migrations.
sqlshape diff, apply and verify-schema work on a MySQL schema the way they do on
PostgreSQL: the difference between the database and schema.sql becomes DDL, the DDL is checked
by its end state, then run. Both sides are read as the server's own SHOW CREATE TABLE /
SHOW CREATE VIEW, the target's in a scratch database on the -db server, and the DDL runs
statement by statement since MySQL's DDL commits implicitly. What is compared, the enum
declaration's form and the requirements are in migrations.md.
A diagnostic that carries a MySQL error number is one of two kinds. An error is certain: every
execution of the statement fails the same way, vet reports it, and the statement has to change.
A failure mode may happen at run time, for some rows or some values: it goes on the
statement's expect line, and mysql.Violates matches it when it comes back. The constraint
names above are failure modes. mysql-errors.md lists, with an index by error
number, the rest of what the checker predicts from a statement's own form: values stored into columns,
constant expressions, conversions and function arguments, spatial values, trigger and routine
bodies, name resolution, views, and statements without a result set.
The parser and lexer are MySQL 8.4's own, extracted from the server source (the grammar with its
actions stripped, the lexer as it is) and built to WebAssembly; the function catalog is read from
the same source. The analyzer is a pure-Go implementation over that tree; checking never connects
to a MySQL. Because it carries MySQL's parser, the MySQL side of the checker (check/mysql) is
under the GNU General Public License v2; it is linked into the sqlshape binary only, never into
your program.
The verdicts are checked against a running mysqld: 5,033 typed statements (the result types of
every built-in function in every argument combination), 65 error statements, the 376 statements
of the ONLY_FULL_GROUP_BY check, and 22 statements and 6 writes under non-default sql_mode
values agree with 8.4. Every server behavior this page and mysql-errors.md
describe was measured against mysqld 8.4.
MySQL's own test corpus is replayed as well: the 1,281 files of mysql-test/t (some 137,000
statements) run against a mysqld and the analyzer side by side, a SELECT's columns compared by
name, type family and nullability, an error by its number. The statements on which the two
knowingly disagree -- 2,525 -- are pinned one by one
(check/mysql/internal/analyze/testdata/corpus_baseline.txt, each with the class of the
disagreement: an error the analyzer does not predict or predicts under another number, a column
typed differently, a schema construct the loader does not model), and a new disagreement fails
the build. That file is the honest list of what the MySQL analyzer does not yet decide the way
the server does.
The MySQL side of the checker (check/mysql) carries MySQL's own parser and is under the GNU
General Public License v2, see cmd/sqlshape/LICENSE; it is linked into
the sqlshape binary, a development tool, and nothing under it is linked into your program. The
runtime (mysql) is Apache License 2.0, like the declarations.