/ 
DBIO-MySQL-0.900004
/ DBIO::MySQL::Storage

NAME

DBIO::MySQL::Storage - MySQL storage layer for DBIO

VERSION

version 0.900004

SYNOPSIS

package MyApp::Schema;
use base 'DBIO::Schema';
__PACKAGE__->load_components('MySQL');

# Optionally enable strict mode on connect
my $schema = MyApp::Schema->connect(
  $dsn, $user, $pass,
  { on_connect_call => 'set_strict_mode' },
);

DESCRIPTION

MySQL storage backend for DBIO. Extends DBIO::Storage::DBI with MySQL-specific behavior:

  • Uses backtick quoting and MySQL's LIMIT [offset,] rows syntax, rendered by "apply_limit" in DBIO::MySQL::SQLMaker.

  • Routes SQL generation through DBIO::MySQL::SQLMaker, which automatically wraps UPDATE and DELETE statements that reference the modification target in a subquery.

  • Disables the DBD's auto-reconnect (mysql_auto_reconnect or mariadb_auto_reconnect) on every connect by default to prevent silent transaction loss.

This class is auto-registered for the mysql DBI driver and is set as the active storage class when "connection" in DBIO::MySQL is called. That pin holds for every DSN, so it also runs over a dbi:MariaDB: DSN. Every DBD-private attribute is therefore named by the DBD that owns the handle, never by this class ("_dbd_attribute").

Connecting with utf8mb4: the attribute hash, not the DSN

Under DBD::mysql (measured with 4.055) mysql_enable_utf8mb4 works only as an attribute of the connect-info hash. Put in the DSN (dbi:mysql:database=app;mysql_enable_utf8mb4=1) it is ignored without a word, and the strings that come back are undecoded bytes:

$schema->connect(
  'dbi:mysql:database=app', $user, $pass,
  { mysql_enable_utf8mb4 => 1 },
);

DBD::MariaDB (measured with 1.24) has no such attribute: it refuses the connect with Unknown attribute mysql_enable_utf8mb4 (and likewise for mariadb_enable_utf8mb4), whether in the DSN or the hash. Leave it out over a dbi:MariaDB: DSN.

The base EXISTS rendering is right for both server products

This class declares the supports_exists_query capability (ADR 0037), which is what enables "results_exist" in DBIO::ResultSet, "results_exist_as_query" in DBIO::ResultSet and "results_exist_async" in DBIO::ResultSet. The flag is not a formality: core defaults it to false everywhere and treats it as an assertion that somebody checked the rendering against this engine, so what was checked is recorded here.

The statement is core's own "_exists_value_sql" in DBIO::Storage::DBI, deliberately not overridden:

SELECT CASE WHEN EXISTS (SELECT 42 FROM `artist` `me` WHERE ...) THEN 1 ELSE 0 END

It asks two things of the server, and both are documented for both products:

  • A SELECT with no FROM. The MySQL 8.0 Reference Manual, section 15.2.13 (SELECT Statement): "SELECT can also be used to retrieve rows computed without reference to any table", and "MySQL does not require FROM DUAL if no tables are referenced". The MariaDB SELECT reference says the same -- "SELECT can also be used to retrieve rows computed without reference to any table", with "If no table is involved, FROM DUAL can be specified" as the optional alternative. This is the clause that forces Oracle, DB2 and Firebird to override the hook; neither of these two needs to.

  • EXISTS in a condition position, as the WHEN of a CASE. That is strictly less than these engines already do: ADR 0037's portability table records MySQL and MariaDB as accepting EXISTS as a bare value expression (SELECT EXISTS (...)), which is the spelling core emitted before that ADR and the reason results_exist worked here all along. An engine that takes EXISTS as a value takes it as a predicate.

Neither half is a version fact and neither is a DBD fact, so the declaration belongs on this class -- shared by dbi:mysql: and dbi:MariaDB: connections and by both server products -- rather than on DBIO::MySQL::Storage::MariaDB, which is the DBD::MariaDB binding and nothing else.

Set operations: version-bounded on both products, and spelled the same on both

"union" in DBIO::ResultSet and its five siblings take their keyword from "_set_operation_keyword" in DBIO::Storage::DBI (ADR 0039). This class overrides that hook, but not to re-spell anything: both server products call all six operations by their ANSI names on every release that has them, so core's base map is right here and the override exists only to refuse.

What each product has, and since when

UNION and UNION ALL predate anything this driver could meet, on either product. The other four are younger, and the two products drew two different lines:

  • MySQL 8.0.31 -- all four at once. "EXCEPT was added in MySQL 8.0.31" (MySQL 8.0 Reference Manual, section 15.2.4, EXCEPT Clause) and "INTERSECT was added in MySQL 8.0.31" (same manual, section 15.2.8, INTERSECT Clause). Both pages document the modifier in the operator's own grammar -- query_expression_body EXCEPT [ALL | DISTINCT] query_expression_body -- and the 8.0.31 release notes say it outright: "In this release MySQL adds support for the SQL standard INTERSECT and EXCEPT table operators. ... INTERSECT and EXCEPT both support DISTINCT and ALL, with DISTINCT the default in both cases" (WL #349). So on MySQL there is one boundary, not two: 8.0.31 delivered INTERSECT, INTERSECT ALL, EXCEPT and EXCEPT ALL together.

  • MariaDB 10.3, then 10.5 -- two boundaries. "MariaDB has supported INTERSECT (as well as EXCEPT) in addition to UNION since MariaDB 10.3" (MariaDB INTERSECT reference; the 10.3.0 release notes list "INTERSECT and EXCEPT" under new syntax, MDEV-10141). The ALL forms came two releases later: the MariaDB 10.5.0 release notes list "EXCEPT ALL and INTERSECT ALL" under Syntax (MDEV-18844), and both reference pages carry the version banner "MariaDB starting with 10.5.0" over their ALL/DISTINCT section, whose pre-10.5 tab reads "Only EXCEPT DISTINCT is available" and "DISTINCT is the only behavior available".

A MariaDB between 10.3 and 10.4 inclusive therefore has EXCEPT and INTERSECT but not their ALL forms -- the one real deployment state that a single availability flag could not express, and the reason there are two below.

The refusal is declared, not probed -- and it defaults to available

The hook must answer as a static class fact: "_set_operation" in DBIO::ResultSet bakes the keyword into a literal from clause while the query is being built, so asking the server which product and version it is would open a connection at query-build time, purely in order to throw (ADR 0039 section 7). This driver cannot look, so a deployment that cares declares:

# this deployment's server predates EXCEPT / INTERSECT entirely
# (MySQL < 8.0.31, or MariaDB < 10.3)
DBIO::MySQL::Storage->_use_set_operator_intersect_except(0);

# ... or has them, but not their ALL forms (MariaDB 10.3 / 10.4)
DBIO::MySQL::Storage->_use_set_operator_all_family(0);

Both default to 1, i.e. all six operations available and no refusal at all. This is the opposite polarity from DBIO::Oracle::Storage, which declares its equivalent _use_set_operator_all_family as 0, and the difference is an engine fact rather than a taste: Oracle's long-term releases (11.2, 19c) are the ones without the ... ALL family, so a permissive default there would send supported servers SQL they reject. Here the reverse holds. Every non-EOL release of either product is past every boundary above -- MySQL 8.0 (extended support), 8.4 LTS and 9.x; MariaDB 10.6 LTS, 10.11 LTS, 11.4 LTS and 11.8 LTS -- so a default of 0 would refuse four working operations on essentially the whole installed base, which is exactly the regression ADR 0039 section 3 declined when it refused a capability gate defaulting to 0.

What the switches buy an old deployment is therefore not correctness but diagnosis: with the relevant one turned off, $rs->except_all($other) throws locally, before a single operand is rendered, with a message naming both products' boundaries -- instead of reaching the server as ERROR 1064 (42000): You have an error in your SQL syntax several layers below the method the caller called.

Reach for the class-level form shown above: it names the class, so it holds from load time and needs no storage to exist yet. The same switch can be set on one storage -- $schema->storage->_use_set_operator_all_family(0), for a process talking to more than one server -- on a schema that declares this driver, whose storage is this class from the connect on (core ADR 0067).

Nothing is re-spelled, and nothing is refused unconditionally

Both products call set difference EXCEPT on every release that has it, so there is no keyword for this driver to rename. (MariaDB additionally accepts MINUS as a synonym from 10.6.1 on, but only under sql_mode=ORACLE, which DBIO never sets, and EXCEPT keeps working in that mode anyway.) And no operation is missing from all versions of either product, so unlike DBIO::MSSQL::Storage or DBIO::SQLite::Storage this driver has nothing to refuse outright -- ADR 0039 section 7's option (a) would here withhold four operations from every server that has them.

Both declarations sit on this class rather than on DBIO::MySQL::Storage::MariaDB: which operations a server has is a fact about the server, and that subclass is the DBD::MariaDB binding and nothing else (this dist's ADR 0001). DBD::MariaDB reaches MySQL servers and dbi:mysql: reaches MariaDB ones, so a declaration made there would answer differently for the same server depending on which DSN found it.

Random ordering: RAND(), and it really is evaluated per row

"rand" in DBIO::ResultSet drops "_random_function" in DBIO::Storage::DBI into an order_by literal. This class answers RAND(), because core's inherited RANDOM() exists on neither product.

The spelling is the easy half. The half worth checking is whether the engine evaluates the function per row: one computed once per statement hands every row the same sort key, and ORDER BY over it is then not a shuffle at all and says nothing about it. That is not hypothetical -- it is what Sybase ASE's rand() does, which is why DBIO::Sybase::Storage::ASE answers NEWID() instead. Here both products document the per-row behaviour, and neither leaves it to inference:

  • MySQL. The RAND() entry (MySQL 8.0 Reference Manual, section 14.6.2, Mathematical Functions) shows SELECT i, RAND() FROM t over a three-row table returning three different values, states that "RAND() in a WHERE clause is evaluated for every row (when selecting from one table) or combination of rows (when selecting from a multiple-table join)" and that "thus, for optimizer purposes, RAND() is not a constant value", and then names this exact use: "If the goal is to retrieve rows in random order, you can use a statement like this: SELECT * FROM tbl_name ORDER BY RAND();", with "To select a random sample from a set of rows, combine ORDER BY RAND() with LIMIT" -- which is precisely the statement $rs->rand($n) builds.

  • MariaDB. The RAND() reference page carries the same worked example with three distinct values, and says what the server does with the ORDER BY form outright: SELECT * FROM my_table ORDER BY RAND() LIMIT 10 makes MariaDB "read all rows in the table, generate a random value for each of them, order them, and finally ... apply the LIMIT clause". A random value for each of them is the property being asserted here.

Two caveats that are documented rather than discovered, and neither of which changes the answer. MySQL's manual warns that "use of a column with RAND() values in an ORDER BY or GROUP BY clause may yield unexpected results because for either clause a RAND() expression can be evaluated multiple times for the same row" -- that is about selecting RAND() into the column list and then ordering by that alias, and it is the opposite failure from the one that matters: over-evaluation still shuffles, and the same manual recommends the direct ORDER BY RAND() form on the very next line. And RAND() "is not meant to be a perfect random generator"; $rs->rand promises an unpredictable order, not a cryptographic one.

The cost is the other thing both manuals agree on: this sorts the whole result set. MariaDB's page says the technique "should never be used on a large table as it will be extremely slow", and the reason it gives is the mechanism above. That is a property of the idiom, not of this driver.

The declaration sits on this class, shared by both DBD drivers and both server products, because it is true of both: RAND() is not a DBD::MariaDB fact (this dist's ADR 0001).

Query plans: core's plain EXPLAIN is the operation, unchanged

"explain" in DBIO::ResultSet asks this storage for a plan through core's "_explain_query" in DBIO::Storage::DBI seam (core ADR 0040), and this driver does not override it. What runs is core's base operation:

EXPLAIN <the resultset's SELECT>

through "_execute_rendered" in DBIO::Storage::DBI, with the plan collected as $sth->fetchall_arrayref. That is already the correct operation on both products, which is why the non-override is a decision and is recorded as one:

  • It is the plan statement on both. "When EXPLAIN is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan" (MySQL 8.0 Reference Manual, section 15.8.2, EXPLAIN Statement); "when you precede a SELECT, UPDATE or a DELETE statement with the keyword EXPLAIN, MariaDB displays information from the optimizer about the query execution plan" (MariaDB EXPLAIN reference).

  • It does not execute the query. The engines' executing variants are spelled differently and are not what this seam issues: EXPLAIN ANALYZE on MySQL (8.0.18+) and the ANALYZE statement on MariaDB, whose own page describes it as "similar to EXPLAIN, but it runs the query". Plain EXPLAIN plans. This is the same cut DBIO::PostgreSQL::Storage made when it dropped its ANALYZE override: a caller asking what the planner intends does not expect side effects. A caller who genuinely wants measured timings overrides _explain_query on a storage subclass and connects through it -- routing every statement through "_execute_rendered" in DBIO::Storage::DBI, never $self->dbh, as the contract requires.

  • The answer is a rowset. Both products return the plan as an ordinary result set with named columns (id, select_type, table, type, possible_keys, key, rows, Extra, ...), which is the shape fetchall_arrayref collects. Unlike SQL Server and Sybase ASE, neither needs a session setting; unlike Oracle and DB2, neither writes into a plan table that a second statement has to read; unlike SQLite, neither answers a bare EXPLAIN with something that is not a query plan.

What is not verified. The bind values of the explained query travel into the EXPLAIN as placeholders. Both DBDs replace those client-side by default -- DBD::MariaDB documents that "by default prepared statements are not used and placeholder replacement is done by DBD::MariaDB prior to sending SQL statement to MariaDB or MySQL server" -- so the server never sees a prepared EXPLAIN unless mariadb_server_prepare / mysql_server_prepare is switched on, and even then DBD::MariaDB "fallbacks to normal non-prepared statement and tries again" for statements the server will not prepare. A connection that sets mariadb_server_prepare_disable_fallback removes that safety net; that combination was not tested here, and no server was run for this section at all.

METHODS

with_deferred_fk_checks

$storage->with_deferred_fk_checks(sub { ... });

Executes the given coderef with FOREIGN_KEY_CHECKS disabled for the duration of the call. Useful when loading data with circular foreign key dependencies.

connect_call_set_strict_mode

my $schema = MyApp::Schema->connect(
  $dsn, $user, $pass,
  { on_connect_call => 'set_strict_mode' },
);

Connection callback that enables ANSI, TRADITIONAL, and ONLY_FULL_GROUP_BY SQL modes and disables SQL_AUTO_IS_NULL. Pass set_strict_mode as an on_connect_call option to activate it.

_dbd_attribute

my $attr = $storage->_dbd_attribute($dbh->{Driver}{Name}, 'auto_reconnect');
my $attr = DBIO::MySQL::Storage->_dbd_attribute(MariaDB => 'insertid');

The name the DBD driver $dbd gives one of its private attributes or methods, for mysql (DBD::mysql, mysql_*) or MariaDB (DBD::MariaDB, mariadb_*). $dbd is the driver name as DBI reports it in $dbh->{Driver}{Name}. Throws for any other driver, since this class cannot know what that driver calls it.

auto_reconnect   attribute: the auto-reconnect switch
insertid         attribute: the last AUTO_INCREMENT id, on a database
                 and on a statement handle
async            prepare attribute: arms a non-blocking statement
socket_fd        database handle method: the connection's socket fd
async_ready      statement handle method: the result will not block
async_result     statement handle method: finalise the statement
serverinfo       attribute: the server's version string from the handshake

The sync storage needs the first two. The future_io transport (DBIO::MySQL::Storage::Async) needs all seven: they are its async binding.

Every lookup is keyed on the DBD, not on the storage class. The class does not tell you the DBD. DBIO::MySQL pins this class for every DSN, so load_components('MySQL') over a dbi:MariaDB: DSN runs this class on a DBD::MariaDB handle, which dies on a FETCH of any mysql_* attribute ("Unknown attribute"). dbio-mysql-ev connects exactly that way. The sync storage therefore asks with the handle's own driver name ("_run_connection_actions", "_dbh_last_insert_id"). The future_io transport asks per connection: with the DBD the DSN names for the connect attributes, which are built before a handle exists, and with the handle's own driver name from then on ("One dist, two DBD drivers" in DBIO::MySQL::Storage::Async).

_dbh_last_insert_id

The AUTO_INCREMENT id of the last INSERT on $dbh. It reads the insertid attribute of whichever DBD owns the handle: mysql_insertid or mariadb_insertid ("_dbd_attribute").

_run_connection_actions

Turns the DBD's auto-reconnect off on every new handle, then runs the inherited connection actions. The attribute is mysql_auto_reconnect or mariadb_auto_reconnect, whichever DBD owns the handle ("_dbd_attribute"). An explicit value for that attribute in the connect attributes is honoured (this dist's ADR 0005).

A reconnect that DBIO does not see loses the open transaction without an error. DBD::mysql turns the attribute on by itself at connect under MOD_PERL or GATEWAY_INTERFACE. The reset runs at connect and spells the attribute for the DBD that owns the handle, so a connect through DBD::MariaDB works as one through DBD::mysql does.

session_setup

DBIO::MySQL::Storage::session_setup($dbh);

What a connection of this driver does to its session before anything else runs on it: $dbh->do(strict_sql_mode_statement()). One function for every connection the driver opens -- the synchronous one (_run_connection_actions) and each pooled and listen connection of the future_io transport ("_mysql_connect" in DBIO::MySQL::Storage::Async) -- so the two cannot differ.

strict_sql_mode_statement

my $sql = DBIO::MySQL::Storage::strict_sql_mode_statement();

The statement every connection runs first (the sync storage in _run_connection_actions, every pooled and listen connection of the future_io transport): STRICT_ALL_TABLES is added to the sql_mode of the session unless it already holds STRICT_ALL_TABLES or STRICT_TRANS_TABLES. Both servers are strict by default; a server configured without it (or a MySQL before 5.7, a MariaDB before 10.2.4) stores a value that does not fit silently cut or rewritten -- eleven characters in a CHARACTER(10) column are kept as ten. The driver does not let that happen unasked. Only the session changes, never the server; an on_connect_do or on_connect_call of the caller runs afterwards and can set any mode.

bind_attribute_by_data_type

my $attr = $storage->bind_attribute_by_data_type($data_type);

Returns { TYPE => DBI::SQL_BINARY } for the binary types (blob and its sizes, binary, varbinary, bytea), undef for any other. Without it a value bound to a BLOB column is text to DBD::MariaDB, which encodes every byte above 0x7F as UTF-8 on the way in (a byte string of 256 different bytes is stored as 384, 0x83 as 0xC2 0x83). With the binary type the bytes go in as they are.

_adjust_bind_values

Under DBD::mysql with mysql_enable_utf8mb4 (or mysql_enable_utf8) a text value is sent as it is held: a string with characters above 0xFF is encoded as UTF-8, one without ("\x{fc}" in Perl's native 8-bit form) goes out as the single byte 0xFC, which the server refuses as an incorrect string value -- or, on a server that is not strict, stores cut. The same value written as "\x{fc}\x{20ac}" arrives whole. DBD::MariaDB upgrades the string itself. This hook gives DBD::mysql the same reading: every plain string that is not flagged as characters, and is not bound as binary ("bind_attribute_by_data_type"), is upgraded to characters in a copy before it is bound. The caller's values are not touched. A handle without the attribute is bytes in, bytes out, and stays as it was.

This is core's seam for a value changed before it is bound ("_adjust_bind_values" in DBIO::Storage::DBI), which both ways to the server run through: the statements bound one by one (create, search, update) and each row of a populate in void context. Overriding _bind_sth_params would have left the second one out.

This changes what an existing caller gets. Text that the caller has already encoded to UTF-8 and binds as bytes (a string not flagged as characters, with bytes above 0x7F) is encoded once more under this attribute. That is the right reading: with mysql_enable_utf8mb4 the DBD hands text back as characters, so characters are what goes in, and bytes belong in a binary column (blob, binary), which is bound as binary and left alone.

text_binds

my $bind = DBIO::MySQL::Storage::text_binds($dbh, $bind, $bind_attrs);

The bind rule of "_adjust_bind_values" as a function, for every statement the driver sends: $bind is the list of [ $meta, $value ] pairs, $bind_attrs the DBI attribute per position (undef for none, a TYPE for a value bound as binary). Returns $bind itself, or -- under DBD::mysql with mysql_enable_utf8mb4 or mysql_enable_utf8 -- a copy in which every plain string that is not flagged as characters and is not bound as binary is upgraded to characters. The synchronous storage calls it from _adjust_bind_values, the future_io transport for each statement it submits ("_submit_query" in DBIO::MySQL::Storage::Async), so a value reaches the server the same way on both.

_get_server_version

The version of the connected server, read with SELECT VERSION() instead of the inherited get_info(SQL_DBMS_VER).

get_info cannot be trusted here. DBD::MariaDB answers it in a zero-padded packed form -- 08.00.4600 for MySQL 8.0.46, 11.08.0800 for MariaDB 11.8.8 -- and core's normaliser ("_server_info" in DBIO::Storage::DBI) stops at the first dotted component above 999, so (08, 00, 4600) collapses to 8.0.0. Every gate finer than major.minor then reads a server that does not exist: the >= 8.0.20 row-alias gate of "_upsert_row_alias" fails on a server that has the alias, and 11.08.0800 passes it while carrying no product name at all, which is how a MariaDB server ends up being handed AS new and answering with a syntax error.

SELECT VERSION() answers 8.0.46 and 11.8.8-MariaDB-ubu2404: the true numbers and the product name "_is_mariadb" reads. It costs one query per connection -- _server_info caches the result for the lifetime of the $dbh -- and it is the same string @@version holds, so it is a fact about the server rather than about the DBD driver (this dist's ADR 0001).

Falls back to the inherited get_info route when the query yields nothing, so a server that will not answer it is no worse off than before this override existed.

_is_mariadb

True when the connected server is a MariaDB, false when it is a MySQL. Answered from the product name in the version string "_get_server_version" obtained, which is the only side that knows -- the two engines share a wire protocol, a DBD driver, and (up to 5.5) a version numbering.

Deliberately not answered from the storage class or the DBD driver name. DBIO::MySQL::Storage::MariaDB is the DBD::MariaDB binding, not the MariaDB server: DBD::MariaDB talks to MySQL servers and DBD::mysql talks to MariaDB ones, so the class the connection landed in answers a different question than "which engine is at the other end" (this dist's ADR 0001, and the reason this method exists rather than a second =~ /mariadb/i at every call site).

_determine_supports_insert_returning

Declares insert_returning for MariaDB 10.5 and up, which is where INSERT ... RETURNING arrived, and never for MySQL, which has no RETURNING at all.

ADR 0036 assumed MariaDB could not combine RETURNING with ON DUPLICATE KEY UPDATE and built the whole MySQL identity story on that assumption. It is wrong: measured on MariaDB 11.8.8 on 2026-07-25, the combination is accepted and hands back the right row on both branches -- the update branch returned the pre-existing row carrying its new values. So MariaDB falls under the ADR's rule 1, "the engine returns the written row", and needs neither the LAST_INSERT_ID(pk) idiom nor the follow-up SELECT the other rules cost. _mysql_upsert_plan leaves the idiom out where this capability is in effect.

Gated on the server ("_is_mariadb" plus the version), not on the storage class. Declaring it in DBIO::MySQL::Storage::MariaDB would tie a server capability to the DBD driver: the same MariaDB server would have RETURNING through a dbi:MariaDB: DSN and not through a dbi:mysql: one, and a MySQL server reached through DBD::MariaDB -- which is how this dist's live suite reaches MySQL -- would be promised a clause its parser rejects (this dist's ADR 0001).

insert_returning_bound is deliberately not declared: like PostgreSQL and SQLite, and unlike Oracle's RETURNING ... INTO :bind, MariaDB hands the values back as an ordinary result set on the statement handle, which is exactly what core fetches when no returning container is requested.

_lock_dialect

my $dialect = $storage->_lock_dialect;

The locking-read grammar of the connected server, handed to "_lock_select" in DBIO::MySQL::SQLMaker through this class's sql_maker. A hashref:

  • share -- the keyword $rs->search({}, { for => 'share' }) renders as, FOR SHARE or LOCK IN SHARE MODE.

  • of -- whether for => { of => ... } can be rendered at all.

  • nowait -- likewise for modifier => 'nowait'.

  • skip_locked -- likewise for modifier => 'skip_locked'. Two keys and not one modifiers flag, because the two products did not introduce the two modifiers together.

  • server -- a human label for the exception the maker throws on a refusal, carrying the version string the boundaries below were measured against.

Four engine facts decide it, and all four are read from the server:

  • MySQL 8.0.1 introduced all three parts at once. "SELECT ... FOR SHARE is a replacement for SELECT ... LOCK IN SHARE MODE, but LOCK IN SHARE MODE remains available for backward compatibility. The statements are equivalent. However, FOR SHARE supports OF table_name, NOWAIT, and SKIP LOCKED options." (MySQL 8.0 Reference Manual, section 17.7.2.4, Locking Reads.) So on a MySQL before 8.0.1 the only spelling is LOCK IN SHARE MODE and there is no OF and no modifier to render; from 8.0.1 on, FOR SHARE is the one that carries them.

  • MariaDB has FOR SHARE in no version, and OF in none either. Its SELECT grammar reads [FOR UPDATE lock_option | LOCK IN SHARE MODE lock_option] with lock_option: [WAIT n | NOWAIT | SKIP LOCKED] -- the modifiers attach to both locking clauses, and no OF appears anywhere.

  • MariaDB's two modifiers did not arrive together. NOWAIT (and the MariaDB-only WAIT n this driver has no DSL for) came in 10.3: MDEV-11379, "AliSQL: [Feature] Issue#8: SELECT FOR UPDATE WAIT", fix version 10.3.0, resolution Fixed. SKIP LOCKED came three releases later, in 10.6: MDEV-13115, "Implement SELECT [FOR UPDATE|LOCK IN SHARED MODE] SKIP LOCKED", fix version 10.6.0, resolution Fixed. A MariaDB from 10.3 through 10.5 therefore has one modifier and not the other -- the deployment state a single flag could not express, exactly as the MariaDB 10.3/10.4 gap in the set operations above needed two switches rather than one. Where SKIP LOCKED does exist it is still not unconditional: the MariaDB SELECT reference records that it is "only implemented on InnoDB tables and ignored otherwise", so on another engine the modifier is accepted and silently does nothing. That is invisible from here -- the storage engine is a per-table property of the statement, not of the server -- so this method does not gate on it; it is named because a silently ignored modifier is a different trap from a rejected one.

  • LOCK IN SHARE MODE is understood by every version of both products, which is why it is the answer whenever FOR SHARE is not available rather than something needing its own fallback.

Decided here, from "_is_mariadb" plus the server version, and not from the storage class. A choice made on DBIO::MySQL::Storage::MariaDB's sql_maker_class, i.e. on the DBD driver name, gets two of the four DBD/engine combinations wrong: a MariaDB server reached through a dbi:mysql: DSN (which does not reach that subclass: a schema declares its driver, and one that loads the MySQL component keeps this class) would be handed FOR SHARE, which MariaDB rejects, and a MySQL 8 server reached through DBD::MariaDB -- how this dist's own live suite reaches MySQL -- would be handed LOCK IN SHARE MODE OF `t` NOWAIT, which MySQL also rejects, because the legacy spelling is exactly the one that carries neither part.

An unknown or unreadable server version is treated as a current MySQL, the same polarity the set-operation declarations above use and for the same reason: every non-EOL MySQL is past 8.0.1, and both servers that need the older spelling are ones this method can actually recognise. The MariaDB branch answers the same way within its own product: a version string that names MariaDB but carries no readable number is taken to have both modifiers, since every non-EOL MariaDB is past 10.6.

_upsert_row_alias

Returns the row-alias name to use in INSERT ... VALUES (...) AS new ON DUPLICATE KEY UPDATE col = new.col, or undef when the older VALUES(col) function must be used instead.

MySQL 8.0.20 introduced the row alias and deprecated VALUES(col) in the same release, so the switch is a pure server-version question -- answered here from _server_info->{normalized_dbms_version}, never from the DBD driver name (this dist's ADR 0001). The one wrinkle is MariaDB: it never grew the alias syntax, and its version numbers (10.x, 11.x) sail straight past a numeric >= 8.0.20 gate, so "_is_mariadb" is consulted first. Both halves rest on "_get_server_version" asking the server itself; with the inherited get_info string neither of them is reliable.

insert

Rides the core insert path (ADR 0036 section 2) and adds the two things only this driver can decide, both from static schema metadata and both before a single statement is sent:

  • Conflict-target validation (ADR 0036 section 1). MySQL is the only engine whose grammar has no conflict target, so a caller who demanded one gets an exception when the table has any further unique key -- the guarantee is not deliverable here, and the same call raises a unique violation on PostgreSQL while it would silently rewrite an unrelated row on MySQL.

  • Row identity (ADR 0036 section 4). Whether the written row can be named, and if so how. With an AUTO_INCREMENT primary key the conflict set gets the `pk` = LAST_INSERT_ID(`pk`) idiom appended so the ordinary core retrieval (last_insert_id plus a SELECT on the primary key) names the row that was actually touched. Where no channel exists, the primary key is withheld from the returned column set, which is core's signal for "the engine could not name the row": row_or_undef returns undef, row_or_die throws.

    On a server that has RETURNING -- MariaDB 10.5 and up, see "_determine_supports_insert_returning" -- none of that applies to a statement whose primary key core is fetching: the engine hands the written row back itself, so the idiom is left out of the conflict set rather than carried along as a no-op assignment nobody reads. A transport that reads SELECT LAST_INSERT_ID() instead of RETURNING (the ev backend) says so with returning => 0 in the options handed to _mysql_upsert_plan; the plan then keeps the idiom on MariaDB 10.5 and up as it does everywhere else. What remains decided the hard way is the shape where the caller supplied the primary key, because core then asks for no RETURNING column at all and the supplied value is still no evidence of which row a targetless statement touched.

A plain insert (no on_conflict in the options) passes straight through.

Two things worth knowing about the boundaries of this:

  • Only declared constraints are visible. Both rules read "unique_constraints" in DBIO::ResultSource, so a unique index that exists in the database but was never declared on the result class cannot be taken into account -- the engine will still fire on it. Declaring the unique keys is therefore not decoration here; it is what makes the target rule true.

  • on_conflict => 'nothing' can still hand back a row. The emulation assigns a column to itself, and on an AUTO_INCREMENT table that assignment is the identity idiom, so an already-present row is named and returned. PostgreSQL's DO NOTHING ... RETURNING yields no row in the same situation and upsert returns undef there. Telling the two apart here would mean reading affected-rows, which CLIENT_FOUND_ROWS (on by default in DBD::mysql and in DBD::MariaDB) makes ambiguous on MySQL and on MariaDB alike -- 1 for the row that was already there as for the one just inserted, and so result => 'count' does not tell them apart either (measured, see "_insert_on_conflict" in DBIO::MySQL::SQLMaker) -- so the more useful of the two answers is given rather than a guess.

deploy_defaults

MySQL has no transactional DDL and no IF NOT EXISTS on all statement types. Returns add_drop_table => 1 so that DBIO::Test can safely re-deploy the same schema in a test sequence without hitting table already exists errors.

deploy_setup

Strips NO_ZERO_DATE and NO_ZERO_IN_DATE from the session sql_mode before deploying a schema during tests. MySQL 8 strict mode rejects 0000-00-00 which the test suite uses to verify datetime_undef_if_invalid behaviour.

Delegates to "connect_call_set_zero_date_permissive". Note this only relaxes the sql_mode on the handle live at deploy time; when later writes (not just the deploy) must tolerate zero dates, wire set_zero_date_permissive through on_connect_call instead so every (re)connect inherits the relaxed mode.

connect_call_set_zero_date_permissive

my $schema = MyApp::Schema->connect(
  $dsn, $user, $pass,
  { on_connect_call => 'set_zero_date_permissive' },
);

Strips NO_ZERO_DATE and NO_ZERO_IN_DATE from the session sql_mode so 0000-00-00 can be written under MySQL 8 strict mode. Installed as an on_connect_call it re-runs on every (re)connect, so the session actually issuing the writes -- not merely the connection alive at deploy time -- carries the relaxed mode. This is the seam "deploy_setup" delegates to.

_mask_error_values

my $text = $storage->_mask_error_values($text, \@values, $statement);

The seam of core ("_mask_error_values" in DBIO::Storage::DBI) through which the text of an error passes before it is thrown, with the rules of the MySQL and MariaDB engines in front of the base's: the sentences in which the server quotes a value (Duplicate entry, Incorrect ... value, the excerpt after near) and the bound values in the forms both DBDs write them, even when they are shorter than the base looks for ("mask_error_details" in DBIO::MySQL::Util). The type, the column, the key and the constraint stay.

Duplicate entry 'geheim42' for key 'users.PRIMARY'
# becomes
Duplicate entry '***' for key 'users.PRIMARY'

Applies to DBIO::MySQL::Storage::MariaDB as well. Never throws; a text it cannot treat is answered unchanged.

_bind_host_into_dsn

my $dsn = $storage->_bind_host_into_dsn('mysql', 'dbi:mysql:app:db1:3306', 'db2', 3307);
# 'dbi:mysql:app:db2:3307'

The seam a DBIO::AccessBroker::HostBound view binds its host (and port) into a DSN with ("_bind_host_into_dsn" in DBIO::Storage::DBI). DBD::mysql and DBD::MariaDB read the same two forms and both reach this method. The key form (host=, port=) is core's default. This adds the positional form database[:host[:port]], in which the parts that are not key=value fill database, host and port in that order: the host is put in its place (a missing one is added), the port as well when the view has one and the DSN's own stays when it has not, and the form stays positional.

It declines, naming the form and never a value of the DSN, what it cannot bind without guessing: a mysql_socket= or mariadb_socket= key (the socket names the server and the host would be ignored, so a replicant would still reach the local one), a positional part next to key=value parts or a semicolon, and more than three positional parts.

_ddl_sql_dialect

my $dialect = $storage->_ddl_sql_dialect;    # 'mysql'

Names the SQL dialect this driver's DDL is written in, for the splitter "_execute_ddl" in DBIO::Deploy::Base cuts install and upgrade DDL with ("_ddl_sql_dialect" in DBIO::Storage::DBI::Capabilities). Declared, the splitter reads a backslash in a string literal as MySQL does, as an escape: 'it\'s; fine' is one literal. Undeclared, it takes both readings and cuts at the semicolon either of them sees. MariaDB declares mysql as well, so DBIO::MySQL::Storage::MariaDB inherits this; it is a fact about the SQL both products speak, like _random_function, not about the DBD.

is_replicating

Returns true if the connected MySQL or MariaDB replica is currently replicating from its source (both IO and SQL threads running). Intended for use with DBIO::Replicated::Storage.

Reads the thread columns under both spellings, because the row it gets them from carries whichever the statement "_replication_status_statement" chose produced: a MySQL 8.0.22 or newer answering SHOW REPLICA STATUS names them Replica_IO_Running and Replica_SQL_Running, everything else Slave_IO_Running and Slave_SQL_Running. Returns undef -- core's "this driver cannot answer" -- when the server is not a replica, or when a status row came back in which neither spelling is present.

_replication_status_statement

my $sql = $storage->_replication_status_statement;

Which spelling of the replica-status statement the connected server understands: SHOW REPLICA STATUS or SHOW SLAVE STATUS. Both products renamed it, at different releases, and one of them has since removed the old name, so this is a fact about the server and its version and nothing else (this dist's ADR 0004):

  • MySQL renamed it in 8.0.22 and removed the old name in 8.4. "From MySQL 8.0.22, use SHOW REPLICA STATUS in place of SHOW SLAVE STATUS, which is deprecated from that release" (MySQL 8.0 Reference Manual, section 15.7.7.35), and the 8.4 manual lists SHOW SLAVE STATUS under Features Removed in MySQL 8.4: "Removed SQL statements relating to replicas ... SHOW SLAVE STATUS: Use SHOW REPLICA STATUS." So on a MySQL 8.4 replica the legacy statement is a syntax error, not a deprecation warning.

  • MySQL renamed the output columns with it. "The statement works in the same way as before, only the terminology used for the statement and its output has changed" (section 15.7.7.36). SHOW REPLICA STATUS answers with Replica_IO_Running, Replica_SQL_Running and Seconds_Behind_Source; SHOW SLAVE STATUS, where it still exists, keeps the Slave_ / Seconds_Behind_Master names. This is why picking the statement is only half the job -- "is_replicating" and "lag_behind_master" read both spellings.

  • MariaDB added the new name in 10.5.1 and kept both, with the old columns. Its SHOW REPLICA STATUS page documents the grammar as SHOW { SLAVE | REPLICA } ["connection_name"] STATUS and still describes the output as Slave_IO_Running, Slave_SQL_Running and Seconds_Behind_Master. So on MariaDB the rename is a pure alias and either statement works; this method still prefers the modern spelling where the server has it, so that one rule serves both products.

_replication_status_row

my $row = $storage->_replication_status_row;

Returns the replica-status row as a hashref, or undef when the connected server is not a replica. The statement it issues comes from "_replication_status_statement", i.e. from the server's product and version; the column names in the row it returns follow from that same choice.

This is the engine seam: anything that needs to read replication state should go through here instead of issuing SHOW ... STATUS itself.

lag_behind_master

Returns the number of seconds the replica is behind its source, as reported by the replica-status row -- Seconds_Behind_Source on a MySQL answering the modern statement, Seconds_Behind_Master everywhere else. Returns undef if replication status is unavailable.

SEE ALSO

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.