MariaDB
Access configuration details to connect Soda to a MariaDB data source.
MariaDB support is currently available in private preview for Soda's design partners.
Soda verifies contracts against MariaDB, and can host a Diagnostics Warehouse 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:
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
Connecting to MariaDB requires having installed the soda-mariadb 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-mariadb" -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 MariaDB server.
user
Yes
—
MariaDB user.
password
Yes
—
MariaDB password.
port
No
3306
MariaDB port.
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.
TLS
ssl_mode follows MySQL's --ssl-mode vocabulary, which MariaDB shares. 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 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:
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:
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.
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.
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 freshnessMAX()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 aCOPY-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_ROWSis an estimate rather than a count, so Soda does not use it: total row count is computed withcount(*)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.
Troubleshoot
Problem: A connection fails with MariaDB is not supported or a message naming the mariadb type against a MySQL server.
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.
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 error 1038, a sort-buffer overflow.
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.
Last updated
Was this helpful?