MySQL
A local endpoint that speaks MySQL's protocol. The mysql client, your app and its tests connect to it unchanged.
Start it
A source in sosie.yml, its connection string in a variable of yours. npx @sosie/cli writes this for you the first
time.
sources:
app:
upstream: ${APP_URL}
export APP_URL='mysql://readonly:…@db.internal:3306/app'
npx @sosie/cli
✓ app db.internal:3306/app, read-only: 42 tables, 3 views
✓ Every value is faked
app: mysql://127.0.0.1:7674/app
The address takes any user and no password, and listens on 127.0.0.1 and ::1 only, unless sosie.yml gives another
host. It is
127.0.0.1, not localhost: MySQL’s own client takes localhost for its Unix socket.
mysql --protocol=tcp -h 127.0.0.1 -P 7674 app
?ssl-mode=REQUIRED (or VERIFY_CA, VERIFY_IDENTITY) on the connection string makes the connection upstream use TLS.
REQUIRED encrypts without checking the server’s certificate, and without ssl-mode there is no TLS at all, unlike
the mysql client’s own default: for a server across a network, use VERIFY_IDENTITY.
Sosie’s own address speaks no TLS: it is on your machine.
MySQL 8.4 and 9 are tested; 8.0 is read. MariaDB is not, yet.
A read-only login
Sosie’s session is read-only from its start, and its guard refuses writes, but a login that cannot write is safer.
Sosie warns when its login can change data, is an administrator, or may read files (FILE).
CREATE USER 'sosie_reader'@'%' IDENTIFIED BY '…';
GRANT SELECT, SHOW VIEW ON app.* TO 'sosie_reader'@'%';
SHOW VIEW lets Sosie see which tables a view reads, so a view’s columns share their tables’ fakes. Without it, a
view’s column shares a table’s fakes only where one table of the database has a column of that name, and Sosie says so
when it starts.
Clients
Tested with the mysql client, mysql2’s query() and execute(), and the mariadb driver, which Prisma’s MySQL
adapter uses.
- Statements as text (
COM_QUERY), as themysqlclient and mysql2’squery()send them. Several statements in one query run when the client turns them on (mysql2’smultipleStatements). Each is checked before the first runs, so one that Sosie refuses stops them all, and the first that fails stops the rest, as MySQL does. - Prepared statements (
COM_STMT_PREPARE,COM_STMT_EXECUTE), as mysql2’sexecute(), Prisma and Go’s driver use them, answered in MySQL’s binary protocol. MySQL says what a statement returns and how many parameters it has; each execution then runs as text, its parameters written in as literals, so it is checked, turned back and faked as a statement sent as text is. Sosie opens no cursor: a client that asks for one gets the rows at once, as MySQL answers a statement that cannot have one.
Over MCP
sosie mcp’s query tool runs SQL on a running MySQL Sosie, as on a Postgres one: up to 200 rows of each statement
as JSON, several statements at once, bytes as hex, and Sosie’s notes (a result cut at the cap) as warnings.
What the guard allows
Each statement is parsed, by the MySQL dialect of sqlparser
compiled to WebAssembly, before it goes anywhere. It reads executable comments (/*!50000 … */) as MySQL runs them.
A statement it cannot parse is refused.
- Allowed:
SELECTandWITHwithoutINTOor row locks;SHOW TABLES,COLUMNS,CREATE TABLE,INDEXand the like;DESCRIBE;USE; transactions;SETof the session’s own settings, to a value written in the query. - Refused: everything else. A statement that changes data gets
This statement changes data. Sosie is read-only.EXPLAINis refused too: a plan would print the real values Sosie puts in your query. - Refused databases and tables:
mysql(password hashes, query logs),performance_schemaandsys(queries with their values), and frominformation_schemathe process list, column statistics (histograms of real values), open transactions and where the files are. - Refused functions and settings:
LOAD_FILE(), locks other sessions wait on (GET_LOCK()), and settings that say where the database is (@@datadir,@@hostname,@@port, key and certificate paths and the like).SHOW VARIABLES,SHOW STATUSandSHOW PROCESSLISTtoo. - Settings that keep the session safe cannot be changed: read-only;
sql_mode, which takes only modes by name, never a number, and none ofANSI_QUOTES,NO_BACKSLASH_ESCAPES,PIPES_AS_CONCAT,IGNORE_SPACEandHIGH_NOT_PRECEDENCE, which change how a statement is read; and amax_execution_timeabove the one Sosie started with. Nor by an optimizer hint, which sets them for one statement:MAX_EXECUTION_TIME(…)andSET_VAR(…)are held to the same rules. - The server’s settings are not changed:
SET GLOBAL,SET PERSISTandSET GLOBAL TRANSACTIONare refused. - A function that is not MySQL’s own may be a stored one, made since Sosie started, whose code reads rows: what it gives is faked.
The session upstream
One connection upstream per client, read-only from its start, with:
| Setting | Value |
|---|---|
transaction_read_only |
ON |
max_execution_time |
30 s, or the source’s timeout; a client may lower it, never lift it |
sql_mode |
The server’s, without the modes that change how a statement is read |
sql_select_limit |
One more than max-rows: a result cut at the cap says so in SHOW WARNINGS |
MySQL’s max_execution_time stops a SELECT only. Any other statement that runs past the limit (a SET @x = (SELECT …), a SHOW), Sosie stops a second later, with KILL QUERY from a connection of its own: it gets error 3024, as a
SELECT would.
A connection reset (COM_RESET_CONNECTION) opens a new session upstream, set the same way, in the database the
client was using, as MySQL keeps it.
Query by what you saw
Literals compared by equality (=, <>, <=>, IN) are looked up in the domain of the column on the other side,
through aliases, databases, subqueries, derived tables, CTEs and an introducer such as _utf8mb4'…'. Only the
literals turned back change:
the rest of the statement goes upstream exactly as the client wrote it.
SELECT o.id, o.total FROM orders o JOIN users u ON u.id = o.user_id WHERE u.email = 'van29@example.com';
A prepared statement’s parameter compared the same way (WHERE email = ?) is turned back too. A date or time is
looked up with its fraction of a second as long as its column’s, so a Date a driver read from a fake finds its row.
What each result column becomes
MySQL names the database, table and column of each result column. Such a column is faked by its field, whatever Sosie made of the statement.
A column MySQL names no table for (anything computed, and every column of a UNION) rests on how Sosie read the
statement: it passes only when the statement reads no table of yours, or when it is a COUNT(…) or a literal in
every branch. Where Sosie cannot tell, a function or table it does not know, it reads the statement as one that reads
your data.
| The result column | Becomes |
|---|---|
A table’s column, through SELECT *, aliases, joins, derived tables and CTEs |
A fake by its field |
| A view’s column | A fake by its field, in its table’s domain when Sosie can tell which column it shows |
COUNT(…), and a literal the client wrote |
Unchanged |
A UNION’s column |
A fake by its type, unless every branch has a COUNT(…) or a literal there |
information_schema, SHOW and DESCRIBE |
Unchanged |
A path into a json column (profile->'$.address', JSON_EXTRACT(profile, '$.work[0]'), profile->>'$.city') |
The column’s JSON from there: its word keys kept, its leaves the fakes they are in the whole column |
Anything else a query computes (UPPER(email), MAX(id), a path with a wildcard) |
A fake by its type |
A computed column’s name is its text as you wrote it. MySQL names one by the text it received, with the real value
in it where Sosie turned a fake back (email = 'real@…'), a derived table’s column in its org_name too; Sosie takes
the name from your own text, at the same place. In a prepared statement a parameter shows as ?, as MySQL names it.
0.2.0 showed the real value in such a name. Upgrade to 0.3.0 or later, or on 0.2.0 give a computed column that compares with a fake a name of your own:
SELECT email = '…' AS matches.
Errors and warnings
- An error about the statement itself (a syntax error, an unknown column or table, access denied) comes back, with
every value Sosie substituted turned back into its fake. A syntax error’s quote of the statement (
near '…') is taken from your own text, at the same place: MySQL’s quote of what Sosie sent stops after 80 characters, which may be inside a real value. Anything else in quotes that you did not write, and that names nothing in the schema, comes back as'…'. - Any other error comes back as its code only:
Error 1292: Sosie hides the rest of this message, which may quote a value from your rows. SHOW WARNINGSandSHOW ERRORSshow the last statement’s, the same way: MySQL’s warnings quote values (Truncated incorrect INTEGER value: '…').
Limits
- Order and ranges follow the real values:
ORDER BYsorts by them, and>orBETWEENcompare them. LIKEand other partial matches filter the real values, so a piece of a fake finds nothing.- A value Sosie never showed is used as written, so it filters on the real value it happens to be.
- A literal compared with a column through a function (
LOWER(email) = '…') is used as written. 'a' 'b', which MySQL joins into one literal, is used as written.- Bytes (
BINARY,VARBINARY, aBLOB) are never turned back: a binary key cannot be queried by its fake. - A
UNION’s columns are faked by their type, not their field: a total row’s label (SELECT 'All', COUNT(*) … UNION ALL …) is faked with the rest of its column, and the same email has another fake there than in its table. - Spatial and
VECTORvalues come back asNULL. ABITcomes back as as many bits, and aBIT(1)flag flipped or not, as a boolean is; a literal compared with one is used as written.