If you don’t want to wear down your SD card with your non-critical sensor data and you don’t want to use external resources, give this add-on a try.
If you are trying to minimize your SD-card’s wear by using the built-in SQLite with
db_url: 'sqlite:///:memory:' recorder configuration but fed up with the uncountable
(sqlite3.OperationalError) cannot commit - no transaction is active errors in your HA log, give this add-on a try.
It will also protect you from the data loss caused by HA core restarts when in-memory SQLite used. Though it won’t protect you from power failures, add-on or host restarts or updates.
This version uses tmpfs to store MariaDB databases in-memory. The default
InnoDB storage engine is replaced with Aria storage engine.
Problem: InnoDB storage engine wastes a great amount of disk space, but the only storage engine that is compatible with recorder.
- Memory storage engine can’t handle TEXT columns and
- Aria storage engine can’t handle foreign keys in the recorder database schema,
- MyRocks storage engine (though it has compression and flash-friendly) is not available for 32-bit platforms.
Workaround: A modified, storage engine compatible database schema is created when the add-on starts (ie. before recorder tries to connect and tries to create a schema that the storage engine can’t handle):
- foreign keys on
states.old_state_idare removed (Aria can’t handle them), but the indexes are remained,
states.statecolumn’s length is reduced from 255 to 128 char (they were too long for Aria keys),
events.event_typecolumn’s length is increased from 32 to 64 char (it was too small for some events, causing SQL errors in recorder),
states.attributesTEXT columns got compressed.
Link & how to install: https://github.com/lmagyar/homeassistant-addon-mariadb-inmemory
Disclaimer: I’m new to HA, this is my first add-on, first docker container, first MariaDB install… So any idea on how to improve it, or any warning on some unknown dangers are welcome.