Migrate: Failure during a migration command: Connector error. (error: Error querying the database: Error querying the database: Server error: `ERROR 42000 (1075): Incorrect table definition; there can be only one auto column and it must be defined as a key'

[RoyBkker]

This is the error when running prisma migrate up --experimental..

Failure during a migration command: Connector error. (error: Error querying the database: Error querying the database: Server error: `ERROR 42000 (1075): Incorrect table definition; there can be only one auto column and it must be defined as a
 key'
   0: migration_core::api::ApplyMigration
           with migration_id="20200601232032-initial"
             at migration-engine/core/src/api.rs:77)

I have no clue. It happend suddenly, was working with it the whole day, and suddnely i get this error...

model Item {
  id             Int         @default(autoincrement())
  description    String
  date           DateTime
  articleId      String
  itemId      String      @id
  title          String
  tags           String
  thumbnail      String
  commentCount   Int?
  statsOnItem    StatsOnItem @relation(fields: [statsOnItemdId], references: [id])
  statsOnItemdId Int
  mediaOnItem    MediaOnItem
}

model StatsOnItem {
  id         Int   @default(autoincrement()) @id
  Item       Item?
  viewsTotal Int
  viewsToday Int
  pointsTotal Int
  pointsToday Int
}

model MediaOnItem {
  id        Int    @default(autoincrement()) @id
  Item      Item?  @relation(fields: [itemId], references: [id])
  itemId    Int?
  mediaType String
  duration  Int
}

model User {
  id       Int       @default(autoincrement()) @id
  userName String
  comments Comment[]
}

model Comment {
  id             Int      @default(autoincrement())
  articleId      Int      @unique
  articleLink    String
  articleTitle   String
  banned         Boolean? @default(false)
  commentId      Int      @id
  createdAt      DateTime
  displayContent String
  points          Int
  reportCount    Int
  topComment Boolean  @default(false)
  User           User?    @relation(fields: [userId], references: [id])
  userId         Int?
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

Running beta-7 and to help u even more, the report id is 6679.

[pantharshit00]

I can reproduce this error when I try to migrate the above datamodel.

Version used in reproduction:

Environment variables loaded from ./prisma/.env
@prisma/cli          : 2.0.0-alpha.1299
Current platform     : debian-openssl-1.1.x
Query Engine         : query-engine d88852ac341cdf3c7379a08fa1ebc5c444a4aa67 (at /home/harshit/code/reproductions/issue-464/node_modules/@prisma/cli/query-engine-debian-openssl-1.1.x)
Migration Engine     : migration-engine-cli d88852ac341cdf3c7379a08fa1ebc5c444a4aa67 (at /home/harshit/code/reproductions/issue-464/node_modules/@prisma/cli/migration-engine-debian-openssl-1.1.x)
Introspection Engine : introspection-core d88852ac341cdf3c7379a08fa1ebc5c444a4aa67 (at /home/harshit/code/reproductions/issue-464/node_modules/@prisma/cli/introspection-engine-debian-openssl-1.1.x)
Format Binary        : prisma-fmt d88852ac341cdf3c7379a08fa1ebc5c444a4aa67 (at /home/harshit/code/reproductions/issue-464/node_modules/@prisma/cli/prisma-fmt-debian-openssl-1.1.x)

The comment model has two primary key declared commentId and id. We should throw a meaningful error.

[RoyBkker]

Thanks for looking into it @pantharshit00 .
For my understanding, when you declare an id with @default(Autoincrement()), this will be a primary key as well?
So best practice would be to remove the id field?

[pantharshit00]

There should be only one field marked with @id

[RoyBkker]

It is right? I only have on @id field

[theRocket]

I received the same error. Is it this?

`` model TranConfirm { id String @default(cuid()) @id number Int @default(autoincrement(1000000000)) @unique } ```` I took out theautoincrement()` part on the number field, and the migration works (of course, I have to save a new one, but I just deleted the whole db and started over).

We successfully received the error report
To help us even more, please create an issue at https://github.com/prisma/prisma/issues/new
mentioning the report id 6988.

[RoyBkker]

When I remove the id field in the Comment model, I still have the same error.. I also removed the whole db and restarted everything..

[theRocket]

I'm guessing you can have @id with autoincrement(), but not on separate fields. You need at minimum @id tagged, then you can optionally add autoincrement() for integer IDs. I use CUIDs, so cannot use that one.

[tomhoule]

We validate and forbid having @default(autoincrement()) on non-key fields in the Prisma schema on MySQL now, since this is not supported by the database. Thanks for reporting the problem!