PostgreSQL: "incorrect binary data format in bind parameter x"

[divyenduz]

To reproduce, import this (pasted below) SQL into a Postgres database.

create extension if not exists "pgcrypto";

create or replace function set_updated_at() RETURNS trigger
  LANGUAGE plpgsql
  AS $$
begin
  new.updated_at := current_timestamp;
  return new;
end;
$$;

create table if not exists users (
  id uuid primary key not null default gen_random_uuid(),
  "name" text not null,
  handle text not null,
  created_at timestamp not null default now(),
  updated_at timestamp not null default now()
);
create trigger updated_at before update on users for each row execute procedure set_updated_at();

create table if not exists locations (
  id uuid primary key not null default gen_random_uuid(),
  latitude float not null,
  longitude float not null
);

create table if not exists tweets (
  id uuid primary key not null default gen_random_uuid(),
  created_at timestamp not null default now(),
  "text" text not null,
  "owner" uuid not null references users(id) on delete cascade,
  "location" uuid not null references locations(id) on delete cascade
);

Introspect this database

prisma2 introspect --url 'postgresql://root:prisma@localhost:5432/basic-twitter?schema=public'

Run the following client call:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const data = await prisma.locations.create({
    data: {
      id: 'f70c6744-c2cb-5a28-b4c6-5aa0680dac0c',
      latitude: 234.96,
      longitude: -391.52,
    },
  })
  console.log(data)
}

main().finally(() => prisma.disconnect())

Error observed:

Invalid `prisma.locations.create()` invocation in 
./index.js:6:60 
 
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("22P03"), 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(1828), routine: Some("exec_bind_message") }) }) }) 
  at ​index.js:6:2​
  at ​​​processTicksAndRejections​​​ ​internal/process/task_queues.js:85​

---

Internal notes:

Test utils database(s) affected:

• https://github.com/prisma/introspection-engine-output/blob/test_utils/test-utils/results/postgresql_public_test_utils_basic-twitter_stderr.log
• https://github.com/prisma/introspection-engine-output/blob/test_utils/test-utils/results/postgresql_public_test_utils_jira_stderr.log
• https://github.com/prisma/introspection-engine-output/blob/test_utils/test-utils/results/postgresql_public_test_utils_sportsdb_stderr.log

[janpio]

I can not locally reproduce the problem for basic-twitter.

While creating the table I had problems though, had to follow the instructions here to get it working: https://dba.stackexchange.com/a/146222 Not sure if related.

[janpio]

Details JIRA:

020-03-11T13:58:03.229Z prisma-client prisma.aO_0AC321_RECOMMENDATION_AO.create({
  data: {
    CATEGORY: 'gewse',
    CUSTOM_FIELD_ID: 779,
    ID: 'c9947e5b-827d-5c19-93f7-b9ff9bed51a8',
    NAME: 'kadril',
    PERFORMANCE_IMPACT: -555.79,
    PROJECT_IDS: 'jeofhi',
    RESOLVED: false,
    TYPE: 'lahugwi'
  }
})
2020-03-11T13:58:03.229Z prisma-client Generated request:
2020-03-11T13:58:03.229Z prisma-client mutation {
  createOneAO_0AC321_RECOMMENDATION_AO(data: {
    CATEGORY: "gewse"
    CUSTOM_FIELD_ID: 779
    ID: "c9947e5b-827d-5c19-93f7-b9ff9bed51a8"
    NAME: "kadril"
    PERFORMANCE_IMPACT: -555.79
    PROJECT_IDS: "jeofhi"
    RESOLVED: false
    TYPE: "lahugwi"
  }) {
    CATEGORY
    CUSTOM_FIELD_ID
    ID
    NAME
    PERFORMANCE_IMPACT
    PROJECT_IDS
    RESOLVED
    TYPE
  }
}

...

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("22P03"), message: "incorrect binary data format in bind parameter 5", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("postgres.c"), line: Some(1772), routine: Some("exec_bind_message") }) }) })
    at PrismaClientFetcher.request (/home/runner/work/introspection-engine-output/introspection-engine-output/clients/postgresql_public_test_utils/jira/runtime/index.js:1:47203)
    at processTicksAndRejections (internal/process/task_queues.js:97:5)

model AO_0AC321_RECOMMENDATION_AO {
  CATEGORY           String?
  CUSTOM_FIELD_ID    Int?
  ID                 String   @id
  NAME               String?
  PERFORMANCE_IMPACT Float?
  PROJECT_IDS        String?
  RESOLVED           Boolean?
  TYPE               String?
}

  CREATE TABLE public."AO_0AC321_RECOMMENDATION_AO" (
    "CATEGORY" character varying(255),
    "CUSTOM_FIELD_ID" bigint,
    "ID" character varying(255) NOT NULL,
    "NAME" character varying(255),
    "PERFORMANCE_IMPACT" double precision,
    "PROJECT_IDS" text,
    "RESOLVED" boolean,
    "TYPE" character varying(255)
  );
ALTER TABLE public. "AO_0AC321_RECOMMENDATION_AO" OWNER TO prisma;

ALTER TABLE ONLY public."AO_0AC321_RECOMMENDATION_AO"
ADD
  CONSTRAINT "AO_0AC321_RECOMMENDATION_AO_pkey" PRIMARY KEY ("ID");

Exact same error from error log can be executed locally:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const data = await prisma.aO_0AC321_RECOMMENDATION_AO.create({
    data: {
      CATEGORY: 'gewse',
      CUSTOM_FIELD_ID: 779,
      ID: 'c9947e5b-827d-5c19-93f7-b9ff9bed51a8',
      NAME: 'kadril',
      PERFORMANCE_IMPACT: -555.79,
      PROJECT_IDS: 'jeofhi',
      RESOLVED: false,
      TYPE: 'lahugwi'
    }
  })
  console.log(data)
}

main().finally(() => prisma.disconnect())

[janpio]

What version was this tested with?

In the successful experiments I was using:

λ prisma2 -v
prisma2@2.0.0-alpha.902, binary version: 7f34f2252d46d463cf9a2e45488385c5adb0a49b

[divyenduz]

I had tested this with

divyendusingh [specs]$ prisma2 --version
prisma2@2.0.0-alpha.892, binary version: ab4ecca10c0626c60a85ecc55e25c175b60a1d83

Note to self, always use prisma2 version from package.json

[janpio]

We should probably output that in the logs we are looking at in the test-utils automation, to be able to properly reproduce locally what we are seeing there, right @divyenduz?

[divyenduz]

Yes! We should

[divyenduz]

This one is no longer reproducible on the latest alpha (starting with 902)

divyendusingh [introspection-engine-output]$ prisma2 --version
prisma2@2.0.0-alpha.902, binary version: 7f34f2252d46d463cf9a2e45488385c5adb0a49b

[umudik]

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 5", detail: None, hint: None, position: None, where_: Some("unnamed portal parameter $5"), schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("postgres.c"), line: Some(1912), routine: Some("exec_bind_message") }) }), transient: false }) at Zr.handleRequestError (/app/node_modules/@prisma/client/runtime/library.js:171:6587) at Zr.handleAndLogRequestError (/app/node_modules/@prisma/client/runtime/library.js:171:5948) at Zr.request (/app/node_modules/@prisma/client/runtime/library.js:171:5786) at PrismaService._request (/app/node_modules/@prisma/client/runtime/library.js:174:10455) at OfferService.create (/app/src/gql_services/offer/offer.service.ts:21:22) at OfferResolver.create_offer (/app/src/gql_services/offer/offer.resolver.ts:23:16) at target (/app/node_modules/@nestjs/core/helpers/external-context-creator.js:77:28) at Object.create_offer (/app/node_modules/@nestjs/core/helpers/external-proxy.js:9:24)

 This issue started again.

{
      start: "2023-04-03T06:33:00.000Z"
      end: "2023-04-05T06:33:00.000Z"
      latitude: 37.785834
      longitude: -121.785834
}

There are lat long parameters in both entity. Its a only chance?

[janpio]

Please open a new issue for this @umudik and provide all the information the bug issue template asks for. We will not be able to help you in a closed issue.