Compatibility

SQL Statement Compatibility

Marmot supports a wide range of MySQL/SQLite statements through its MySQL protocol server. The following table shows compatibility for different statement types:

Legend

• ✅ Full: Fully supported and working
• ✅ Parsed: Statement is parsed and recognized
• ⚠️ Limited: Works but has limitations in distributed context
• ❌ No: Not supported or not replicated
• N/A: Not applicable (read-only or session-local)

Important Notes

1. Schema Changes (DDL): DDL statements are fully replicated with cluster-wide locking and automatic idempotency. See the DDL Replication section for details.

2. XA Transactions: Marmot has its own distributed transaction protocol based on 2PC. MySQL XA transactions are not compatible with Marmot's replication model.

3. User Management (DCL): User and privilege management statements are local to each node. For production deployments, consider handling authentication at the application or proxy level.

4. Table Locking: LOCK TABLES statements are recognized but not enforced across the cluster. Use application-level coordination for distributed locking needs.

5. Qualified Names: Marmot fully supports qualified table names (e.g., db.table) in DML and DDL operations.

6. LOAD DATA Scope: Marmot supports LOAD DATA LOCAL INFILE (client-uploaded file bytes). LOAD DATA INFILE (server-side file access) is not supported.

MySQL Protocol & Metadata Queries

Marmot includes a MySQL-compatible protocol server, allowing you to connect using any MySQL client (DBeaver, MySQL Workbench, mysql CLI, etc.). The server supports:

Metadata Query Support

Marmot provides full support for MySQL metadata queries, enabling GUI tools like DBeaver to browse databases, tables, and columns:

• SHOW Commands: SHOW DATABASES, SHOW TABLES, SHOW COLUMNS FROM table, SHOW CREATE TABLE, SHOW INDEXES
• INFORMATION_SCHEMA: Queries against INFORMATION_SCHEMA.TABLES, INFORMATION_SCHEMA.COLUMNS, INFORMATION_SCHEMA.SCHEMATA, and INFORMATION_SCHEMA.STATISTICS
• Type Conversion: Automatic SQLite-to-MySQL type mapping for compatibility

These metadata queries are powered by the rqlite/sql AST parser, providing production-grade MySQL query compatibility.

Connecting with MySQL Clients

# Using mysql CLI
mysql -h localhost -P 3306 -u root
 
# Connection string for applications
mysql://root@localhost:3306/marmot

MySQL Compatibility Functions

Marmot supports key MySQL functions for application compatibility:

Auto-Increment ID Generation

Marmot generates unique distributed IDs for BIGINT AUTO_INCREMENT columns. Two modes are available:

Configure via [mysql].auto_id_mode in config. The compact mode uses: 41 bits timestamp + 6 bits node ID + 6 bits sequence.

These modes apply only to BIGINT AUTO_INCREMENT columns. Narrower columns use range claims instead (below).

Narrow AUTO_INCREMENT Columns

An AUTO_INCREMENT column declared narrower than BIGINT (TINYINT, SMALLINT, MEDIUMINT, INT, signed or UNSIGNED) gets ids that fit its declared width. Each node takes its ids from a range of the column it claims through a cluster quorum, so no two nodes hand out the same id while the cluster membership is stable (see Membership Changes and Narrow AUTO_INCREMENT Ids in Operations).

• Single node: set cluster.standalone = true with seed_nodes empty. The node claims ranges without peers.
• Cluster: a node whose system database is new holds its claim votes until it has merged claim bases from enough cluster members, and logs AUTO_INCREMENT claim votes released after merging claim bases when it does. A narrow insert through that node during the hold waits for the release, up to transaction.lock_wait_timeout_seconds, and returns the retryable 1205 only if the hold outlasts it. Inside an explicit transaction that has already written a row, the insert does not wait: it returns 1205 at once, because the release can depend on peers joining from a snapshot that waits for that transaction.
• Ids increase within one node's range, not across nodes: two nodes inserting into the same table issue ids from different ranges. Ids a range did not issue are skipped for good, for example after a restart.
• LAST_INSERT_ID() reports the generated id as usual.

Explicit ids in a narrow column:

A column whose ids are used up returns 1062 on the column's maximum, as MySQL does.

Ids are not reset by DROP TABLE followed by CREATE TABLE, or by DROP DATABASE followed by CREATE DATABASE: the recreated table continues above the ids the old one had claimed.

Limitations

• Selective Table Watching: All tables in a database are replicated. Selective table replication is not supported.
• WAL Mode Required: SQLite must use WAL mode for reliable multi-process changes.
• Eventually Consistent: Rows may sync out of order. SERIALIZABLE transaction assumptions may not hold across nodes.
• Concurrent DDL: Avoid running concurrent DDL operations on the same database from multiple nodes (protected by cluster-wide lock with 30s lease).