MySQL wire protocol

Available since: v4.0.0

The MySQL wire protocol listener lets a MySQL client, such as the mysql command-line client, JDBC through Connector/J, or the Python, Go, Node.js and PHP drivers, connect to SurrealDB on a TCP port and run queries. A connection runs the MySQL dialect of SQL against SurrealDB tables by default, and can switch to SurrealQL, the PostgreSQL dialect or ISO GQL. Queries written for MySQL 8.0 run against SurrealDB tables. This page covers how to enable the listener, how to connect, and where the MySQL dialect differs from MySQL. The Postgres wire protocol page covers the equivalent listener for PostgreSQL clients.

Enable the listener

The listener is included in the official binaries, installers and Docker images, and is off until you give it an address. Pass --mysql-bind to surreal db start to start it, on a separate address from the HTTP server:

surreal db start --user root --pass secret \
  --mysql-bind 127.0.0.1:3306 \
  memory

The environment variable equivalent is SURREAL_MYSQL_BIND:

export SURREAL_MYSQL_BIND=127.0.0.1:3306
surreal db start --user root --pass secret memory

The server logs the address when the listener starts. A server started without --mysql-bind or SURREAL_MYSQL_BIND does not listen for MySQL connections. The mysql route capability also controls the listener: with --deny-http mysql, the server refuses each connection.

Connect with mysql

mysql -h 127.0.0.1 -P 3306 -u root -psecret -D main/main \
  --default-auth=mysql_native_password

On a plaintext connection, pass --default-auth=mysql_native_password as above. The listener does not offer an RSA public key, and a MySQL or MariaDB client that uses its default caching_sha2_password method over plaintext asks for one before it sends the password, so the connection fails with ERROR 2061: Couldn't read RSA public key from server. This also happens on a loopback address. Over TLS, the default method works and the option is not needed.

The database name selects the namespace and the database as ns/db, with a single slash, in the same way as Surreal-NS and Surreal-DB on HTTP. USE ns/db changes both on an open connection.

The server announces itself as MySQL 8.0.35-SurrealDB, and SELECT VERSION() and SELECT @@version both return that string.

Authentication

A client signs in as a system user, such as the root user or a database user with a password. The server accepts the caching_sha2_password, mysql_native_password and mysql_clear_password authentication methods. caching_sha2_password is the default of MySQL 8 clients, mysql_native_password serves older clients and drivers, and mysql_clear_password sends the password as clear text. A sign-in that sends the password needs a TLS connection or a loopback address, such as 127.0.0.1, and is refused with error 1045 Authentication requires an encrypted (TLS) connection or loopback otherwise.

TLS

The listener uses the certificate and key that --web-crt and --web-key give the HTTP server. Without them, the listener serves plaintext connections and the server logs a warning. TLS is recommended for any connection that does not come from the same host.

Query languages

A new connection runs the MySQL dialect. SET dialect switches the dialect of an open connection:

CREATE TABLE person (id INT PRIMARY KEY, name TEXT, age INT);
INSERT INTO person (id, name, age) VALUES (1, 'Ada', 36), (2, 'Bob', 28);
SELECT name, age FROM person WHERE age > 30 ORDER BY name;

SET dialect = 'surrealql';
SELECT * FROM person;

In the MySQL and PostgreSQL dialects, every record ID in a result is sent as its key, as a SQL client expects a primary key to be: person:1 is sent as 1.

The MySQL dialect

The MySQL dialect parses MySQL 8.0 and 8.4 syntax, and runs SELECT with joins, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT, aggregate functions, non-recursive common table expressions (WITH), INSERT (including INSERT ... ON DUPLICATE KEY UPDATE), UPDATE, DELETE, CREATE TABLE, DROP TABLE, TRUNCATE and the statements that drivers send while they connect. Prepared statements, statements sent several at a time, START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT and SET autocommit are supported. A statement that names a table in another database is refused.

CREATE TABLE defines a field of a sized number type for each number column: TINYINT is i8, SMALLINT is i16, MEDIUMINT and INT are i32, BIGINT is int, INT UNSIGNED is u32, FLOAT is f32 and DOUBLE is float. A value out of range for the column, such as 300 for a TINYINT or -1 for an INT UNSIGNED, is refused with 1366.

The implemented functions are tested against the answers that MySQL 8.4 gives for the same arguments, and include the keyword forms EXTRACT(YEAR FROM d), POSITION('b' IN s) and SUBSTRING(s FROM 2 FOR 3), and INTERVAL arithmetic such as DATE_ADD(d, INTERVAL 1 DAY). LENGTH counts bytes, as in MySQL.

Many points behave as they do over the PostgreSQL dialect, which shares the same query planner. The PostgreSQL dialect section describes these in more detail: tables that declare their columns, unqualified columns in a join, t.*, LIKE patterns, subqueries used as a value, and the limits on how deeply a FROM clause can nest. Over this listener, the failures that section describes are reported with the MySQL error numbers listed under Error codes, and a FROM clause that nests too deeply fails with 1064.

Transactions and DDL

START TRANSACTION or BEGIN opens a transaction, and SET autocommit = 0 makes each statement run in a transaction until COMMIT. As in MySQL, a DDL statement (CREATE, ALTER, DROP, TRUNCATE or RENAME) first commits the open transaction and then runs on its own, so a ROLLBACK does not take back a CREATE TABLE. This applies only in the MySQL dialect. After SET dialect = 'pgsql' or SET dialect = 'surrealql', DDL statements run inside the open transaction, and a ROLLBACK takes them back.

CREATE TABLE and DROP TABLE of several tables are each answered with one OK packet. If one part of the statement fails, nothing of the statement is kept, so DROP TABLE a, missing fails and keeps a.

TRUNCATE TABLE t and TRUNCATE t answer with an OK packet that reports 0 affected rows, as a plain statement, as a prepared statement and inside a batch of statements.

System variables

A SELECT of system variables, such as SELECT @@version, @@sql_mode, @@autocommit, answers one column for each variable, in the order asked, named by its alias or by the variable. A number is sent as an integer, and other values as text. @@name, @@session.name, @@local.name and @@global.name read the same value, in any letter case. A connection with no database chosen can run a SELECT of only system variables, which drivers send before they choose a database.

The values are the fixed settings of the server, so a SET does not change what @@name reads. A name that MySQL 8.4 or 8.0.35 does not have fails with error 1193 Unknown system variable. A variable that MySQL has but this server does not model reads as NULL, and so do variables that describe the host, such as @@datadir, @@hostname, @@port and @@version_compile_os.

SHOW and DESCRIBE

SHOW and DESCRIBE answer with MySQL 8.4's column names, order and types, with one row per table, field, indexed column or database:

Each SHOW reads under the session's own permissions, and needs a database to be chosen. SHOW DATABASES needs permission to view the namespace. A table that does not exist fails, and a LIKE pattern is limited to 512 characters. Some answers differ from MySQL:

• •

  Columns and indexes are listed by name, not in the order they were defined.

• •

  SHOW CREATE TABLE returns the SurrealQL definition, not MySQL DDL.

• •

  With LIKE, the first column of SHOW TABLES is named Tables_in_<db>, where MySQL adds the pattern to the name.

• •

  Cardinality in SHOW INDEX is NULL.

• •

  An instance that denies built-in functions refuses these statements.

Error codes

A failed statement is answered with the error number and SQLSTATE that MySQL 8.4 gives for the same kind of failure, for plain and for prepared statements. Drivers use the number to decide what to do, so PyMySQL, for example, raises IntegrityError for a duplicate key. The message text is SurrealDB's own.

The numbers can differ from MySQL's for the same statement:

• •

  Every refused value is 1366, where MySQL gives 1292 for a value that is not a valid date.

• •

  SurrealDB refuses some values that MySQL converts, such as '5' or true for an INT column.

• •

  1/0 written into an INT column is 1366, where MySQL gives 1365.

Not yet supported

The MySQL dialect refuses or differs from MySQL in these places:

• •

  Window functions (OVER (...)) and recursive common table expressions are refused with 1235, before the statement changes any data.

• •

  ALTER TABLE and LOAD DATA are not supported yet.

• •

  information_schema lists the tables and declared columns of the database in PostgreSQL's form: the tables are in the schema public, not in a schema named after the database, so WHERE table_schema = DATABASE() finds none, data_type holds PostgreSQL type names such as integer, and column_type is NULL. SHOW TABLES and SHOW COLUMNS answer in MySQL's form.

• •

  FOREIGN KEY constraints are not supported. CREATE TABLE with one fails with 1105.

• •

  LIKE is refused with 1235 in an INSERT, UPDATE or DELETE, including in its WHERE clause, so DELETE FROM t WHERE name LIKE 'a%' fails before it deletes anything. It works in a SELECT. Functions, such as SUBSTRING, LTRIM and CONCAT, work in both.

• •

  TIMESTAMPDIFF with the unit MONTH, and compound interval units such as DAY_HOUR, are refused with 1235.

• •

  A value too long for a VARCHAR(n) column is accepted.

• •

  TRUNCATE does not restart an AUTO_INCREMENT counter. Records that the table's permissions do not let the user delete are kept, and the statement still answers OK.

• •

  @@performance_schema is 0. @@session.name of a variable that is global only reads a value, where MySQL gives error 1238.

• •

  SET GLOBAL timestamp is error 1235, where MySQL gives 1228.

• •

  A batch of several statements that names an unknown system variable fails as a whole before any statement runs.

• •

  In a batch of several statements, only the first statement keeps its column names. Unnamed or repeated columns of later statements are named ?column?1, ?column?2, and so on.

• •

  LIKE ... ESCAPE is refused, and e and é are not equal in a comparison.

• •

  A ? parameter placeholder is accepted only in a prepared statement.

Capabilities and security

Connections are gated like other query surfaces:

• •

  The mysql route. --deny-http mysql refuses every connection to the listener with error 1045. The route also allows the MySQL and SurrealQL dialects on the listener.

• •

  Other dialects. SET dialect = 'pgsql' needs the postgres route, and SET dialect = 'gql' needs the gql route. When the server denies the route, the SET dialect statement fails, and a query in a dialect the user may not run fails with 1045.

• •

  Arbitrary queries. --deny-arbitrary-query applies to connections on this listener as it does on the other surfaces.

The listener accepts at most 1,024 connections at a time and answers further connections with error 1040 Too many connections. A packet is limited to 64 MiB, the value of @@max_allowed_packet, and a larger packet is refused with error 1153.

Related pages

• •

  Postgres wire protocol: the equivalent listener for PostgreSQL clients, and the PostgreSQL dialect

• •

  surreal db start: CLI reference including --mysql-bind

• •

  Capabilities: lock down arbitrary queries and routes

• •

  Migrating from MySQL: map MySQL types to SurrealQL, and import data with Surreal Sync