Failed Prisma migration locks out machines

[ziding]

Good evening Fly community.

My issue stems from deploying a problematic Prisma/sqlite migration, which has since totally blocked the starting of machines. I am thus unable to SSH console for debugging, SFTP to export my data, and most importantly, deploy a fix. I cannot backup the data without a machine to do so with.

I must emphasise that machines do not seem to start; mounting its volume throws an error, triggering a restart, leading to the ‘appears to be crashing’ error. Therefore, machines do not reach the necessary state to deploy a fix.

Here’s a summary of the issue:
This is the beginning of the monitoring logs

1. Mounting /dev/vdb at /data w/ uid: 0, gid: 0 and chmod 0755
2. Preparing to run: /app/docker-entrypoint.js npm run start as root
3. Datasource “db”: SQLite database “sqlite.db” at “file:///data/sqlite.db”
4. Error: P3009 - migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: Migration troubleshooting in production

My docker-entrypoint.js runs npx prisma migrate deploy, and throws “WARN Reaped child process with pid: 357 and signal: SIGTERM, core dumped? false” when that fails.

It seems that no matter what code I change, Dockerfile, fly.toml, or prisma/*, the same error persists. This makes me suspect that I have bricked the volume, which blocks the usage of machines.

Deploying to a new machine and volume on a separate app works fine, proving that my migration fixes are OK. Yet the original problem remains: how can I backup my volume data without a working machine?

Thank you for your time and consideration; Fly has only been a pleasure to use.

[ziding]

Solved.

Context: Remix, using Docker and Prisma/sqlite, deploying to Fly.io with mounted volumes.

If you have a docker-entrypoint.js, the issue lies here.

#!/usr/bin/env node

import { spawn } from 'node:child_process'

const env = { ...process.env }

;(async() => {
  // If running the web server then migrate existing database
  if (process.argv.slice(2).join(' ') === 'npm run start') {
    await exec('npx prisma migrate deploy')
  }

  // launch application
  await exec(process.argv.slice(2).join(' '))
})()

function exec(command) {
  const child = spawn(command, { shell: true, stdio: 'inherit', env })
  return new Promise((resolve, reject) => {
    child.on('exit', code => {
      if (code === 0) {
        resolve()
      } else {
        reject(new Error(`${command} failed rc=${code}`))
      }
    })
    resolve()
  })
}

Your Dockerfile’s entrypoint is this file, which executes a Prisma migrate deploy if npm run start was run before it. Convenience of automatically performing migrations is nice, but if this command fails, the Promise inside exec() rejects with an error containing ...failed rc=.... This rejection causes a SIGTERM, shutting the machine, leaving you in the turmoil I was in.

If you’ve made the mistake of misdeploying/hacking a solution to a problematic migration and end up in a state like mine, here’s how to fix it:

1. Comment out child.on() in exec()
2. Ensure the Promise resolves by returning resolve(). This will let the machine start up so you may interact with your data
3. Redeploy, your machine should now start up without issue.
4. fly ssh console, then sqlite3 <path_to_database.db> to open sqlite3 cli
5. Locate the offending migration SELECT name, sql FROM sqlite_master WHERE type='table' AND name='_prisma_migrations';. Note its id
6. Delete the offending migration DELETE FROM _prisma_migrations WHERE id = '<migration_id>';
7. Reconcile your prisma/migrations directory as per Prisma troubleshooting. I strongly recommend following their guidelines carefully before hacking your own way.
8. Redeploy, and back up your data. fly sftp get <path_to_database> <local_target_path> is speedy.

If the data is important, let it not exist precariously. For neglible cost, backups save lives; unrivalled value, so do it early and do it often. Today I learned.

Thanks For Coming To My TED Talk