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:

Statement Type	Support	Replication	Notes
DML - Data Manipulation
`INSERT` / `REPLACE`	✅ Full	✅ Yes	Includes qualified table names (db.table)
`UPDATE`	✅ Full	✅ Yes	Includes qualified table names
`DELETE`	✅ Full	✅ Yes	Includes qualified table names
`SELECT`	✅ Full	N/A	Read operations
`LOAD DATA`	✅ Full	✅ Yes	Bulk data loading
DDL - Data Definition
`CREATE TABLE`	✅ Full	✅ Yes	Replicated with cluster-wide locking
`ALTER TABLE`	✅ Full	✅ Yes	Replicated with cluster-wide locking
`DROP TABLE`	✅ Full	✅ Yes	Replicated with cluster-wide locking
`TRUNCATE TABLE`	✅ Full	✅ Yes
`RENAME TABLE`	✅ Full	✅ Yes	Replicated with cluster-wide locking
`CREATE/DROP INDEX`	✅ Full	✅ Yes	Replicated with cluster-wide locking
`CREATE/DROP VIEW`	✅ Full	✅ Yes	Replicated with cluster-wide locking
`CREATE/DROP TRIGGER`	✅ Full	✅ Yes	Replicated with cluster-wide locking
Database Management
`CREATE DATABASE`	✅ Full	✅ Yes	Replicated with cluster-wide locking
`DROP DATABASE`	✅ Full	✅ Yes	Replicated with cluster-wide locking
`ALTER DATABASE`	✅ Full	✅ Yes	Replicated with cluster-wide locking
`SHOW DATABASES`	✅ Full	N/A	Metadata query
`SHOW TABLES`	✅ Full	N/A	Metadata query
`USE database`	✅ Full	N/A	Session state
Transaction Control
`BEGIN` / `START TRANSACTION`	✅ Full	N/A	Transaction boundary
`COMMIT`	✅ Full	✅ Yes	Commits distributed transaction
`ROLLBACK`	✅ Full	✅ Yes	Aborts distributed transaction
`SAVEPOINT`	✅ Full	✅ Yes	Nested transaction support
Locking
`LOCK TABLES`	✅ Parsed	❌ No	Requires distributed locking coordination
`UNLOCK TABLES`	✅ Parsed	❌ No	Requires distributed locking coordination
Session Configuration
`SET` statements	✅ Parsed	❌ No	Session-local, not replicated
XA Transactions
`XA START/END/PREPARE`	✅ Parsed	❌ No	Marmot uses its own 2PC protocol
`XA COMMIT/ROLLBACK`	✅ Parsed	❌ No	Not compatible with Marmot's model
DCL - Data Control
`GRANT` / `REVOKE`	✅ Parsed	❌ No	User management not replicated
`CREATE/DROP USER`	✅ Parsed	❌ No	User management not replicated
`ALTER USER`	✅ Parsed	❌ No	User management not replicated
Administrative
`OPTIMIZE TABLE`	✅ Parsed	❌ No	Node-local administrative command
`REPAIR TABLE`	✅ Parsed	❌ No	Node-local administrative command

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

Schema Changes (DDL): DDL statements are fully replicated with cluster-wide locking and automatic idempotency. See the DDL Replication section for details.
XA Transactions: Marmot has its own distributed transaction protocol based on 2PC. MySQL XA transactions are not compatible with Marmot's replication model.
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.
Table Locking: LOCK TABLES statements are recognized but not enforced across the cluster. Use application-level coordination for distributed locking needs.
Qualified Names: Marmot fully supports qualified table names (e.g., db.table) in DML and DDL operations.

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

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).

Replication SQL Reference