Check the manual that corresponds to your mysql server version for the right syntax to use near

Thank you for reporting this! It's not the first time I see this error, we'll have to research support for CURRENT TIMESTAMP across different MySQL versions.

CURRENT_TIMESTAMP is supported as far back as MySQL 5.6 so that's probably not the issue. (official docs). Would you be able to share your prisma schema here so we can try to reproduce the crash?

// schema.prisma
datasource mysql {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

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

model Vendor {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  imageUrl String?
  name      String?
  role      Role     @default(VENDOR)
  vendorStrings     VendorString[]
  vendorStringM2M VendorString[] @relation(name:"M2MVendorVendorStrings", references: [id])

  orders Order[] @relation(name:"M2MVendorsOrders", references:[id])
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model VendorString {
  id         Int        @id @default(autoincrement())
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt
  name      String  @unique
  vendor     Vendor      @relation(fields: [vendorId], references: [id])
  vendorId Int
  vendors     Vendor[]      @relation(name:"M2MVendorVendorStrings", references: [id])
}

enum Role {
  VENDOR
  ADMIN
}

model Customer {
  id  Int @id @default(autoincrement())
  stId String @unique
  name  String?
  email String?
  phoneNumber String?
  orders  Order[]

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Order {
  id  Int @id @default(autoincrement())
  stId  String @unique
  lineItem LineItem
  status  String
  name String
  subtotalLineItemsQuantity Int @default(0)
  subtotalPrice String @default("0.00")
  totalPrice  String @default("0.00")
  totalTax  String @default("0.00")
  associatedVendor Vendor @relation(fields: [vendorId], references: [id])
  vendorId Int
  associatedVendors Vendor[] @relation(name:"M2MVendorsOrders", references:[id])
  address MailingAddress? @relation(fields: [addressId], references:[id])
  addressId Int?

  customer Customer @relation(fields: [customerId], references: [id])
  customerId Int

  notifiedVendor Boolean @default(false)
  lastNotified DateTime?

  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
}

model MailingAddress {
  id Int @id @default(autoincrement())
  stId String
  address1 String?
  address2 String?
  city String?
  company String?
  country String?
  countryCode String?
  firstName String?
  formattedArea String?
  lastName String?
  latitude Float?
  longitude Float?
  name String?
  phone String?
  province String?
  provinceCode String?
  zip String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model LineItem {
  id  Int @id @default(autoincrement())
  name String
  order Order @relation(fields: [orderId], references: [id])
  orderId Int

  unitCost String @default("0.00")
  originalTotal String @default("0.00")
  originalUnitPrice String @default("0.00")
  productId String
  productVariantId String
  quantity Int @default(0)
  sku String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model VendorNotification {
  id Int @id @default(autoincrement())
  lastNotifiedDate DateTime @default(now())
  lastWeeklyNotifiedDate DateTime @default(now())
  vendor Vendor @relation(fields: [vendorId], references:[id])
  vendorId Int

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

I run MySQL on docker and here is the .yml

version: '3'
services:
  mysql:
    image: mysql:5.7
    restart: always
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: <blah>
    volumes:
      - mysql:/var/lib/mysql
volumes:
  mysql:

I am unable to reproduce this issue, I used the exact same docker setup that you have posted:

Please try this with the latest alpha once, use npx @prisma/cli migrate save --experimental && npx @prisma/cli@alpha migrate up --experimental

I ran into this today, report #4946 -- after looking at the README.md for the migration, it looks like this statement is invalid:

ALTER TABLE `lre`.`actions` ALTER COLUMN `createdAt` SET DEFAULT CURRENT_TIMESTAMP;

I'm migrating from another ORM where createdAt was managed by the ORM, and my migration is setting @default(now()) from my new prisma schema (where there was no default before).

Per https://stackoverflow.com/questions/11605701/how-to-alter-an-existing-column-to-default-to-the-current-time-on-insert-in-mys, the syntax should actually be:

ALTER TABLE `lre`.`actions` MODIFY `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;

Quick update: I started working on this this morning, and it's pretty subtle.

ALTER TABLE `lre`.`actions` ALTER COLUMN `createdAt` SET DEFAULT (CURRENT_TIMESTAMP);

(notice the parentheses)

will work on MySQL 8 and MariaDB, but not MySQL 5. So we can't treat now() like a regular default. I am working on a MODIFY-based solution, which should work but will also required new runtime checks, for example when you change an optional field from to required with a default value, and there are existing NULL values in the column, at the moment it will work, but it gets much trickier with MODIFY.

I think we will be able to return good warnings for these cases and make migrating the defautls on DateTime columns smoothly possible. I'll update this issue when I have a PR for this.

@tomhoule thanks for your work on resolving this. Do you have a sense for when we'd see a resolution to test? It's still a blocker here for one of my use-cases. In the interim on the development environment I'm nuking the Migrations table and manually editing steps.json to remove the default(now()) steps, but this isn't a sustainable solution.

janpio changed the title Report id 4223 -- prisma migrate check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_TIMESTAMP' at line 1')

I'm migrating Prisma 1 to Prisma 2, right now we're facing this issue as well.
Here is the report id 7249.

This was referenced

This was released yesterday as part of the 2.1 release, can someone confirm that the crash no longer happens?

I can validate this later today, thank you for working on it.

@tomhoule better late than never, but appears to be working well here. I was able to create a new migration and not do the previous hacks/workarounds I had to do before. Thanks for this work!

Thanks for checking, glad it's fixed the problem for you :)

How do I fix MySQL syntax error?

How do I check MySQL version?

What is the current version of MySQL?

How do I check for MySQL updates?