The Vitess Docs | MySQL Compatibility

MySQL Compatibility

Introduction #

Vitess supports MySQL and gRPC server protocols, allowing it to serve as a drop-in replacement for MySQL Server without changes to application code. However, because Vitess is a distributed system, there are compatibility differences to be aware of.

Transaction and Isolation Levels #

Vitess provides MySQL’s default REPEATABLE READ semantics for single-shard transactions, ensuring strong consistency within a shard. For multi-shard transactions, Vitess optimizes for performance and scalability by using READ COMMITTED semantics, enabling efficient distributed transactions.

With Two-Phase Commit (2PC) support, Vitess ensures atomic writes across shards, making it easier to manage distributed transactions reliably.
You can adjust the isolation level at the shard level using the SET statement on a connection.
START TRANSACTION supports MySQL modifiers like WITH CONSISTENT SNAPSHOT, READ WRITE, and READ ONLY, applying them to the next transaction on the same shard.
SET TRANSACTION allows setting the isolation level at the session scope, influencing how transactions behave at the shard level.

Optimizing Read Consistency in Multi-Shard Transactions #

If an application requires strong consistency, it can issue queries with update locks (SELECT ... FOR UPDATE) to ensure the latest data is read while preventing modifications until the transaction completes.
Using Vitess’s two-phase commit (2PC) ensures atomicity for distributed writes, providing reliable transaction execution across shards.
For workloads requiring higher isolation, transactions can be designed to operate within single shards, where REPEATABLE READ consistency is fully maintained.

SQL Support #

While Vitess is mostly compatible with MySQL, there are some limitations. A current list of unsupported queries is maintained in the Vitess GitHub repo.

DDL #

Vitess supports all DDL queries:

Managed, online schema changes (non-blocking, revertible, etc.).
Non-managed DDL is also supported.

Refer to making schema changes for more details.

Join, Subqueries, Union, Aggregation, Grouping, Having, Ordering, Limit Queries #

Vitess supports most of these query types. For the best experience:

Leave schema tracking enabled to leverage full support.

Prepared Statements #

Vitess supports:

Prepared statements via MySQL binary protocol.
SQL statements: PREPARE, EXECUTE, DEALLOCATE.

Stored Procedures #

You can call stored procedures (CALL) with the following limitations:

Must be on an unsharded keyspace or target a specific shard.
No results can be returned.
Only IN parameters are supported.
Transaction state cannot be changed by the procedure.

CREATE PROCEDURE is not supported through Vitess; create procedures on the underlying MySQL servers directly.

Views #

Views are supported for sharded keyspaces as an experimental feature:

Enable with --enable-views on VTGate and --queryserver-enable-views on VTTablet.
Views are only readable (no updatable views).
All tables referenced by the view must belong to the same keyspace.

See the Views RFC for more details.

Temporary Tables #

Vitess has limited support for temporary tables, only for unsharded keyspaces:

Creating a temporary table forces the session to start using reserved connections.
Query plans in this session won’t be cached.

USE Statements #

Vitess allows selecting a keyspace (and shard/tablet-type) using the MySQL USE statement:

USE `mykeyspace:-80@rdonly`

Or refer to another keyspace’s table via standard dot notation:

SELECT * 
FROM other_keyspace.table;

Common Table Expressions #

Non-recursive CTEs are supported.
Recursive CTEs have experimental support; feedback is encouraged.

Window Functions #

Window Functions are not currently supported in Vitess.

Killing Running Queries #

Starting with Vitess v18, you can terminate running queries with the KILL command through VTGate:

Issue KILL connection or KILL query from a new client connection (similar to ctrl+c in MySQL shell).
You can also ask Vitess to kill queries that run beyond a specified timeout. The timeout can be set per query or globally.
query_timeout_ms (per-query timeouts).
mysql_server_query_timeout command-line flag (global default timeout).

SELECT … INTO Statement #

Vitess supports SELECT ... INTO DUMPFILE and SELECT ... INTO OUTFILE for unsharded keyspaces:

Position of INTO must be at the end of the query.
For sharded keyspaces, you must specify the exact shard with a USE statement.

LOAD DATA Statement #

LOAD DATA (the counterpart to SELECT ... INTO OUTFILE) is supported only in unsharded keyspaces:

Must be used similarly to the SELECT ... INTO statement.
For sharded keyspaces, use the USE Statement to target an exact shard.

Create/Drop Database #

Vitess does not support CREATE DATABASE or DROP DATABASE by default:

A plugin mechanism (DBDDLPlugin interface) exists for provisioning databases.
The plugin must handle database creation, topology updates, and VSchema updates.
Register the plugin with DBDDLRegister and specify --dbddl_plugin=myPluginName when running vtgate.

User Defined Functions #

Vitess can track UDFs if you enable the --enable-udfs flag on VTGate. More details on creating UDFs can be found in the MySQL Docs.

LAST_INSERT_ID #

Vitess supports LAST_INSERT_ID both for returning the last auto-generated ID and for the form LAST_INSERT_ID(expr), which sets the session’s last-insert-id value.

Example:

insert into test (id) values (null); -- Inserts a row with an auto-generated ID
select LAST_INSERT_ID(); -- Returns the last auto-generated ID
SELECT LAST_INSERT_ID(123); -- Sets the session’s last-insert-id value to 123
SELECT LAST_INSERT_ID(); -- Returns 123

Limitation: When using LAST_INSERT_ID(expr) as a SELECT expression in ordered queries, MySQL sets the session’s LAST_INSERT_ID value based on the last row returned. Vitess, however, does not guarantee which row’s value will be used.

Example:

SELECT LAST_INSERT_ID(col) 
FROM table 
ORDER BY foo;

Cross-shard Transactions #

Vitess supports multiple transaction modes: SINGLE, MULTI and TWOPC .

Default: MULTI — multi-shard transactions on a best-effort basis.
A single-shard transaction is fully ACID-compliant.
Multi-shard commits are done in a specific order; partial commits can be manually undone if needed.

Auto Increment #

Avoid the auto_increment column attribute in sharded keyspaces; values won’t be unique across shards. Use Vitess Sequences instead — they behave similarly to auto_increment.

Character Set and Collation #

Vitess supports ~99% of MySQL collations. For details, see the collations documentation.

Data Types #

Vitess supports all MySQL data types. Using FLOAT as part of a PRIMARY KEY is discouraged because it can break features like filtered replication and VReplication.

SQL Mode #

Vitess behaves similarly to STRICT_TRANS_TABLES and does not recommend changing the SQL Mode.

Network Protocol #

Authentication Plugins #

Vitess supports MySQL authentication plugins, such as mysql_native_password and caching_sha2_password.

Transport Security #

To enable TLS on VTGate:

Set --mysql_server_ssl_cert and --mysql_server_ssl_key.
Optionally require client certificates with --mysql_server_ssl_ca.
If no CA is specified, TLS is optional.

X Dev API #

Vitess does not support the X Dev API.

Workload #

By default, Vitess applies strict limitations on execution time and row counts, often referred to as OLTP mode:

These parameters can be tweaked with queryserver-config-query-timeout, queryserver-config-transaction-timeout, and others on vttablet.
You can switch to OLAP mode by issuing:

SET workload = olap;