Search code examples
mysqlprisma

Prisma error: p1001: can't reach database server at `localhost`:`3306`

I am running a nextJS application on a VPS and using nextAuth with prisma. I have MySQL server running on port 3306. When runnning npx prisma migrate dev --name init --schema src/prisma/schema.prisma I get an error:

Environment variables loaded from .env
Prisma schema loaded from src/prisma/schema.prisma
Datasource "db": MySQL database "node-users" at "localhost:3306"

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

I ve tried the solutions I could find online:

  • downgraded node because 16.17 was supposedly causing issues
  • added connect_timeout to my url
  • restarted mysql.service
  • added a new user

but with no success.

  • schema:
generator client {
  provider = "prisma-client-js"
}
datasource db {
  provider = "mysql"
  url      = "mysql://admin:password@localhost:3306/node-users?connect_timeout=300"
}

model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?
  user              User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
  @@index([userId], map: "Account_userId_fkey")
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId], map: "Session_userId_fkey")
}

model User {
  id               String      @id @default(cuid())
  name             String?
  email            String?     @unique
  emailVerified    DateTime?
  image            String?
  stripeCustomerId String?     @unique
  hasBook          Boolean     @default(false)
  paymentIntent    String?     @unique
  paymentDate      DateTime? 
  accounts         Account[]
  sessions         Session[]
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

  • netstat: tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 25865/mysqld

  • node_version: v16.13.2

The whole configuration works on my local, just not on the vps. Also I am able to login mysql with the user and the password.

Solution

  • I had the same issue, In .env` file, I removed the quotes for the Databse URL and it is working now, Not sure if it was the thing which fixed it but that thing worked for me.

    The error which I got while pushing into prisma DB

    Error while pushing into prisma db

    Potential Fix (Which worked for me)

    Put the DB URL in .env file and import like this

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

    Inside .env

    DATABASE_URL = url_with_quotes