MySQL

Safety rules (apply whenever running SQL against MySQL)

1. Idempotent where possible. Prefer INSERT IGNORE / ON DUPLICATE KEY UPDATE over plain INSERT. Guard DDL with CREATE TABLE IF NOT EXISTS; MySQL has no CREATE INDEX IF NOT EXISTS, so declare indexes inline inside that block. For a table with no single-column natural key guard the seed INSERT with WHERE NOT EXISTS (...). Guard natural-key seeds with INSERT IGNORE against a UNIQUE constraint.
2. Single statement only. Never send several statements separated by ; in one execution (SELECT 1; DROP TABLE Foo;).
3. Timeouts. 10 s connection timeout (--connect-timeout=10). 30 s query timeout with SET SESSION MAX_EXECUTION_TIME=30000; or the hint /*+ MAX_EXECUTION_TIME(30000) */; it throttles only SELECT, not DML/DDL.
4. Row cap. Append LIMIT 10000 to ad hoc SELECTs.
5. Credentials. Never echo a full connection string or password; redact (Password=REDACTED). -p'password' inline triggers the CLI warning "Using a password on the command line interface can be insecure"; the value still must not be echoed.

Tool: python C:/Github/tools/sql_run.py --engine mysql --host <host> --user <user> --db <db> -e "<one statement>" enforces rules 2 to 5 (refuses a second statement, 10 s / 30 s timeouts, 10000 row stop, password only via MYSQL_PWD, redacted output) and notes a non-idempotent statement. --via <container> borrows a running container's mysql client for any host. Read its docstring for the flags (--read-only, --allow-multi for a reviewed seed file, --dry-run).

Connecting

• No Windows Integrated Auth equivalent: plan username/password auth in every environment. Use alphanumeric-only passwords for anything passed through shell commands (special characters broke CLI connections through shell escaping).
• Borrow a running container's client to reach any target: docker exec <container> mysql -h <any host, including a remote RDS/Lightsail endpoint> -u <user> -p'<password>' <db> -e "<sql>". No native client needed on the host.
• Credentials without a command line: MYSQL_PWD=... mysql -h 127.0.0.1 -u user db < file.sql (the mysql client reads it; the .NET connectors do not: add it to the connection string from the environment at startup).

Case, names and escapes

• DESC in an index is honored as true descending on InnoDB since 8.0. There is no INCLUDE syntax: a plain composite index is the closest (InnoDB secondary indexes carry the primary key).

Stored procedures and routines

• CALL p(...), IN/OUT; a plain SELECT returns a result set. Errors with SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...', MYSQL_ERRNO = 55509; a foreign-key delete failure is error 1451 (DECLARE EXIT HANDLER FOR 1451 SELECT -1). Parameters shadow columns of the same name: prefix them.
• CALL returning a flag: CASE WHEN EXISTS (...) THEN 1 ELSE 0 END AS IsSelected arrives in the Oracle EF provider's SqlQueryRaw<T> as a bool property without a cast.

Types and information_schema

• tinyint(1) is a boolean, a signed tinyint is -128..127, unsigned changes the C# type, text/longtext/json are unbounded, datetime has no zone, timestamp is converted to UTC; no money type (use decimal(19,4)).
• information_schema.COLUMNS.COLUMN_TYPE carries the length, unsigned and the enum list; EXTRA has auto_increment, DEFAULT_GENERATED (default is an expression), STORED GENERATED; COLUMN_KEY = 'PRI'; unique indexes from STATISTICS (NON_UNIQUE = 0); foreign keys from KEY_COLUMN_USAGE (REFERENCED_TABLE_NAME IS NOT NULL).

EF Core

• Default to Microting.EntityFrameworkCore.MySql on the MySqlConnector driver. It is Pomelo's fork under a new package and namespace (same API) and follows EF Core. Pomelo lagged a major EF Core release, and Oracle's MySql.EntityFrameworkCore (UseMySQL(...)) brings a second driver (MySql.Data, with its own MySqlParameter, MySqlException and MySqlConnectionStringBuilder), so the exception, parameter and builder types differ from the schema reader's MySqlConnector. Pomelo 9.0.0 (EF Core 9) cannot even be configured on EF Core 10.0.13 (MissingMethodException); Microting 10.0.14 targets EF Core 10 and needs MySqlConnector 2.6.2 or later.
• The call: options.UseMySql(connectionString, serverVersion). ServerVersion.AutoDetect(conn) opens a connection while the options are built, so an app that must start with the server down uses a fixed new MySqlServerVersion(new Version(8, 0, 0)). The password from MYSQL_PWD is added with MySqlConnector.MySqlConnectionStringBuilder (neither driver reads that variable). The error number is MySqlConnector.MySqlException.Number (1062 duplicate, 1451 referenced, 1452 no parent, 1406 too long, 1048 null); the parameter type is MySqlConnector.MySqlParameter.
• BIT(64) is a ulong to MySqlConnector. An entity property typed decimal (the only type that holds 64 bits) fails on read with Unable to cast object of type 'System.UInt64' to type 'System.Decimal'; give it a converter, new ValueConverter<decimal, ulong>(v => (ulong)v, v => v). BIT(1) reads as bool and BIT(8) as a number without help. A SET is its comma-separated text, YEAR a number, and spatial columns need a converter that turns well-known text into the binary form (a 4-byte SRID and the geometry); with SRID 4326 the stored order is latitude first, so write the text with ST_GeomFromText(..., 4326, 'axis-order=long-lat') when the application means longitude first.
• FromSqlRaw("CALL p(@a, @b)", params) + ToListAsync() works; a CALL is not composable (no .Count()/.Single() in SQL). A scalar count procedure returns SELECT COUNT(*) AS Value for SqlQueryRaw<int>.

Triggers and defaults

Disposable copies and throwaway servers

• Copy a database by tables: CREATE DATABASE scratch; then, per table, CREATE TABLE scratch.t LIKE src.t; INSERT INTO scratch.t SELECT * FROM src.t; (rows and secondary indexes came across in the test). A login that has a grant on one database only cannot run CREATE DATABASE (error 1044).
• Test a routine when you cannot create a database: create a copy of it under another name in the same database (ZZ_<name>; the script needs DELIMITER $$), call it between START TRANSACTION; and ROLLBACK;, then DROP PROCEDURE and check information_schema.routines for leftovers. Put each call that may be refused in its own file: the mysql client stops at the first error, so a refused call ends the script before the lines after it (tested: error 1644 at the SIGNAL, the later SELECT did not run). A good call inside the transaction raised the row count from 2 to 3 and ROLLBACK put it back to 2.
• A throwaway MySQL server: docker run -d --rm --name <name> -e MYSQL_ROOT_PASSWORD=<throwaway> -p 127.0.0.1:3307:3306 mysql:8.4 on a spare port (never the user's containers); wait with mysqladmin ping; load the schema with docker exec -i <name> mysql -uroot -p... < schema.sql; remove it with docker rm -f <name>. Checked with 8.4.13. The saved tool C:\Github\tools\throwaway_db.py does all of this (up, sql, list, down, --skip-log-bin): it touches only zz- containers it made and never pulls an image, so prefer it to typing the commands.

How this was verified

Tested on this machine. The evidence, the versions used and the items not re-run are in reference/verification.md: read it before saying a claim here was tested, and treat anything listed there as not re-run as unverified.

What this does not prove

• That a rule holds on MySQL 5.7, MariaDB, macOS (lower_case_table_names=2) or a managed service such as RDS. Tested versions: 8.0.46 on Windows and 8.4.13 on Linux.
• That a trigger script works: say so in the script header until it has run on the target server.
• That a generator's output is right for enum, set or CHECK forms not seen in the samples.