SequelizeTimeoutError: SQLITE_BUSY: database is locked

[PJHRobles]

Describe the bug
The cron for deleting old builds ran at the same time lighthouse was running and probably caused the SQLITE_BUSY error

To Reproduce

1. Wait for the delete cron to run
2. Run lhci upload at the same time
3. See error below

Expected behavior
Delete cron works properly deleting several runs and lighthouse is able to save the run

Logs/Screenshots

Publish lighthouse report with ./node_modules/.bin/lhci upload --ignoreDuplicateBuildFailure=true --serverBaseUrl=https://lighthouse-server.k.tools.flnltd.com --target=lhci --token=****
Saving CI project gaf-dev (509b5f7e-d9cd-44e0-87d9-7f156ad669d1)
Saving CI build (2895552a-ac39-4c99-9237-e99067afd96c)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (ec39a174-23cf-4219-b4fb-a40872d7acc0)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (25749821-edc0-4372-b58c-ac9223b02bed)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (e280787d-1f7f-483b-b640-fc4138c55dd4)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (a22f6d22-ca98-4d7a-9e3b-c7ede32bb4d8)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (41a04b19-e208-4d7e-ab28-f2b1737bc044)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (8625b1d0-4f61-422f-8681-c27844c6c0c1)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (20469065-8243-4137-8e33-c41bcab974a0)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (839f9660-8293-477b-b3ad-bd5c1989cd6a)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (0009bac2-ee07-442b-bd9d-e093cc8c57aa)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (68292fbc-ece4-47ff-9c67-47dcf67304e1)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (07e9a877-5f0b-4257-82b7-2b0de6dd9d6b)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (b6481c3e-a747-4028-9405-2f6a527e38a0)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (0bcfd00d-8340-428b-8725-e4d022290790)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (70ed86c4-2672-48c5-b2fd-509b60c59f25)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (7661f759-0ece-4fcd-b88a-24eedddf1193)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (4a5ba045-2d56-4cc8-8591-da576c99871a)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (10f22ed4-d551-4033-a3cb-f5cae3682b29)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (f169dc66-f561-479c-8779-3577a74c0a18)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (099c59ec-729e-4c67-9c18-c4f5917e6a16)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (763960ec-1a8b-4b10-9107-9b24b3e2c9be)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (3e24143e-2f6e-4569-b6ff-27eb2cd5bb75)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (125c1bb2-8e91-45b1-b585-994ffa29c0b1)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (f5a93266-0522-4146-8b35-258fda30fe73)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (34189aba-b19c-4af1-bf7c-3e8d087d1e1a)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (23f2ae8d-2369-40e9-ac1f-cc2cb98a888e)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (0b96f771-df36-4288-ac90-6b364b4caf64)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (cadf5685-475a-4ce7-9c35-41145d0c040a)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (690a2a1b-6dc6-4ddd-a1e7-f6f099ed348d)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (09de8632-60a2-43cc-b0d6-3b78e9263058)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (e487cb2b-94f4-47b8-a41d-7e7ade55dd3b)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (4ec6eeac-74a4-4dc9-90e1-96c38432509d)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (78d877c0-cb5e-4350-825c-5eca56f2bec0)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (12ead7da-4f15-4efe-875e-28a76c40fd99)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (e10355cf-1605-4720-adc8-0218e555ef91)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (3a81d4bb-c8e7-4764-af4c-77d7b04b6ee6)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (42cfe668-9670-434a-9b07-138a6e5ca701)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (c6a2390b-95bc-4e39-90cb-d10dcc955eda)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (d7f6e623-1042-4cc9-b9fe-4e69efcd4335)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (5c5ff5bf-8c46-4822-89ae-cbfeeeb345e4)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (a2c19e7f-682e-4db9-9932-1914a5796ff8)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (65736b28-6820-4421-b4aa-17f7bf7a3c34)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (bcc38166-76e8-403c-b00e-8babf9788e52)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (41e97681-ccae-4e2d-ad23-0fea4b5d3cdb)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (28956c4f-7412-46bf-9e03-9c4225dd90b7)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (cba3d495-782d-4137-b96e-278f6691ead3)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (c0109448-8aa3-4971-920a-5e2e2c41c51c)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (23f98ebc-a947-40b5-853e-7660ba15eab2)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (87513b8c-9efc-4396-8bb0-38ce43ee4b33)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (2fc1fe03-b9d5-485f-bd7b-0c07b6392d83)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (f39d4abf-4da1-44ad-9f24-748b2c0e12a8)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (5f9b8490-cf20-40e7-8150-f76583417939)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (dc1aea0f-13ef-48b5-b0d5-fe5566712508)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (14e31df0-9ba4-4903-a6bf-0cb85a5ebab8)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (15cc5be4-eca9-489f-ba4d-66fefc93d57c)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (6e40e46b-e4cc-4bf6-8c93-018b62e4028c)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (6133458a-c290-4473-997e-f004dcb7a093)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (fcfc6ac3-6a2f-4203-b24c-fbf4a043c77a)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (cf5354f8-ef1f-4cab-b8fb-7120953531c0)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (a41203ef-eeaf-4fab-900a-f3618a67718c)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (b877884d-f0c0-4940-b191-6de40a969d64)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (a590f7da-882d-4659-9c61-996c09d160c5)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (1425ccc4-2bc2-4f21-ab13-e10e428c8aed)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (e677aa33-fa6e-451a-9305-3c4986d67e45)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (c907ee01-96a1-454d-aa48-db0ae012f8e0)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (7f5849f4-e977-4f11-bac4-3c3775ca5415)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (f0b733b3-b1ff-460d-9e03-7233b17649b8)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (671423bf-1a3a-4bf1-b121-feafd35bfffd)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (2dfa580c-d5a2-4a7c-8235-6c9dafd4a300)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (8cbffac1-6bf5-4ba5-a3ad-9a05265c75cd)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (1c126b74-e51d-4892-8efd-64d28ae16a33)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (fd6a8388-b840-4d11-ab15-e7e10375d9bb)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (c014c098-6e9b-4621-9f16-56b2ee82fc0d)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (5cf46de4-231a-4f70-b3e5-57cb16a94a53)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (e06ccda1-7b57-4d38-b683-c58c0501bb65)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (86cf54e2-26f1-4a88-b4c4-3ca24642bba3)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (0ec77f30-3316-4b4b-b020-d8b51001ec9b)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (54aa3ad5-580d-4a32-ad5b-4b04c4adcc8b)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (56dd8324-6be6-4ab1-a74e-0f615fadfcc1)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (4a3c46a8-923e-4fe5-bf8d-e74725146325)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (4bc6ca03-f624-4750-ba33-5615bc4f89d0)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (f1907bea-e908-48a4-bd50-19022f0321ec)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (d6777b06-7c5a-48fa-b80c-64cb958132b7)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (2521cfbb-cf6f-4031-b59f-cb9114583c5c)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (853d8433-ea8c-4e38-a243-2a7e847b6438)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (528874a2-25f5-4570-9590-03a7725591b7)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (05f08a14-64cc-435c-b32d-2ac07996f42e)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (5f890369-63f2-48c1-b4df-0aa89b407908)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (d02938e3-cf02-4c89-adf6-b6e29fba536a)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (bc31913e-8908-4af1-a959-4ece67cc3787)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (c491a3e8-3678-4fbe-921b-b2db4ced920d)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (9d967879-3f98-49ff-bab0-9d67aeeb09f4)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (80b68827-bfe1-4193-b896-20874d0f61bb)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (3ef036ee-dd32-45e7-ba0a-f47e33b4e68a)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (d90c6bb0-985c-48e4-906b-15df63f53fcc)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (d2cbd682-8014-4eb8-a2ea-148b53dfcdd3)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (00524cc7-4aed-45fe-90bd-c83f65a33852)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (b06d19f7-3066-443f-b1c1-a9a4af6ee6bf)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (4b536f3b-77bc-442d-acc7-5eb9af121632)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (d60c930d-dab3-4f9a-8d82-8f3d97b06314)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (9dff3550-f5fd-414b-979a-155c338edb42)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (858a6a12-d9ba-4781-a2d1-bf37bda72d58)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (3b5251c8-5f04-47f0-bfde-5ce2024e23a4)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (88c1eb2e-ce10-4ec2-9f44-3c0ca13a05b6)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (25c5d09c-9bd3-44c0-91da-7acb5e726930)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (dd2de29c-a4aa-41d0-8509-ae0f0e00acd3)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (a073aa8f-74bc-4258-ad8a-bfaf9433bf4a)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (47021c93-c545-4e68-94a7-d5b1ed9e53c9)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (343e4e2e-97d8-4d28-bed9-a42963e08420)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (6f4eee5c-22c4-4177-a5f6-8907b8fafbd1)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (31315f8b-9392-4b6d-93c9-e9f91f80e118)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (b6bee46a-1d4b-43bb-b542-d63254d0ed40)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (7fcdfc99-d0ac-4f6b-ad3d-9f6d53994b76)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (c9d273b6-bbc4-4457-b461-de2e61428380)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (87492f23-7f47-4fb7-9008-d7467bd5f1bf)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (0b8ec007-5b23-4490-8886-5e4446b76223)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (cdf894b1-0b4f-44a8-b3fd-840b77bf1abb)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (8415efbd-3267-470b-9172-94a781b8a1fd)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (aa5b6ae7-8b24-4f83-bd49-5a2195821d71)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (458ca5b0-5f6a-480a-be8f-d00a2d41db93)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (174553ac-b49f-4bf0-888c-b2b92b4d50cc)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (4d7df7ba-bfaa-4bd7-b896-c025fcb88ed4)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (a09e39b0-95b8-465e-98f8-53cf44979eb3)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (3da2ea7e-29da-4dcc-9a22-5819a761b055)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (abb40472-9f20-488a-bdc3-30af7d4a7c79)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (0ffde17e-1a66-48f6-a26c-47654015fb56)

Saved LHR to https://lighthouse-server.k.tools.flnltd.com (78edcd9b-76eb-44ff-8eea-90737fafcdc1)

Error: Unexpected status code 500
  <!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Error</title>
</head>
<body>
<pre>SequelizeTimeoutError: SQLITE_BUSY: database is locked<br> &nbsp; &nbsp;at Query.formatError (/usr/src/lhci/node_modules/sequelize/lib/dialects/sqlite/query.js:428:16)<br> &nbsp; &nbsp;at Statement.afterExecute (/usr/src/lhci/node_modules/sequelize/lib/dialects/sqlite/query.js:119:32)<br> &nbsp; &nbsp;at Statement.wrappedAfterExecute (/usr/src/lhci/node_modules/sequelize/lib/dialects/sqlite/query.js:256:39)<br> &nbsp; &nbsp;at Statement.replacement (/usr/src/lhci/node_modules/sqlite3/lib/trace.js:19:31)</pre>
</body>
</html>

    at ApiClient._convertFetchResponseToReturnValue (/root/gaf/webapp/node_modules/@lhci/utils/src/api-client.js:79:21)
    at runMicrotasks (<anonymous>)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async runLHCITarget (/root/gaf/webapp/node_modules/@lhci/cli/src/upload/upload.js:452:3)
    at async Object.runCommand (/root/gaf/webapp/node_modules/@lhci/cli/src/upload/upload.js:592:16)
    at async run (/root/gaf/webapp/node_modules/@lhci/cli/src/cli.js:109:7)
Error: child process exited with code 1
    at ChildProcess.<anonymous> (/root/gaf/webapp/scripts/sh.ts:23:16)
    at ChildProcess.emit (events.js:315:20)
    at ChildProcess.EventEmitter.emit (domain.js:467:12)
    at Process.ChildProcess._handle.onexit (internal/child_process.js:277:12)

Additional Context
Not sure if it's relevant but the branch/commit is unique and should be different from what the cron is deleting so I have no idea why it failed. For reference, here is the config we use for our server:

{
  "ci": {
    "server": {
      "port": 9001,
      "storage": {
        "storageMethod": "sql",
        "sqlDialect": "sqlite",
        "sqlDatabasePath": "/data/lhci.db"
      },
      "deleteOldBuildsCron": {
        "schedule": "0 19 * * *",
        "maxAgeInDays": 30
      }
    }
  }
}

I have tested that the cron works on our lighthouse staging environment and it deleted the builds. We have thousands of branches on our lighthouse server prod so that could be a factor? i.e. takes longer time to delete everything and could conflict when running upload at the same time

[patrickhulce]

We have thousands of branches on our lighthouse server prod so that could be a factor? i.e. takes longer time to delete everything and could conflict when running upload at the same time

This combined with the high volume of LHRs you're uploading means sqlite just isn't a good fit for your needs anymore. sqlite locks the entire database for any writes which makes it a poor fit when your workload is lots of concurrent activity.

LHCI already supports postgres and mysql (Heroku server example in the docs uses postgres), it's time to upgrade your installation beyond the sqlite default :)

[PJHRobles]

Thanks for the quick response @patrickhulce! I'll have a look at using mysql instead. Do we have a guide on migrating data between database management systems (is it possible in the first place)?or do we have to let go of existing data?

[patrickhulce]

You don't have to let go of existing data. How to migrate databases is more a generic DBA question than anything specific to LHCI.

https://stackoverflow.com/questions/18671/quick-easy-way-to-migrate-sqlite3-to-mysql might help

Protip though: I might let LHCI create the tables for you in mysql so the scheme is correct and then just use the sqlite dump to insert the data

[PJHRobles]

Ahh right I realized that was a bit of a dumb question. Different question though, this time, the cron ran and successfully deleted the builds. However, when I checked the volume, the usage is still the same/almost full. Any ideas? Is this perhaps an actual bug?

[patrickhulce]

Do docker volumes ever shrink? Most virtual disk implementations I'm aware of will never be downsized once they're increased to accommodate data.

If you have a minimal repro where builds aren't actually deleted from the database then please feel free to file an issue about that with instructions.

[PJHRobles]

I am talking about the database file not shrinking after deletion of entries but it looks like that's just how sqlite works https://www.sqlite.org/lang_vacuum.html

Unless SQLite is running in "auto_vacuum=FULL" mode, when a large amount of data is deleted from the database file it leaves behind empty space, or "free" database pages. This means the database file might be larger than strictly necessary. Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file.

Anw, thank you for your time and patiently answering my questions!