MySQL
Access configuration details to connect Soda to a MySQL data source.
MySQL support is currently available in private preview for Soda's design partners.
Soda verifies contracts against MySQL, and can host a Diagnostics Warehouse on it.
Requirements
MySQL 8.0.19 or later. Soda refuses to connect to an earlier server. MySQL 8.0 introduced
REGEXP_LIKEand theutf8mb4_0900collations, and the SQL Soda emits additionally needsVALUES ROW(...)(8.0.19) andCAST(x AS FLOAT)(8.0.17).A MariaDB server needs the MariaDB data source type. Soda refuses the connection when the server identifies itself as MariaDB, because the two emit genuinely different SQL. Use MariaDB with
type: mariadbinstead.
Aurora MySQL 3.x reports its version as 8.0.mysql_aurora.3.04.0, whose third component is Aurora's own version rather than MySQL's. Soda cannot fully parse that banner, so it logs a warning and proceeds.
Server configuration
To host a Diagnostics Warehouse on MySQL, max_allowed_packet must be at least as large as the largest single diagnostics row Soda writes, which is often above MySQL's own 64 MB default. Only the server can raise it: MySQL rejects SET SESSION max_allowed_packet with error 1621. Soda reads whatever the server reports and sizes its statements against that, dividing by four because utf8mb4 encodes up to four bytes per character.
MySQL answers an oversized packet by closing the connection rather than rejecting the statement. An undersized max_allowed_packet therefore surfaces mid-scan as a broken pipe that names neither the limit nor the row that exceeded it.
Connection configuration reference
Connecting to MySQL requires having installed the soda-mysql package using the Private PyPi installation flow available with an Enterprise license, unless you are using a Runner.
Need access to the private PyPI? Please contact us.
Install the following package:
pip install "soda-mysql" -i "https://${SODA_CLOUD_API_KEY_ID}:${SODA_CLOUD_API_KEY_SECRET}@enterprise.pypi.soda.io/simple"${SODA_CLOUD_API_KEY_ID}and${SODA_CLOUD_API_KEY_SECRET}are environment variables that you should store with your API Key ID and API Key Secret, respectively.For US host users, use
@enterprise.pypi.us.soda.ioFor EU host users, use@enterprise.pypi.soda.io
Data source YAML
Create the config file:
The data source configuration YAML should look like the following:
Connection parameters
host
Yes
—
Hostname or IP address of the MySQL server.
user
Yes
—
MySQL user.
password
Yes
—
MySQL password.
port
No
3306
MySQL port.
Soda declares host as required rather than defaulting it to localhost as Soda v3 did, so a data source meant to be remote fails at connect instead of sending its credentials to whatever runs the scan.
Any parameter not listed here is passed through to the driver, except sql_mode, charset, autocommit, conv, init_command, cursorclass, collation, local_infile and client_flag. Each of those carries a decision the rest of the connector depends on, so Soda reserves them and refuses a configuration that sets them.
TLS
ssl_mode follows MySQL's own --ssl-mode vocabulary. Soda encrypts the connection by default.
REQUIRED
The default. The connection must be encrypted, and the handshake fails if the server declines TLS.
PREFERRED
Encrypts the connection when the server offers TLS, and falls back to an unencrypted connection when it does not.
DISABLED
No encryption.
None of these modes verifies the server's certificate: ssl_mode controls whether the connection is encrypted, not whether the server is authenticated.
Session settings Soda pins
Soda puts every connection into the same state, so a verification behaves the same whatever the server's own configuration is.
sql_mode
STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION
Without strictness, MySQL clamps an out-of-range value and truncates an over-long one, emitting a warning the driver never surfaces. Diagnostics would record silently wrong values. Not configurable.
ANSI_QUOTES and NO_BACKSLASH_ESCAPES
Off
With ANSI_QUOTES on, a double-quoted string literal in a check filter becomes a column reference. Soda's escaping doubles backslashes to match NO_BACKSLASH_ESCAPES being off.
charset
utf8mb4
time_zone
+00:00 (configurable)
TIMESTAMP stores UTC and renders in the session zone, and MySQL's default session zone is the server host's. Unpinned, every TIMESTAMP reads shifted by that offset and freshness is wrong by the same amount.
autocommit
On
So that no connection holds a read view open or blocks a DDL teardown.
Soda reads sql_mode and time_zone back after setting them, because a proxy can accept the statement without applying it.
The storage engine is the server's, not Soda's. Soda emits no ENGINE clause, so tables it creates inherit default_storage_engine. Soda warns once at connect about a non-transactional default rather than overriding it, because naming InnoDB on a server where it is absent would turn a working connection into error 1286.
Connection test
Test the data source connection:
Databases and datasets
MySQL has no catalog tier, so a MySQL database is what every other data source in Soda calls a schema: the single namespace level between the server and a table. A dataset is addressed as <data source>/<database>/<dataset> — there is no third segment. There is no schema connection property.
Soda Cloud emits a four-segment name, <data source>/<database>/<schema>/<dataset>, for any data source whose connection carries a database. Soda collapses the extra segment rather than rejecting it, keeping the one nearest the dataset and logging MySQL addresses one namespace level: dropping 1 leading segment(s).
One connection can read every database the account has rights on, which makes database narrower here than elsewhere:
A dataset that names a database, such as ds/sales/orders
Queried in that database, whatever database says.
A dataset that names none, such as ds/orders
Resolved against database. This is what the property is for.
Dataset discovery
Scoped to database. Omit database to discover every database the connection can see.
Soda always excludes the four system schemas: information_schema, mysql, performance_schema and sys.
Required privileges
To verify contracts, the Soda user needs SELECT on the datasets under check.
A Diagnostics Warehouse hosted on MySQL additionally needs CREATE, DROP, ALTER, INSERT, UPDATE, DELETE, CREATE VIEW and SHOW VIEW on its own database. ALTER is on the list because schema evolution adds columns. Soda needs no SUPER, FILE, PROCESS, RELOAD or SHUTDOWN privilege.
MySQL filters information_schema to what the account can see, so a missing privilege and a missing dataset look the same: the scan reports the dataset as not found. Soda runs no pre-flight privilege check, so a Diagnostics Warehouse with insufficient grants fails at its first write.
Case sensitivity
Table names follow the server's lower_case_table_names, which is fixed when the data directory is initialized and cannot be changed afterwards.
0
Linux
Names are stored and compared as given.
1
Windows, and most managed offerings
Names are stored folded, and Soda folds its catalog lookups to match.
2
macOS
Partially supported. Soda does not fold its catalog lookups, so a contract whose table name differs in case from the DDL resolves in SQL but not in the catalog, and Soda skips creating the diagnostics view. Untested.
Column names are compared case-insensitively whatever that variable says, and keep the case they were written with. A contract naming createdat against a column declared CreatedAt resolves in SQL and every data check on it evaluates correctly — but the schema check compares the two names exactly and reports the column as both missing and unexpected. Write contract column names with the case the DDL uses.
Values follow the server's collation. MySQL's default, utf8mb4_0900_ai_ci, ignores case and accents, as did every MySQL default collation before it back to latin1_swedish_ci. A contract means what it means on the data source, so under that collation 'ABC' and 'abc' are one value: an equality matches both, a value list counts both, and a duplicate check calls them duplicates. That is what your own SQL returns and what a UNIQUE index enforces, and it is the position Soda takes on SQL Server's equally insensitive default. Soda wraps no comparison in a COLLATE, so predicates stay index-usable.
Two things are deliberately not left to the collation:
Regex predicates match case- and accent-sensitively, the same exception Soda makes on SQL Server, because a pattern is authored to match literally. Soda normalizes the operand's character set first, which is what keeps a pattern correct against a
latin1column holdingcafé.Reconciliation key ordering. A merge join needs both streams in one order, and
LOWER()on the peer data source cannot reproduce an accent-insensitive order, so Soda orders MySQL's text keys under a binary collation.
For a case-sensitive verdict on a particular column, give that column a _bin or _cs collation in its DDL, or reach it through a column_expression that names one.
Limitations & edge cases
Verdicts
A multi-column duplicate check can report PASS on a real duplicate, when the key has trailing spaces under a PAD SPACE collation. The server treats
'a'and'a 'as equal; the hash the check groups by does not. A single-column check takes its verdict from the server, so only its failed-rows sample is short, but a multi-column check takes its verdict from the hash. The scope is wide: most of MySQL's collations are PAD SPACE, and the NO PAD ones are theutf8mb4_*_0900_*collations plusbinary.JSONcolumns are unaffected. Give such columns autf8mb4_0900_*collation, or verify multi-column keys over trailing-space data at your own risk.Text verdicts ignore case and accents, following the server's collation, so the same contract can answer differently on MySQL than on PostgreSQL:
missing_values: ['N/A']also counts'n/a',valid_values: ['BE']accepts'be', and a reference check accepts a childbeagainst a parentBE. Regex checks are the exception. See Case sensitivity.Zero dates read as NULL.
'0000-00-00','2020-00-15'and impossible dates such as'2007-02-31'were legal before MySQL 5.7 and are common in migrated estates. Soda maps them to NULL, so the source shows a date where diagnostics show NULL, and a freshnessMAX()over such a column yields nothing. Those rows are also invisible to a verdict:IS NULLis false on the server, and comparing the column to'0000-00-00'is rejected outright with error 1525 under the session's pinnedsql_mode.A backtick inside an identifier is not escaped, so a dataset or column whose name contains one produces broken SQL.
Performance and scale
Reads are fully buffered. The MySQL driver materializes a whole result set before returning the first row, and Soda subclasses that cursor to carry the character set id that separates a binary column from a text one. The failed-rows and reconciliation paths that exist to bound memory therefore do not bound it on MySQL, and
cursorclassis reserved so there is no workaround. Size your deployment for the largest failed-rows set a contract can produce.Row sampling costs a full scan and a sort. MySQL has no
TABLESAMPLE, so Soda rewrites each source into(SELECT * FROM t ORDER BY RAND() LIMIT n). Sampling bounds the result size, not the scan, so enabling it makes verification slower rather than faster — the opposite of a block-sampling data source such as Snowflake. Use a dataset filter to read less. Absolute row limits only; a percentage is refused.Diagnostics writes use the generic multi-row
INSERT. There is no bulk-load path, so a Diagnostics Warehouse on MySQL peaks at roughly two to three times the memory of aCOPY-based data source. Soda creates no materialized views on MySQL.
Diagnostics Warehouse data types
Over-wide text columns are widened, not clamped.
utf8mb4charges four bytes per character against MySQL's 65,535-byte per-column ceiling, soVARCHARholds at most 16,383 characters andCHARat most 255. A wider source column becomesLONGTEXT, and Soda logs the substitution. The whole-row limit is separate and cumulative: Soda re-types the widest non-key string columns toTEXTuntil the row fits. Primary-key columns are exempt, because aTEXTkey needs an explicit prefix length, so a table whose key alone exceeds the budget is reported as an error rather than emitting DDL the server would reject.UNSIGNEDintegers keep their signed width.information_schemareportsINT UNSIGNEDasint, and Soda's canonical type set has no unsigned integer, so the diagnostics column is signed. A value above the signed maximum fails the insert rather than being stored wrongly.NaN and ±Infinity become NULL. MySQL can represent neither, and a cross-source transfer from PostgreSQL or Snowflake does deliver them, so Soda maps them with a warning rather than failing the batch. A non-finite value in a contract, such as
valid_min: .inf, is not substituted and reaches the server, which rejects it.A failed insert batch on a non-transactional storage engine leaves rows behind that the skipped-row count reports as lost. Verdicts are unaffected.
Metric Monitoring
Quartile metrics are unavailable. MySQL has neither a percentile function nor
WITHIN GROUP, so Soda skips the Q1, median and Q3 column monitors at creation rather than reporting a measurement for them.Soda reads no warehouse metadata from MySQL.
information_schema.TABLES.TABLE_ROWSis an estimate for InnoDB tables rather than a count, so Soda does not use it: total row count is computed withcount(*)and last modification time is unavailable. MySQL exposes no change log either, so a backfill has no metadata history to draw on and runs on data-based metrics only. See Metadata data sources.
Reconciliation
Cross-data-source
reference_diffcan mis-order text keys.rows_diffis covered, because Soda folds MySQL's own key ordering to match the peer, but that normalization is wired intorows_diffonly. This is not MySQL-specific:reference_diffbuilds its streams withSELECT DISTINCT, which is why the same normalization cannot simply be applied there.
Troubleshoot
Problem: A scan writing to a Diagnostics Warehouse on MySQL fails partway through with a broken pipe or a lost-connection error, naming no row and no limit.
Solution: Raise the server's max_allowed_packet above the largest single diagnostics row Soda writes. MySQL closes the connection on an oversized packet instead of rejecting the statement, which is why the error names neither. Soda cannot set this per connection; see Server configuration.
Problem: A scan fails with error 2013: Lost connection to MySQL server during query on a query that ran for a long time.
Solution: Raise read_timeout in the connection configuration, or set it to null to block indefinitely. Its default is 600 seconds.
Problem: A scan fails with MySQL error 1038, a sort-buffer overflow.
Solution: Raise sort_buffer_size in the connection configuration. Soda already raises it to 4 MiB from MySQL's 256 KB default, which is not always enough when a row identity sorts wide or long-text keys.
Last updated
Was this helpful?