> 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/mariadb.md).

# MariaDB

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

{% hint style="success" %}
MariaDB 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 MariaDB, and can host a [Diagnostics Warehouse](broken://pages/UZCAHd8ZBgl01336NkZa) on it.

MariaDB and MySQL are separate data source types in Soda. Use `type: mariadb` for a MariaDB server and `type: mysql` for a MySQL one — each adapter refuses the other's server at connect, because the SQL they emit genuinely differs.

### Requirements

**MariaDB 10.4 or later.** Soda refuses to connect to an earlier server. Every term in that floor is something the emitted SQL contains:

| Requirement                 | Available since | Used for                                     |
| --------------------------- | --------------- | -------------------------------------------- |
| `utf8mb4_nopad_bin`         | 10.2            | Regex checks and reconciliation key ordering |
| Common table expressions    | 10.2            | Every failed-rows fetch                      |
| `VALUES` table constructor  | 10.3            | Between-source failed-rows extraction        |
| `CAST(x AS FLOAT / DOUBLE)` | 10.4            | Profiling and group-by                       |

MariaDB 10.x and 11.x can announce themselves to an older client as `5.5.5-10.6.4-MariaDB`, where the `5.5.5-` prefix exists so that a pre-10.0 client does not read the major version as 1. Soda strips that prefix before reading the version. When it cannot read a version from the banner at all, it logs a warning and proceeds.

### Connection configuration reference

{% hint style="warning" %}
Connecting to MariaDB requires having installed the `soda-mariadb` 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-mariadb" -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: mariadb
name: my_mariadb
connection:
  host: mariadb.example.com
  port: 3306                       # optional, defaults to 3306
  user: soda
  password: ${env.MARIADB_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 MariaDB server.                            |
| `user`     | Yes      | —          | MariaDB user.                                                            |
| `password` | Yes      | —          | MariaDB password.                                                        |
| `port`     | No       | `3306`     | MariaDB port.                                                            |
| `database` | No       | —          | Default database. See [Databases and datasets](#databases-and-datasets). |
| `ssl_mode` | No       | `REQUIRED` | TLS enforcement level. See [TLS](#tls).                                  |

Instead of `password`, a connection may carry `password_file` with the path to a file holding the password, or `connection_string` with a complete connection URL.

<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. The server'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. The server'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. |

</details>

#### TLS

`ssl_mode` follows MySQL's `--ssl-mode` vocabulary, which MariaDB shares. 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.

#### 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` is set to a strict combination so that an out-of-range value fails instead of being clamped, `charset` is `utf8mb4`, `time_zone` is pinned to `+00:00` so a `TIMESTAMP` column reads as the instant it stores, and `autocommit` is on so that no connection holds a read view open. Soda reads `sql_mode` and `time_zone` back after setting them, because a proxy can accept the statement without applying it.

#### Connection test

Test the data source connection:

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

***

### Databases and datasets

MariaDB has no catalog tier, so a MariaDB `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.

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

```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" %}
MariaDB 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. On Linux the default is `0`, and names are stored and compared as given; where the default is `1`, names are stored folded and Soda folds its catalog lookups to match.

**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. MariaDB's default collations ignore case, so `'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. 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-sensitively, because a pattern is authored to match literally.
* **Reconciliation key ordering.** A merge join needs both streams in one order, so Soda orders MariaDB's text keys under `utf8mb4_nopad_bin`.

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 on MariaDB, because its collations are PAD SPACE unless the name carries `_nopad_`. Give such columns a `_nopad_` collation, or verify multi-column keys over trailing-space data at your own risk.
* **Zero dates read as NULL.** `'0000-00-00'` and impossible dates such as `'2007-02-31'` 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.
* **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 driver materializes a whole result set before returning the first row. The failed-rows and reconciliation paths that exist to bound memory therefore do not bound it on MariaDB. Size your deployment for the largest failed-rows set a contract can produce.
* **Row sampling costs a full scan and a sort.** MariaDB 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. 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 MariaDB peaks at roughly two to three times the memory of a `COPY`-based data source.

#### Metric Monitoring

* **Quartile metrics are unavailable.** MariaDB 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 MariaDB.** `information_schema.TABLES.TABLE_ROWS` is an estimate rather than a count, so Soda does not use it: total row count is computed with `count(*)` and last modification time is unavailable. MariaDB 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#mariadb).

***

### Troubleshoot

<i class="fa-square-xmark">:square-xmark:</i> **Problem:** A connection fails with `MariaDB is not supported` or a message naming the `mariadb` type against a MySQL server.

<i class="fa-square-check">:square-check:</i> **Solution:** Check the `type` in the data source YAML. Each adapter refuses the other's server at connect: use `type: mariadb` for MariaDB and `type: mysql` for MySQL.

<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 **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 the server's 256 KB default, which is not always enough when a row identity sorts wide or long-text keys.

<br>

***

{% 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).
{% 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 by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

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

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

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.