"Data too long for column \'...\' at row 1" / "value too long for type character ...(...)"

[janpio]

When using prisma-test-utils via prisma-test-utils-automation, some databases (schemas from: https://github.com/prisma/database-schema-examples) fail with this error message:

Error in: employees
PrismaClientUnknownRequestError:
Invalid `: new departmentsClient()` invocation in
C:\Users\Jan\Documents\prisma-test-utils-automation\dbs\employees\node_modules\prisma-client\index.js:535:7

   531   args || {},
   532   [],
   533   errorFormat,
   534   measurePerformance
→  535 ) : new departmentsClient

Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1406, message: "Data too long for column \'dept_no\' at row 1", state: "22001" })) })
    at PrismaClientFetcher.request (C:\Users\Jan\Documents\prisma-test-utils-automation\dbs\employees\node_modules\prisma-client\index.js:90:17)
    at processTicksAndRejections (internal/process/task_queues.js:97:5)
error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

Notes:

• Would be neat if the error message would show a bit more context, but that is a Prisma Client problem I guess.
  • prisma-test-utils could output the query it tried to execute though for example
• dept_no is CHAR(4) in SQL - and it probably tries to enter something that is longer than that, so it fails

[janpio]

Another one:

CREATE TABLE `currencies` (
  `currency` char(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `rate` float DEFAULT '0'
) ENG

Error in: kanboard
PrismaClientUnknownRequestError:
Invalid `: new currenciesClient()` invocation in
C:\Users\Jan\Documents\prisma-test-utils-automation\dbs\kanboard\node_modules\prisma-client\index.js:2735:7

  2731   args || {},
  2732   [],
  2733   errorFormat,
  2734   measurePerformance
→ 2735 ) : new currenciesClient

Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1406, message: "Data too long for column \'currency\' at row 1", state: "22001" })) })
    at PrismaClientFetcher.request (C:\Users\Jan\Documents\prisma-test-utils-automation\dbs\kanboard\node_modules\prisma-client\index.js:90:17)
    at processTicksAndRejections (internal/process/task_queues.js:97:5)

[janpio]

Postgres equivalent:

Error in: shakespeare
PrismaClientUnknownRequestError:
Invalid `: new characterClient()` invocation in
C:\Users\Jan\Documents\prisma-test-utils-automation\dbs\shakespeare\node_modules\prisma-client\index.js:805:7

   801   args || {},
   802   [],
   803   errorFormat,
   804   measurePerformance
→  805 ) : new characterClient

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("22001"), message: "value too long for type character varying(32)", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("d:\\pginstaller_12.auto\\postgres.windows-x64\\src\\backend\\utils\\adt\\varchar.c"), line: Some(636), routine: Some("varchar") }) }) })
    at PrismaClientFetcher.request (C:\Users\Jan\Documents\prisma-test-utils-automation\dbs\shakespeare\node_modules\prisma-client\index.js:90:17)
    at processTicksAndRejections (internal/process/task_queues.js:97:5)

[divyenduz]

This one happens when the string generated by prisma-test-utils is larger than the underlying storage. For example, in this database table, test-utils generated the following query and failed

2020-03-09T10:52:33.251Z prisma-client Requests:
2020-03-09T10:52:33.251Z prisma-client [
  'mutation {\n' +
    '  createOnecdl_label(data: {\n' +
    '    name: "f70c6744-c2cb-5a28-b4c6-5aa0680dac0c"\n' +
    '  }) {\n' +
    '    name\n' +
    '  }\n' +
    '}'
]
2020-03-09T10:52:34.316Z prisma-client Results:
2020-03-09T10:52:34.318Z prisma-client [
  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("22001"), message: "value too long for type character varying(30)", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("varchar.c"), line: Some(624), routine: Some("varchar") }) }) })
      at NodeEngine.graphQLToJSError (/home/runner/work/introspection-engine-output/introspection-engine-output/clients/postgresql_public_test_utils/helpdesk/runtime/index.js:1:17768)
      at /home/runner/work/introspection-engine-output/introspection-engine-output/clients/postgresql_public_test_utils/helpdesk/runtime/index.js:1:16702
      at Array.map (<anonymous>)
      at /home/runner/work/introspection-engine-output/introspection-engine-output/clients/postgresql_public_test_utils/helpdesk/runtime/index.js:1:16669
      at processTicksAndRejections (internal/process/task_queues.js:97:5)
      at Dataloader.loader (/home/runner/work/introspection-engine-output/introspection-engine-output/clients/postgresql_public_test_utils/helpdesk/index.js:56:23)
]

It is not possible to test utils to know the size of the underlying field as that information is lost in Prisma schema but a potential solution that can go far could be to keep generated strings to be of a smaller size (say, 10).

@janpio @maticzav What do you think?

---

Internal notes:

Test utils database(s) affected:

• https://github.com/prisma/introspection-engine-output/blob/test_utils/test-utils/results/postgresql_public_test_utils_helpdesk_stderr.log

[janpio]

Yes, make the generated so much smaller that it fits all columns we try to put it. Ugly, but right now the only option.

[divyenduz]

This helpdesk case is tricky to solve! It is a String and an ID and it is using this faker. We can't shorten it further because then it is an invalid GUID and would probably break other cases.

We might have to apply some heuristic on the field name (like its name is name) to shorten it. Very ugly indeed.

Or expose field length/underlying SQL/some pointer type in the dmmf.

[janpio]

Or expose field length/underlying SQL/some pointer type in the dmmf.

Not possible as not available in the schema.

Maybe some of the schemas just do not work - that is also acceptable.

(Or we could hardcode a different faker for a schema maybe... Sucks as a general solution, but brings us forward here)

[divyenduz]

Or we could hardcode a different faker for a schema maybe

I would rather hardcode it to field name "name" (for name/value) or "key" (for key/value) and see if the table has <2 fields. That should work. What do you think?

[janpio]

That seems equally specific to me, but could have false positives with unintended side effects. Why better that than table or database name?

[divyenduz]

That seems equally specific to me, but could have false positives with unintended side effects. Why better that than table or database name?

That is a bigger change I think. If I understand it correctly, test-utils is not aware of DB name, I will need to add that feature (table name and field name can be used, I would still choose field name as it feels more specific).

Field/table (model actually) name should be a 1-liner.

[janpio]

whatever works