prisma
Raw query failed. Code: `22P03`. Message: `db error: ERROR: incorrect binary data format in bind parameter 1`
Bug description
Raw query using PostGIS functions fails with the following error when using float values as bind parameters.
Invalid `prisma.$queryRaw()` invocation:
Raw query failed. Code: `22P03`. Message: `db error: ERROR: incorrect binary data format in bind parameter 1`
How to reproduce
I have a raw query using a PostGIS function:
export const calculate = async (
longitude: number,
latitude: number,
radiusInMeters: number,
) => {
return prisma.$queryRaw<{ sum: number }[]>`
SELECT SUM("range")::text
FROM "Locations" AS s
WHERE ST_DWithin(s.coordinates, ST_MakePoint(${longitude}, ${latitude}), ${radiusInMeters})
`;
};
This works perfectly fine when called via a script, e.g.
calculate(10.1, 50.1, 5000).then(console.log);
Now, I'm using this function in a Next.js application (with tRPC) and the user provides the longitude and latitude parameters via a form. Whenever I send non-integer (float) numbers into the function, the query fails with:
Raw query failed. Code: `22P03`. Message: `db error: ERROR: incorrect binary data format in bind parameter 1`
Integer values work all the time.
I double-checked that the passed in values are indeed floats and even tried explicitly parsing them. I tried passing hard-coded float values from my API route to the function instead of the ones provided by the user via the form. I added type-casting to the SQL query to have the values interpreted as floats.
But no matter what I try, the error keeps popping up.
I went ahead and turned on query logging in my Postgres and for all non-working queries, the bind parameters are completely absent:
2022-12-04 09:47:55.791 UTC [73] LOG: statement: SELECT 1
2022-12-04 09:47:55.796 UTC [73] ERROR: incorrect binary data format in bind parameter 1
2022-12-04 09:47:55.796 UTC [73] CONTEXT: unnamed portal parameter $1
2022-12-04 09:47:55.796 UTC [73] STATEMENT:
SELECT SUM("range")::text AS sum
FROM "Locations" AS s
WHERE ST_DWithin(s.coordinates, ST_MakePoint($1, $2), $3)
And here's where things get really strange: After restarting my Next.js application the error suddenly went away. After more debugging, I found out that it depends on the order of putting in values. If I start my Next.js application and run the query with integer values (which always succeeds) and afterwards pop in a float value, the query breaks with the aforementioned error. If I then restart my Next.js application again and put in float values right from the start, the query works. So, my assumption is that this could be some internal optimization (caching of data types?) done by the Prisma engine?
Thanks for any hints!
Expected behavior
The raw query should work every time.
Prisma information
The schema looks something like this:
model Locations {
id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
range Decimal? @db.Decimal(16, 2)
coordinates Unsupported("GEOGRAPHY(Point)")?
@@index([coordinates], name: "coordinates_idx", type: Gist)
}
My prisma module used inside the Next.js application is based on the create-t3-app one:
import { PrismaClient } from '@prisma/client';
declare global {
// allow global `var` declarations
// eslint-disable-next-line no-var
var prisma: PrismaClient | undefined;
}
export const prisma =
global.prisma ||
new PrismaClient({
log:
process.env.NODE_ENV === 'development'
? ['error', 'warn'] // Add 'query' to log queries
: ['error'],
});
export * from '@prisma/client';
if (process.env.NODE_ENV !== 'production') {
global.prisma = prisma;
}
Environment & setup
- OS: macOS
- Database: PostgreSQL with PostGIS (Docker image:
postgis/postgis:14-3.3) - Node.js version:
v18.12.1 - Next.js version:
13.0.0 - tRPC version:
10.4.2 - TurboRepo:
1.6.3
Prisma Version
prisma : 4.7.0
@prisma/client : 4.7.0
Current platform : darwin-arm64
Query Engine (Node-API) : libquery-engine 39190b250ebc338586e25e6da45e5e783bc8a635 (at ../../node_modules/.pnpm/@[email protected]/node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine : migration-engine-cli 39190b250ebc338586e25e6da45e5e783bc8a635 (at ../../node_modules/.pnpm/@[email protected]/node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine : introspection-core 39190b250ebc338586e25e6da45e5e783bc8a635 (at ../../node_modules/.pnpm/@[email protected]/node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary : prisma-fmt 39190b250ebc338586e25e6da45e5e783bc8a635 (at ../../node_modules/.pnpm/@[email protected]/node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Format Wasm : @prisma/prisma-fmt-wasm 4.7.0-74.39190b250ebc338586e25e6da45e5e783bc8a635
Default Engines Hash : 39190b250ebc338586e25e6da45e5e783bc8a635
Studio : 0.477.0
I think I found a way to work around this by always adding a fixed number of decimal places to the query params and explicitly casting them to numeric in the query:
export const calculate = async (
longitude: number,
latitude: number,
radiusInMeters: number,
) => {
const lng = longitude.toFixed(6);
const lat = latitude.toFixed(6);
return prisma.$queryRaw<{ sum: string }[]>`
SELECT SUM("range")::text AS sum
FROM "Locations" AS s
WHERE ST_DWithin(s.coordinates, ST_MakePoint(${lng}::numeric, ${lat}::numeric), ${radiusInMeters}::numeric)
`;
}
But I believe this is unnecessarily complex…
Similar issue here: I was trying to Benchmark Prisma and had to "normalize" (${foo.toFixed(2)}::numeric one parameter before parsing it.
Thanks for sharing the solution @soulchild <3
Was too facing this issue with double-precision columns. Able to insert integer values into column but when trying to insert floating values was getting error. The above fix worked well for me.
Ran into this issue as well. Using toFixed(6) on the values and then casting to ::float worked for me when using $queryRaw to insert a number with 10 numbers past the decimal point into a float8 column on Postgres. I didn't need the extra precision, thankfully.
@soulchild I was able to reproduce a similar error by having the following schema at first:
model User {
id Int @id @default(autoincrement())
name String
email String @unique
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
then in another folder, I would have a similar schema, but making name optional:
model User {
id String @id @default(cuid())
email String @unique
name String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
The latter is wrong, and out of sync with the database, so when I create with it:
await prisma.user.create({
data: {
email,
name: "test",
}
})
Then I get the following error:
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(E22P03), message: "incorrect binary data format in bind parameter 1", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("postgres.c"), line: Some(1743), routine: Some("exec_bind_message") }) }), transient: false })
Even for raw queries, we actually need correct schema information. If these get out of sync, you can end up with such errors.
So now I need to clarify with you @soulchild @Lord-Leonard @vamseekm @nsilvestri. Perhaps there are two sides to this story.
-
The first side would be about a bad issue that caused Prisma Clients to be cached in Vercel deployments, so if you deployed there a few months ago then you'd have such issues and the risk of having the error I demonstrated above. The solution back then was to clear the vercel/netlify cache, or running
prisma generateyourself. We now error to let you know. Are you deploying to Vercel or Netlify? -
The second side could be that we made some unintended changes wrt to numeric types when we re-worked our raw queries. So If you weren't deploying to Vercel or Netlify, this is actually what I should investigate next. Right now, I would need a clear reproduction for that. @soulchild you mentioned that it worked locally. What about you @Lord-Leonard @vamseekm @nsilvestri, how can I reproduce your issues?
@millsp Thanks for taking a look at this! I was seeing this error locally during development as well as in production, running a Docker container on our own K8s cluster.
The interesting part was that the order in which you put parameters in matters:
-
intfirst, thenfloat=> application is now basically broken.
(restart application)
-
floatfirst, thenfloatorint=> application works as expected
The first kind of parameter you put in somehow primes the system to always expect that type of parameter in subsequent queries.
I've been repeatedly running into this on batch jobs that update many columns. Can confirm that it's only happening on floats. Why is this bug "unconfirmed", the repro given above seems very straightforward to me?
I was able to workaround this with full precision by passing the float in as a string and CASTing the type in the query to FLOAT8
@recurrence Because no one our side did a successful reproduction of the problem, as you can see above in Pierre's message. Can you respond to the 2 questions he asked to help us if the reproduction is so straightforward to you? Thanks.
Oh sorry @janpio , I thought @soulchild accurately answered Pierre's questions back in August? I did go into the Prisma discord and asked for a link to a batteries included repl to demonstrate some Prisma bugs that my teams have been encountering but nobody replied. I think you'd have a much easier time obtaining reproductions with a repl that has a postgres container connected.
- I was seeing this in local environments as well as in AWS on ECS containers and Lambda executions.
- I think his example will reproduce the problem but without a batteries included repl I can't say for sure.
FINALLY, this has been approved in https://github.com/prisma/prisma/issues/22482, so we might as well close this issue in favor of the other issue.
According to #22482 the underlying problem seems to be that you are running similar raw queries for the same table with different parameters types. Can this also be possible in your case @soulchild? As far as I can follow above, you did not mention this circumstance before.
@janpio, my initial example shows that I'm indeed running the same raw query - only with different parameter types (int vs float). The order of the parameter types matters, which I explained here. I believe #22482 is expressing the same problem.
Not 100% sure, but I think your answer expresses a "Yes, that is indeed the case" to my question.
I think I am/was experiencing a similar issue.
For context: I'm trying to do an infinite scroll on a page that shows a list of posts that a user has favorited. I tried using Prisma's built in cursor pagination at first, but I guess you can't sort by the relation table's columns? I wanted to sort by the most recently favorited post first. I ended up resorting to just doing a raw query.
const posts = await db.$queryRaw`
SELECT post.*
json_agg(json_build_object('postId', favorites.post_id, 'userId', favorites.user_id, 'createdAt', favorites.created_at)) AS favorites
FROM post
JOIN favorites ON post.id = favorites.post_id
WHERE favorites.user_id = ${user.id}
AND (${cursorId}::text IS NULL OR (favorites.created_at < ${cursorDate}::timestamp AND post.id > ${cursorId}))
GROUP BY post.id
ORDER BY MAX(favorites.created_at) DESC, post.id DESC
LIMIT 25
`;
I had this conditional where clause that doesn't trigger on the first page of the infinite scroll but does for every other page
`AND (${cursorId}::text IS NULL OR (favorites.created_at < ${cursorDate}::timestamp AND post.id > ${cursorId}))`
Whenever I scrolled to "page 2" of the infinite scroll, it would usually throw the 22P03 error
Invalid `prisma.$queryRaw()` invocation:
Raw query failed. Code: `22P03`. Message: `db error: ERROR: incorrect binary data format in bind parameter 3`
bind parameter 3 suggests that it's erroring at the favorites.created_at < ${cursorDate}::timestamp part of the where clause.
I was also experiencing the similar problem that the error would happen most of the time, but not every time, which was incredibly confusing to me. If it is some sort of caching issue, then that makes a lot more sense to me now. Clearly in my case, my problem was having the conditional where clause.
I ended up just taking the conditional out of the query itself, and doing an if/else statement to run 2 slightly different queries instead.
let posts;
if (cursorId && cursorDate) {
posts = await db.$queryRaw`
SELECT post.*
json_agg(json_build_object('postId', favorites.post_id, 'userId', favorites.user_id, 'createdAt', favorites.created_at)) AS favorites
FROM post
JOIN favorites ON post.id = favorites.post_id
WHERE favorites.user_id = ${user.id}
AND favorites.created_at < ${cursorDate}::timestamp
AND post.id > ${cursorId}
GROUP BY post.id
ORDER BY MAX(favorites.created_at) DESC, post.id DESC
LIMIT 25
`;
} else {
posts = await db.$queryRaw`
SELECT post.*
json_agg(json_build_object('postId', favorites.post_id, 'userId', favorites.user_id, 'createdAt', favorites.created_at)) AS favorites
FROM post
JOIN favorites ON post.id = favorites.post_id
WHERE favorites.user_id = ${user.id}
GROUP BY post.id
ORDER BY MAX(favorites.created_at) DESC, post.id DESC
LIMIT 25
`;
}
the only difference being that one has the additional where clauses and the other doesn't
AND favorites.created_at < ${cursorDate}::timestamp
AND post.id > ${cursorId}
I seem to be getting this error for many months for at what seems to me like random times. 99% of my queries run fine but I get around one of these errors a day or so.
In a case where it fails, I am doing an insert with these bind params:
[
{
"values": [
"c238eb9b-e619-444d-be2e-62237298276c",
"07358ec2-6be1-455d-8b57-3854fa6b1a99",
0.3209535,
1.29388823
],
"strings": [
"(",
"::uuid, ",
"::uuid, ",
"::double precision, ",
"::double precision)"
]
}
]
message: 'ERROR: incorrect binary data format in bind parameter 4'
In my case I was facing a problem similar to this issue, but getting the same error message that you're getting (ERROR: incorrect binary data format in bind parameter 4). After a day or 2 of research and struggle turned out that type of one of the fields in my model (in schema.prisma) was not in-sync with its corresponding field (column) in the database. In my case the type that was set in my schema.prisma file was "Float" but the type used for its respective column in the database was "Integer".
Then we figured out that one of the migrations related to changing this type on DB have not been executed, synchronizing the types between schema.prisma and database table resolved the issue for us.
I hope it helps.
I am also having this issue, and it's a weird one. My app (and all queries) can run fine for days/hours, and all of a sudden I get this error in a specific query:
PrismaClientKnownRequestError:
Invalid `prisma.$queryRawUnsafe()` invocation:
Raw query failed. Code: `22P03`. Message: `ERROR: incorrect binary data format in bind parameter 5`
at async eval (./src/components/server_actions/scripter/lhsm_crud.ts:207:28)
at async $$ACTION_3 (./src/components/server_actions/scripter/lhsm_crud.ts:181:5)
at async $$ACTION_2 (./src/components/server_actions/scripter/lhsm_crud.ts:171:13)
It's always the same query, and it's always about parameter 5.
const result = await tx.$queryRawUnsafe<T[]>(query, ...itemValues, primaryKeyValue);
UPDATE "ScripterView"
SET "project_id" = $1, "view_id" = $2, "viewport_x" = $3::numeric, "viewport_y" = $4::numeric, "viewport_zoom" = $5::numeric
WHERE "view_id" = $6
RETURNING *
The params:
'641b0527-7ed7-443e-814c-e8ed2e7edf2a',
'06b7002a-4a17-4f0c-8ede-ae5488f9f49b',
57.77,
-49.13,
1.1
Params 3, 4 and 5 are all Floats (I convert them explicitly before using them in the query). I also tried converting the query fields to 'double precision' before the update, but same issue. The weird thing is, when param 5 (viewport_zoom) is a full integer (e.g when the zoom level lands on 1 or 2) the query runs fine.
Sometimes, the error 'recovers' when I stop my DEV-server and browser and start everything up again. Sometimes it recovers when I regenerate my Prisma client. But sometimes it looks like the database row is nuked or something and I can't get it working again with that row. Only full integers on param 5 will work then.
I use Postgres as my back-end.
This is the Prisma model:
model ScripterView {
view_id String @id @default(dbgenerated("uuid_generate_v4()"))
project_id String
friendlyname String @default("Default view")
view_type String @default("default")
viewport_x Float @default(100.0)
viewport_y Float @default(100.0)
viewport_zoom Float @default(1.1)
created_at DateTime @default(now())
updated_at DateTime @default(now()) @updatedAt
}
Any help would be appreciated :)
Thank you for reporting the issue @soulchild!
If you are not using driver adapters for Postgres, can you please try this version and tell us if it fixes the issue? Note that this is a dev version and it is not suitable for production use.
npm i [email protected]
npm i @prisma/[email protected]
Thanks everyone!
The issue should be fixed and the fix will be shipped as a part of 5.15.0 release next week.
As my co-worker mentioned, you can try out the fix now by using 5.15.0-dev.47 snapshot. We do not recommend using snapshot versions in production, but it's enough to verify the fix.
Feel free to comment if that version does not fix the problem for you.
I changed the data type of the createdUserId field in my Prisma schema from Int to String.
However, I didn't update the corresponding column in the PostgreSQL database, which was still expecting an integer.
"incorrect binary data format in bind parameter"
Steps to Resolve:
- I updated my Prisma schema to reflect the new data type for createdUserId
- Execute prisma migration
- Reflect migration
.sqlfile to PostgreSQL database
Take note: Always run and apply new migrations after making changes to your Prisma schema to avoid runtime errors and data type mismatches.
