ER_LOCK_WAIT_TIMEOUT on deleting collections

[HoseinGhanbari]

Describe the bug on a particular scenario (which will mention) and using sample data populated during @vendure/create, deleting multiple collections throws ER_LOCK_WAIT_TIMEOUT with the following stack

[server] error TIME - [Vendure Server] ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction
[server] QueryFailedError: ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction
[server]     at Query.<anonymous> (backend-2.0.8\node_modules\typeorm\src\driver\mysql\MysqlQueryRunner.ts:222:33)
[server]     at Query.<anonymous> (backend-2.0.8\node_modules\mysql\lib\Connection.js:526:10)
[server]     at Query._callback (backend-2.0.8\node_modules\mysql\lib\Connection.js:488:16)
[server]     at Query.Sequence.end (backend-2.0.8\node_modules\mysql\lib\protocol\sequences\Sequence.js:83:24)
[server]     at Query.ErrorPacket (backend-2.0.8\node_modules\mysql\lib\protocol\sequences\Query.js:92:8)
[server]     at Protocol._parsePacket (backend-2.0.8\node_modules\mysql\lib\protocol\Protocol.js:291:23)
[server]     at Parser._parsePacket (backend-2.0.8\node_modules\mysql\lib\protocol\Parser.js:433:10)
[server]     at Parser.write (backend-2.0.8\node_modules\mysql\lib\protocol\Parser.js:43:10)
[server]     at Protocol.write (backend-2.0.8\node_modules\mysql\lib\protocol\Protocol.js:38:16)
[server]     at Socket.<anonymous> (backend-2.0.8\node_modules\mysql\lib\Connection.js:88:28)

To Reproduce

1. create fresh db using

CREATE DATABASE `vendure_dev` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

2. create fresh server using following command and let the @vendure/create populate db with sample data

npx @vendure/[email protected] backend --log-level verbose

3. update excludes array of tsconfig.json

"exclude": [
    "node_modules",
    "migration.ts",
    "src/plugins/**/ui/*",
    "admin-ui"
],

4. run the development server using

yarn dev

5. now delete all products from inventory through admin-ui
6. next delete all facets from catalog through admin-ui
7. now go to collections from catalog through admin-ui, select multiple collections and use the With 2 selected... button choose Delete option. it should wait unreasonable amount of time and then inside server console you will see the ER_LOCK_WAIT_TIMEOUT sometimes actual removal will be persisted but some other times it wont.

• note that you should select multiple collections for removal (selecting single collection will not always reproduce the exception)
• note that nothing was touched on vendure-config.ts

Expected behavior successful removal of selected collections without any unreasonable timeout or exception

Environment (please complete the following information):

• @vendure/core 2.0.8:
• Nodejs v18.17.1
• Database (mysql/postgres etc): mysql 10.4.28-MariaDB

Additional context nothing yet

[michaelbromley]

Hi,

I'm unable to reproduce this. I tried in several ways: following your instructions of deleting all the products & facets first; I also tried creating new collections, nesting them, and deleting. I did not encounter any errors.

I'm using 11.1.2-MariaDB, latest master branch (~2.1.5)

[HoseinGhanbari]

Hi,

That's all right. I will keep this issue updated if found anything new & relevant.

Thank you for the great Vendure project 🙏

[HoseinGhanbari]

Hi,

This issue happened to me again, the scenario was exactly the same as before.

I removed all products, all facets but when came to collections, selected multiple collections and tried to delete them but nothing happened and after a period of time, server console logged the following

[Vendure Server] ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction
QueryFailedError: ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction
    at Query.<anonymous> (program\node_modules\typeorm\src\driver\mysql\MysqlQueryRunner.ts:222:33)
    at Query.<anonymous> (program\node_modules\mysql\lib\Connection.js:526:10)
    at Query._callback (program\node_modules\mysql\lib\Connection.js:488:16)
    at Query.Sequence.end (program\node_modules\mysql\lib\protocol\sequences\Sequence.js:83:24)
    at Query.ErrorPacket (program\node_modules\mysql\lib\protocol\sequences\Query.js:92:8)
    at Protocol._parsePacket (program\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (program\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (program\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (program\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (program\node_modules\mysql\lib\Connection.js:88:28)

I can send you a copy of DB (on discord / privately) that is being used to let you debug against that.

environment: @vendure/core: 2.1.7 Nodejs: v20.11.0 Database: MySQL Community Server - version 8.0.36