---
title: "MySQL Wire Protocol Tutorial"
---

# MySQL Wire Protocol Tutorial

**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, current release (`heliosdb-nano --version`)
- A MySQL client (`mysql` CLI, or any driver listed below)
- About 15 minutes

---

## 1. Start with MySQL Enabled

```bash
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:

```bash
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](https://www.heliosdb.com/docs/nano/guides/quickstart/unix_sockets_quickstart/).

---

## 2. The `mysql` CLI

```bash
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

```sql
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

```sql
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

```sql
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):

```java
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`

```bash
npm install mysql2
```

```javascript
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](https://www.heliosdb.com/docs/nano/guides/tutorials/drizzle_prisma_compat/) — those ORMs target Postgres dialects in most projects.

---

## 5. PHP — `mysqli`

```php
<?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):

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

WordPress runs on top of `mysqli` — see [WORDPRESS_DROP_IN](https://www.heliosdb.com/docs/nano/guides/tutorials/wordpress_drop_in_tutorial/) for the full setup.

---

## 6. Python — `PyMySQL`

```bash
pip install pymysql
```

```python
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

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

```bash
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](https://www.heliosdb.com/docs/nano/guides/quickstart/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 (older releases) | Upgrade to the current release |
| `WHERE id = '1'` returns 0 rows on integer PK | String→Int coercion was missing in older releases (`$wpdb->prepare("...%s", 1)` produces a quoted literal) | Upgrade to the current release: `coerce_pk_value` 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 | Older KEY-index regex matched inside column names | Upgrade to the current release |
| WordPress `wpdb` empty result on read-after-write | Stale result-cache key in older releases | Upgrade to the current release |

---

## Where Next

- [WORDPRESS_DROP_IN](https://www.heliosdb.com/docs/nano/guides/tutorials/wordpress_drop_in_tutorial/) — replace MySQL under WordPress unchanged.
- [UNIX_SOCKETS_QUICKSTART](https://www.heliosdb.com/docs/nano/guides/quickstart/unix_sockets_quickstart/) — same-host MySQL connections without TCP.
- [DRIZZLE_PRISMA_COMPAT](https://www.heliosdb.com/docs/nano/guides/tutorials/drizzle_prisma_compat/) — TypeScript ORMs over the PostgreSQL wire (separate path).
- [BAAS_REST_API](https://www.heliosdb.com/docs/nano/guides/tutorials/baas_rest_api_tutorial/) — REST surface over the same tables.