---
name: PG→MySQL dump export
description: Pitfalls when exporting the Postgres DB to a MySQL dump (scripts/export-mysql-dump.mjs) for the PHP app installer.
---

# PG → MySQL dump export

When generating a MySQL dump from the Postgres DB for the standalone PHP app:

- **Emit UNIQUE constraints, not just the primary key.** Drizzle/PG marks columns
  like `users.email`, `profiles.profile_id`, `users.phone` unique. If the dump omits
  them, the installer's admin "upsert" (`INSERT ... ON DUPLICATE KEY UPDATE`) silently
  inserts duplicate rows and `SELECT ... WHERE email=? LIMIT 1` login becomes
  nondeterministic.
- **TEXT columns need a key prefix length in MySQL.** A unique key on a `TEXT` column
  fails with "BLOB/TEXT column used in key specification without a key length". Emit
  `UNIQUE KEY name (col(191))`; 191 is the safe utf8mb4 prefix under the old 767-byte
  index limit.
- **Installer admin creation should be schema-agnostic.** Prefer explicit
  `SELECT id WHERE email=?` then `UPDATE` or `INSERT` over relying on a unique key, so
  re-running setup.php is deterministic regardless of constraints.

**Why:** code review caught that the first dump had only PKs; the admin upsert and
login both depended on a `users.email` unique that didn't exist in the dump.

**How to apply:** any time `scripts/export-mysql-dump.mjs` is changed or rerun, confirm
`UNIQUE KEY` lines appear (with `(191)` prefixes on TEXT cols) before shipping the dump
into `php-app/storage/schema.sql`.
