Prepared statement contains too many placeholders

[robmurtagh]

Bug description

When creating many 1000s of rows (in my case also inside a transaction) in a MySQL database we hit:

ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1390, message: "Prepared statement contains too many placeholders", state: "HY000" })) })

I don't think these is an obvious workaround in the Prisma ecosystem.

How to reproduce

You can reproduce this with a large Prisma transaction given your available schema.

In my case this looks like:

const results = await this.prisma.$transaction([
  this.prisma.parent.update(/** ... */)
  this.prisma.child1.createMany(/** ... */),
  this.prisma.child1.deleteMany(/** ... */),
  this.prisma.child2.createMany(/** ... */),
  this.prisma.child2.deleteMany(/** ... */),
]);

Expected behavior

I think this is more like a feature request, but I'd expect the createMany or$transaction to allow some opportunity to support a batchSize / chunkSize / transactionChunk, like for example Knex BatchInsert or Sequelize Batch Create

Prisma information

Environment & setup

• OS: Mac OS
• Database: MySQL 8
• Node.js version: v12.18.1

Prisma Version

prisma               : 2.26.0
@prisma/client       : 2.26.0
Current platform     : darwin
Query Engine         : query-engine 9b816b3aa13cc270074f172f30d6eda8a8ce867d (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 9b816b3aa13cc270074f172f30d6eda8a8ce867d (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 9b816b3aa13cc270074f172f30d6eda8a8ce867d (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 9b816b3aa13cc270074f172f30d6eda8a8ce867d (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : 9b816b3aa13cc270074f172f30d6eda8a8ce867d
Studio               : 0.408.0

[janpio]

Is this only in the context of a transaction, or also on individual Prisma Client queries?

[robmurtagh]

Hi @janpio, ran a few more tests and it's inside the createMany statement:

Error: 
Invalid `prisma.foo.createMany()` invocation:


Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1390, message: "Prepared statement contains too many placeholders", state: "HY000" })) })
    at cb (/Users/.../Code/platform/server/node_modules/@prisma/client/runtime/index.js:33900:17)
    at processTicksAndRejections (internal/process/task_queues.js:97:5)

It's failing on a createMany with 9000 elements. Each record being created has 13 fields.

[matthewmueller]

Seems related to: #6693, but for MySQL.

[wguerram]

Hello I'm having the same issue with createMany.

I'm using MariaDB, I'm migrating from typeorm and was using mysql2 package. with that setup it does work.

Do you think this is related to the underlying driver Prisma is using?

[MichalLytek]

I have the same error with a single update call:

await prisma.segmentData.update({
  where: { id: segmentId },
  data: {
    lastActiveAt: new Date(),
    customers: { disconnect: { id: customerId } },
  },
});

This is my model:

model SegmentData {
  id               Int                     @id @default(autoincrement()) @db.UnsignedInt
  storeId          Int                     @db.UnsignedInt
  name             String?                 @db.VarChar(255)
  segmentType      SegmentData_segmentType
  lastActiveAt     DateTime                @db.DateTime(0)
  blueLinkId       Int?                    @db.UnsignedInt
  isDynamic        Boolean                 @default(false)
  blueLink         BlueLink?               @relation(fields: [blueLinkId], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "fk_SegmentData_BlueLink1")
  store            Store                   @relation(fields: [storeId], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "fk_SegmentData_Store1")
  campaignSegments CampaignSegment[]
  filters          SegmentFilter[]
  customers        Customer[]              @relation("CustomerToSegment")
  autoPilotTasks   AutoPilotTask[]         @relation("SegmentToAutoPilotTask")

  @@unique([storeId, segmentType, blueLinkId], map: "segmentStoreTypeBlueLink_UNIQUE")
  @@index([blueLinkId], map: "fk_SegmentData_BlueLink1")
}

This is the error:

Invalid `prisma.segmentData.update()` invocation:

  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1390, message: "Prepared statement contains too many placeholders", state: "HY000" })) })

[gapetto]

Same issue. Version 2.27, createMany throws same error.

[janpio]

This has been fixed in Prisma 3.4.0. You will need to upgrade to benefit from this improvement.

[gapetto]

It works in 3.4.0 indeed. Thanks!

[robmurtagh]

Just to say @janpio, I'm not sure that the solution is actually working as expected. With Prisma 3.6 I'm running something like:

const results = await prisma.$transaction([
  // ... other options
  prisma.foo.createMany(...)
]);

Where the createMany has many more than 9,000 rows, each row containing 13 fields.

What I'm finding is that no error is thrown, but the data is not actually written to the database 😬 . Is this definitely working as expected for other people on this thread? I wonder if wrapping in prisma.$transaction causes some issues?

[janpio]

I would have expected others to report similar issues before if the plain use case would still not work. I think the best way forward here is for you to create a new bug report issue and provide all the information it asks for - optimally a minimal reproduction as well. Then we can look into this. Thanks.