"npx prisma migrate dev" fails -> Error: P1001: Can't reach database server at localhost:3306

[ManuWeb3]

Question

In my basic Next.js13 project, I'm using Prisma ORM to interact with MySQL Server 8.2 instance set up locally at port 3306.

schema.prisma & .env files pasted in the appropriate section.

Upon running the command:

npx prisma migrate dev

I get the error below:

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "DBSampleDB" at "127.0.0.1:3306"

Error: P1001: Can't reach database server at `127.0.0.1`:`3306`

Please make sure your database server is running at `127.0.0.1`:`3306`.

I ensured everytime the root password eneterd in ther URL is correct.

========

To ensure that MySQL server is up and running and listening at port 3306 , I've checked bunch of things that prove that the server is indeed up at port 3306.

Most evident out of all those checks is the creation of a dummy database DBSampleDB using SQL Command Line and then creating a dummy table Test_Table with 2 columns (id, name) using 2 different IDEs: DataGrip (JetBeans) and DBeaver after TestConnection got passed using both the IDEs.

Snips below:

=============

Can this be a compatibility issue between versions of Prisma (5.5.2) and MySQL Server (8.2)?

I've exhausted all options suggested by ChatGPT and kapa.ai.

I've been stuck at this isuse for 4 days now as I wasn't aware that this will boil down to Prisma though on the face of it, it seems Prisma but i did not want to jump the gun.

As I'm new to Web development, I had to rule out a whole bunch of things and learned debugging along the way (and there were other issues that I got to know along the way, but the error still persists.)

By no means, I'm an expert and, hence, could have still missed to know other debugging options.

Please help as getting stuck for 4 days is a bit too much.

Thanks
Manu

How to reproduce (optional)

Expected behavior (optional)

As per the tutorial, it should have asked for the name of Migration as the next step and created a migration file under the folder Prisma in the root directory of my codebase, hence, migrating the empty database to the schema defined in the schema.prisma

Information about Prisma Schema, Client Queries and Environment (optional)

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

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

model User {
  id        Int     @id @default(autoincrement())
  email     String  @db.VarChar(255)
  password  String  @db.VarChar(255)
  firstName String  @db.VarChar(255)  @map("first_name")
}

.env file:

DATABASE_URL="mysql://root:******@127.0.0.1:3306/DBSampleDB?connect_timeout=300"

Don't have any Prisma Client queries for now.

• OS: Windows 11

• Database: MySQL Server 8.2

• Node.js version: v18.18.2

• Run prisma -v to see your Prisma version and paste it
  5.5.2

[ManuWeb3]

Hi @ruheni @nurul3101 , though my first choice was not to tag and bother anyone from the Prisma team while posting my question here but as it's been around 17 hours and no reply, so thought of tagging.
I'm stuck at this error and can't proceed with anything unless it gets resolved or I get a workaround.

Thank you.

[janpio]

Can you register an account on planetscale.com and get a MySQL database there and try with that connection string?
Just to confirm and show that your Prisma is capable of connecting to MySQL databases.

[ManuWeb3]

Sure, I'll do it right away and update you.

However, I just re-tried with nextapp, same error.
Then, I manually created the db: nextapp using MySQL's Command Line client.

DataGrip's Test Connection passed but the migration did not.

[ManuWeb3]

Hi @janpio , I'm able to connect to MySQL database with PlanetScale via Prisma.

npx prisma db push

It created 2 tables: User and Item in planetdb database in sync with Prisma schema.

schema.prisma

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

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

model User {
  id           Int      @id @default(autoincrement())
  email        String   @unique
  name         String
  followers    Int      @default(0)
  isActive     Boolean  @default(true)
  registeredAt DateTime @default(now())
}

model Item {
  id    Int    @id @default(autoincrement())
  name  String
  price Float
}

DATABASE_URL='mysql://_RandomStringUserName_:_LongRandomStringPassword_@aws.connect.psdb.cloud/planetdb?sslaccept=strict'

My only concern is that PlanetScale recommends using db push instead of migrate dev.

Does this help us in debugging the underlying issue with migrate dev not able to connect to localhost DB server ?

[janpio]

If migrate dev does not work locally for you, then db push also does not work. Just run it to confirm.

This means that the problem is with your local database (or the connection string you are trying to use for it). It is not a general Prisma problem when talking to a MySQL database.

[ManuWeb3]

Yes, just checked. db push failed locally.
Weird.

Having noted that there are no issues with Prisma but the local DB, can you provide me any other insights/pointers to debug further at my end?

I already reached out to MySQL open community (when I wasn't even sure that issue lies with DB) and opened a discussion but never heard back.
I've already spent days trying to debug it.
Anything which you feel can help?

[janpio]

The difference between uppercase and lowercase database seems weird, and could indicate that something off is happening.
Besides that, nothing comes to mind, sorry.

[ManuWeb3]

Ok.
But, have upper/lower case been the issue, don't you think migration should have run with nextapp name?
Because, nextapp did appear everywhere (MySQL CLI, DataGrip IDE, etc.) in lowercase only unlike DBSampleDB name.

I know you already provided me with the best you could think of, just that this crossed my mind and I shared.

[janpio]

Yes, that also confuses me - if you really tried all combinations, this should exclude this as the source. But it is the only weird and non expected thing that we saw from your description and screenshots. It's not normal, so this is where I would start and figure out how to remove it from the equation. If the problem then still persists, you would know that it was not that.

[MrVhek]

Not sure if this would help, but you can try using localhost instead of 127.0.0.1, I already had issue with this.

[ManuWeb3]

Hey @MrVhek , thanks for the reply. Actually, I already tried this switch during my initial debugging but it did not help.
Either way, the issue still persisted.
I also tried to change the default port 3306 to 3309 in MySQL Server's Configurator and changed the port number in the connection string.
Still, the same issue.

[daytatech]

I am also having this issue

[laxmariappan]

I got the same error, too

Error: P1001: Can't reach database server at db:10010

Please make sure your database server is running at db:10010.

mysql://user:passwd@localhost:10010/dbname

[ManuWeb3]

Mine never worked till date and now I've stopped worrying about it, already wasted tons of my time on it.
Kindly refer to (#21666 (reply in thread))
I'm happy working with PlanetScale's MYSQL dbms solution.

You may also want to give it a shot. Good luck.

[mconfjp]

I have a similar situation and I'm writing about it here.

Situation

I was creating an app with Docker-compose.yml looking like this

version: '3.7'
services:
  app:
    build: .
    tty: true
    ports:
      - 3005:3005
      - 5555:5555
    volumes:
      - .:/app
      - /app/node_modules
    depends_on:
      - db
  db:
    image: mysql:8.0
    volumes:
      - db-store:/var/lib/mysql
      - ./logs:/var/log/mysql
      - ./docker/mysql/my.cnf:/etc/mysql/conf.d/my.cnf
      - ./docker/init:/docker-entrypoint-initdb.d
    environment:
      - MYSQL_DATABASE=${DB_NAME}
      - MYSQL_USER=${DB_USER}
      - MYSQL_PASSWORD=${DB_PASS}
      - MYSQL_ROOT_PASSWORD=${DB_PASS}
      - TZ=${TZ}
    ports:
      - ${DB_PORT}:3306

And the DATABASE _URL is this

DATABASE_URL="mysql://root:password@localhost:3306/database"

And same error

Solution

DATABASE _URL was like this

DATABASE_URL="mysql://root:password@db:3306/database"

prisma generate command location was in the app, so it seems that specifying localhost referred to the app itself.

[Shivanand0297]

https://forum.codewithmosh.com/t/trouble-with-prisma-mysql-connection-string-when-using-socket-connection-method/23823

this helped me make sure your password in the connection string is URL encoded.

[Womoemy]

Using the percent encoder here also worked for me.

[Tejas73]

Hey try this command because it worked for me when I faced the same issue today
npx prisma migrate dev --name init