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.
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 \
memoryThe environment variable equivalent is SURREAL_MYSQL_BIND:
export SURREAL_MYSQL_BIND=127.0.0.1:3306
surreal db start --user root --pass secret memoryThe 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 \
--get-server-public-keyOn 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.
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_passwordis 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_passwordserves 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 tomysql_clear_password, so the client must allow clear-text passwords, as themysqlclient 8.x does with--enable-cleartext-plugin.mysql_clear_passwordsends 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.
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.
Client tools
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.
Query languages
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
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.
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.
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.
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:
| Statement | Answers with |
|---|---|
SHOW TABLES | The tables of the current database, in a Tables_in_<db> column |
SHOW TABLE STATUS | MySQL'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 t | Field, Type, Null, Key, Default and Extra, one row per field |
SHOW INDEX FROM t, SHOW KEYS FROM t | MySQL's 15 columns, one row per indexed column |
SHOW DATABASES | The databases of the session's namespace, in a Database column |
SHOW VARIABLES | Variable_name and Value, for the variables this server models |
SHOW ENGINES, SHOW COLLATION, SHOW CHARACTER SET | MySQL's columns |
SHOW STATUS, SHOW PROCESSLIST, SHOW WARNINGS, SHOW ERRORS, SHOW TRIGGERS | MySQL'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 TABLEreturns the SurrealQL definition, not MySQL DDL.With
LIKE, the first column ofSHOW TABLESis namedTables_in_<db>, where MySQL adds the pattern to the name.CardinalityinSHOW INDEXisNULL.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.
| Number | SQLSTATE | Reported when |
|---|---|---|
1062 | 23000 | A write conflicts with a UNIQUE index or a primary key. |
1048 | 23000 | A write gives NULL to a field that requires a value. |
1364 | HY000 | A write leaves out a field that requires a value. |
1366 | HY000 | A write gives a field a value of another type. |
1406 | 22001 | A write gives a text column a value longer than its declared type allows, such as VARCHAR(5). |
1146 | 42S02 | The statement names a table that does not exist. |
1054 | 42S22 | The statement names a column that does not exist. |
1050 | 42S01 | CREATE TABLE names a table that already exists. |
1305 | 42000 | The statement calls a function that does not exist. |
1142 | 42000 | The user may not run the statement. |
1193 | HY000 | The statement names a system variable that does not exist. |
3593 | HY000 | A window function is used where it is not allowed, such as in WHERE. |
1213 | 40001 | The transaction conflicts with another transaction. Retry it. |
3024 | HY000 | The statement exceeds its timeout. |
1235 | 42000 | The statement uses a feature that SurrealDB does not support. |
1644 | 45000 | THROW raises an error, or a subquery used as a value returns more than one row. |
1064 | 42000 | The statement cannot be parsed. |
1105 | HY000 | Any 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 gives1292for a value that is not a valid date.SurrealDB refuses some values that MySQL converts, such as
'5'ortruefor anINTcolumn.1/0written into anINTcolumn is1366, where MySQL gives1365.
Not yet supported
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
GROUPSframes andDISTINCTinside a window call, are refused with1235.INTERSECT ALLandEXCEPT ALLare refused with1235, as are the other forms ofINTERSECTandEXCEPTthat the PostgreSQL dialect refuses. Text is compared exactly, soSELECT 'a' INTERSECT SELECT 'A'answers no rows, where MySQL's default collation ignores letter case.SQL_CALC_FOUND_ROWSis refused.SQL_NO_CACHE,SQL_CACHE,SQL_SMALL_RESULT,SQL_BIG_RESULT,SQL_BUFFER_RESULTandHIGH_PRIORITYare accepted and ignored.ALTER TABLEandLOAD DATAare not supported yet. ALATERALsubquery and anORDER BYinsideGROUP_CONCATfail with1064.information_schemalists the tables and declared columns of the database in PostgreSQL's form: the tables are in the schemapublic, not in a schema named after the database, soWHERE table_schema = DATABASE()finds none,data_typeholds PostgreSQL type names such asinteger, andcolumn_typeisNULL.SHOW TABLESandSHOW COLUMNSanswer in MySQL's form.FOREIGN KEYconstraints are not supported.CREATE TABLEwith one fails with1105.LIKEis refused with1235in anINSERT,UPDATEorDELETE, including in itsWHEREclause, soDELETE FROM t WHERE name LIKE 'a%'fails before it deletes anything. It works in aSELECT. Functions, such asSUBSTRING,LTRIMandCONCAT, work in both.CAST(x AS UNSIGNED)of a value read from a column fails with1366when 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.TIMESTAMPDIFFwith the unitMONTH, and compound interval units such asDAY_HOUR, are refused with1235.TRUNCATEkeeps the records that the table's permissions do not let the user delete, and still answers OK.An
INSERTthat gives anAUTO_INCREMENTprimary keyNULLis refused, where MySQL numbers the row.@@performance_schemais0.@@session.nameof a variable that is global only reads a value, where MySQL gives error1238.SET GLOBAL timestampis error1235, where MySQL gives1228.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 ... ESCAPEis refused, andeandé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
mysqlroute.--deny-http mysqlrefuses every connection to the listener with error1045. The route also allows the MySQL dialect on the listener.GQL.
SET dialect = 'gql'needs thegqlroute. When the server denies the route, theSET dialectstatement fails, and a GQL query fails with1045.Arbitrary queries.
--deny-arbitrary-queryapplies 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
PostgreSQL and MySQL compatibility matrix: the two dialects compared feature by feature
surreal db start: CLI reference including--mysql-bindCapabilities: lock down arbitrary queries and routes
Migrating from MySQL: map MySQL types to SurrealQL, and import data with Surreal Sync