> For the complete documentation index, see [llms.txt](https://docs.soda.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.soda.io/reference/data-source-reference-for-soda-core/mysql.md).

# MySQL

Access configuration details to connect Soda to a MySQL data source.

{% hint style="success" %}
MySQL support is currently available in [private preview](/reference/documentation-access-and-licensing/release-stages.md#private-preview) for Soda's design partners.
{% endhint %}

Soda verifies contracts against MySQL, and can host a [Diagnostics Warehouse](broken://pages/UZCAHd8ZBgl01336NkZa) on it.

### Requirements

* **MySQL 8.0.19 or later.** Soda refuses to connect to an earlier server. MySQL 8.0 introduced `REGEXP_LIKE` and the `utf8mb4_0900` collations, and the SQL Soda emits additionally needs `VALUES ROW(...)` (8.0.19) and `CAST(x AS FLOAT)` (8.0.17).
* **MariaDB is not supported.** Soda refuses the connection when the server identifies itself as MariaDB. MariaDB provides neither `REGEXP_LIKE` nor the `utf8mb4_0900` collations, so regex checks and reconciliation over a text key cannot work.

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.

{% hint style="warning" %}
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.
{% endhint %}

### Connection configuration reference

{% hint style="warning" %}
Connecting to MySQL requires having installed the `soda-mysql` package using the [Private PyPi installation flow](/deployment-options/soda-python-libraries.md#private-pypi-installation-flow) available with an Enterprise license, unless you are using a Runner.

Need access to the private PyPI? Please [contact us](mailto:support@soda.io).
{% endhint %}

Install the following package:

{% code overflow="wrap" %}

```bash
pip install "soda-mysql" -i "https://${SODA_CLOUD_API_KEY_ID}:${SODA_CLOUD_API_KEY_SECRET}@enterprise.pypi.soda.io/simple"
```

{% endcode %}

* `${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.io`\
  For EU host users, use `@enterprise.pypi.soda.io`

{% hint style="info" %}
Learn how to [generate API keys](/reference/soda-apis/generate-api-keys.md).
{% endhint %}

#### Data source YAML

**Create the config file:**

```shellscript
soda data-source create -f ds_config.yml
```

The data source configuration YAML should look like the following:

{% code title="ds\_config.yml" %}

```yaml
type: mysql
name: my_mysql
connection:
  host: mysql.example.com
  port: 3306                       # optional, defaults to 3306
  user: soda
  password: ${env.MYSQL_PASSWORD}
  database: analytics              # optional, see Databases and datasets
  ssl_mode: REQUIRED               # optional, defaults to REQUIRED
```

{% endcode %}

#### Connection parameters

| Parameter  | Required | Default    | Description                                                              |
| ---------- | -------- | ---------- | ------------------------------------------------------------------------ |
| `host`     | Yes      | —          | Hostname or IP address of the MySQL server.                              |
| `user`     | Yes      | —          | MySQL user.                                                              |
| `password` | Yes      | —          | MySQL password.                                                          |
| `port`     | No       | `3306`     | MySQL port.                                                              |
| `database` | No       | —          | Default database. See [Databases and datasets](#databases-and-datasets). |
| `ssl_mode` | No       | `REQUIRED` | TLS enforcement level. See [TLS](#tls).                                  |

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.

<details>

<summary>Timeout and buffer tuning</summary>

| Parameter           | Default  | Description                                                                                                                                                                                                                                                                                                                  |
| ------------------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `connect_timeout`   | unset    | Seconds to wait for the handshake. It does not cover a socket that dies mid-query, which is what `read_timeout` covers.                                                                                                                                                                                                      |
| `read_timeout`      | `600`    | Seconds to wait for data from the server. A scan that fails with **error 2013**, `Lost connection to MySQL server during query`, is usually this. Set to `null` to block indefinitely.                                                                                                                                       |
| `write_timeout`     | `600`    | Seconds to wait while sending to the server. Set to `null` to block indefinitely.                                                                                                                                                                                                                                            |
| `lock_wait_timeout` | `300`    | Seconds to wait for a metadata lock. MySQL's own default is a year, which turns a lock held elsewhere into a scan that hangs silently.                                                                                                                                                                                       |
| `sort_buffer_size`  | 4 MiB    | Per-connection sort buffer. MySQL's 256 KB default overflows with error 1038 when row identity sorts wide or long-text keys.                                                                                                                                                                                                 |
| `time_zone`         | `+00:00` | Session time zone, as a `[+-]HH:MM` offset or a named zone from the server's timezone tables. Override it only if your own SQL depends on a local `NOW()`, and expect `TIMESTAMP` columns to be read in that zone. `SYSTEM` is refused: it resolves to the database host's zone, which is the value the pin exists to avoid. |

</details>

#### TLS

`ssl_mode` follows MySQL's own `--ssl-mode` vocabulary. Soda encrypts the connection by default.

| Mode        | Effect                                                                                                            |
| ----------- | ----------------------------------------------------------------------------------------------------------------- |
| `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.

{% hint style="info" %}
`DISABLED` or `PREFERRED` against a server that declines TLS needs the `cryptography` package installed alongside `soda-mysql`. MySQL's `caching_sha2_password` plugin encrypts the password against the server's public key only when the connection itself is unencrypted, and `soda-mysql` depends on `pymysql` without the `[rsa]` extra.
{% endhint %}

#### 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.

| Setting                                  | Value                                                                                                          | Why                                                                                                                                                                                                                |
| ---------------------------------------- | -------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `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:

```bash
soda data-source test -ds ds_config.yml
```

***

### 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:

| Configuration                                              | Effect                                                                                   |
| ---------------------------------------------------------- | ---------------------------------------------------------------------------------------- |
| 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.

```sql
GRANT SELECT ON analytics.* TO 'soda'@'%';

GRANT CREATE, DROP, ALTER, INSERT, UPDATE, DELETE, CREATE VIEW, SHOW VIEW
  ON soda_diagnostics.* TO 'soda'@'%';
```

{% hint style="warning" %}
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.
{% endhint %}

### 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.

| Mode | Where it is the default             | Behavior                                                                                                                                                                                                                 |
| ---- | ----------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `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 `latin1` column holding `café`.
* **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 the `utf8mb4_*_0900_*` collations plus `binary`. `JSON` columns are unaffected. Give such columns a `utf8mb4_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 child `be` against a parent `BE`. Regex checks are the exception. See [Case sensitivity](#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 freshness `MAX()` over such a column yields nothing. Those rows are also invisible to a verdict: `IS NULL` is false on the server, and comparing the column to `'0000-00-00'` is rejected outright with error 1525 under the session's pinned `sql_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 `cursorclass` is 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 a `COPY`-based data source. Soda creates no materialized views on MySQL.

#### Diagnostics Warehouse data types

* **Over-wide text columns are widened, not clamped.** `utf8mb4` charges four bytes per character against MySQL's 65,535-byte per-column ceiling, so `VARCHAR` holds at most 16,383 characters and `CHAR` at most 255. A wider source column becomes `LONGTEXT`, and Soda logs the substitution. The whole-row limit is separate and cumulative: Soda re-types the widest non-key string columns to `TEXT` until the row fits. Primary-key columns are exempt, because a `TEXT` key 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.
* **`UNSIGNED` integers keep their signed width.** `information_schema` reports `INT UNSIGNED` as `int`, 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_ROWS` is an estimate for InnoDB tables rather than a count, so Soda does not use it: total row count is computed with `count(*)` 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](/data-observability/metadata-data-sources.md#mysql).

#### Reconciliation

* **Cross-data-source `reference_diff` can mis-order text keys.** `rows_diff` is covered, because Soda folds MySQL's own key ordering to match the peer, but that normalization is wired into `rows_diff` only. This is not MySQL-specific: `reference_diff` builds its streams with `SELECT DISTINCT`, which is why the same normalization cannot simply be applied there.

***

### Troubleshoot

<i class="fa-square-xmark">:square-xmark:</i> **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.

<i class="fa-square-check">:square-check:</i> **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](#server-configuration).

<br>

<i class="fa-square-xmark">:square-xmark:</i> **Problem:** A scan fails with `error 2013: Lost connection to MySQL server during query` on a query that ran for a long time.

<i class="fa-square-check">:square-check:</i> **Solution:** Raise `read_timeout` in the connection configuration, or set it to `null` to block indefinitely. Its default is 600 seconds.

<br>

<i class="fa-square-xmark">:square-xmark:</i> **Problem:** A scan fails with MySQL **error 1038**, a sort-buffer overflow.

<i class="fa-square-check">:square-check:</i> **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.

***

{% hint style="info" %}
You are **not logged in to Soda** and are viewing the default public documentation. Learn more about [Licensing & documentation access](/reference/documentation-access-and-licensing.md).

If you do have a Soda license, make sure to **log in to Soda Cloud in this same browser**.
{% endhint %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.soda.io/reference/data-source-reference-for-soda-core/mysql.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.