Twelve entries locally, eight on the VPS

I was going through the knowledge endpoint on the VPS one entry at a time, checking it against my local copy, and the counts didn't line up. Twelve entries locally. Eight on the server. Same code, same schema, nominally same database.

My first guess was boring and wrong: a forgotten manual INSERT. So I dug through the migration history on the VPS looking for a failed import. Nothing. Every migration had run clean on both sides.

The real answer was dumber than that. The missing entries had never been in any migration. I had typed them by hand, straight into the local endpoint over REST. Hand-typed data doesn't travel. It lives in exactly one database, and the only way it reaches the VPS is if future-me remembers it exists and re-types it.

A seed file that runs on every container start

The fix moved baseline data into the repo. Six entries in knowledge_seed.json, a small seed_knowledge.py that reads it, and five lines added to the entrypoint script so the seeder runs on every container start, right after create_all() creates the schema. The core of the seeder is one statement per row:

INSERT INTO knowledge_entries
    (slug, title, category, content, keywords, is_active)
VALUES
    (:slug, :title, :category, :content, :keywords, :is_active)
ON DUPLICATE KEY UPDATE
    title = VALUES(title),
    content = VALUES(content)

MariaDB documents INSERT ... ON DUPLICATE KEY UPDATE as an upsert: when the statement hits a duplicate unique or primary key, the insert turns into an update [1]. The key choice matters more than the syntax. Slug is a natural key: the same entry always has the same slug, a new entry gets a new slug. The auto-increment primary key can't play that role because its value differs per environment — the same row can be ID 15 locally and ID 8 on the VPS.

The ground rules are two. The repo wins for slugs defined in the seed file: every restart, seed values overwrite the table. Slugs added later over REST are never touched, an upsert by slug never deletes rows. And if seeding fails, the entrypoint just prints a note and keeps booting; the next restart tries again.

The affected-rows number I had backwards

While testing, I assumed an affected-rows value of 2 meant two rows had changed. That's the contract of this statement: 1 if the row was inserted as a new row, 2 if an existing row was updated [2]. One upsert touches one row but reports two. I almost shipped a log line that would have lied.

One more detail worth pausing on: 0 shows up when an existing row is set to the exact values it already had, unless the connection sets the CLIENT_FOUND_ROWS flag, which reports 1 instead [2].

A third trap to keep in mind: don't use this statement on a table with more than one unique index. When two unique indexes match, only the first one gets updated [1]. Slug is this table's only unique index, so the pattern is safe here.

VALUES() is fine in MariaDB, deprecated in MySQL

The statement above deliberately uses VALUES(title). In MariaDB, which this project runs, the pattern is still valid and still the documented reference [1]. In MySQL, though, using VALUES() to reference the new row is deprecated as of 8.0.20, and the replacement is a row alias written as AS new [2].

So I didn't rush to change anything, but I wrote it down. If this table ever migrates to plain MySQL, every VALUES(...) in the update clause needs reworking to the alias form.

The bigger lesson comes from Twelve-Factor: backing services in development and production should be the same type and version, because small incompatibilities tend to surface as bugs that never showed up locally [3]. The seeder in the entrypoint is the practical application. As long as both sides run the same seed file, baseline data converges without manual steps. A cousin problem, naive datetimes from MariaDB making API timestamps wrong, is in an earlier post.

Setting up the VPS is now: pull the repo, start the container. The knowledge entries that went missing come back on boot, without a single line of SQL typed by hand.

Sources:

• MariaDB documentation, INSERT ON DUPLICATE KEY UPDATE [1]
• MySQL 8.4 Reference Manual, INSERT ... ON DUPLICATE KEY UPDATE Statement (read via Wayback snapshot 2026-08-06) [2]
• The Twelve-Factor App, Dev/prod parity [3]