SQLite query times out after time N/A

Aarav-Juneja commented 1 month ago

Bug description

When running a large number of update operations >1500, the operation times out. I'm using Promise.all, and the queries are being resolved quite quickly, but it still times out.

Here's the error:

16 | `;throw new Error(u)}else if(r==="warn"){let u=`Conflict for env var${s.length>1?"s":""} ${s.map(c=>H(c)).join(", ")} in ${X(a)} and ${X(l)}
17 | Env vars from ${X(l)} overwrite the ones from ${X(a)}
18 |       `;console.warn(`${Ie("warn(prisma)")} ${u}`)}}}}function zo(e){if(zu(e)){ai(`Environment variables loaded from ${e}`);let t=li.default.config({path:e,debug:process.env.DOTENV_CONFIG_DEBUG?!0:void 0});return{dotenvResult:Ko(t),message:Ae(`Environment variables loaded from ${gt.default.relative(process.cwd(),e)}`),path:e}}else ai(`Environment variables not found at ${e}`);return null}function Yo(e,t){return e&&t&&gt.default.resolve(e)===gt.default.resolve(t)}function zu(e){return!!(e&&Wr.default.existsSync(e))}var Zo="library";function Ht(e){let t=Yu();return t||(e?.config.engineType==="library"?"library":e?.config.engineType==="binary"?"binary":Zo)}function Yu(){let e=process.env.PRISMA_CLIENT_ENGINE_TYPE;return e==="library"?"library":e==="binary"?"binary":void 0}var Ge;(t=>{let e;(E=>(E.findUnique="findUnique",E.findUniqueOrThrow="findUniqueOrThrow",E.findFirst="findFirst",E.findFirstOrThrow="findFirstOrThrow",E.findMany="findMany",E.create="create",E.createMany="createMany",E.createManyAndReturn="crea | ... truncated
19 | ${(0,is.default)(ec(n),2)}
20 | }`}};function Xu(e){let t;if(e.length>0){let r=e.find(n=>n.fromEnvVar!==null);r?t=`env("${r.fromEnvVar}")`:t=e.map(n=>n.native?"native":n.value)}else t=void 0;return t}function ec(e){let t=Object.keys(e).reduce((r,n)=>Math.max(r,n.length),0);return Object.entries(e).map(([r,n])=>`${r.padEnd(t)} = ${tc(n)}`).join(`
21 | `)}function tc(e){return JSON.parse(JSON.stringify(e,(t,r)=>Array.isArray(r)?`[${r.map(n=>JSON.stringify(n)).join(", ")}]`:JSON.stringify(r)))}var Yt={};jt(Yt,{error:()=>ic,info:()=>nc,log:()=>rc,query:()=>oc,should:()=>os,tags:()=>zt,warn:()=>mi});var zt={error:ce("prisma:error"),warn:Ie("prisma:warn"),info:Oe("prisma:info"),query:et("prisma:query")},os={warn:()=>!process.env.PRISMA_DISABLE_WARNINGS};function rc(...e){console.log(...e)}function mi(e,...t){os.warn()&&console.warn(`${zt.warn} ${e}`,...t)}function nc(e,...t){console.info(`${zt.info} ${e}`,...t)}function ic(e,...t){console.error(`${zt.error} ${e}`,...t)}function oc(e,...t){console.log(`${zt.query} ${e}`,...t)}function Hr(e,t){if(!e)throw new Error(`${t}. This should never happen. If you see this error, please, open an issue at https://pris.ly/prisma-prisma-bug-report`)}function De(e,t){throw new Error(t)}function gi(e,t){return Object.prototype.hasOwnProperty.call(e,t)}var hi=(e,t)=>e.reduce((r,n)=>(r[t(n)]=n,r),{});function ht(e,t){let r={};for | ... truncated

PrismaClientKnownRequestError:
Invalid `prisma.question.update()` invocation in
/home/aaravj/Desktop/Testing/Bun/Prisma/index.ts:35:31

  32
  33 await Promise.all(
  34     res.questions.map(async (question, index) => {
→ 35         await prisma.question.update(
Operations timed out after `N/A`. Context: The database failed to respond to a query within the configured timeout — see https://pris.ly/d/sqlite-connector for more details. Database: /home/aaravj/Desktop/Testing/Bun/Prisma/prisma/./dev.db
 code: "P1008"

      at new PrismaClientKnownRequestError (/home/aaravj/Desktop/Testing/Bun/Prisma/node_modules/@prisma/client/runtime/library.js:21:1414)
      at handleRequestError (/home/aaravj/Desktop/Testing/Bun/Prisma/node_modules/@prisma/client/runtime/library.js:121:7753)
      at handleAndLogRequestError (/home/aaravj/Desktop/Testing/Bun/Prisma/node_modules/@prisma/client/runtime/library.js:121:7061)
      at /home/aaravj/Desktop/Testing/Bun/Prisma/node_modules/@prisma/client/runtime/library.js:121:6745

Bun v1.1.20 (Linux x64)

I have the code below, and it only seems to be erroring out when I keep the find operation, and if I use a static value, the query is able to execute correctly. I suspect that's just because static values resolve in under the timeout.

How to reproduce

Use the code, schema, and json format I have provided. The code will timeout after time M/A

Expected behavior

All queries should work and there should be no timeout for database operations, especially large ones.

Prisma information

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

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

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

model Sample {
  id   Int    @id @default(autoincrement())
  name String @unique
  val  Int
}

model Test {
  id          Int        @id @default(autoincrement())
  name        String
  description String?
  // Question
  questions   Question[]
}

model Question {
  id       Int    @id @default(autoincrement())
  test     Test   @relation(fields: [testId], references: [id])
  testId   Int
  question String

  correctAnswerId Int
  answers         Answer[]
}

model Answer {
  id         Int      @id @default(autoincrement())
  question   Question @relation(fields: [questionId], references: [id])
  questionId Int
  answer     String
}

import { PrismaClient } from "@prisma/client";

export const prisma = new PrismaClient();

const data = (await import("./database_1.json")).default;

const res = await prisma.test.create({
    data: {
        name: "Test 1",
        questions: {
            create: data.map(question => ({
                question: question.question,
                answers: {
                    create: question.choices.map(choice => ({
                        answer: choice,
                    })),
                },
                correctAnswerId: 0,
            })),
        },
    },
    include: {
        questions: {
            include: {
                answers: true,
            },
        },
    },
});

await Promise.all(
    res.questions.map(async (question, index) => {
        await prisma.question.update({
            where: {
                id: question.id,
            },
            data: {
                correctAnswerId: question.answers.find(
                    answer => answer.answer === data[index].cor_choice[0]
                )?.id,
            },
        });
    })
);

The json file has 1600 values with the format:

{
    question: string;
    choices: string[];
    cor_choice: string[];
}[]

Environment & setup

OS: Linux DESKTOP-BNBDO4K 5.15.153.1-microsoft-standard-WSL2 #1 SMP Fri Mar 29 23:14:13 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux
Database: SQLite
Bun version: 1.1.20

Prisma Version

prisma                  : 5.18.0
@prisma/client          : 5.18.0
Computed binaryTarget   : debian-openssl-1.1.x
Operating System        : linux
Architecture            : x64
Node.js                 : v22.3.0
Query Engine (Node-API) : libquery-engine 4c784e32044a8a016d99474bd02a3b6123742169 (at ../../../../../tmp/bunx-1000-prisma@latest/node_modules/@prisma/engines/libquery_engine-debian-openssl-1.1.x.so.node)
Schema Engine           : schema-engine-cli 4c784e32044a8a016d99474bd02a3b6123742169 (at ../../../../../tmp/bunx-1000-prisma@latest/node_modules/@prisma/engines/schema-engine-debian-openssl-1.1.x)
Schema Wasm             : @prisma/prisma-schema-wasm 5.18.0-25.4c784e32044a8a016d99474bd02a3b6123742169
Default Engines Hash    : 4c784e32044a8a016d99474bd02a3b6123742169
Studio                  : 0.502.0

sarthakgaur commented 2 weeks ago

Got the same issue. Here are the details:

schema.prisma:

datasource db {
provider = "sqlite"
url      = "file:./dev.db?connection_limit=2"
}

journal_mode is set to WAL.
PrismaServiceOptions:

  createPrismaOptions(): Promise<PrismaServiceOptions> | PrismaServiceOptions {
    return {
      prismaOptions: {
        log: ['info'],
        transactionOptions: {
          maxWait: 5000,
          timeout: 10000,
          isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
        }
      },
    }
  }

Code which failed:

    return await this.prisma.print_order.update({
      where: {
        print_id: printOrder.printId,
      },
      data: {
        is_printed: printOrder.isPrinted,
        failure_reason: printOrder.failureReason,
      },
    });

Error message:

30/09/2024, 17:51:07: error "Query print_order.update failed: 
Invalid 'this.prisma.print_order.update()' invocation
in \nC:\Users\XX\AppData\Local\XXXXXXXXXXXXXXXXXXXXXXX\app-
1.0.25\resources\app\nestjs-server\dist\print-order\print-
order.service.js:51:46\n 48 async updatePrintOrder(message) {n 49 const 
printOrder = message.payload;n 50 
this.checkPrintOrderUpdatePayload(printOrder);\n 51 return await 
this.prisma.print_order.update()\nOperations timed out after 'N/A'. Context: The 
database failed to respond to a query within the configured timeout — see 
https://pris.ly/d/sqlite-connector for more details. Database: 
C:\Users\XX\AppData\Local\XXXXXXXXXXXXXXXXXXXXXXX\app- 
1.0.25\resources\app\nestjs-server\prisma\dev.db"

OS: Windows 10
Prisma version: 5.19.1

I am not using Promise.all anywhere in my code. This error happens randomly. I'll try to use this url = "file:./dev.db?connection_limit=2&socket_timeout=10" in schema.prisma to see if it solves the issue. The default timeout if 5 seconds. And, there is nothing in my application that would lock the database for even 5 seconds.

sarthakgaur commented 2 weeks ago

@Aarav-Juneja Can you try the following in your schema.prisma file?

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db?connection_limit=1&socket_timeout=10"
}

Does this solve your problem?

prisma / prisma