---
title: "WordPress Drop-In Tutorial"
---

# WordPress Drop-In Tutorial

**Build:** default — no feature flag required
**Endpoints:** TCP `127.0.0.1:3306` or Unix socket `/tmp/heliosdb-mysql.sock`

---

## UVP

WordPress is the largest-installed-base MySQL workload on the planet. Most "alternative database" projects need a `db.php` drop-in plugin to massage the queries `wpdb` emits — patches that go stale, miss edge cases, and silently corrupt serialized PHP data. HeliosDB Nano runs WordPress with **the standard, unmodified `wpdb` class** by translating MySQL syntax at the wire layer: `LAST_INSERT_ID()`, `ON DUPLICATE KEY UPDATE`, `SHOW TABLES / COLUMNS / FULL COLUMNS`, backslash-escaped strings, and table-level `PRIMARY KEY (col)` all do the right thing. **No `wp-content/db.php`. No patches.**

---

## Prerequisites

- HeliosDB Nano, current release (`heliosdb-nano --version`)
- PHP 7.4+ with `mysqli` extension
- WordPress source tree (or `wp-cli` to fetch one)
- Web server (nginx, Apache, or `php -S` for testing)
- About 20 minutes

---

## 1. Start the Server

```bash
heliosdb-nano start \
  --data-dir ./wp-data \
  --mysql \
  --mysql-socket /tmp/heliosdb-mysql.sock
```

The Unix socket path is the recommended setup for same-host PHP + WordPress:

- No TCP overhead.
- PHP `mysqli` connects to a path string starting with `/`.
- Permissions are set `0o777` so the web user can connect without root.

For multi-host or containerised deployments, use TCP:

```bash
heliosdb-nano start --data-dir ./wp-data --mysql --mysql-listen 0.0.0.0:3306
```

---

## 2. Bootstrap the WordPress Database

```bash
mysql --socket=/tmp/heliosdb-mysql.sock <<'SQL'
CREATE DATABASE IF NOT EXISTS wordpress;
SQL
```

WordPress will create its own tables on first install — the tables created by `dbDelta()` use MySQL DDL that gets translated automatically:

```sql
CREATE TABLE wp_options (
  option_id    BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  option_name  VARCHAR(191) NOT NULL DEFAULT '',
  option_value LONGTEXT NOT NULL,
  autoload     VARCHAR(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (option_id),
  UNIQUE KEY option_name (option_name)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

What HeliosDB does with this:

- `BIGINT(20) UNSIGNED... AUTO_INCREMENT` → `BIGSERIAL`
- `VARCHAR(191)` → `TEXT` (HeliosDB has no length cap, the value is preserved on the column for catalog lookups)
- `LONGTEXT` → `TEXT`
- `DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci` → stripped (HeliosDB is UTF-8 native)
- `PRIMARY KEY (option_id)` is propagated to the column's `primary_key` flag so `LAST_INSERT_ID()` returns the new id
- `UNIQUE KEY option_name (option_name)` becomes `UNIQUE(option_name)`

---

## 3. Configure `wp-config.php`

Edit the WordPress source's `wp-config.php`:

```php
<?php
/** Database name */
define( 'DB_NAME', 'wordpress' );

/** Database user — any string; auth defaults to permissive */
define( 'DB_USER', 'root' );

/** Database password — match --auth/--password if set, else empty */
define( 'DB_PASSWORD', '' );

/**
 * Database host — choose ONE:
 *   1. Unix socket (recommended for same-host installs):
 */
define( 'DB_HOST', 'localhost:/tmp/heliosdb-mysql.sock' );
/**
 *   2. TCP:
 *
 * define( 'DB_HOST', '127.0.0.1:3306' );
 */

/** Charset — wpdb expects utf8mb4 */
define( 'DB_CHARSET', 'utf8mb4' );

/** Collation — empty is fine; HeliosDB ignores it */
define( 'DB_COLLATE', '' );

/** Table prefix — leave default */
$table_prefix = 'wp_';

/** WordPress secret keys — generate fresh ones */
define( 'AUTH_KEY',         'put-your-unique-phrase-here' );
define( 'SECURE_AUTH_KEY',  'put-your-unique-phrase-here' );
// ... (the rest as generated by wordpress.org/secret-key/1.1/salt/)

if ( ! defined( 'ABSPATH' ) ) {
    define( 'ABSPATH', __DIR__ . '/' );
}
require_once ABSPATH . 'wp-settings.php';
```

The host string `localhost:/tmp/...` is the format `mysqli` expects for socket connections. The literal `localhost` keyword tells mysqli to use the path that follows the colon as a socket.

---

## 4. Run the Installer

Browse to `http://your-server/wp-admin/install.php` (or whatever host serves your WordPress source). The installer:

1. Probes the connection — calls `mysqli_connect()` and `SHOW TABLES`.
2. Creates the WordPress schema via `dbDelta()` — issues 20+ `CREATE TABLE` statements with MySQL DDL.
3. Populates `wp_options`, `wp_users`, `wp_usermeta`.
4. Calls `wp_insert_user()` for the admin account.

All of these go through the translator. The historically tricky steps, all handled in the current release:

| Step | How it is handled |
|------|-------------------|
| `LAST_INSERT_ID()` returning 0 after `wp_insert_user` | PK flag propagated from the table-level constraint |
| `WHERE ID = '1'` finding no row (string→int PK lookup) | String-to-integer coercion on PK lookups |
| Serialized PHP data with embedded `;` truncating mid-statement | Quote-aware statement splitter |
| Backslash escapes corrupting `mysqli_real_escape_string` output | Translator pass |
| `ON CONFLICT` falling back when conflict is on UNIQUE not PK | UNIQUE constraints are conflict targets |
| `meta_key` regex matching inside column names | KEY-index parsing no longer matches inside column names |
| Implicit comma joins for `_update_post_term_count` | Implicit comma joins supported |
| `ALTER TABLE ADD KEY... (col(191))` schema check | Prefix-length keys accepted |

On the current release the installer completes without errors. Older releases may need workarounds; upgrading is the recommended path.

---

## 5. Verify the Install

```bash
mysql --socket=/tmp/heliosdb-mysql.sock wordpress -e "SHOW TABLES;"
```

```
+---------------------+
| Tables_in_wordpress |
+---------------------+
| wp_commentmeta      |
| wp_comments         |
| wp_links            |
| wp_options          |
| wp_postmeta         |
| wp_posts            |
| wp_term_relationships |
| wp_term_taxonomy    |
| wp_termmeta         |
| wp_terms            |
| wp_usermeta         |
| wp_users            |
+---------------------+
```

```bash
mysql --socket=/tmp/heliosdb-mysql.sock wordpress \
  -e "SELECT option_name FROM wp_options WHERE autoload='yes' LIMIT 10;"
```

Confirm the admin user exists:

```bash
mysql --socket=/tmp/heliosdb-mysql.sock wordpress \
  -e "SELECT ID, user_login, user_email FROM wp_users;"
```

---

## 6. The Patterns That Work Out of the Box

These are the WordPress core query patterns and the HeliosDB feature that supports each.

### `LAST_INSERT_ID()`

```sql
INSERT INTO wp_posts (post_title) VALUES ('Hello world');
SELECT LAST_INSERT_ID();   -- returns the new ID
```

Tracked per MySQL connection. Used by `wp_insert_post`, `wp_insert_user`, `wp_insert_term`.

### `ON DUPLICATE KEY UPDATE`

```sql
INSERT INTO wp_options (option_name, option_value, autoload)
VALUES ('siteurl', 'http://example.com', 'yes')
ON DUPLICATE KEY UPDATE option_value = VALUES(option_value);
```

Translated to native PostgreSQL `ON CONFLICT DO UPDATE SET option_value = EXCLUDED.option_value`. Works for both PK and UNIQUE conflicts. Used by `update_option`, transient API, rewrite rules, cron events.

### `SHOW TABLES` / `SHOW COLUMNS` / `SHOW FULL COLUMNS`

```sql
SHOW FULL COLUMNS FROM wp_options;
```

Returns all 9 MySQL fields including `Collation` (`utf8mb4_unicode_ci`), `Privileges`, and `Comment`. `wpdb::get_col_charset()` reads these to format prepared statements; without them WordPress falls back to bypass mode.

### `SHOW INDEX FROM`

```sql
SHOW INDEX FROM wp_options;
```

Returns PRIMARY entries plus UNIQUE-constraint indexes. Used by `dbDelta()` to determine whether an index already exists before issuing `ADD INDEX` (which would otherwise error on duplicate).

### Serialized PHP data

WordPress stores serialized PHP arrays in `wp_options.option_value` and `wp_usermeta.meta_value`:

```
a:1:{s:13:"administrator";b:1;}
```

Embedded `;` characters used to split statements prematurely and embedded `\"` used to corrupt the value in older releases. Both are now handled at the translator layer.

### `$wpdb->prepare()` quoting

```php
$wpdb->prepare("SELECT * FROM wp_users WHERE ID = %s", 1);
// Emits: SELECT * FROM wp_users WHERE ID = '1'
```

The PG-side ART index lookup coerces `'1'` to `Int8(1)` so the query finds the row.

---

## 7. Plugins and Themes

The patterns above cover WordPress core. Most plugins use the same `wpdb` API; if a plugin runs custom DDL, that DDL goes through the same translator. Specific patterns reported as working:

- **Multi-table `DELETE`** — translated to `DELETE... WHERE id IN (subquery)`
- **Implicit comma joins** (`FROM t1, t2 WHERE t1.id = t2.id`) — `_update_post_term_count`
- **`ALTER TABLE ADD KEY <col>(<n>)`** — prefix lengths silently accepted
- **`USE database`** — accepted at SQL level (older releases accepted it only as COM_INIT_DB)

If a plugin requires a feature not yet supported, the failure surfaces as a clear MySQL error code rather than a silent corruption.

---

## 8. Backups and Branching

The full `wp_*` schema is just a HeliosDB database. You get the engine's other features for free:

```bash
# Snapshot the live site
heliosdb-nano dump --data-dir ./wp-data --output wp-backup.heliodump

# Clone the live site to a staging branch
psql "postgresql://postgres@127.0.0.1:5432/wordpress" \
  -c "CREATE BRANCH staging FROM main;"
```

Switch to the staging branch from the MySQL session:

```sql
USE BRANCH staging;
```

Updates on `staging` are invisible to `main` until `MERGE BRANCH staging INTO main`. Useful for trying a plugin upgrade without risking the live site.

Time-travel queries (`AS OF TIMESTAMP '...'`) and audit logging also apply to the WordPress tables — every change is recoverable and auditable.

---

## Troubleshooting

| Symptom | Cause | Fix |
|---------|-------|-----|
| Installer hangs at "Creating database tables" | Older statement splitter breaks on serialized data | Upgrade to the current release |
| `wp_options` has rows but `get_option()` returns false | Older releases missed ON DUPLICATE KEY conflicts on UNIQUE | Upgrade to the current release |
| Admin login redirects to login page (loop) | `wp_capabilities` not written: string→int PK coercion bug in older releases | Upgrade to the current release |
| `mysqli` connect fails with "No such file or directory" | Wrong socket path in `DB_HOST`, or server started without `--mysql-socket` | Verify path with `ls /tmp/heliosdb-mysql.sock`; restart with the flag |
| Plugin reports "Unknown collation" | Plugin reads `information_schema.COLLATIONS` (limited surface in Nano) | Most plugins fall back; if blocking, file the specific query |

---

## Where Next

- [MYSQL_WIRE](https://www.heliosdb.com/docs/nano/guides/tutorials/mysql_wire_tutorial/) — full MySQL protocol details, every translator pass.
- [UNIX_SOCKETS_QUICKSTART](https://www.heliosdb.com/docs/nano/guides/quickstart/unix_sockets_quickstart/) — same-host MySQL without TCP.
- [BAAS_REST_API](https://www.heliosdb.com/docs/nano/guides/tutorials/baas_rest_api_tutorial/) — expose the WordPress data via REST without writing PHP.
- [GETTING_STARTED_TUTORIAL](https://www.heliosdb.com/docs/nano/guides/tutorials/getting_started_tutorial/) — first-time setup walkthrough.