Binary Query Engine keeps running even after the parent process is killed when using `migrate reset` or `db seed`

[bencun]

Bug description

The issue is in that the query engine processes keep the connection pool open even when the parent Node process crashes/is killed. This means massive memory and connection leaks in production if your application is known to crash and is restored automatically by some other entity. This usually results in too many open connections and your DB rejecting any additional connection requests and

On my MBP query-engine-darwin keeps running long after my NestJS app process is gone. It even happens after running some of the migrations related stuff, e.g. prisma migrate reset --preview-feature.

Caching the Prisma instance resolves this only for use cases described in prisma/prisma#1983, e.g. hot reloading in Node apps such as NestJS.

How to reproduce

Run prisma migrate reset --preview-feature multiple times and check the running processes on your system. Make sure to have a seed file that establishes a connection to the database.

Expected behavior

All native processes should end after the parent Node process has quit.

Prisma information

Any schema connecting to the PostgreSQL DB in a container.

Environment & setup

• OS: macOS
• Database: PostgreSQL
• Node.js version: 14.16.1
• Prisma version: 2.22.1

  prisma               : 2.22.1
  @prisma/client       : 2.22.1
  Current platform     : darwin
  Query Engine         : query-engine 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/query-engine-darwin)
  Migration Engine     : migration-engine-cli 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/migration-engine-darwin)
  Introspection Engine : introspection-core 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/introspection-engine-darwin)
  Format Binary        : prisma-fmt 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/prisma-fmt-darwin)
  Default Engines Hash : 60cc71d884972ab4e897f0277c4b84383dddaf6c
  Studio               : 0.379.0

[janpio]

Thank for the issue @bencun - that indeed does not sound great and is definitely not intended. Usually we try to clean up after ourselves of course.

Run prisma migrate reset --preview-feature multiple times and check the running processes on your system. Make sure to have a seed file that establishes a connection to the database.

Interesting. Can you maybe share a project with a minimal seed file you are using?

Are you observing this in any other cases, when not using seeding via migrate? That could help us pinpoint what is going on.

[bencun]

@janpio Yes, as described this also happens when I kill my app's Node process with Ctrl+C (it's a NestJS app) or even force quit it through the Activity Monitor. The Query Engine process keeps running.

Interestingly it doesn't happen when I run prisma migrate dev but it does when running prisma migrate reset.

I'll create a seed file that creates a reproducible case for the reset command very soon.

[bencun]

Here's the minimal reproducible seed.ts file. Running this seed results in a new query-engine-darwin process that doesn't end after the parent Node process ends:

import { Role, PrismaClient } from '@prisma/client';
import * as bcrypt from 'bcrypt';

const prisma = new PrismaClient();

async function main() {
  return await prisma.user.create({
    data: {
      email: 'test@test.com',
      password: bcrypt.hashSync('pw12345678', 10),
      role: Role.ADMIN,
      activated: false,
    },
  });
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(() => {
    console.info('Exiting...');
    process.exit(0);
  });

[janpio]

Yes, as described this also happens when I kill my app's Node process with Ctrl+C (it's a NestJS app) or even force quit it through the Activity Monitor. The Query Engine process keeps running.

That is also not really as expected. But let's investigate the seeding weirdness here first, otherwise we get confused what we are talking about. (If you want, you can open another issue about this [and add "after seeding" to the title of this one] and describe just the Ctrl+C problem you are experiencing - we can do many issue in parallel, but only 1 topic per issue 😄)

@pantharshit00 will try to reproduce with your script and report back.

[bencun]

Sure, but I added that info here since I made an educated guess that the core issue is the same in both cases - regardless of whether it's the seed.ts running or any other Node app that uses Prisma - the Query Engine should terminate when the parent process terminates. If you guys manage to figure it out and it turns out that it is completely unrelated I'll be glad to investigate more and open a new issue with new reproducible steps.

[bencun]

Additional info: I can't reproduce this behaviour on a Windows 10 machine:

@prisma/client       : 2.22.1
Current platform     : windows
Query Engine         : query-engine 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules\@prisma\engines\query-engine-windows.exe)
Migration Engine     : migration-engine-cli 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules\@prisma\engines\migration-engine-windows.exe)
Introspection Engine : introspection-core 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules\@prisma\engines\introspection-engine-windows.exe)
Format Binary        : prisma-fmt 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules\@prisma\engines\prisma-fmt-windows.exe)
Default Engines Hash : 60cc71d884972ab4e897f0277c4b84383dddaf6c
Studio               : 0.379.0

[pantharshit00]

I am also on a windows machine and I wasn't able to reproduce this on Windows. I will try this again on Mac.

[pantharshit00]

@bencun Can you please try 2.23 on Mac? I wasn't able to reproduce this on Mac.

[bencun]

Confirmed, still happens on my machine with 2.23 Node 14.16.1 npm 6.14.13 macOS 11.3.1

Running migrate reset and db seed always results in a hanging query-engine-darwin process for me.

I'll try testing on another Mac.

[bencun]

Tested on another MBP 16" running macOS 11.3.1 - bug reproduced.

[pantharshit00]

Hey I tried this again and I was able to reproduce this:

Looks like I accidentally pass wrong args to ps last time. Apologies for that.

I posted a simple reproduction here for team member to look at: https://github.com/harshit-test-org/prisma-issue-7020

[klausbadelt]

Manually disconnecting i.e. adding .finally(async () => prisma.$disconnect()) solved it for us. Here's a much simplified version of our seed.js:

const { PrismaClient } = require('@prisma/client')
const userSeeds = require('./seeds/users')

const prisma = new PrismaClient({ log: ['query', 'warn', 'error'] })
const truncateDb = async () => prisma.$executeRaw`TRUNCATE TABLE users CASCADE`
const seedUsers = async () => prisma.users.createMany({ data: userSeeds })

const seed = async () => {
  truncateDb()
    .then(seedUsers)
    .catch(console.error)
    .finally(async () => prisma.$disconnect())
}

module.exports = {
  seed,
  default: seed,
}

[Jolg42]

Changed issue to docs issue since it's working as expected and adding .finally(async () => await prisma.$disconnect()) is what needs to be done during seeding to stop Prisma Engine.

[bencun]

@Jolg42 This doesn't seem to be a seed issue only. Please, pay attention to the original comment: this is also relevant in any other use case, not just the seeding. In our case it was a NestJS app that had a proper $disconnect() call when an app is destroyed. That call did nothing on our end and, therefore, even after our app exits properly we still get a hanging query engine process. Mac only, on Windows there's no such issue.

[janpio]

Note: binary query engine is not super relevant any more, so could potentially be skipped.

[craig-pyrra]

I'm seeing this issue, too, with my unit tests running with Vitest. Currently I am (very hackily, because I don't have time to look into the problem) running exec('killall query-engine-darwin-arm64') in a global teardown function. @janpio what did you mean by

Note: binary query engine is not super relevant any more, so could potentially be skipped.

[janpio]

@janpio what did you mean by

Note: binary query engine is not super relevant any more, so could potentially be skipped.

Prisma Client does not run the binary engine as default any more since a long time. We have only a few users still using it. If you are using it, you should have a very good reason. It is inferior in many ways to the default engine that Prisma runs for normal usage.

[craig-pyrra]

Ok, thanks, I was unaware the binary engine was not recommended. I don't remember why I was using it (something to do with building AMD64 Docker containers on Apple silicon?) but I'll get rid of it.

[badoet]

hi, we running into the same issue as well, mind sharing how to not run this binary engine and use the library instead? is it by default running library engine instead of binary engine?

[janpio]

That is the default behavior of using Prisma Client in any recent version. There should be no running query-engine binary engine.