Skip to content

APIs and protocols

MySQL wire protocol

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, and can switch to 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.

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.

mysql -h 127.0.0.1 -P 3306 -u root -psecret -D main/main \
  --get-server-public-key

On a plaintext connection, pass --get-server-public-key as above. The client then asks the server for its RSA public key and sends the password encrypted with it, as the default caching_sha2_password method requires over plaintext. Without the option, the mysql client fails with ERROR 2061: Couldn't read RSA public key from server. Over TLS, 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.

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. It works over TLS, and over a plaintext connection from any address with the server's RSA public key, which the server generates when it first needs it.

  • mysql_native_password serves older clients and drivers. The server verifies it directly only for a user who signed in earlier since the server started. Otherwise the server asks the client to switch to mysql_clear_password, so the client must allow clear-text passwords, as the mysql client 8.x does with --enable-cleartext-plugin.

  • mysql_clear_password sends the password as clear text.

A sign-in that sends the password as clear text 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.

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.

mysqladmin (ping, version, status and variables) and mysqlslap run against the listener. mysqldump of a table runs to completion when given --no-tablespaces and --skip-lock-tables, and writes the rows of the table as INSERT statements. The structure section of the dump holds the SurrealQL definition that SHOW CREATE TABLE returns, so a MySQL server cannot replay it. Pass --no-create-info to dump the rows only.

A new connection runs the MySQL dialect. SET dialect = 'gql' switches an open connection to ISO GQL, and SET dialect = 'mysql' (or 'sql') switches it back.

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;

SurrealQL and the PostgreSQL dialect are not available over this listener: SET dialect = 'surrealql' and SET dialect = 'pgsql' fail with error 1045. Send SurrealQL over HTTP, RPC or an SDK, and PostgreSQL queries over the Postgres wire protocol.

In the MySQL dialect, 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 tables are SurrealDB tables, so a SurrealQL query over HTTP or an SDK reads the same records.

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, window functions, START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT and SET autocommit are supported. A statement that names a table in another database is refused.

User variables and prepared statements in SQL work as in MySQL. SET @a = expr keeps the value of the expression for the connection, a later @a reads it, and a variable that was never set reads as NULL. PREPARE name FROM 'statement' (or FROM @variable), EXECUTE name [USING @a, ...] and DEALLOCATE PREPARE name prepare and run a statement, and EXECUTE of an unknown name fails with 1243. COM_RESET_CONNECTION clears both.

A column without an alias is named by its expression exactly as it was written, as MySQL names it, so SELECT COUNT(*), MAX(k) + 1 answers the columns COUNT(*) and MAX(k) + 1. A name is cut at 255 bytes.

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.

CREATE TABLE also records the type of each column as the field's SQL TYPE, and every write is checked against it, so a value too long for a VARCHAR(n) column is refused with 1406. A DECIMAL column with a precision above 28 defines a bigdecimal field. An ENUM('s','m','l') column defines a field of the inline enum enum<'s', 'm', 'l'>, which refuses any other value with 1366 and orders its values in the order the column lists them.

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. CAST(x AS UNSIGNED) of a constant follows MySQL as well: a negative integer wraps around to the top of the unsigned range, so -1 becomes 18446744073709551615, a number with a fraction is rounded first, and a number above the largest unsigned integer becomes 18446744073709551615.

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, window functions, 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.

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.

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.

An AUTO_INCREMENT column draws its numbers from a sequence, and is NOT NULL. TRUNCATE starts the numbering again from 1, as does CREATE TABLE of a table that was dropped. Because TRUNCATE defines the sequence again, a user who may delete records but not define a sequence, such as an editor, is refused TRUNCATE of a table with an AUTO_INCREMENT column.

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 answer with MySQL 8.4's column names, order and types, with one row per table, field, indexed column or database:

StatementAnswers with
SHOW TABLESThe tables of the current database, in a Tables_in_<db> column
SHOW TABLE STATUSMySQL's 18 columns, one row per table, with sizes of 0, times of NULL, and VIEW as the Comment of a view
SHOW COLUMNS FROM t, SHOW FIELDS FROM t, DESCRIBE t, DESC tField, Type, Null, Key, Default and Extra, one row per field
SHOW INDEX FROM t, SHOW KEYS FROM tMySQL's 15 columns, one row per indexed column
SHOW DATABASESThe databases of the session's namespace, in a Database column
SHOW VARIABLESVariable_name and Value, for the variables this server models
SHOW ENGINES, SHOW COLLATION, SHOW CHARACTER SETMySQL's columns
SHOW STATUS, SHOW PROCESSLIST, SHOW WARNINGS, SHOW ERRORS, SHOW TRIGGERSMySQL's columns and no rows

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.

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.

NumberSQLSTATEReported when
106223000A write conflicts with a UNIQUE index or a primary key.
104823000A write gives NULL to a field that requires a value.
1364HY000A write leaves out a field that requires a value.
1366HY000A write gives a field a value of another type.
140622001A write gives a text column a value longer than its declared type allows, such as VARCHAR(5).
114642S02The statement names a table that does not exist.
105442S22The statement names a column that does not exist.
105042S01CREATE TABLE names a table that already exists.
130542000The statement calls a function that does not exist.
114242000The user may not run the statement.
1193HY000The statement names a system variable that does not exist.
3593HY000A window function is used where it is not allowed, such as in WHERE.
121340001The transaction conflicts with another transaction. Retry it.
3024HY000The statement exceeds its timeout.
123542000The statement uses a feature that SurrealDB does not support.
164445000THROW raises an error, or a subquery used as a value returns more than one row.
106442000The statement cannot be parsed.
1105HY000Any other failure, where MySQL has no number for it.

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

  • Every refused value apart from a text that is too long 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.

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

  • Recursive common table expressions are refused with 1235, before the statement changes any data.

  • The window function features that the PostgreSQL dialect refuses, such as GROUPS frames and DISTINCT inside a window call, are refused with 1235.

  • INTERSECT ALL and EXCEPT ALL are refused with 1235, as are the other forms of INTERSECT and EXCEPT that the PostgreSQL dialect refuses. Text is compared exactly, so SELECT 'a' INTERSECT SELECT 'A' answers no rows, where MySQL's default collation ignores letter case.

  • SQL_CALC_FOUND_ROWS is refused. SQL_NO_CACHE, SQL_CACHE, SQL_SMALL_RESULT, SQL_BIG_RESULT, SQL_BUFFER_RESULT and HIGH_PRIORITY are accepted and ignored.

  • ALTER TABLE and LOAD DATA are not supported yet. A LATERAL subquery and an ORDER BY inside GROUP_CONCAT fail with 1064.

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

  • CAST(x AS UNSIGNED) of a value read from a column fails with 1366 when the value is negative, or is a text that is not a number such as '12abc'. MySQL wraps the negative value, and reads the number at the start of the text.

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

  • TRUNCATE keeps the records that the table's permissions do not let the user delete, and still answers OK.

  • An INSERT that gives an AUTO_INCREMENT primary key NULL is refused, where MySQL numbers the row.

  • @@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.

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 dialect on the listener.

  • GQL. SET dialect = 'gql' needs the gql route. When the server denies the route, the SET dialect statement fails, and a GQL query 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.

Was this page helpful?

Last updated