MySQL Compatibility

VTGate servers speak both gRPC and the MySQL server protocol. This allows you to connect to Vitess as if it were a MySQL Server without any changes to application code. This document refers to known compatibility issues where Vitess differs from MySQL.

Transaction Model

Vitess provides READ COMMITTED semantics when executing cross-shard queries. This differs to MySQL, which defaults to REPEATABLE READ.

SQL Syntax

The following describes some of the major differences in SQL Syntax handling between Vitess and MySQL. For a list of unsupported queries, check out the test-suite cases.

DDL

Vitess supports MySQL DDL, and will send ALTER TABLE statements to each of the underlying tablet servers. For large tables it is recommended to use an external schema deployment tool and apply directly to the underlying MySQL shard instances. This is discussed further in Applying MySQL Schema.

Join Queries

Vitess supports INNER JOIN including cross-shard joins. LEFT JOIN is supported as long as there are not expressions that compare columns on the outer table to the inner table in sharded keyspaces.

Ordering

Vitess supports the ordering for all the columns irrespective of the data type. However it further requests collation specific information (weight_string) from the underlying MySQL instance. This can be avoided for numeric columns which is discussed further in Advanced VSchema Properties

Aggregation

Vitess supports a subset of GROUP BY operations, including cross-shard operations. The VTGate servers are capable of scatter-gather operations, but can only stream results. Thus, a query that performs a GROUP BY colx ORDER BY coly may be refused if the intermediate result set is larger than VTGate’s in-memory limit.

Subqueries

Vitess supports a subset of subqueries. For example, a subquery combined with a GROUP BY operation is not supported.

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.

Window Functions and CTEs

Vitess does not yet support Window Functions or Common Table Expressions.

Killing running queries

Vitess does not yet support killing running shard queries via the KILL command through VTGate. Vitess does have strict query timeouts for OLTP workloads (see below). If you need a query, you can connect to the underlying MySQL shard instance and run KILL from there.

Cross-shard Transactions

By default, Vitess does not support transactions that span across shards. While Vitess can support this with the use of Two-Phase Commit, it is usually recommended to design the VSchema in such a way that cross-shard modifications are not required.

OLAP Workload

By default, Vitess sets some intentional restrictions on the execution time and number of rows that a query can return. This default workload mode is called OLTP. This can be disabled by setting the workload to OLAP:

  1. 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:

  1. 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:

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

Network Protocol

Prepared Statements

Starting with version 4.0, Vitess features experimental support for prepared statements via the MySQL protocol. Session-based commands using the PREPARE and EXECUTE SQL statements are not supported.

Authentication Plugins

Vitess supports the mysql_native_password authentication plugin. Support for caching_sha2_password can be tracked in #5399.

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.

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.

Character Set and Collation

Vitess only supports utf8 and variants such as utf8mb4.

SQL Mode

Vitess behaves similar to the STRICT_TRANS_TABLES sql mode, and does not recommend changing the SQL Mode setting.

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.

Auto Increment

Tables in sharded keyspaces do not support the auto_increment column attribute, as the values generated would be local only to each shard. Vitess Sequences are provided as an alternative, which have very close semantics to auto_increment.

Extensions to MySQL Syntax

SHOW Statements

Vitess supports a few additional options with the SHOW statement.

  • SHOW keyspaces – A list of keyspaces available.
  1. Example Output:
  2. +----------+
  3. | Database |
  4. +----------+
  5. | commerce |
  6. | customer |
  7. +----------+
  • SHOW vitess_tablets – Information about the current Vitess tablets such as the keyspace, key ranges, tablet type, hostname, and status.
  1. Example Output:
  2. +-------+----------+-------+------------+---------+------------------+------------+----------------------+
  3. | Cell  | Keyspace | Shard | TabletType | State   | Alias            | Hostname   | MasterTermStartTime  |
  4. +-------+----------+-------+------------+---------+------------------+------------+----------------------+
  5. | zone1 | commerce | 0     | MASTER     | SERVING | zone1-0000000100 | <redacted> | 2021-04-22T04:10:29Z |
  6. | zone1 | commerce | 0     | REPLICA    | SERVING | zone1-0000000101 | <redacted> |                      |
  7. | zone1 | commerce | 0     | RDONLY     | SERVING | zone1-0000000102 | <redacted> |                      |
  8. | zone1 | customer | -80   | MASTER     | SERVING | zone1-0000000300 | <redacted> | 2021-04-22T04:12:23Z |
  9. | zone1 | customer | -80   | REPLICA    | SERVING | zone1-0000000301 | <redacted> |                      |
  10. | zone1 | customer | -80   | RDONLY     | SERVING | zone1-0000000302 | <redacted> |                      |
  11. | zone1 | customer | 80-   | MASTER     | SERVING | zone1-0000000400 | <redacted> | 2021-04-22T04:12:23Z |
  12. | zone1 | customer | 80-   | REPLICA    | SERVING | zone1-0000000401 | <redacted> |                      |
  13. | zone1 | customer | 80-   | RDONLY     | SERVING | zone1-0000000402 | <redacted> |                      |
  14. +-------+----------+-------+------------+---------+------------------+------------+----------------------+
  • SHOW vitess_shards – A list of shards that are available.
  1. Example Output:
  2. +--------------+
  3. | Shards       |
  4. +--------------+
  5. | commerce/0   |
  6. | customer/-80 |
  7. | customer/80- |
  8. +--------------+
  • SHOW vschema tables – A list of tables available in the current keyspace’s vschema.
  1. Example Output for customer keyspace:
  2. +----------+
  3. | Tables   |
  4. +----------+
  5. | corder   |
  6. | customer |
  7. | dual     |
  8. +----------+
  • SHOW vschema vindexes – Information about the current keyspace’s vindexes such as the keyspace, name, type, params, and owner. Optionally supports an “ON” clause with a table name.
  1. Example Output:
  2. +----------+------+------+--------+-------+
  3. | Keyspace | Name | Type | Params | Owner |
  4. +----------+------+------+--------+-------+
  5. | customer | hash | hash |        |       |
  6. +----------+------+------+--------+-------+
  • show global gtid_executed [FROM <keyspace>] – retrieves the global gtid_executed from each shard in the keyspace either selected or provided in the query.
  1. Example Output for customer keyspace:
  2. +----------+-------------------------------------------+-------+
  3. | db_name  | gtid_executed                             | shard |
  4. +----------+-------------------------------------------+-------+
  5. | customer | e9148eb0-a320-11eb-8026-98af65a6dc4a:1-43 | 80-   |
  6. | customer | e0f64aca-a320-11eb-9be4-98af65a6dc4a:1-43 | -80   |
  7. +----------+-------------------------------------------+-------+

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:

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

  1. -- `KeyspaceName:shardKeyRange@tabletType`
  2. 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.

Create/Drop Database

Vitess does not support CREATE and DROP DATABASE queries out of the box.

But, to make it possible to provision databases, a plugin mechanism exists. 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.

  1. type DBDDLPlugin interface {
  2.     CreateDatabase(ctx context.Context, name string) error
  3.     DropDatabase(ctx context.Context, name string) error
  4. }

It must then register itself 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