Can't run prisma migrate on mysql8: Unknown authentication plugin `sha256_password'.

[travisneilturner]

Bug description

Using this docker-compose:

version: "3.9"

services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_USER: user
      MYSQL_PASSWORD: password
      MYSQL_ROOT_PASSWORD: root_password
      MYSQL_DATABASE: mydatabase
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
    command: --default-authentication-plugin=mysql_native_password

volumes:
  mysql_data:

When I run

❯ docker compose up -d
❯ prisma migrate dev --name init

I see:

Environment variables loaded from .env
Prisma schema loaded from schema.prisma
Datasource "db": MySQL database "mydatabase" at "localhost:3306"

Error: Migration engine error:
Error querying the database: Unknown authentication plugin `sha256_password'.

How to reproduce

Run

prisma migrate dev --name init

Expected behavior

The migrations are applied successfully

Prisma information

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

// generator
generator client {
  provider             = "prisma-client-py"
  interface            = "asyncio"
  recursive_type_depth = 5
}

model Organization {
  id   String @id @default(cuid())
  name String

  websiteUrl   String?
  contactEmail String?

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

  users User[]
}

Environment & setup

• OS: MacOS Ventura
• Database: MySQL 8
• Python version: Python 3.10.10
• Prisma version:

prisma                  : 4.11.0
prisma client python    : 0.8.2
platform                : darwin
expected engine version : 8fde8fef4033376662cad983758335009d522acb
installed extras        : []
install path            : /Users/highlander85/Library/Caches/pypoetry/virtualenvs/my-service-hE8eyDXL-py3.10/lib/python3.10/site-packages/prisma
binary cache dir        : /Users/highlander85/.cache/prisma-python/binaries/4.11.0/8fde8fef4033376662cad983758335009d522acb

[RobertCraigie]

Looks like there have been a couple reports of this on the main prisma repo, https://github.com/prisma/prisma/issues/9454, https://github.com/prisma/prisma/issues/17306, https://github.com/prisma/prisma/issues/14417.

I also faced this issue. I solved the issue by correcting a wrong DB user name in the config.

DATABASE_URL="mysql://root:123456@127.0.0.1:3306/36pic" correct DATABASE_URL="mysql://root@123456@127.0.0.1:3306/36pic" incorrect

Could you look into some of these suggestions (from the linked issues) & report back if that fixes your issue?