(sqlite3.OperationalError) database is locked

[juergenschubert]

Make sure you are running the latest version of Home Assistant before reporting an issue.

You should only file an issue if you found a bug. Feature and enhancement requests should go in the Feature Requests section of our community forum:

Home Assistant release (hass --version):
0.33.4

Python release (python3 --version):

Python 3.4.2

Component/platform:
Raspberry PI all in one installation

Description of problem:

Dec 06 14:52:36 home-assistant hass[8491]: 16-12-06 14:52:36 ERROR (Thread-8) [homeassistant.components.recorder] Error during query: (sqlite3.OperationalError) database is locked
Dec 06 14:52:36 home-assistant hass[8491]: 16-12-06 14:52:36 INFO (Thread-8) [homeassistant.components.recorder] Retrying in 0.1 seconds
Dec 06 14:52:41 home-assistant hass[8491]: 16-12-06 14:52:41 ERROR (Thread-8) [homeassistant.components.recorder] Error during query: (sqlite3.OperationalError) database is locked
Dec 06 14:52:41 home-assistant hass[8491]: 16-12-06 14:52:41 INFO (Thread-8) [homeassistant.components.recorder] Retrying in 0.1 seconds
Dec 06 14:52:46 home-assistant hass[8491]: 16-12-06 14:52:46 ERROR (Thread-8) [homeassistant.components.recorder] Error during query: (sqlite3.OperationalError) database is locked
Dec 06 14:52:46 home-assistant hass[8491]: 16-12-06 14:52:46 INFO (Thread-8) [homeassistant.components.recorder] Retrying in 0.1 seconds

Expected:

Problem-relevant configuration.yaml entries and steps to reproduce:

Traceback (if applicable):

Additional info:

[keatontaylor]

This happens if the file system is not sufficiently speedy to keep up with write requests to the sqlite database from home assistant. If you have sufficiently high writes to the database, such as having a large number of components in home assistant take a look into installing and using a mysql database instead, that will run in memory, not from disk. See: https://home-assistant.io/components/recorder/

[w1ll1am23]

I can confirm @keatontaylor's comment is correct and has fixed this error for me. @juergenschubert set up a mySQL database and your issue should go away.

[n8henrie]

Disclaimer: I don't know anything about this topic.

I'm facing this issue pretty frequently on my Raspberry Pi, and I'm wondering if there's a workaround without having to set up a mysql or postgres server.

I just watched this PyCon 2016 talk about sqlite, and at the timestamp I've linked to it mentions WAL mode, and I immediately thought of this issue.

WAL requires a relatively recent version of sqlite, but I think all versions of Python capable of running Home Assistant should suffice:

$ python3.4 -c 'import sqlite3; print(sqlite3.sqlite_version)'
3.8.7.1

After a little SO search I cobbled together this commit. Before that change, I got lots of database is locked errors. Afterwards, I didn't get any, and hass seemed to be running normally.

Can anybody more knowledgeable chime in as to whether this is worth investigating more? Bonus points if you include a little explanation as to why or why not, and thanks in advance.

Relevant links for future reference:

• https://youtu.be/D7wSMnapDp4?t=12m5s
• http://stackoverflow.com/a/23661501/1588795
• http://docs.sqlalchemy.org/en/rel_0_9/dialects/sqlite.html#foreign-key-support
• https://github.com/g2p/bedup/pull/86/files

[reedriley]

@n8henrie's patch fixed this when I hit this error after upgrading to 0.39.1.