MySQL Compatibility
Vitess supports MySQL and gRPC server protocol. This allows Vitess to be a drop-in replacement for MySQL Server without any changes to application code. As Vitess is a distributed system, it is important to understand the differences between Vitess and MySQL on compatibility.
Transaction Model #
Vitess provides MySQL default semantics i.e. REPEATABLE READ
for single-shard transactions. For multi-shard transactions the semantics change to READ COMMITTED
.
The clients can change the shard level transaction mode with SET
statement on a connection.
SQL Support #
The following describes some differences in query handling between Vitess and MySQL. The Vitess team maintains a list of unsupported queries which is kept up-to-date as we add support for new constructs.
This is an area of active development in Vitess. Any unsupported query can be raised as an issue in the Vitess GitHub Project.
DDL #
Vitess supports all DDL queries. It offers both managed, online schema changes and non-managed DDL. It is recommended to use Vitess's managed schema changes, which offer non-blocking, trackable, failure agnostic, revertible, concurrent changes, and more. Read more about making schema changes.
Join, Subqueries, Union, Aggregation, Grouping, Having, Ordering, Limit Queries #
Vitess supports most of these types of queries. It is recommended to leave schema tracking enabled in order to fully utilize the available support.
Prepared Statements #
Vitess supports prepared statements via both the MySQL binary protocol and the PREPARE
, EXECUTE
and DEALLOCATE
SQL statements.
Start Transaction #
There are multiple ways to start a transaction like begin
, start transaction
and start transaction [transaction_characteristic [, transaction_characteristic] ...]
with several modifiers that control transaction characteristics.
transaction_characteristic: {
WITH CONSISTENT SNAPSHOT
| READ WRITE
| READ ONLY
}
The scope of these modifications is limited to the next transaction only. These modifications have a special purpose and more can be read about in the MySQL reference manual.
Set Transaction #
Set Transaction statement is used to change the isolation level or access mode for transactions. Vitess as of now only supports modification of isolation level at the session scope. The change in isolation level only changes the shard level transaction isolation level and not the global Vitess level.
More details about the isolation level can be read in the MySQL reference manual.
Stored Procedures #
Calling stored procedures using CALL is only supported for:
- unsharded keyspaces
- if you directly target a specific shard
There are further limitations to calling stored procedures using CALL:
The stored procedure CALL cannot return any results
Only IN parameters are supported
If you use transactions, the transaction state cannot be changed by the stored procedure.
For example, if there is a transaction open at the beginning of the CALL, a transaction must still be open after the procedure finishes. Likewise, if no transaction is open at the beginning of the CALL, the stored procedure must not leave an open transaction after execution finishes.
CREATE PROCEDURE is not supported. You have to create the procedure directly on the underlying MySQL servers and not through Vitess.
Views #
Views are supported for sharded keyspaces as an experimental feature, it has to be enabled using: --enable-views
on VTGate and --queryserver-enable-views
on VTTablet. Views are only readable.
Here is an example of how to create a view:
CREATE VIEW my_view AS SELECT id, col FROM user
When using the view in a SELECT
statement it will be rewritten to a derived table:
-- the query:
SELECT id FROM my_view
-- will be rewritten to:
SELECT id FROM (SELECT id, col FROM user) as my_view;
Limitations:
The table referenced by the view must belong to the same keyspace as the view's.
Views are only readable. Updatable views are not supported.
The RFC for views support is available on GitHub.
Temporary Tables #
Vitess has limited support for temporary tables. It works only for unsharded keyspaces.
If the user creates a temporary table then the session will start using reserved connections for any query sent on that session.
The query plans generated by this session will not be cached. It will still continue to use the query plan cached from other non-temporary table sessions.
USE Statements #
Vitess allows you to select a keyspace using the MySQL USE
statement, and corresponding binary API used by client libraries. SQL statements can refer to a table in another keyspace by using the standard dot notation:
SELECT * FROM my_other_keyspace.table;
Vitess extends this functionality further by allowing you to select a specific shard and tablet-type within a USE
statement (backticks are important):
-- `KeyspaceName:shardKeyRange@tabletType`
USE `mykeyspace:-80@rdonly`
A similar effect can be achieved by using a database name like mykeyspace:-80@rdonly
in your MySQL application client connection string.
Window Functions and CTEs #
Vitess does not yet support Window Functions or Common Table Expressions.
Killing running queries #
In v18, Vitess introduced the ability to terminate running queries using the KILL
command through VTGate.
To execute a "kill connection" or "kill query" statement, the client needs to establish a new connection.
This behavior is similar to when a user on the MySQL shell client terminates a command by pressing ctrl+c.
The RFC highlights the current limitation of the Kill statement
support.
Alternatively,
- query_timeout_ms query comment directive can be set to define a query timeout. This ensures that the query either returns a result or aborts within the specified time.
- mysql_server_query_timeout command-line flag can be set on VTGate to establish a default timeout.
Vitess does have strict query timeouts for OLTP workloads (see below).
Workload #
By default, Vitess applies specific limitations on the execution time and the number of rows a query can return.
These limitations can be modified by adjusting the parameters like queryserver-config-query-timeout
, queryserver-config-transaction-timeout
and more in vttablet.
This default workload mode is referred as OLTP
. This can be disabled by switching to OLAP
mode by executing the following SQL statement:
set workload = olap;
SELECT ... INTO Statement #
The SELECT ... INTO
form of SELECT
in MySQL enables a query result to be stored in variables or written to a file. Vitess supports SELECT ... INTO DUMFILE
and SELECT ... INTO OUTFILE
constructs for unsharded keyspaces but does not support storing results in variable. Moreover, the position of INTO
must be towards the end of the query and not in the middle. An example of a correct query is as follows:
SELECT * FROM <tableName> INTO OUTFILE 'x.txt' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\t' LINES TERMINATED BY '\n'
For sharded keyspaces this statement can still be used but only after specifying the exact shard with a USE Statement.
LOAD DATA Statement #
LOAD DATA
is the complement of SELECT ... INTO OUTFILE
that reads rows from a text file into a table at a very high speed. Just like SELECT ... INTO
statement, LOAD DATA
is also supported in unsharded keyspaces. An example of a correct query is as follows:
LOAD DATA INFILE 'x.txt' INTO REPLACE TABLE <tableName> FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\t' LINES TERMINATED BY '\n'
For sharded keyspaces this statement can still be used but only after specifying the exact shard with a USE Statement.
Create/Drop Database #
Vitess does not support CREATE and DROP DATABASE queries out of the box.
However, a plugin mechanism is available that can be used to provision databases. The plugin has to take care of creating and dropping the database, and update the topology & VSchema so that Vitess can start receiving queries for the new keyspace.
The plugin should implement the DBDDLPlugin
interface, and be saved into a new file in the go/vt/vtgate/engine/
directory.
type DBDDLPlugin interface {
CreateDatabase(ctx context.Context, name string) error
DropDatabase(ctx context.Context, name string) error
}
It must then register itself by calling DBDDLRegister
.
You can take a look at the dbddl_plugin.go
in the engine package for an example of how it's done.
Finally, you need to add a command line flag to vtgate to have it use the new plugin: --dbddl_plugin=myPluginName
Cross-shard Transactions #
Vitess supports multiple transaction modes: SINGLE
, MULTI
and TWOPC
.
The default mode is MULTI i.e. multi-shard transactions as best-effort. A transaction that affects only one shard will be fully ACID complaint. When a transactions affects multiple shards, any failure on one or more shards will rollback the effect of that query. Committing the multi-shard transaction issues commits to the participating shards in a particular order. This allows the application or user to undo the effects of partial commits in case of failures.
Auto Increment #
Tables in sharded keyspaces should not be defined using the auto_increment
column attribute, as the values generated will not be unique across shards.
It is recommended to use Vitess Sequences instead. The semantics are very similar to auto_increment
and the differences are documented.
Character Set and Collation #
Vitess supports ~99% of MySQL collations. More details can be found here.
Data Types #
Vitess supports all of the data types available in MySQL. Using the FLOAT
data type as part of a PRIMARY KEY
is strongly discouraged, since features such as filtered replication and VReplication will not correctly be able to detect which rows should be included as part of a modification.
SQL Mode #
Vitess behaves similar to the STRICT_TRANS_TABLES
sql mode, and does not recommend changing the SQL Mode setting.
Network Protocol #
Authentication Plugins #
Vitess supports both 5.7 and 8.0 authentication. E.g. mysql_native_password
, caching_sha2_password
, etc.
Transport Security #
To configure VTGate to support TLS
set --mysql_server_ssl_cert
and --mysql_server_ssl_key
. Client certificates can also be mandated by setting --mysql_server_ssl_ca
. If there is no CA specified then TLS is optional.
X Dev API #
Vitess does not support X Dev API.