MySQL Wire Protocol Tutorial

MySQL Wire Protocol Tutorial

Available since: v3.8.0 (2026-04-02) Build: default — no feature flag required Endpoints: TCP 127.0.0.1:3306 (MySQL wire v10), Unix socket /tmp/heliosdb-mysql.sock (configurable)

---

UVP

Replacing MySQL almost always means rewriting connection strings, reauditing schema files, and praying the ORM doesn’t trip over AUTO_INCREMENT or backticks. HeliosDB Nano speaks MySQL protocol v10 on port 3306 and translates MySQL-specific DDL — TINYINT(1), LONGTEXT, DATETIME, backtick identifiers, AUTO_INCREMENT, ON DUPLICATE KEY UPDATE, CHARSET=utf8mb4 — at the wire layer. Existing apps connect with the same drivers, the same connection strings, and the same SQL. WordPress, Laravel Eloquent, mysql2, PyMySQL, mysql-connector-j — all unmodified.

---

Prerequisites

• HeliosDB Nano v3.8+ (heliosdb-nano --version)
• A MySQL client (mysql CLI, or any driver listed below)
• About 15 minutes

---

1. Start with MySQL Enabled

heliosdb-nano start --memory --mysql

The MySQL listener is opt-in — without --mysql only the PostgreSQL wire and HTTP ports come up. Default bind address is 127.0.0.1:3306. To listen on all interfaces:

heliosdb-nano start --memory --mysql --mysql-listen 0.0.0.0:3306

For local-only PHP / WordPress deployments, prefer a Unix socket — see UNIX_SOCKETS_QUICKSTART.

---

2. The mysql CLI

mysql -h 127.0.0.1 -P 3306 -u root

Welcome to the MySQL monitor.
Server version: 8.0.35-HeliosDB-Nano
mysql>

The server version string is real (SELECT VERSION() returns 8.0.35-HeliosDB-Nano) so client compatibility checks pass. The version banner is intercepted in the MySQL handler — the underlying engine is HeliosDB.

Run MySQL DDL unchanged

CREATE TABLE products (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  active      TINYINT(1) DEFAULT 1,
  description LONGTEXT,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY name_idx (name(64))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO products (name, description) VALUES
  ('Widget', 'A useful widget'),
  ('Gadget', 'A modern gadget');

SELECT id, name, active, created_at FROM products;

The translator (src/protocol/mysql/translator.rs) rewrites this to PostgreSQL-compatible SQL before parsing:

MySQL	Translated to	Notes
INT AUTO_INCREMENT	SERIAL	BIGINT AUTO_INCREMENT → BIGSERIAL
TINYINT(1)	BOOLEAN	TINYINT (no (1)) → SMALLINT
MEDIUMINT	INTEGER
LONGTEXT, MEDIUMTEXT, TINYTEXT	TEXT
LONGBLOB, MEDIUMBLOB, TINYBLOB	BYTEA
DATETIME	TIMESTAMP
YEAR	SMALLINT
INT(11), BIGINT(20)	INT, BIGINT	display width stripped
INT UNSIGNED	BIGINT	promoted to avoid overflow
DOUBLE	DOUBLE PRECISION
ENUM('a','b')	TEXT
ENGINE=InnoDB	(stripped)	also MyISAM, Aria, etc.
CHARSET=utf8mb4	(stripped)	also DEFAULT CHARSET=, CHARACTER SET
COLLATE=utf8mb4_unicode_ci	(stripped)
`name`	name	backticks stripped (not converted to double-quotes)

LAST_INSERT_ID and ON DUPLICATE KEY

INSERT INTO products (name) VALUES ('Sprocket');
SELECT LAST_INSERT_ID(); -- 3

-- Native ON DUPLICATE KEY UPDATE — translated to ON CONFLICT DO UPDATE
INSERT INTO products (id, name) VALUES (1, 'Widget v2')
  ON DUPLICATE KEY UPDATE name = VALUES(name);

SELECT id, name FROM products WHERE id = 1;
-- Widget v2

LAST_INSERT_ID() is tracked per MySQL connection (state on the MySqlConnection object). VALUES(col) references inside the UPDATE clause translate to EXCLUDED.col so the planner’s native ON CONFLICT path runs.

SHOW commands

SHOW TABLES;
SHOW COLUMNS FROM products;
SHOW FULL COLUMNS FROM products;   -- 9 fields incl. Collation, Privileges, Comment
SHOW INDEX FROM products;
SHOW CREATE TABLE products;

SHOW FULL COLUMNS returns all 9 MySQL fields — required for wpdb::get_col_charset() and other ORMs that introspect column charsets. SHOW INDEX returns PRIMARY entries plus UNIQUE-constraint indexes so dbDelta() doesn’t try to recreate them.

---

3. JDBC

The standard mysql-connector-j JDBC driver works unchanged. Connection string:

jdbc:mysql://localhost:3306/heliosdb?useSSL=false&allowPublicKeyRetrieval=true

Java example (Hibernate / JPA / plain JDBC):

String url = "jdbc:mysql://localhost:3306/heliosdb?useSSL=false&allowPublicKeyRetrieval=true";
try (Connection conn = DriverManager.getConnection(url, "root", "")) {
    try (PreparedStatement ps = conn.prepareStatement(
        "SELECT id, name FROM products WHERE active = ?")) {
        ps.setBoolean(1, true);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.printf("%d  %s%n", rs.getInt(1), rs.getString(2));
            }
        }
    }
}

Hibernate dialect: org.hibernate.dialect.MySQL8Dialect. The wire protocol announces itself as 8.0.35-HeliosDB-Nano so the dialect picker chooses the modern path.

---

4. Node.js — mysql2

npm install mysql2

import mysql from "mysql2/promise"

const conn = await mysql.createConnection({
  host: "127.0.0.1",
  port: 3306,
  user: "root",
  database: "heliosdb",
  // Or use a Unix socket for embedded mode:
  // socketPath: "/tmp/heliosdb-mysql.sock",
})

const [rows] = await conn.execute(
  "SELECT id, name FROM products WHERE name LIKE ?",
  ["%idget%"]
)
console.log(rows)

const [result] = await conn.execute(
  "INSERT INTO products (name) VALUES (?)",
  ["Bolt"]
)
console.log("inserted id:", result.insertId)

await conn.end()

Sequelize and TypeORM with mysql2 as the underlying driver work identically. For Drizzle / Prisma / TypeORM running on the PostgreSQL wire, see DRIZZLE_PRISMA_COMPAT — those ORMs target Postgres dialects in most projects.

---

5. PHP — mysqli

<?php
$db = new mysqli("127.0.0.1", "root", "", "heliosdb", 3306);
if ($db->connect_error) {
    die("Connect failed: " . $db->connect_error);
}

$db->set_charset("utf8mb4");

$stmt = $db->prepare("SELECT id, name FROM products WHERE active = ?");
$active = 1;
$stmt->bind_param("i", $active);
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo "{$row['id']}\t{$row['name']}\n";
}

// INSERT and read LAST_INSERT_ID
$db->query("INSERT INTO products (name) VALUES ('Hammer')");
echo "new id: " . $db->insert_id . "\n";

For local Unix-socket connections (no TCP):

$db = new mysqli(":/tmp/heliosdb-mysql.sock", "root", "", "heliosdb");

WordPress runs on top of mysqli — see WORDPRESS_DROP_IN for the full setup.

---

6. Python — PyMySQL

pip install pymysql

import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    database="heliosdb",
    cursorclass=pymysql.cursors.DictCursor,
)

with conn.cursor() as cur:
    cur.execute("SELECT id, name FROM products WHERE name LIKE %s", ("%idget%",))
    for row in cur.fetchall():
        print(row)

    cur.execute("INSERT INTO products (name) VALUES (%s)", ("Drill",))
    conn.commit()
    print("new id:", cur.lastrowid)

conn.close()

mysql-connector-python and SQLAlchemy with the mysql+pymysql:// URL also work. SQLAlchemy ORM round-trips through the translator so model definitions don’t need to change.

---

7. The Translator in Detail

Every MySQL-shaped SQL statement passes through translate() (in src/protocol/mysql/translator.rs) before reaching the planner. The translator runs these passes in order:

1. Backslash escape normalization — strips MySQL-style \", \\, \n, \r, \t, \0 inside single-quoted strings (PHP mysqli_real_escape_string produces these). Without this step, WordPress serialized PHP data corrupts on storage.
2. Backticks — `col` becomes col (stripped, not converted to double-quotes — backticks are case-insensitive in MySQL).
3. Type translations (table above).
4. AUTO_INCREMENT → SERIAL / BIGSERIAL.
5. CHARSET / COLLATE / ENGINE — stripped.
6. ON DUPLICATE KEY UPDATE → ON CONFLICT DO UPDATE SET (with VALUES(col) → EXCLUDED.col).
7. REPLACE INTO → INSERT INTO (conflict handling deferred to the engine).
8. INSERT IGNORE → INSERT … ON CONFLICT DO NOTHING.
9. LIMIT n, m → LIMIT m OFFSET n (MySQL’s two-argument LIMIT).
10. MySQL functions — DATE_FORMAT, DATE_ADD, UNIX_TIMESTAMP, STR_TO_DATE, etc. (14 date/time functions). Most translate to PG equivalents; a handful are evaluated directly in the MySQL handler.
11. Multi-table DELETE — translated to two separate DELETE ... WHERE id IN (subquery) statements.
12. KEY / INDEX with prefix lengths — KEY meta_key(meta_key(191)) and similar are normalized.
13. ALTER TABLE ADD KEY / ADD INDEX with prefix lengths — silently accepted (stripped).

The result is parsed by sqlparser-rs as PostgreSQL SQL and runs through the same planner, optimizer, and storage path as a psql query.

---

8. Connect from Same-Host Apps via Unix Socket

heliosdb-nano start --memory --mysql --mysql-socket /tmp/heliosdb-mysql.sock

mysql --socket=/tmp/heliosdb-mysql.sock

PHP mysqli and WordPress with DB_HOST = ':/tmp/heliosdb-mysql.sock' work out of the box. See UNIX_SOCKETS_QUICKSTART.

---

Troubleshooting

Symptom	Cause	Fix
Connection refused on port 3306	--mysql not passed at startup	Restart with --mysql (the listener is opt-in)
LAST_INSERT_ID() returns 0	Table-level PRIMARY KEY (col) not propagated to column flags (fixed in 3.9.3+)	Upgrade to v3.9.3 or later
WHERE id = '1' returns 0 rows on integer PK	String→Int coercion was missing pre-3.9.9 ($wpdb->prepare("...%s", 1) produces a quoted literal)	Upgrade to v3.9.9 or later — coerce_pk_value now handles String→Int8/Int4/Int2
Multi-byte characters appear as ? or mojibake	Driver charset mismatch	mysqli::set_charset("utf8mb4") / ?charset=utf8mb4 on connection string
Unknown column 'meta_key' after CREATE TABLE	Pre-3.8.2 KEY-index regex matched inside column names	Upgrade to v3.8.2 or later
WordPress wpdb empty result on read-after-write	Stale result-cache key (3.14.5–3.14.6 issue, fixed in 3.14.6)	Upgrade to v3.14.6 or later

---

Where Next

• WORDPRESS_DROP_IN — replace MySQL under WordPress unchanged.
• UNIX_SOCKETS_QUICKSTART — same-host MySQL connections without TCP.
• DRIZZLE_PRISMA_COMPAT — TypeScript ORMs over the PostgreSQL wire (separate path).
• BAAS_REST_API — REST surface over the same tables.