Perfect-MySQL 4.0.0

The first release since 3.5.1 (December 2019). It is the Swift 6 rewrite of the package on top of Perfect-CRUD 3, and it fixes a run of correctness bugs found since: nested transactions, statements MySQL can't prepare, binary and non-UTF-8 values that came back truncated, table creation order for related tables, .reconcileTable data loss, identifier quoting, and building against MariaDB Connector/C. It is a major version: some raw API results change type and several behaviours change. Read "Breaking and behaviour changes" before upgrading.

Requirements

• Swift tools 6.2 (// swift-tools-version: 6.2); 3.5.1 declared 4.0.
• Platforms: macOS 12+ and Linux. No iOS (the client library isn't available there).
• The MySQL client library, found through pkg-config as mysqlclient: Oracle's libmysqlclient 8.0 or newer (Homebrew mysql-client on macOS, keg-only, so set PKG_CONFIG_PATH; libmysqlclient-dev on Linux), or MariaDB Connector/C through Debian/Ubuntu's libmariadb-dev-compat. The separate Perfect-mysqlclient / Perfect-mysqlclient-Linux packages are no longer used.
• Perfect-CRUD 3.0.1 or newer (3.0.1 fixes four crashes in 3.0.0).

.package(url: "https://github.com/PerfectlySoft/Perfect-MySQL.git", from: "4.0.0")

This release adds a GitHub Actions workflow for every push and pull request: Linux (Swift 6.2 and 6.4 with libmysqlclient, plus Swift 6.4 with MariaDB Connector/C, debug and release) against a MySQL 8.4 service container with the live tests required, in non-UTC time zones, and macOS (Xcode 26.0.1 and 26.6, debug and release) with the server-independent tests. Warnings are errors everywhere.

Highlights

• Swift 6 (#60). Builds in the Swift 6 language mode. MySQL, MySQLStmt, both Results classes and MySQLDatabaseConfiguration are @unchecked Sendable (still one task at a time per connection); MySQLOpt, MySQLServerOpt and MySQLCRUDError are Sendable. The client library is wrapped by an inline system-library target instead of a separate package.
• Perfect-CRUD 3 support (#60), including its Dynamic API: Database.select(DynamicQuery), mutate(DynamicMutation) (with affected-row counts and inserted IDs) and execute(DynamicSQL) work on MySQL.
• Nested transactions (#61). Perfect-CRUD 3's nested transaction {} uses SAVEPOINT, which MySQL can't prepare; these statements now run directly.
• Exact bytes from query results (#65). MySQL.Results gains nextBytes(), forEachRowBytes(callback:) and fieldIsBinary(at:).
• TLS modes with MariaDB Connector/C (#67). setOption(.MYSQL_OPT_SSL_MODE, …) works when built against Connector/C, mapped onto its own options (see the limitations below).
• Related tables are created in dependency order (#68), so create(Parent.self) works when a sub-table has a @ForeignKey back to the parent.

Breaking and behaviour changes

• Toolchain, platforms and the client library (#60). Swift 6.2+, macOS 12+. Upgrade: install the client library and pkg-config (on macOS, brew install mysql-client pkg-config and export PKG_CONFIG_PATH=$(brew --prefix mysql-client)/lib/pkgconfig), and drop any direct dependency on Perfect-mysqlclient.
• Perfect-CRUD 3 (#60). Its own breaking changes apply (for example, Expression is now CRUDExpression). Upgrade: follow the Perfect-CRUD 3.0.0 release notes.
• Binary values from MySQLStmt are [UInt8] (#64, #65). BINARY, VARBINARY, BIT and GEOMETRY columns, binary expressions (CAST(x AS BINARY), CHAR(n) without USING, x'..' literals, UNHEX, FROM_BASE64, UUID_TO_BIN, INET6_ATON, CONCAT with a binary operand) and every string column after SET NAMES binary used to come back as String, usually truncated or empty. JSON and binary collations such as utf8mb4_bin stay text. Upgrade: where you cast a raw MySQLStmt value to String, cast to [UInt8] and use String(decoding:as: UTF8.self), or CAST(… AS CHAR) in SQL. Through Perfect-CRUD nothing changes for String, UUID, Date, URL or Codable properties over binary columns, and [UInt8] / Data properties over VARBINARY now decode instead of reading as empty. A String property over a BLOB column now decodes the text instead of "".
• Text is no longer truncated at invalid UTF-8 (#65). MySQL.Results.next() and MySQLStmt's text path stopped at the first invalid byte (SELECT X'61FF62' returned "a"); they now return the whole value with U+FFFD for each invalid sequence. Upgrade: use nextBytes() where you need the exact bytes; fieldIsBinary(at:) says which columns are binary.
• Statements MySQL can't prepare run directly (#61). BEGIN, START, COMMIT, ROLLBACK, SAVEPOINT, RELEASE, LOCK, UNLOCK and USE are matched on their first keyword, ignoring case and leading whitespace, and anything else the server refuses to prepare (error 1295) falls back to direct execution. Uppercase CREATE/DROP/ALTER still run directly; lowercase DDL is still prepared, so it can take bindings. If such a statement returns rows (CHECK TABLE, XA RECOVER), it now throws instead of silently returning nothing. Upgrade: run row-returning admin statements with MySQL.query(statement:) and storeResults().
• setOption is stricter (#67). MYSQL_OPT_SSL_MODE accepts only 1...5 (libmysqlclient accepted 0 and 6), and an integer option that doesn't fit a UInt32, such as -1, returns false instead of trapping. Upgrade: check the return value.
• Options survive a failed connect (#67). connect() always passes CLIENT_REMEMBER_OPTIONS; both client libraries otherwise reset every option after a failed attempt, so a retry used to run without them (including the SSL mode). Upgrade: nothing, unless you relied on a retry dropping options.
• SSL modes on MariaDB Connector/C (#67). REQUIRED is enforced only after authenticating: Connector/C falls back to plaintext with a non-TLS server, so connect() then closes the connection and fails with error 2026, by which point an attacker able to tamper with the connection could have captured the authentication exchange (or the password, if the server asks for mysql_clear_password). REQUIRED also turns off MYSQL_OPT_RECONNECT. VERIFY_CA also checks the host name (Connector/C 3.4 skips the host-name check, and without MYSQL_OPT_SSL_CA the CA check, on local connections). DISABLED still uses TLS if any MYSQL_OPT_SSL_* file or cipher option is set. With libmysqlclient nothing changes. Upgrade: on Connector/C use VERIFY_IDENTITY with MYSQL_OPT_SSL_CA, which fails before authenticating.
• Table creation order (#68). create() emits each table after the tables it references within the set (sub-tables used to come first, so a child's FOREIGN KEY to the parent failed); with .dropTable, all of them are dropped first in reverse order. A child's FOREIGN KEY clause no longer leaks into the parent's CREATE TABLE. Upgrade: nothing; mutually referencing tables still can't be created with create().
• .reconcileTable (#68, #69). It no longer drops and re-adds existing columns whose names contain capital letters (they lost their data, or the DROP failed for a @ForeignKey column). A @ForeignKey column it adds gets its FOREIGN KEY constraint in the same ALTER TABLE, so the ALTER now fails if existing rows violate it. A @ForeignKey column removed from the model has its constraint dropped with it (this failed with error 1828 and left the schema half-reconciled). Upgrade: clean up orphaned rows before reconciling a model that gains a foreign key; reconcile is still not atomic.
• Dates are read as UTC (#72, #73). Perfect-CRUD has always written Date values as UTC text, but 3.5.1 parsed them in the process's local time zone. Outside UTC, dates came back shifted by the UTC offset, and select() silently stopped (dropping that row and every later one) when a row's UTC time fell in a local daylight-saving gap (for example 2026-03-08 02:30:00 under America/Denver), or when the column held DATETIME(6) fractions or a DATE. Dates are now parsed as UTC, fractions and DATE values are read, and the zero date reads as nil for an optional Date (a non-optional one is a decoding error, and Perfect-CRUD's select() stops at such a row, dropping it and every later one). Year 0 is accepted, so Date.distantPast (0000-12-30) round-trips, and a Date MySQL can't store now throws when bound: only non-finite dates used to crash, while years outside 0–9999 were written and then rejected by the server. The error comes from insert, update, count() and sql; in a select() whose where compares against such a Date, Perfect-CRUD catches it, logs it and returns no rows ([]). Dates also use the proleptic Gregorian calendar, as the server does (Foundation's calendar put dates before 1582-10-15 10 or more days off and rejected 1582-10-05…14), and fractions stay inside their second, so 9999-12-31 23:59:59.999999 no longer becomes year 10000 (written back as 9999-12-31 23:59:59; dates are written in whole seconds, truncated). Upgrade: rows written through Perfect-CRUD on or after 1582-10-15 read back correctly with no migration. Dates before 1582-10-15 that 3.x wrote (it used Foundation's Julian-before-1582 calendar both ways, and stored Date.distantPast as 0001-01-01) now read back 2 or more days different. Otherwise only apps that compensated for the old shift by hand, and local-time values written outside Perfect-CRUD (NOW() / CURRENT_TIMESTAMP on a server whose time zone isn't UTC, TIMESTAMP columns read in a non-UTC session time_zone), now shift; run those connections in UTC (for example SET time_zone = '+00:00') if you need them to line up. The connector doesn't set it for you.
• CALL and multiple result sets (#72, #73). A CALL through Perfect-CRUD now returns the procedure's first result set (it read every column as missing), and the remaining result sets are discarded, so the connection doesn't fail later statements with "Commands out of sync". An error the procedure raises after its first result set (a failing INSERT, SIGNAL) now throws from typed db.sql(_:_:) and Dynamic calls instead of being dropped; untyped db.sql("CALL …") reads only the first result, so it can only log such an error. Upgrade: if a procedure returns several result sets you need, read them with MySQLStmt directly.
• Counts and insert IDs (#72, #73). MySQL.numberAffectedRows() returns -1 after a failed query or before storeResults(), as the C API does, and lastInsertId() returns an ID above Int64.max as its bit pattern; both trapped. Through Perfect-CRUD's Dynamic API, a statement that returns rows reports their count as affectedRows and insertedID nil; an insert ID above Int64.max is reported as nil. Upgrade: use UInt64(bitPattern:) for BIGINT UNSIGNED auto-increment IDs from lastInsertId().
• Errors while reading rows throw (#72, #73). A query that fails partway through its rows used to return the rows read so far (none, in practice) through Perfect-CRUD; it now throws MySQLCRUDError. Upgrade: nothing, unless you relied on the empty result.
• DECIMAL columns decode into Double / Float (#72, #73). They read as 0.0; text that doesn't parse now throws. Upgrade: nothing.
• Duplicate column names (#72, #73). When a result has several columns with the same name, a Codable model or a Dynamic row now reads the first one; Dynamic rows also treat names that differ only by case as the same name. Upgrade: alias columns (c.id AS child_id) when you need both.
• listTables / listDatabases wildcards (#72, #73; security). The wildcard could end the string literal and, with CLIENT_MULTI_STATEMENTS, run SQL: on a MariaDB Connector/C build with any ' (Connector/C's mysql_list_tables doesn't escape at all); with libmysqlclient under big5, cp932, gbk, gb18030 or sjis (its escaping is byte-by-byte, so a non-ASCII byte can swallow the backslash); and with either library after a stored routine changed sql_mode, which leaves the client's NO_BACKSLASH_ESCAPES bit stale. The query is now built so it doesn't depend on that bit: a wildcard with ' has each ' doubled and nothing else changed; any other wildcard is escaped by the client library (mysql_real_escape_string_quote / mysql_real_escape_string); and these wildcards are refused (the result is []): any containing both ' and \, and under those five charsets any with a non-ASCII byte and a ' or \. Ordinary wildcards work in both NO_BACKSLASH_ESCAPES modes. Upgrade: a wildcard combining ' and \ now returns []; set the charset with MYSQL_SET_CHARSET_NAME rather than SET NAMES.
• Identifier quoting (#69, #71). quote(identifier:) doubles every backtick, per Unicode scalar, so table and field names passed through Perfect-CRUD's Dynamic API can't close the quoted identifier and inject SQL. This assumes a utf8mb4 connection, which the database:host: initializers set. Upgrade: if you build MySQLDatabaseConfiguration(connection:) from a connection using gbk, big5 or gb18030, don't pass untrusted identifiers through it.

Fixes

• #61: Nested transactions, LOCK/UNLOCK TABLES, START TRANSACTION and USE work through Perfect-CRUD (from #56). The package builds against MariaDB Connector/C (the my_bool shim from the Swift 6 rewrite redefined Connector/C's own typedef). MySQLStmt.fieldNames() no longer leaks a MYSQL_RES per prepared CRUD statement. A MySQLStmt keeps its MySQL connection alive.
• #62: Cached result metadata is refreshed per result set (Connector/C crashed with SIGBUS after nextResult()), and results() on a CALL no longer crashes without a prior fieldNames().
• #63, #66: Three tests that failed against current servers (a Perfect-CRUD cache-key collision, since fixed in Perfect-CRUD, and a test that ignored the port).
• #64: BINARY/VARBINARY values are bytes instead of truncated strings.
• #65: No truncation at invalid UTF-8; byte access for MySQL.Results; BIT and GEOMETRY are bytes. Decoding a 3 MB value went from about 1.2 s to about 3 ms in release.
• #67: MYSQL_OPT_SSL_MODE with Connector/C; options kept across a failed connect; out-of-range integer options no longer trap.
• #68: Parent tables are created before sub-tables that reference them; .reconcileTable keeps mixed-case columns and adds FK constraints for new columns.
• #69: .reconcileTable drops a removed @ForeignKey column's constraint; backticks are escaped in identifiers.
• #70: The live tests skip unless MYSQL_TEST_PORT names a test server and the host isn't localhost (they could otherwise drop databases on a local server on 3306).
• #71: Backticks are escaped per Unicode scalar, closing an identifier injection through the Dynamic API.
• #72, #73: DynamicMutation through a Connector/C build trapped ("Not enough bits to represent the passed value"), because the affected-row count was read after the statement was reset, which Connector/C reports as -1. The count and inserted ID are now read right after execution. Reproduced on Linux with libmariadb-dev-compat before the fix; a regression test covers it.
• #72, #73: Date parsing in UTC, with fractions, DATE values and zero dates (see above).
• #72, #73: numberAffectedRows() and lastInsertId() no longer trap on the C API's -1 sentinel or IDs above Int64.max.
• #72, #73: CALL returns its rows and leftover result sets are drained, also when a caller stops reading early, so a connection (including a pooled one) isn't left out of sync.
• #72, #73: Dynamic statements that return rows report the row count and no stale insert ID (on Connector/C the insert ID is the connection's).
• #72, #73: DECIMAL columns decode into Double and Float properties.
• #72, #73: a statement that fails while its rows are being read (for example a scalar subquery returning more than one row, error 1242) crashed with SIGSEGV on Connector/C and silently returned no rows with libmysqlclient. Through Perfect-CRUD it now throws; raw MySQLStmt.Results stops and sets the new failed, errorCode and errorMessage properties, and forEachRow returns false.
• #72, #73: parameter memory leaked on every execution: the bind array of every parameterized statement, and the buffer of every blob parameter ([UInt8], [Int8], bindParam(_:length:), so every Data/[UInt8] value Perfect-CRUD binds). leaks --atExit over 300 executions of a 3-blob INSERT went from 891 leaks (37,984 bytes) to 0. Preparing a MySQLStmt again with binds pending leaked and then trapped. All fixed.
• #72, #73: reusing a MySQLStmt (execute, read results, reset, execute again) corrupted the heap: the client library kept pointers into freed result buffers. A Results now detaches them when it's released, if the statement is still bound to it, so reassigning a results variable and walking a CALL's result sets also work. Re-executing a prepared CALL on Connector/C read freed metadata; the cached metadata is now refetched after each execution. execute() after reset() without binding every parameter read freed parameter buffers; it now returns false with error 2031 (CR_PARAMS_NOT_BOUND). That re-execution wasn't only reachable through direct MySQLStmt use: calling next() again on a Perfect-CRUD SelectIterator that had already finished re-executed its statement the same way. Binding more values than a statement has parameters wrote past the bind array and crashed, also through db.sql(_:bindings:) with too many bindings; the extra values are now refused with error 2034 (CR_INVALID_PARAMETER_NO), and execute() refuses until reset().
• #72, #73: an error raised by a CALL after its first result set is reported (see above).
• #72, #73: listTables / listDatabases SQL injection through the wildcard, including after a stored routine changes sql_mode (see above); wildcards keep working under NO_BACKSLASH_ESCAPES.
• #72, #73: [Int8] properties holding negative values read back instead of crashing, and with Perfect-CRUD 3.0.1 (now required) they round-trip through CRUD.
• #72, #73: dates use the proleptic Gregorian calendar and keep fractions inside their second (see above).
• #72, #73: a Dynamic select or execute whose result had column names differing only by case (SELECT 1 AS a, 2 AS A, or a join returning p.Name and c.name) crashed with "Duplicate values for key", because Perfect-CRUD 3.0.0's DynamicRow lowercases its keys. Dynamic rows now keep the first column of each name, ignoring case. Columns with exactly the same name (p.id, c.id) also resolve to the first column, for Codable decoding too; before, which one was kept was arbitrary.

Upgrade notes

1. Depend on from: "4.0.0", build with Swift 6.2 or newer, and raise your deployment target to macOS 12.
2. Install the client library and pkg-config and make sure pkg-config --cflags mysqlclient works (on macOS, set PKG_CONFIG_PATH for Homebrew's keg-only mysql-client). Remove any Perfect-mysqlclient dependency.
3. Apply Perfect-CRUD 3.0.0's upgrade notes.
4. Search for raw MySQLStmt reads of binary columns or binary expressions and switch them to [UInt8].
5. If you built against MariaDB Connector/C and use MYSQL_OPT_SSL_MODE, prefer VERIFY_IDENTITY with a CA over REQUIRED.
6. Before reconciling a model that gains a @ForeignKey, make sure existing rows satisfy it.
7. If you run outside UTC and corrected Perfect-CRUD dates by hand, remove the correction; if you store local-time values written by the server, consider SET time_zone = '+00:00' on the connection.

Known limitations: dates are written with whole seconds (fractions are read, not written). Only the first result set of a multi-result CALL is returned through Perfect-CRUD, and untyped db.sql("CALL …") can't report an error raised after it (it's logged). Perfect-CRUD's select() stops at a row that fails to decode, silently dropping it and every later row, instead of throwing. Running the test suite against a MariaDB server fails two DirectStatementTests that assume MySQL behaviour (MariaDB can prepare XA RECOVER, and types SELECT 2 + 2 differently).

Legacy

The pre-Swift-6 code is preserved on the legacy branch, which points at the merge of #52, one whitespace-only commit before the 3.5.1 tag. Stay on 3.5.1 if you can't move to Swift 6.2 yet.