MySQL Compatibility

ElyraSQL speaks the MySQL wire protocol, so standard MySQL clients, GUIs, and drivers connect without modification.

What works

• Text protocol (COM_QUERY) — the common path for most clients and CLIs.
• Prepared statements (COM_STMT_PREPARE/EXECUTE) — typed parameters (including DATE/DATETIME/TIME from the binary protocol), value escaping, statement reuse; used by many ORMs and drivers.
• Authentication — mysql_native_password by default (widest driver compatibility), with caching_sha2_password available opt-in (see below).
• TLS — clients may negotiate SSL.
• Handshake — reports a MySQL-looking version, e.g. 8.0.12-ElyraSQL-1.12.4, and answers the session/introspection queries clients send on connect (SELECT @@version_comment, SELECT VERSION(), SET ..., SHOW VARIABLES/STATUS/COLLATION/DATABASES/TABLE STATUS, and the information_schema tables GUI tools read to build their schema tree).

Laravel / Eloquent

ElyraSQL runs Laravel migrations (schema builder), Eloquent models and relationships, the query builder, transactions, and pagination. Point the mysql connection at ElyraSQL and set the database name to elyra (used as the information_schema schema for Schema::hasTable/hasColumn and SHOW):

// config/database.php  -> connections.mysql
'host'     => env('DB_HOST', '127.0.0.1'),
'port'     => env('DB_PORT', '3307'),
'database' => env('DB_DATABASE', 'elyra'),
'options'  => [
    // Recommended: client-side prepared statements. Native prepares work for
    // common shapes, but some (e.g. information_schema `SELECT *`) are not yet
    // reliable with strict drivers; emulation sends fully-formed queries.
    PDO::ATTR_EMULATE_PREPARES => true,
],

With that setting a full Eloquent workload -- Schema::create (including $table->id(), foreignId()->constrained(), indexes), model CRUD with lastInsertId, hasMany/belongsTo, eager loading, withCount, query-builder joins/aggregates/groupBy+having, updateOrInsert, transactions and cascading deletes -- runs cleanly.

Since 1.7.0 this is exercised against real applications rather than only synthetic tests: the migration and test suites of four commercial Laravel codebases run against ElyraSQL, the largest with 469 migration files. The compatibility fixes that came out of that work are covered by 103 end-to-end tests through the wire protocol, which run on every pull request alongside a query battery compared differentially against MySQL 8.4.

Verified clients

• mysql / mariadb command-line clients
• PyMySQL, mysql-connector-python
• PHP PDO / Laravel Eloquent
• Rust drivers sqlx and mysql_async (the latter backs ElyraSQL's own observability sink — DB-verified end to end)
• DBeaver, MySQL Workbench (via the standard MySQL driver)
• Any language driver that speaks the MySQL protocol

Authentication plugin

ElyraSQL advertises mysql_native_password by default. This is the pragmatic choice for driver compatibility: the client completes the simple challenge/response handshake (SHA1(SHA1(password))) with no key exchange, so PDO, PyMySQL, mysql-connector, sqlx, and mysql_async all connect out of the box.

caching_sha2_password (MySQL 8's default) is available opt-in via ELYRASQL_AUTH_PLUGIN=caching_sha2_password. ElyraSQL performs full authentication every time (it keeps no fast-auth cache): the password is read over the TLS channel, or — on a plaintext connection — RSA-encrypted with the server's public key and decrypted server-side. Prefer TLS when using it, and note that some drivers (e.g. mysql_async) are happiest on mysql_native_password and may stumble on the full-auth exchange — the default already avoids this.

Character set and collation

The default character set is utf8mb4 and the default collation is utf8mb4_0900_ai_ci, matching MySQL 8. The collation is case- and accent-insensitive: 'café' = 'cafe', 'Straße' = 'Strasse', 'ae' = 'æ', and ordering interleaves non-ASCII with ASCII (Ærlig, ål, Ape, cafe, cat, øl, zz).

The folding table is derived from MySQL's own WEIGHT_STRING output, so equality and ordering agree with MySQL for Latin text. Characters that have their own primary weight in MySQL (þ, ŋ, ı) are case-folded but not reduced to ASCII, and non-Latin scripts (Greek, Cyrillic, CJK) order by codepoint rather than full UCA weights.

A database created by ElyraSQL before 1.5.0 is migrated automatically on first open: text index entries are rebuilt and text primary keys re-keyed, before any connection is accepted. Databases whose indexed text is pure ASCII are unaffected.

Result-column metadata

Result columns carry the collation and declared width MySQL sends, because clients use both to compute display widths: a character column advertises its limit in bytes under its collation, and the client divides by the charset's bytes per character. So a VARCHAR(32) reports 128, and a driver that knows utf8mb4 shows 32.

Measured against MySQL 8.4, on a table declared (a VARCHAR(32), b CHAR(8), c TEXT, d VARBINARY(16), e BINARY(4), f BLOB, g JSON, h INT, i BIGINT UNSIGNED, j DECIMAL(10,2)):

Widths and collations agree with MySQL everywhere except INT, which is stored as a 64-bit integer and advertised as such. The type-code differences come from collapsing all character types onto one storage type and all binary types onto another; drivers decode both correctly, but a client that inspects type codes will see VAR_STRING where MySQL says STRING or BLOB.

Tables created before 1.9.1 have no stored declaration and advertise the unbounded width for character and binary columns.

Differences and gaps

ElyraSQL implements a focused, growing subset of MySQL SQL. Notable current gaps:

• Subqueries (WHERE + SELECT-list, correlated + uncorrelated, including over joins), derived tables, CTEs including WITH RECURSIVE, HAVING, window functions with explicit ROWS frames and named windows, GROUP BY ... WITH ROLLUP, set operations, numeric value-offset RANGE frames, and peer-offset GROUPS frames are supported. Temporal RANGE offsets are not yet supported.
• Views, materialized views, row-level triggers, and stored procedures (parameters, local and session variables, IF/WHILE/LOOP/REPEAT, cursors, condition handlers) are supported; user-defined functions and scheduled events are not.
• ALTER TABLE supports add/drop/rename/MODIFY/CHANGE column, rename table, ADD INDEX/KEY/UNIQUE (with backfill), ADD PRIMARY KEY (with an atomic table recluster), and ADD FOREIGN KEY. SHOW CREATE TABLE echoes CHECK and FOREIGN KEY constraints (with their referential actions) since 1.8.0, so its output can be replayed without losing them.
• A broad scalar function library (string, math, date/time, JSON, MD5/SHA1/ SHA2, HEX/UNHEX, FORMAT, FIND_IN_SET, FROM_UNIXTIME, ...), statistical and bitwise aggregates (STDDEV*, VAR*, BIT_OR/AND/XOR), LAST_INSERT_ID()/ROW_COUNT(), @@system variables, and CONVERT(). The MySQL shorthands INSERT ... SET and the <</>>/~ bitwise operators all work. LOAD DATA INFILE reads a server-side file using bounded bulk insert units; client-streamed LOAD DATA LOCAL INFILE is not supported.
• Vector search and VEC_DISTANCE(...) are ElyraSQL extensions (they mirror MySQL 9's vector direction but are not identical).
• Integer storage is always 64-bit, but the declared width and UNSIGNED are both enforced (since 1.9.1 and 1.8.0 respectively), so a value too wide for its column raises 1264 as in MySQL. Tables created by earlier versions have no width recorded and are not retroactively constrained — see data types.
• Session variables. SET accepts sql_mode, autocommit, foreign_key_checks, group_concat_max_len, transaction_isolation and time_zone, in the SET x, SET SESSION x and SET @@session.x spellings, with a comma-separated list and with a scalar subquery as the value (SET sql_mode=(SELECT CONCAT(@@sql_mode, ',...')), which is how sqlx opens a connection). Other variables are refused with error 1235 rather than silently ignored. time_zone accepts UTC (+00:00, SYSTEM, UTC) and any numeric offset in [+-]HH:MM form within ±14:00 (#125): the local now-family (NOW, CURRENT_TIMESTAMP, LOCALTIME/LOCALTIMESTAMP, CURDATE, CURTIME) reads in the session zone, the UTC_* forms stay in UTC, and FROM_UNIXTIME/UNIX_TIMESTAMP(dt) interpret their value in the session zone (UNIX_TIMESTAMP() with no argument is an absolute instant). A named zone (Europe/Oslo) is refused, needing a zone table with DST rules a fixed offset cannot express; converting stored TIMESTAMP columns on read is tracked separately. UTC_TIMESTAMP()/UTC_DATE()/UTC_TIME() return the current UTC instant, and CONVERT_TZ() shifts by a fixed offset (+HH:MM, SYSTEM, UTC); a named zone returns NULL there, as MySQL does with no time-zone tables loaded. || follows MySQL: logical OR by default, string concatenation under PIPES_AS_CONCAT. @@sql_mode keeps the client's flag order rather than MySQL's canonical one.
• NOW() is frozen per statement. NOW(), CURRENT_TIMESTAMP, LOCALTIME/LOCALTIMESTAMP, CURDATE, CURTIME and the UTC_* forms return one instant captured at statement start -- so two reads agree and an INSERT with several of them is consistent, as in MySQL. The local forms carry the session offset (above); the UTC_* forms do not. SYSDATE() is the exception: it reads the clock when it runs.
• One database, under any name. Every table lives in one database. A connection selects it as elyra, or under its own name (USE app_test, or the database named at connect, such as Laravel's DB_DATABASE), and DATABASE(), SHOW DATABASES and information_schema.tables.table_schema then report that name -- an alias, not a separate namespace: two connections using different names see the same tables. CREATE DATABASE/SCHEMA is refused unless written with IF NOT EXISTS; see the note under Laravel / Eloquent above. DROP DATABASE refuses the connection's own name and elyra, which would drop every table; any other name holds nothing, so it is MySQL's 1008 database doesn't exist (a no-op with IF EXISTS).
• Isolation levels: SET TRANSACTION ISOLATION LEVEL ... is accepted for all four standard levels, but only two engines exist — SERIALIZABLE (opt-in) and snapshot isolation, which backs everything else. Snapshot is at least as strong as READ UNCOMMITTED, READ COMMITTED, and REPEATABLE READ (no dirty reads, repeatable reads, no phantoms within a transaction), so a client that asks for READ COMMITTED gets more isolation, never less. The one behavioural difference: a long transaction under snapshot does not see other transactions' commits mid-flight (it reads a consistent snapshot from BEGIN), whereas true READ COMMITTED would. @@transaction_isolation reports REPEATABLE-READ (MySQL's default), which is what most ORMs expect.
• Read-only transactions work as in MySQL. START TRANSACTION READ ONLY (with or without WITH CONSISTENT SNAPSHOT), SET TRANSACTION READ ONLY for the next transaction, and SET SESSION TRANSACTION READ ONLY or @@transaction_read_only = 1 for the session refuse every write -- INSERT, UPDATE, DELETE, REPLACE, SELECT ... FOR UPDATE, temporary tables -- with error 1792 Cannot execute statement in a READ ONLY transaction, before it runs. DDL in a read-only transaction commits it first (see below), so only a read-only session refuses DDL, as in MySQL. SELECT ... FOR SHARE is allowed. READ WRITE and the combined form (SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED, READ ONLY) are accepted, and an unscoped SET TRANSACTION inside an open transaction is refused with 1568, as MySQL does.
• Implicit commit, as in MySQL. DDL (CREATE, ALTER, DROP, RENAME, TRUNCATE, indexes, views, triggers, procedures), account statements (CREATE USER, GRANT, REVOKE, ...), LOCK TABLES, ANALYZE TABLE and a new BEGIN/START TRANSACTION commit the open transaction before they run -- also when the statement then fails. A ROLLBACK afterwards no longer undoes the writes made before the DDL. Temporary tables do not commit, and neither does REFRESH MATERIALIZED VIEW, ElyraSQL's own statement, which rewrites a table's rows inside the transaction like the DML it amounts to.
• SHOW and information_schema cover what GUI tools and drivers need to connect and browse (tables, columns, engines, schemata, views, routines, triggers, events, statistics, partitions); it is not the complete MySQL catalog.

See Limitations for the full picture.