Prisma problem inserting into autogenerated int ID field: integer out of range for type int4-CodePudding

model Comment {
  id        Int             @id @default(autoincrement())
  cid       String      
  bid       String
  author    AppProfile      @relation(fields: [bid], references: [bid])
  comment   String
  created   DateTime        @default(now())
}

const results = await prisma.comment.create({
  data: {
    cid: req.body.cid,
    comment: req.body.comment,
    bid: appProfile.bid,
  },
});

prisma:query INSERT INTO "public"."Comment" ("cid","bid","comment","created") VALUES ($1,$2,$3,$4) RETURNING "public"."Comment"."id"
prisma:query ROLLBACK
error - PrismaClientUnknownRequestError:
Invalid `prisma.comment.create()` invocation:


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(E22003), message: "integer out of range for type int4", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("eval.go"), line: Some(65), routine: Some("init") }) }) })
    at RequestHandler.handleRequestError (/Users/jmalicke/Projects/muf/dev/repos/muf/bubbles-serverless/node_modules/@prisma/client/runtime/index.js:34314:13)
    at RequestHandler.request (/Users/jmalicke/Projects/muf/dev/repos/muf/bubbles-serverless/node_modules/@prisma/client/runtime/index.js:34293:12)
    at async PrismaClient._request (/Users/jmalicke/Projects/muf/dev/repos/muf/bubbles-serverless/node_modules/@prisma/client/runtime/index.js:35273:16)
    at async comment (webpack-internal:///(api)/./pages/api/comment/index.ts:26:25) {
  clientVersion: '4.6.1',
  page: '/api/comment'
}

This is strange because the id column is an Int and should be default autoincrementing. The error message complains about int4. If I look in the database the only int4 column is the id field.

CodePudding user response：

It seems your users are too active, if there is more than 2.147.483.647 comment ids, the Int id has reach it max value

You can convert it to BigInt like:

id BigInt @id @default(autoincrement())

But the best is maybe to use uuid like:

id String @id @unique @default(uuid())

Int max =   2.147.483.647
BigInt max = 9.223.372.036.854.775.807
UUID max = 3.4*10^38 or 340000000000000000000000000000000000000 (thanks to Belayer)