Keeping the Users Table Alive When db-push Swaps the Database

A routine db-push finished, everything looked done, and then I tried to log into the KotaPortal staging admin. Login failed. The users table was completely empty: the test accounts that existed yesterday were gone without a trace and without a single error message.

My first guesses: a corrupt script, or someone dropping tables for fun. Both wrong. The pattern was too consistent: the loss always coincided with a db-push, and always the same table. The culprit was the db-push itself: the whole staging database gets replaced with a dump from the development environment, and that dev dump deliberately excludes the users table. Every push is a full database swap, and staging accounts were its routine, unrecorded casualties.

Two Dumps, Two Sources of Truth

The fix is not excluding users from the replacement, because the full import is still needed. It is two small dumps: schema and all other data stay from dev, while the users data is rescued from staging before the DROP runs, then restored after the import. mysqldump already carries the right knives: --no-create-info writes no CREATE TABLE statements, pure data with no table definitions [1], and --no-data is its mirror for schema without contents.

For the users dump the key is --complete-insert: INSERT statements that name their columns [1]. That is where schema compatibility happens. The staging schema trails one column behind dev, and with explicit column names, 19 staging accounts import cleanly into the wider dev schema. The extra column takes its DEFAULT. No conversion script to rewrite every time a schema drifts.

One reframing I took from this incident: a full dump is not a backup, it is a replace. As long as dev and staging databases are not identical, a full swap will eat whatever lives on only one side. The users table just happened to hurt most because admin accounts vanished with it, but the same pattern can hit environment configuration tables, staging feature flags, or any local reference data that never rides along with the dev schema.

The order of operations is its own lesson. The users data dump must complete and pass its sanity checks before any DROP runs, not after. That ordering is what separates a script that fails safe from one that fails while destroying: if the dump comes back empty because SSH dropped mid-transfer, the staging database is still intact and the push can be retried. In the reverse order, accounts are gone permanently and recovery is a prayer pointed at an old backup.

Guards Paid for With Readability

I add --skip-extended-insert, which forces one INSERT per row [1]. The file grows, but the guard gets cheap: a truncated dump is detected by the missing mysqldump header at the end, no SQL parsing needed. The failure chain is layered: the users dump fails, the process stops before DROP executes; the dump is detected truncated, it stops too; the restore fails after the swap, the script exits 1 while pointing at the pre-push backup as the manual recovery path. The worst failure never leaves staging empty without a door back.

One more spot people underestimate: the database name is interpolated straight into a remote SSH command, and ssh executes that command on the remote host with arguments joined by spaces [2]. Interpolation like that is a classic injection door, so the name is strictly restricted to the [A-Za-z0-9_] characters before it ever touches a command line. Validation, not escaping, and it has proven sufficient.

Finally, the users dump carries password hashes. From the second it exists, it is a secret artifact: chmod 600 restricts it to its owner, read and write [3], before the file moves anywhere. A database push is destructive work by nature, but with two small dumps and honest guards, the users table does not have to die every time dev ships code. The final test was unglamorous: run the push, 19 staging accounts come back automatically, admin login works with no manual re-creation. The staging schema still at 13 columns does not matter, because column-named INSERTs sail past it. The next db-push is just a routine, not a tense moment.

One small habit stayed with me after the incident: every script that touches a database over SSH now runs the same checklist in my head. What artifact is produced before the destructive step, what proves that artifact is intact, and where to point when the last step fails. Those three answers separate automation you can sleep on from automation you have to babysit.

## Sources [1] https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html [2] https://man7.org/linux/man-pages/man1/ssh.1.html [3] https://man7.org/linux/man-pages/man1/chmod.1.html