# PostgreSQL to MySQL Moved 24 August 2026. This is what changed, what had to be rebuilt because MySQL has no equivalent, and what still has to happen on the development server. ## Configuration `.env` and `config/database.php` now point at MySQL. Three settings are not defaults and should not be changed without reading why: | Setting | Value | Why | |---|---|---| | `collation` | `utf8mb4_unicode_ci` | Case-insensitive, which is what makes plain `LIKE` behave as Postgres `ILIKE` did. Every search in the application depends on it. | | `strict` | `true` | Without it MySQL truncates over-long strings and turns bad dates into `0000-00-00` **silently**. On an accounting system that is a wrong figure stored with nothing raised. | | `engine` | `InnoDB` | Named rather than left to the server default. The schema has foreign keys throughout and taking rent writes five records in one transaction; MyISAM supports neither and would create the tables without a word. | There is also a second connection, `mysql_sequences`, pointing at the same database. It exists for one thing — see *Entry numbering* below. ## Server requirements **`max_allowed_packet` must be at least 4 MB.** The default of 1 MB is not enough. Member certificates and property photographs are stored as base64 in json columns, and a single member row here reaches **1.3 MB**. Below the limit MySQL does not merely refuse the insert — it drops the connection. ```ini [mysqld] max_allowed_packet=64M ``` On cPanel this goes through *MySQL Databases → Server Configuration*, or a support request. It is a runtime requirement, not just a migration one: saving a member with documents fails without it. ## What had to be rebuilt ### Partial unique indexes → generated columns MySQL has no partial indexes. Two guarantees relied on them: - a payment mode's name is unique **among rows that are not soft-deleted** - **at most one** payment mode is the default Both are now generated columns that are `NULL` where the old `WHERE` clause excluded the row. A unique index ignores NULLs, so the effect is identical: ```sql name_when_live = case when deleted_at is null then name end sole_default = case when is_default = 1 and deleted_at is null then 1 end ``` Verified against both cases each must handle — refusing a duplicate live name while allowing any number of deleted ones, and refusing a second default while letting the default move between modes. ### Sequences → a counter table `EntryNumberGenerator` produced accounting entry codes from a Postgres sequence per (prefix, month). The sequence gave three properties, and the third is easy to lose: - **atomic** — two callers never get the same value - **durable** — survives the gap before the caller's INSERT - **independent of the caller's transaction** — several call sites number an entry *after* their own `commit()` MySQL has no sequences. The replacement is `entry_number_sequences`, one row per bucket, incremented with a single statement: ```sql INSERT INTO entry_number_sequences (name, value) VALUES (?, LAST_INSERT_ID(1)) ON DUPLICATE KEY UPDATE value = LAST_INSERT_ID(value + 1) ``` run on the `mysql_sequences` connection so a rollback in the caller cannot hand the number back. **That table has no `id` column, deliberately.** With an AUTO_INCREMENT column, `LAST_INSERT_ID()` returns the new row's id in preference to the value set by `LAST_INSERT_ID(expr)` — and every bucket silently shared one counter. August started at 4, a new prefix continued another's series, and sixty codes came back with a duplicate. That is precisely the failure this class was written to prevent (four unrelated entries once shared `REC260700027`), so it is tested directly, rollback included. ### Postgres `text` → `mediumText` Postgres `text` is unlimited. MySQL `TEXT` stops at 65,535 bytes, and `->text()` maps to the smallest size. Four columns hold base64 images — `members.member_photo` is 406 KB — and are now `mediumText` (16 MB). Strict mode is what surfaced this: it refused the insert. Without strict mode MySQL would have truncated the photograph and reported success. ### Everything else | Was | Now | Count | |---|---|---| | `ilike` | `like` | 67 | | `string_agg(x, ', ')` | `group_concat(x separator ', ')` | 2 | | `ledger_id::integer` | removed | 23 | | `$table->jsonb()` | `$table->json()` | 13 | | `UPDATE … FROM` | query-builder join | 2 | The casts were **removed rather than translated**: the column is `bigint` and so is `ledgers.id`, so the cast was a leftover from an older schema. Verified against real data first — identical rows and sums with and without. Every `*_id` column is now **unsigned**. MySQL refuses a foreign key whose column differs in signedness from its target, and `$table->id()` is `BIGINT UNSIGNED`. Postgres has no unsigned integers, so nothing in the source schema said which those were; checked before relying on it that none of the 125 id-like columns holds a negative. ## Migrations The 15 pg_dump-derived migrations — `CREATE SEQUENCE`, `USING btree`, `::regclass` defaults, a PL/pgSQL trigger — are now Schema builder, generated from the live schema rather than transcribed so no column could change type on the way across. Four were deleted outright: - `create_update_updated_at_column_function` — a PL/pgSQL trigger. Eloquent stamps `updated_at` in PHP; the triggers existed because the source system wrote rows outside the ORM - `create_reconciliation_enum_types` and `add_missing_reconciliation_enum_labels` — `CREATE TYPE … AS ENUM`. The labels are now inline on the columns - `convert_user_reference_columns_to_bigint` — already reflected in what the rebuilt migrations declare ## Deploying: the whole sequence Order matters, and one step will silently undo another if it comes too early. ```bash # 1. .env — DB_CONNECTION=mysql, host, port 3306, database, user, password. # Also set these, or the admin account is created as admin@kskb.local: # ACCOUNTS_ADMIN_EMAIL=admin@kskb.com.my # ACCOUNTS_ADMIN_PASSWORD=... (a random one is printed if unset) php artisan config:clear # 2. so the new .env is actually read php artisan migrate --force # 3. the schema php artisan db:seed --force # 4. chart of accounts, permissions, # email templates, first admin php artisan accounts:open-year --from=2026-01-01 --to=2026-12-31 php artisan db:copy-from-postgres \ # 5. the data --pg-database=graspsoft_kskb \ --pg-username=graspsoft_kskb_user \ --pg-password=secret php artisan db:verify-copy --pg-database=graspsoft_kskb ... # 6. proof php artisan optimize # 7. LAST — see below ``` **`optimize` goes last.** It writes `bootstrap/cache/config.php`, and once that file exists `.env` is never read again — so a database name changed afterwards is ignored and `migrate` would build the schema somewhere unintended. Re-run `config:clear` before any later `.env` edit, and `optimize` again after. **The accounting year is not optional.** Without one, `/accounts/entries` and the reports answer *"No active accounting year found"* rather than 404-ing mysteriously, but the screens are still empty. `accounts:open-year` creates and activates it. **Step 5 is easy to miss.** The migrations rebuild the schema; nothing in them moves data. Without it the server comes up correctly configured and empty. The copy converts booleans, re-encodes json, disables foreign key checks and shrinks its batches when MySQL refuses one. It refuses to run against a database holding transactional data unless `--force` is given — seeded reference tables do not trip it, since the documented order fills those first. ### What the copy does not touch, and why It copies a table by **emptying it and refilling it from the source**. Three groups of tables are excluded, and each exclusion was written after the alternative broke the development server. **Runtime state** — `cache`, `cache_locks`, `sessions`, `jobs`, `job_batches`, `failed_jobs`, `password_reset_tokens`. `cache` is the dangerous one: with `CACHE_STORE=database`, Spatie keeps its whole permission map there, described by permission **id**. Carrying PostgreSQL's copy across leaves every role check reading ids from the other database. **Authorisation** — `roles`, `permissions`, `role_has_permissions`, `model_has_roles`, `model_has_permissions`. These are seeded from code, and the seeder owns their ids. Emptying them and refilling from a source that has fewer rows leaves the seeded rows deleted with nothing in their place, and **both halves succeed**, so there is no error to see. The symptom is an administrator who exists, looks entirely correct, and holds no role: ``` Spatie\Permission\Exceptions\UnauthorizedException "User does not have any of the necessary access rights." ``` Role assignments are still carried across — they are per-user data — but matched on **role name and guard** afterwards, because the source's `role_id` points at whatever the seeder happened to create in that position here. **The seeded administrator.** `users` *is* copied, so an old database with no account at `ACCOUNTS_ADMIN_EMAIL` would delete the only one that can sign in. It is captured before the copy and put back if the source did not supply it. If the copy finishes with nobody holding any role at all, `super_admin` is granted to that account and said so plainly — an installation with no administrator cannot be repaired from inside the application. The copy ends by reporting whether anyone can actually sign in. Read that line. ### If the accounts screens answer 403 ```bash php artisan accounts:check-access admin@kskb.com.my ``` Reports the four things that must line up — the account exists, a role is attached, the role carries the permission, and the guard is `api` — plus whether the cached map disagrees with the tables. It names the command to run for whichever one is wrong, and distinguishes an empty `roles` table (re-seed first) from a missing attachment (grant it). ```bash php artisan db:seed --force # rebuilds roles and permissions; also # restores super_admin when NOBODY holds any php artisan accounts:user --role=super_admin ``` `accounts:user` changes only what you ask for: the role always, the name and password only with `--name` / `--password`. Granting a role back does not rename the account or invalidate the password. `db:verify-copy` compares row counts table by table and **names the missing ids** rather than reporting a discrepancy in a total — which is how the one member row that exceeded `max_allowed_packet` was found. It skips the same tables the copy skips and says which; without that it reported every deliberate exclusion as a failure and exited non-zero on a copy that was entirely correct. Delete both commands once the last database is across. ## Verified 179 checks across seven suites, on a database built from the migrations and loaded by the copy: ``` payment_modes 44 own_use 28 payments_vs_report 12 multi_property_payment 36 rent_receipts 31 vacate_flow 23 whole_counts 5 ``` Plus the two rebuilt guarantees and the entry numbering, each tested against the specific way it could silently fail.