Aster Web Data / Connect your data

MariaDB

How Aster Web Data uses MariaDB, and how MariaDB's types, collations and results shape what clients receive.

If your data is in MariaDB 10.5 or later, Aster Web Data serves it from your existing InnoDB tables. Filters, sorts, pages, counts and most $expand requests run in MariaDB as one statement, under the column's own collation, so your indexes serve them. Where MariaDB's answer differs from OData's, MariaDB's stands; Departures from OData lists those places.

The choice with the most effect is the collation of your text columns. A _ci collation makes name eq 'smith' match 'Smith', and a PAD SPACE collation ignores trailing spaces, because the comparison is MariaDB's own. The other choices are how the connection verifies TLS and which indexes you add for descending sorts and navigations.

Connecting

Declare the database once under databases:, and name it from each data source stored on it:

databases:
  main:
    kind: mariadb
    url_env: MAIN_DB_URL        # mysql://user:password@host:3306/shop
    tls: verify-full
    ca_file: ca.pem             # optional: otherwise the Mozilla roots

The URL comes from an environment variable (url_env) or a file (url_file), or you give its parts: host, port, user, password_env or password_file, and database. It is read again on every attempt to connect. Secrets explains the rules, and the configuration reference lists every key with its default, including the pool and the retry schedule.

These keys behave in a way particular to MariaDB:

• schema names a MariaDB database, since that is what a schema is in MariaDB. It defaults to the connection's database. If neither is given, the configuration is refused, so nothing depends on the session's default. Every statement names its database, and one entry with a schema: on each data source reads several databases of the server in one statement.
• tls takes the five modes in TLS. Under the default prefer, MariaDB 11.4 and later offer TLS with a certificate of their own, so the connection is encrypted but not verified. A require_ssl, verify_ca, verify_identity or built_in_roots parameter in the URL is refused, because tls sets the same thing.
• setup: recreate creates a missing database with the utf8mb4 character set and creates tables with InnoDB.
• read_only: true also runs SET SESSION TRANSACTION READ ONLY on every session. Read-only databases and privileges gives the rest, and the grants the database user needs.
• options.single_statement is true by default, which reads an $expand in one statement where the table qualifies. false reads every $expand level with a statement of its own. The answers are the same.

Two databases: entries are two connection pools, even on one server, and a request that reads from both is combined in the service, as Reads across databases describes.

The service checks the server version when it opens a connection. It refuses a server older than 10.5, the first with INSERT ... RETURNING, and refuses MySQL, naming the version it found. The tests run on MariaDB 12.3.

Start-up check

When the service opens the database, it checks each data source's table against its configuration. Names are matched without regard to case, as MariaDB does on file systems that ignore case. The check refuses a missing table or column, a column type that does not hold the field's values exactly (the accepted types are listed under Types), and a column whose precision, scale, fractional digits, collation or default differ from the configuration. The message says what to declare.

A failed check makes only that database's sets unavailable, and the check runs again on the retry schedule, so a fix takes effect without a restart. A database goes down describes what clients see meanwhile. To run the check on its own, use the check command.

Types

When setup: recreate creates a table, each field gets the column type below. An existing table must use one of the column types the start-up check accepts, listed after the table.

A 28-digit decimal here is one whose digits fit a 96-bit mantissa: 28 digits always fit, and 29 only up to 79,228,162,514,264,337,593,543,950,335. A non-zero digit past the 28th decimal place also fails.

The start-up check accepts these column types, and refuses any other:

• Text: VARCHAR or a TEXT type. CHAR is refused, because it strips trailing spaces.
• Integers: any integer type. A narrower column refuses values it cannot hold.
• Edm.Single: FLOAT or DOUBLE. Edm.Double: DOUBLE.
• Decimals: DECIMAL. 128-bit unsigned: DECIMAL(39,0) or wider.
• Dates: DATE. Times: TIME. Timestamps: DATETIME or TIMESTAMP.
• GUIDs: CHAR or VARCHAR of at least 36 characters, or UUID. Binary: VARBINARY or a BLOB type. BINARY is refused, because it pads.

Every session uses utf8mb4 and the UTC time zone, with a SQL mode that keeps backslashes as escapes and adds SIMULTANEOUS_ASSIGNMENT, so each assignment in an UPDATE sees the row as it was before the update.

MariaDB computes with an UNSIGNED operand as BIGINT UNSIGNED, so 3 - 5 over INT UNSIGNED columns would raise an error. The service writes CAST(x AS SIGNED) around a 64-bit field stored in a narrower or UNSIGNED column, so the arithmetic is done at OData's width.

Collation and case

A comparison, a sort and like use the column's own collation. name eq 'x' is name = ? and $orderby=name is ORDER BY name, so an ordinary index on the column serves them. $metadata publishes each text field's collation as AsterData.Collation: the field's own, else the database's collation: key, else the column's. The start-up check refuses a column whose collation differs from the one declared. The server's default for utf8mb4 is utf8mb4_uca1400_ai_ci from MariaDB 11.6, and utf8mb4_general_ci before.

• _ci collations compare case-insensitively: name eq 'smith' matches 'Smith'.
• _ai collations compare accent-insensitively: 'e' matches 'é'.
• PAD SPACE collations, which are all those without nopad in the name (the uca1400 ones included), ignore trailing spaces in = and in ordering, so 'a' = 'a ' is true. like never pads.
• utf8mb4_bin and utf8mb4_nopad_bin compare by code point, the second without padding.
• Case-insensitive search. AsterData.icontains, istartswith, iendswith and ilike are a plain LIKE under a _ci collation, and LOWER(x) LIKE LOWER(?) under any other.
• like matches character by character, with the collation's case and accent rules. 'ß' LIKE 'ss' does not match under a primary-strength collation, where it does on PostgreSQL 18. The needle of contains, startswith and endswith has %, _ and \ escaped.
• Case mapping. tolower and toupper are LOWER and UPPER under the argument's collation. In a probe of 34 inputs on MariaDB 12.3.2, İ lowercased to i, a word-final Σ lowercased to σ, and ß was not uppercased to SS. Letters added to Unicode recently are mapped by some collations and not by others.

Where each operation runs

Each OData operator becomes the MariaDB operator with the same meaning, so MariaDB's comparison, arithmetic, rounding and errors give the answer. This request reads a customer with their two largest invoices and their invoice count:

/* aster read customers */ SELECT c.`id`, c.`name`, c.`email`, c.`region`, (SELECT JSON_ARRAYAGG(JSON_ARRAY(i.`issued`, CAST(i.`amount` AS CHAR), i.`id`) ORDER BY i.`amount` DESC, i.`id` LIMIT ?, ?) FROM `test_aster_docs_capture`.`invoices` AS i WHERE i.`customerId` = c.`id`) AS `invoices`, (SELECT COUNT(*) FROM `test_aster_docs_capture`.`invoices` AS i2 WHERE i2.`customerId` = c.`id`) AS c6 FROM `test_aster_docs_capture`.`customers` AS c WHERE c.`id` = ?
-- binds: 0, 2, 3

The invoices come from a sub-query per customer that builds them as a JSON array, with the nested sort and $top as its ORDER BY and LIMIT. MariaDB reads an $expand this way where the navigation's foreign key leads an index and the table has at most 48 columns. Otherwise each level is read by a second statement, by the distinct keys of the parent rows in IN (...) lists padded to a power of two, with the same answer.

A decimal or a double inside that JSON is written as text with CAST(x AS CHAR), because a JSON number would round it through a double. When the service connects, it asks whether the server writes doubles in their shortest round-trip text; where it does not, a table with a double column is always read by key batches. Sessions set group_concat_max_len to its maximum, so the only bound on one aggregated value is max_allowed_packet. A statement that reaches it fails with 400 QueryTooExpensive instead of returning a cut value.

These run in MariaDB:

• Comparisons on every type, and ordering on every type except binary (equality only) and JSON (no ordering). ne against a value on a nullable column is x <> ? OR x IS NULL, and two nullable columns compare with <=>, so the answers are OData's two-valued ones.
• in lists, like, case, exists and uncorrelated sub-queries. MariaDB does not allow outer references inside derived tables, so a navigation such as /Categories(1)/Products/$count, or any or all over a navigation, runs as an uncorrelated query on the foreign key.
• contains, startswith, endswith, indexof, length, concat, substring, trim, tolower and toupper, on tables without a collection column. length counts a character outside the Basic Multilingual Plane as one.
• year, month and day of a date.
• 64-bit integer arithmetic (integer division is DIV, because / returns a fraction), decimals on DECIMAL, doubles on DOUBLE, divby, round, ceiling and floor.
• Regular expressions in the pcre syntax, case-sensitive, for a literal pattern that compiles.
• Sorting, offset and keyset paging, exact counts, grouping, distinct, aggregates, windows, unions, and inner, left, full, semi and anti joins.
• Inserts, updates, deletes and upserts, writes selected by criteria, preconditions on expected values, and atomic increments. MariaDB has no UPDATE ... RETURNING, so an update that returns its rows reads them back.

These run in the service, within the limits on rows it may hold: 128-bit arithmetic, date, time and duration arithmetic, other regular expressions, hour, minute and second, casts from string, other lambda expressions over collections, scalar sub-queries, and full-text search.

Errors

Nulls and ordering

OData puts nulls first in ascending order and last in descending order. MariaDB has no NULLS FIRST clause, so the service writes an IS NULL key ahead of each sort key. That key is left out for a sort key that reads no row or is a bare literal, because MariaDB reads an integer constant in ORDER BY as a column position.

There is no NaN to place, and -0.0 equals 0.0.

A next page is a keyset query with the same collation, null placement and direction as the sort. $count=true is exact and agrees with the page, as A page with its total shows.

Departures from OData

Where OData 4.01 defines a result and MariaDB gives another, MariaDB's result stands. These are the places:

• Division by zero returns null for every numeric type and for divby. OData makes an integer div or mod by zero an error, and gives INF, -INF or NaN for div on doubles and for divby.
• String comparison follows the column's collation: case- and accent-insensitive under _ci and _ai, with trailing spaces ignored under PAD SPACE. OData compares strings exactly.
• round of a double depends on the C library, commonly half to even. OData rounds half away from zero.
• trim removes spaces only. OData's trim removes whitespace.
• tolower and toupper follow the collation's tables, not the full Unicode mappings.
• substring with a negative start counts from the end of the string, so substring('abc', -2) is 'c'.

Writes and transactions

• Isolation. Reads are repeatable read by default; read committed and serializable are available. A read outside an atomic group is a single statement with no transaction of its own, except a Traverse, whose guard and statement run in one repeatable-read transaction. InnoDB's serializable takes shared locks on plain reads, so conflicting groups block each other, and one of them can be chosen as a deadlock victim.

• Atomic groups. A $batch atomicity group runs in one transaction on one connection. A group that checks a rule runs at serializable isolation, with up to four attempts (10, 20 and 30 ms apart) within the request deadline. Deadlocks and record-changed errors are retried; a failed check is not.

• Member by member. MariaDB has no one-statement form for a group's inserts or for a nested insert, so their members run one at a time inside the transaction.

• Row locks. An update or delete locks the rows whose precondition it checked.

• ETags. MariaDB has no row version of its own, so etag: native is refused and a set has no ETag until it declares a token. The usual token is a version column a trigger advances, named in etag: {columns: [version]}, or versionField: version to have Aster advance it:

  ALTER TABLE orders ADD COLUMN version BIGINT NOT NULL DEFAULT 1;
  CREATE TRIGGER orders_version BEFORE UPDATE ON orders FOR EACH ROW SET NEW.version = OLD.version + 1;
  

  A TIMESTAMP(6) ... ON UPDATE CURRENT_TIMESTAMP(6) column works too, but two updates in one microsecond share a value. ETags and concurrent edits explains the tokens.

• Timeouts. limits.statement_timeout_ms is applied as SET STATEMENT max_statement_time to each write and to each read that begins with SELECT. A Traverse is also bounded by a depth of 64 and by the server's max_recursive_iterations.

A connection that fails in a way that shows it is unusable is discarded, not returned to the pool.

Indexes and sort order

Indexes gives the indexes that serve a sorted page and a navigation. The reason a descending sort needs its own index is particular to InnoDB. Paging adds the entity key to every sort, so $orderby=T desc sorts by T DESC, ID. A secondary index on T stores the primary key after T, which serves T, ID and T DESC, ID DESC but not the mixed T DESC, ID, so that order is a sort of every matching row. Index (T DESC, ID) for a descending sort; descending indexes need MariaDB 10.8 or later.

Scaffold

The scaffold command reads information_schema and writes a starting data-source configuration for the columns it can map exactly, leaving the others as comments with the reason. --schema names MariaDB databases on the one connection, so a foreign key to a table in another selected database becomes a navigation. The account needs a privilege on each table, because information_schema shows a table only to an account with one. It runs no DDL and no write. Connect your own database walks through it, and Model your data covers what to add once it has run.