prisma: sqlite timeout error multiple queries run one after another

Bug description

I’ve got a self contained project that runs with NextJS 12 using the API for routing and client side for updates.

I consistently get connection errors like this when the seeding happens when the app first runs, and also when doing simple multiple queries like 3 deletes in a row of different rows. It’s easy to reproduce by building this app and then generating and migrating. The seeding fails. Then, if you seed manually, all you have to do is run the app and delete the 3 note cards showing and you’ll see the errors generated.

I’m attaching a zip of the project as well as two videos. One shows no errors with 2.28 and the other with 3.5 causing the errors doing exactly the same deletes.

How to reproduce

Expected behavior

Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: ConnectionError(Timed out during query execution.) })

[peter-kellner-sqlite-problems-clip-99-final.zip](https://github.com/prisma/prisma/files/7550550/peter-kellner-sqlite-problems-clip-99-final.zip]

https://user-images.githubusercontent.com/241170/142082631-a97735b6-8a0d-48cf-ada0-833640191050.mp4

https://user-images.githubusercontent.com/241170/142084736-8ade24f9-5dc4-483d-828d-5bde42b3557b.mp4

)

Prisma information

Environment & setup

OS: MacOS
Database: SQLite
Node.js version: 16.5.0

Prisma Version

3.5 (see description of bug)

About this issue

Original URL
State: open
Created 3 years ago
Reactions: 5
Comments: 24 (9 by maintainers)

Most upvoted comments

Ok, I can create the ConnectorError(ConnectorError { user_facing_error: None, kind: ConnectionError(Timed out during query execution.) }) sometimes with the newest Prisma version in the UI when one of the cards has additional attributes like being marked with the star. When I then delete multiple notes quick after each other, I get that error as well for one of those requests.

handle prisma.noteAttributes
processGetOnePutAndDelete
handleDelete: 1fdffc4e-aac6-450d-b210-1dd02769dbbc
processGetOnePutAndDelete
handleDelete: 81dbef75-bea9-42b0-8ebe-af4e8cde88bb
prisma:query BEGIN
prisma:query SELECT `main`.`NoteAttributes`.`id`, `main`.`NoteAttributes`.`noteId`, `main`.`NoteAttributes`.`important`, `main`.`NoteAttributes`.`pinned`, `main`.`NoteAttributes`.`updateDate` FROM `main`.`NoteAttributes` WHERE `main`.`NoteAttributes`.`id` = ? LIMIT ? OFFSET ? /* traceparent=00-00-00-00 */
prisma:query SELECT `main`.`NoteAttributes`.`id` FROM `main`.`NoteAttributes` WHERE `main`.`NoteAttributes`.`id` = ? /* traceparent=00-00-00-00 */
prisma:query DELETE FROM `main`.`NoteAttributes` WHERE `main`.`NoteAttributes`.`id` IN (?) /* traceparent=00-00-00-00 */
prisma:query BEGIN
prisma:query COMMIT
prisma:query SELECT `main`.`Note`.`id`, `main`.`Note`.`title`, `main`.`Note`.`description`, `main`.`Note`.`createDate` FROM `main`.`Note` WHERE `main`.`Note`.`id` = ? LIMIT ? OFFSET ? /* traceparent=00-00-00-00 */
prisma:query SELECT `main`.`Note`.`id` FROM `main`.`Note` WHERE `main`.`Note`.`id` = ? /* traceparent=00-00-00-00 */
prisma:query DELETE FROM `main`.`Note` WHERE `main`.`Note`.`id` IN (?) /* traceparent=00-00-00-00 */
prisma:query COMMIT
handle prisma.noteAttributes
processGetOnePutAndDelete
handleDelete: 9e827643-21aa-4c73-b4e0-4c11e5150507
processGetOnePutAndDelete
handleDelete: 3ddb4194-c338-4df1-9cdf-2302ad3f4438
prisma:query BEGIN
prisma:query SELECT `main`.`NoteAttributes`.`id`, `main`.`NoteAttributes`.`noteId`, `main`.`NoteAttributes`.`important`, `main`.`NoteAttributes`.`pinned`, `main`.`NoteAttributes`.`updateDate` FROM `main`.`NoteAttributes` WHERE `main`.`NoteAttributes`.`id` = ? LIMIT ? OFFSET ? /* traceparent=00-00-00-00 */
prisma:query SELECT `main`.`NoteAttributes`.`id` FROM `main`.`NoteAttributes` WHERE `main`.`NoteAttributes`.`id` = ? /* traceparent=00-00-00-00 */
prisma:query BEGIN
prisma:query SELECT `main`.`Note`.`id`, `main`.`Note`.`title`, `main`.`Note`.`description`, `main`.`Note`.`createDate` FROM `main`.`Note` WHERE `main`.`Note`.`id` = ? LIMIT ? OFFSET ? /* traceparent=00-00-00-00 */
prisma:query SELECT `main`.`Note`.`id` FROM `main`.`Note` WHERE `main`.`Note`.`id` = ? /* traceparent=00-00-00-00 */
prisma:query DELETE FROM `main`.`Note` WHERE `main`.`Note`.`id` IN (?) /* traceparent=00-00-00-00 */
prisma:query ROLLBACK

Invalid `prisma.note.delete()` invocation:


Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: ConnectionError(Timed out during query execution.) })
prisma:query DELETE FROM `main`.`NoteAttributes` WHERE `main`.`NoteAttributes`.`id` IN (?) /* traceparent=00-00-00-00 */
prisma:query COMMIT
prisma:query SELECT `main`.`Note`.`id`, `main`.`Note`.`title`, `main`.`Note`.`description`, `main`.`Note`.`createDate` FROM `main`.`Note` WHERE 1=1 LIMIT ? OFFSET ? /* traceparent=00-00-00-00 */

It looks like your app is sending multiple HTTP queries quickly after each other or in parallel, and then when executing these queries one of them instantly fails with this error message.

Reproduction steps:

Download and extract https://github.com/prisma/prisma/files/7582605/clip-12-adding-optimistic-ui-for-better-user-experience-final.zip
Upgrade Prisma and Prisma Client to newest version
npm i
Confirm versions via npx prisma -v
Run resetdb.sh or commands from that to a) migrate database and b) seed data
Start app with npm run dev
Click around on http://localhost:3000/ until error pops up

janpio on Sep 9, 2022

Ok, thanks for the details I’ll let @pantharshit00 checks this

Jolg42 on Nov 22, 2021

Hello @pkellner

Looks like you are running into https://github.com/prisma/prisma/issues/10194 which is a known bug. The execution of seed script from npx prisma migrate dev seems to be failing. Here is a quick workaround for this:

resetdb.sh
rm prisma/dev.db

npx prisma generate

npx prisma migrate dev --skip-seed

npx prisma db seed
Here instead of using the seed script execution from migrate we are going to skip it and explicitly execute the seed script. I tried this out in your projects and I am getting no errors now.

Let me know if this helps and then we can probably close this issue as a duplicate.

The other part of this bug report with timeouts happening on consecutive sql deletes is the more important error that may or not be related. Please don’t close.

pkellner on Nov 17, 2021