MySQL migration invalid default value @default(now())

[tryoasnafi]

Bug description

Error when migrate database MySQL:
When i use @default(now()) it's error, but when i change to @default(dbgenerated("NOW()")) it's success.

I look into the generated file, it generated CURRENT_TIMESTAMP(3) instead of CURRENT_TIMESTAMP.

-- CreateTable
CREATE TABLE `buggy` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Logs:

prisma:getSchema Reading schema from single file /app/prisma/mysql/schema.prisma +0ms
prisma:schemaEngine:rpc SENDING RPC CALL {"id":1,"jsonrpc":"2.0","method":"schemaPush","params":{"force":false,"schema":{"files":[{"path":"/app/prisma/mysql/schema.prisma","content":"// This is your Prisma schema file,\n// learn more about it in the docs: https://pris.ly/d/prisma-schema\n\n// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?\n// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init\n\ngenerator client {\n  provider = \"prisma-client-js\"\n  output   = \"../../node_modules/@prisma/mysql/client\"\n}\n\ndatasource db {\n  provider = \"mysql\"\n  url      = env(\"MYSQL_DB_CONN_STRING\")\n}\n\nmodel buggy {\n  id         Int       @id @default(autoincrement())\n  created_at DateTime  @default(now()) @db.Timestamp()\n}\n"}]}}} +4ms
prisma:schemaEngine:stderr {"timestamp":"2024-06-15T01:57:18.919228101Z","level":"INFO","fields":{"message":"Starting schema engine RPC server","git_hash":"12e25d8d06f6ea5a0252864dd9a03b1bb51f3022"},"target":"schema_engine"} +6ms
prisma:schemaEngine:rpc {
  "jsonrpc": "2.0",
  "error": {
    "code": 4466,
    "message": "An error happened. Check the data field for details.",
    "data": {
      "is_panic": false,
      "message": "Invalid default value for 'created_at'\n   0: sql_schema_connector::apply_migration::migration_step\n           with step=CreateTable { table_id: TableId(0) }\n             at schema-engine/connectors/sql-schema-connector/src/apply_migration.rs:21\n   1: sql_schema_connector::apply_migration::apply_migration\n             at schema-engine/connectors/sql-schema-connector/src/apply_migration.rs:10\n   2: schema_core::state::SchemaPush\n             at schema-engine/core/src/state.rs:461",
      "backtrace": null
    }
  },
  "id": 1
} +30ms
Error: Error: Invalid default value for 'created_at'
   0: sql_schema_connector::apply_migration::migration_step
           with step=CreateTable { table_id: TableId(0) }
             at schema-engine/connectors/sql-schema-connector/src/apply_migration.rs:21
   1: sql_schema_connector::apply_migration::apply_migration
             at schema-engine/connectors/sql-schema-connector/src/apply_migration.rs:10
   2: schema_core::state::SchemaPush
             at schema-engine/core/src/state.rs:461

    at Object.<anonymous> (/app/node_modules/prisma/build/index.js:542:46)
    at bu.handleResponse (/app/node_modules/prisma/build/index.js:533:2867)
    at of.<anonymous> (/app/node_modules/prisma/build/index.js:538:524)
    at of.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3)
    at Readable.push (node:internal/streams/readable:391:5)
    at of._pushBuffer (/app/node_modules/prisma/build/index.js:527:369)
    at of._transform (/app/node_modules/prisma/build/index.js:527:199)
    at Transform._write (node:internal/streams/transform:171:8)

How to reproduce

Run DEBUG="*" ./node_modules/.bin/prisma migrate dev

Expected behavior

Migration sql generated correctly and run successfully

Prisma information

// Add your schema.prisma
model buggy {
  id         Int       @id @default(autoincrement())
  created_at DateTime  @default(now()) @db.Timestamp()
}

Generated SQL:

-- CreateTable
CREATE TABLE `buggy` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Look correct format mysql: https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

Environment & setup

• OS: Ubuntu 22.04
• Database: MySQL 8.0
• Node.js version: v20.14.0
• Docker: v26.1.0

Dockerfile

FROM node:20.14-alpine3.19 as base

# Set the working directory inside the container
WORKDIR /app

# Copy package.json and package-lock.json to the working directory
COPY package*.json ./
COPY yarn.lock ./

# Copy the rest of the application code to the working directory
COPY . .

FROM base as install

# Install dependencies
RUN yarn install --frozen-lockfile

FROM install as test

RUN yarn test

FROM install as lint

RUN yarn lint

FROM install as build

RUN yarn build

CMD ["node", "dist/server.js"]

compose.yaml

services:
  api:
    build: .
    command: sh -c "yarn && yarn mysql:migrate:dev"
    ports:
      - '${API_PORT}:${API_PORT}'
    volumes:
      - .:/app
    env_file:
      - .env
    depends_on:
      mysqldb:
        condition: service_healthy

  mysqldb:
    image: mysql:8.0
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: '${MYSQL_DB_PASS}'
      MYSQL_USER: ${MYSQL_DB_USER}
      MYSQL_PASSWORD: '${MYSQL_DB_PASS}'
      MYSQL_DATABASE: ${MYSQL_DB_NAME}
    ports:
      - $MYSQL_DB_LOCAL_PORT:3306
    volumes:
      - mysql-db-data:/var/lib/mysql
    healthcheck:
      test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
      interval: 5s
      timeout: 5s
      retries: 5
      start_period: 5s

volumes:
  mysql-db-data: {}

.env

API_PORT=3001

# MySQL
MYSQL_DB_USER=mysqluser
MYSQL_DB_PASS=somepassword
MYSQL_DB_NAME=authdb
MYSQL_DB_LOCAL_PORT=3306
MYSQL_DB_CONN_STRING="mysql://mysqluser:somepassword@mysqldb:3306/authdb"

Prisma Version

prisma                  : 5.15.0
@prisma/client          : 5.15.0
Computed binaryTarget   : linux-musl-openssl-3.0.x
Operating System        : linux
Architecture            : x64
Node.js                 : v20.14.0
Query Engine (Node-API) : libquery-engine 12e25d8d06f6ea5a0252864dd9a03b1bb51f3022 (at node_modules/@prisma/engines/libquery_engine-linux-musl-openssl-3.0.x.so.node)
Schema Engine           : schema-engine-cli 12e25d8d06f6ea5a0252864dd9a03b1bb51f3022 (at node_modules/@prisma/engines/schema-engine-linux-musl-openssl-3.0.x)
Schema Wasm             : @prisma/prisma-schema-wasm 5.15.0-29.12e25d8d06f6ea5a0252864dd9a03b1bb51f3022
Default Engines Hash    : 12e25d8d06f6ea5a0252864dd9a03b1bb51f3022
Studio                  : 0.501.0

[ludralph]

Hi @tryoasnafi 👋

Thank you for raising this question. I can confirm that using the @db.Timestamp() without passing the fractional seconds part does indeed throw an error. I am confirming with the engineering team if this is indeed the expected behaviour.

model buggy {
  id         Int       @id @default(autoincrement())
  created_at DateTime  @default(now()) @db.Timestamp()
}

However, if you do pass a a value for the fractional seconds part, then the migration does indeed work. For example:

model buggy {
  id         Int       @id @default(autoincrement())
  created_at DateTime  @default(now()) @db.Timestamp(0)
}

[traviswimer]

To add some clarity to this, it looks like the problem is that the default value of 3 is only applied to the DEFAULT column value and not the column type.

---

For example, this prisma schema and the resulting SQL:

schema.prisma:

createdAt DateTime @default(now()) @db.Timestamp()

SQL:

`createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

---

Interestingly, if you explicitly pass 3 as a parameter, it produces the correct SQL (setting the column type to TIMESTAMP(3)):

schema.prisma:

createdAt DateTime @default(now()) @db.Timestamp(3)

SQL:

`createdAt` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

---

So it seems like the default values for @db attributes are not actually the equivalent of using a default parameter.

That makes me wonder if it might be a bigger problem than just this specific situation, or at least something that needs to be included in the documentation.

[ludralph]

Hi @tryoasnafi

I'll open an issue for this so that the engineering team can take a look at it.

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that haven’t had any recent activity and appear to be outdated.

If this discussion is still important to you or unresolved, we’d love to hear from you! Feel free to reopen it or start a new one with updated details.

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and being part of the community!;

[azzazkhan]

@ludralph Is this issue still not fixed?