Edit this page

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.

Table of Contents #

  1. Transaction and Isolation Levels
  2. SQL Support
    1. DDL
    2. Join, Subqueries, Union, Aggregation, Grouping, Having, Ordering, Limit Queries
    3. Prepared Statements
    4. Stored Procedures
    5. Views
    6. Temporary Tables
    7. USE Statements
    8. Common Table Expressions (CTEs)
    9. Window Functions
    10. Killing Running Queries
    11. SELECT ... INTO Statement
    12. LOAD DATA Statement
    13. Create/Drop Database
    14. User Defined Functions (UDFs)
    15. LAST_INSERT_ID
  3. Cross-shard Transactions
  4. Auto Increment
  5. Character Set and Collation
  6. Data Types
  7. SQL Mode
  8. Network Protocol
    1. Authentication Plugins
    2. Transport Security
    3. X Dev API
  9. Workload

Transaction and Isolation Levels #

Vitess offers MySQL default semantics (REPEATABLE READ) for single-shard transactions. For multi-shard transactions, the semantics change to READ COMMITTED.

  • You can change the isolation level at the shard level using the SET statement on a connection.
  • START TRANSACTION supports modifiers like WITH CONSISTENT SNAPSHOT, READ WRITE, and READ ONLY, but they apply only to the next transaction.
  • SET TRANSACTION is currently supported only for changing the isolation level at the session scope in Vitess (affecting shard-level isolation, not global Vitess).

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:

Prepared Statements #

Vitess supports:

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;
MySQL Compatibility