Error P1010 when running `npx prisma migrate dev` with mysql

[lookto]

Bug description

npx prisma migrate dev throws a P1010 error User "prisma" was denied access on the database "timetracking".

The user however is correctly set up, i have no problems connecting to the db when using dbeaver. Only prisma migrate is acting strange.

How to reproduce

1. Run npx prisma migrate dev in the project folder

Expected behavior

To be able to run npx prisma migrate devwithout P1010 error when the user is having all the right permissions.

Prisma information

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

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

model User {
    id          String        @id @default(uuid())
    name        String
    createdAt   DateTime      @default(now())
    updatedAt   DateTime      @updatedAt
    TrackedTime TrackedTime[]
}

model TrackedTime {
    id     String    @id @default(uuid())
    start  DateTime  @default(now())
    end    DateTime?
    user   User      @relation(fields: [userId], references: [id])
    userId String
}

DATABASE_URL=mysql://prisma:test@localhost:3306/timetracking

Environment & setup

• OS: Ubuntu 22.04 Kernel 6.0.9
• Database: MySQL running as docker container, port 3306 exposed, v8.0.31 or v5.7.40 (makes no difference)
• Node.js version: 18.12.1

Prisma Version

prisma                  : 4.6.1
@prisma/client          : 4.6.1
Current platform        : debian-openssl-3.0.x
Query Engine (Node-API) : libquery-engine 694eea289a8462c80264df36757e4fdc129b1b32 (at node_modules/@prisma/engines/libquery_engine-debian-openssl-3.0.x.so.node)
Migration Engine        : migration-engine-cli 694eea289a8462c80264df36757e4fdc129b1b32 (at node_modules/@prisma/engines/migration-engine-debian-openssl-3.0.x)
Introspection Engine    : introspection-core 694eea289a8462c80264df36757e4fdc129b1b32 (at node_modules/@prisma/engines/introspection-engine-debian-openssl-3.0.x)
Format Binary           : prisma-fmt 694eea289a8462c80264df36757e4fdc129b1b32 (at node_modules/@prisma/engines/prisma-fmt-debian-openssl-3.0.x)
Format Wasm             : @prisma/prisma-fmt-wasm 4.6.1-3.694eea289a8462c80264df36757e4fdc129b1b32
Default Engines Hash    : 694eea289a8462c80264df36757e4fdc129b1b32
Studio                  : 0.476.0

[lookto]

SHOW GRANTS FOR prisma

GRANT CREATE ON *.* TO 'prisma'@'%'
GRANT ALL PRIVILEGES ON `timetracking`.* TO 'prisma'@'%'

npx prisma migrate dev output with debug infos

  prisma:engines  binaries to download libquery-engine, migration-engine, introspection-engine, prisma-fmt +0ms
  prisma:loadEnv  project root found at /home/lookto/projects/timetracking/package.json +0ms
  prisma:tryLoadEnv  Environment variables loaded from /home/lookto/projects/timetracking/.env +0ms
  prisma:getConfig  Using getConfig Wasm +0ms
  prisma:getConfig  config data retrieved without errors in getConfig Wasm +2ms
  prisma:loadEnv  project root found at /home/lookto/projects/timetracking/package.json +5ms
  prisma:tryLoadEnv  Environment variables loaded from /home/lookto/projects/timetracking/.env +3ms
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
  prisma:getConfig  Using getConfig Wasm +1ms
  prisma:getConfig  config data retrieved without errors in getConfig Wasm +1ms
Datasource "db": MySQL database "timetracking" at "localhost:3306"

  prisma:getDMMF  Using CLI Query Engine (Node-API Library) at: /home/lookto/projects/timetracking/node_modules/@prisma/engines/libquery_engine-debian-openssl-3.0.x.so.node +0ms
  prisma:getDMMF  Loaded Node-API Library +2ms
  prisma:getDMMF  unserialized dmmf result ready +2ms
  prisma:getDMMF  dmmf retrieved without errors in getDmmfNodeAPI +0ms
  prisma:getConfig  Using getConfig Wasm +24ms
  prisma:getConfig  config data retrieved without errors in getConfig Wasm +2ms
  prisma:getConfig  Using getConfig Wasm +1ms
  prisma:getConfig  config data retrieved without errors in getConfig Wasm +1ms
  prisma:migrateEngine:rpc  starting migration engine with binary: /home/lookto/projects/timetracking/node_modules/@prisma/engines/migration-engine-debian-openssl-3.0.x +0ms
  prisma:migrateEngine:rpc  SENDING RPC CALL {"id":1,"jsonrpc":"2.0","method":"devDiagnostic","params":{"migrationsDirectoryPath":"/home/lookto/projects/timetracking/prisma/migrations"}} +4ms
  prisma:migrateEngine:stderr  {"timestamp":"2022-11-21T18:17:00.766581912Z","level":"INFO","fields":{"message":"Starting migration engine RPC server","git_hash":"694eea289a8462c80264df36757e4fdc129b1b32"},"target":"migration_engine"} +0ms
  prisma:migrateEngine:rpc  {
  jsonrpc: '2.0',
  error: {
    code: 4466,
    message: 'An error happened. Check the data field for details.',
    data: {
      is_panic: false,
      message: 'User `prisma` was denied access on the database `timetracking`',
      meta: [Object],
      error_code: 'P1010'
    }
  },
  id: 1
} +11ms
Error: Error: P1010

User `prisma` was denied access on the database `timetracking`

    at Object.<anonymous> (/home/lookto/projects/timetracking/node_modules/prisma/build/index.js:93536:25)
    at MigrateEngine.handleResponse (/home/lookto/projects/timetracking/node_modules/prisma/build/index.js:93389:36)
    at LineStream3.<anonymous> (/home/lookto/projects/timetracking/node_modules/prisma/build/index.js:93488:16)
    at LineStream3.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at LineStream3._pushBuffer (/home/lookto/projects/timetracking/node_modules/prisma/build/index.js:93269:17)
    at LineStream3._transform (/home/lookto/projects/timetracking/node_modules/prisma/build/index.js:93263:8)
    at Transform._write (node:internal/streams/transform:175:8)

[happy1836]

my app has the same problem on ubuntu 2204

[lookto]

my app has the same problem on ubuntu 2204

Did you solve it?

[happy1836]

my app has the same problem on ubuntu 2204

Did you solve it?

No ，it should be debian-openssl-3.0.x but not.

[lookto]

I find it very strange, 'migrate' has no problem creating the shadow db. So it's clearly not that prisma can't connect to the db with given user. The 'CREATE ON %.%' permission works like it should.

[lookto]

my app has the same problem on ubuntu 2204

Did you solve it?

No ，it should be debian-openssl-3.0.x but not.

This is a totaly different issue, you have problems with openssl. Maybe try upgrading your openssl version and if that doesn't help open a seperate issue.

[happy1836]

This is a totaly different issue, you have problems with openssl. Maybe try upgrading your openssl version and if that doesn't help open a seperate issue.

update openssl 3.0 on ubuntu 22.04 ,it is running .thank you very much.

[jimboboliver]

@lookto Hi, did you fix this issue? I have the same error.

[Tanguy-Magnaudet]

Hello,

I'm experiencing the same issue with :

prisma                  : 5.1.1
@prisma/client          : 5.1.1
Current platform        : linux-musl-openssl-3.0.x
Query Engine (Node-API) : libquery-engine 6a3747c37ff169c90047725a05a6ef02e32ac97e (at node_modules/.pnpm/@[email protected]/node_modules/@prisma/engines/libquery_engine-linux-musl-openssl-3.0.x.so.node)
Schema Engine           : schema-engine-cli 6a3747c37ff169c90047725a05a6ef02e32ac97e (at node_modules/.pnpm/@[email protected]/node_modules/@prisma/engines/schema-engine-linux-musl-openssl-3.0.x)
Schema Wasm             : @prisma/prisma-schema-wasm 5.1.1-1.6a3747c37ff169c90047725a05a6ef02e32ac97e
Default Engines Hash    : 6a3747c37ff169c90047725a05a6ef02e32ac97e
Studio                  : 0.492.0

npx prisma db push works fine, Prisma client can connect to the database read & write, but npx prisma migrate dev doesn't work:

npx prisma migrate dev --name init
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "abcd" at "mariadb:3306"

Error: P1010
User `xxxx` was denied access on the database `abcd`

It also can create database, as they are visible in adminer :

I'm using this docker container for mariadb :

FROM mariadb:11.0.2

ADD ./scripts/initdb.sh /docker-entrypoint-initdb.d/initdb.sh

initdb.sh does :

mariadb -uroot -p"$MYSQL_ROOT_PASSWORD" -e "CREATE DATABASE IF NOT EXISTS $DB_PRISMA;"
mariadb -uroot -p"$MYSQL_ROOT_PASSWORD" -e "GRANT ALL PRIVILEGES ON $DB_PRISMA* TO $DB_USER@'%' IDENTIFIED BY '$DB_PASSWORD';"

mariadb -uroot -p"$MYSQL_ROOT_PASSWORD" -e "GRANT CREATE ON *.* TO $DB_USER@'%';"
mariadb -uroot -p"$MYSQL_ROOT_PASSWORD" -e "FLUSH PRIVILEGES;"

Hope it helps!

[Anderson-Vilela-op]

vocês podem resolver isso passando o usuario root no link do prisma dentro do .env

[toLog]

I was able to fix the issue by explicitly creating a shadow_db and defining its URL in the the prisma.schema datasource block:

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

🤷‍♂️

[StanislausA]

Encountered this issue, fixed by proper permissions.

For mysql user must have CREATE, ALTER, DROP, REFERENCES ON *.*

ref: prisma docs

[ErickVal]

I was able to fix the issue by explicitly creating a shadow_db and defining its URL in the the prisma.schema datasource block:

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

🤷‍♂️

using docker this worked for me...

created new database

    image: 'mysql/mysql-server:8.0'
    ports:
        - 3307:3306
    environment:
        MYSQL_ROOT_PASSWORD: 'rootpass'
        MYSQL_ROOT_HOST: 'mysqlroot'
        MYSQL_DATABASE: 'database'
        MYSQL_USER: 'user'
        MYSQL_PASSWORD: 'userpass'
        MYSQL_ALLOW_EMPTY_PASSWORD: 1
    networks:
      - mynetwork

then used new shadow path on shadowDatabaseUrl (schema.prisma)

[lookto]

I can't reproduce this anymore. Issue is therefore closed.

[drobking]

I had same issue but im using Postgres I just have to change username in connection string on 'user'

[Sonlowami]

Hello, I am experiencing this issue as well. I am using MySQL and trying to connect to a remote server. When I run npx prisma migrate dev, I get the same error. For context, if I try to connect to the server from the commandline, copying everything from the connection string, I can successfully connect to the database. Running npx prisma db push works well.

Version: prisma : 5.22.0 @prisma/client : 5.22.0 Computed binaryTarget : debian-openssl-1.1.x Operating System : linux Architecture : x64 Node.js : v18.20.4