prisma: prepared statement \"s0\" already exists
Bug description
I’m connecting to Supabase with their connection pooling enabled and I’m seeing the following 500 errors after multiple refreshes of the same page, until eventually it forces me to have to restart my server.
Supabase Connection Pooling
Connection String (Pool Mode = Transaction):
postgres://postgres:[YOUR-PASSWORD]@[host].supabase.co:6543/postgres
With Supabase’s connection pooling I still see the 500 error that is shown below.
Prisma PGBouncer
Connection String:
"postgresql://postgres:[PASSWORD]@[HOST].supabase.co:5432/postgres?pgbouncer=true"
With Prisma’s PG Bouncer I am still getting the same error after multiple refreshes of the same page. This is very problematic because very quickly I am getting these errors. Even when testing locally.
Error:
"PrismaClientUnknownRequestError: Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(
DbError { severity: \"ERROR\", parsed_severity: Some(Error), code: SqlState(\"42P05\"),
message: \"prepared statement \\"s0\\" already exists\", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some(\"prepare.c\"), line: Some(447), routine: Some(\"StorePreparedStatement\")
}) }) })\n}"
How to reproduce
Expected behavior
Prisma queries should be executed normally when connected to a pgbouncer pool or to supabase’s connection pooling.
Prisma information
"@prisma/client": "^3.5.0",
Environment & setup
- OS: Mac OS 12.0.1
- Database: PostgreSQL 12
- Node.js version: v16.3.0
Prisma Version
3.5.0
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Reactions: 5
- Comments: 24 (8 by maintainers)
@woniesong92 I just encountered this same issue and stumbled upon this issue via googling the error
What I’ve found is, if you’ve ever accidentally connected to the without
?pgbouncer=trueadded to the connection string, you will keep getting this error (even after adding?pgbouncer=true) until you restart the Supabase project.TL;RD; Never connect to Supabase without
?pgbouncer=true. If you do by accident, restart the Supabase project.You need to combine the connection pooled connection string from Supabase (port
6543) with adding&pgbouncer=trueto the connection string to get rid of this problem. The addition to the connection string tells Prisma that it is talking to a server running PgBouncer - which is the case for Supabase’s connection pooled connection string of course.Can you confirm that solves the problem?
This problem turned out to ultimately be caused by usage of a pooled connection string (port
6543at Supabase) without supplyingpgbouncer=true. When that was added, the error went away.But the cause for this is a bit more complicated: @juanzgc has a Next.js app where he is using a
.env.localfile. When using the Prisma CLI, only the.envfile is loaded, and not the.env.local. Hence Juan created a.envwith a copy of the database. Then he later messed around with things, and thinking that Prisma would only use.env, only addedpgbouncer=trueto the connection string there. But his Next.js app on startup loads both.env.localand.env, which meant that Prisma in the end was not using the value from.envbut.env.localinstead - which was missing thepgbouncer=true. The weird thing now is that if you only have a.env.localfile, Prisma still complains about an env var not being set when running queries inside the Next.js app, although that clearly loaded the env var when starting. This is what caused him to be in this weird position where it was easy to get this wrong.@codingforfoodllc
In your supabase database settings, there’s a restart button
don’t know if anyone encounter the same issue, i used neon.tech for the server, and i add &pgbouncer=true at the end of DATABASE_URL in .env file, it solved the problem
Hi @janpio,
I ended up using your solution and now my connection string is:
postgres://postgres:[YOUR-PASSWORD]@[host].supabase.co:6543/postgres?pgbouncer=trueHowever, when using prisma locally, after multiple browser refreshes I encounter the issue once again:
prepared statement \\"s0\\" already exists\. I can confirm this does not occur in production, but only when testing locally. I’m not sure how to fix this and this has become a constant everyday issue.I’ll even restart my db server - and I will still face the same issue.
It seems odd because these errors are intermittent and only occur locally. In production all works fine.
I’ve seen the error message go up to:
prepared statement \\"s91\\" already exists\Hi @janpio,
Link to reproduction: https://github.com/juanzgc/repro-prisma-11643
I have also emailed you the credentials to the database. Feel free to share those internally as it’s a newly created db for the reproduction.
From my local testing - it took about 10-15 page refreshes and then the errors appeared.
It doesn’t seem that having the
prismaquery withinutilsDbactually matters, whether or not the prisma calls are decoupled, the errors still persist.I found this issue after running into this problem for the second time in a while and can confirm you must restart the supabase project after adding
?pgbouncer=trueto your connection string. Otherwise, you will keep getting the error.@janpio I just ran into the same issue and adding
?pgbouncer=trueto the connection string didn’t resolve it. Do you have any idea? I checked I was loading the right.envjan@prisma.io or @janpio here on Github or our public Slack. (My NextJS knowledge is minimal, so a fully set up repository would be very helpful.)