In-memory MariaDB (MySQL) add-on for recorder/history integration

Tags: #<Tag:0x00007f3263fde340> #<Tag:0x00007f3263fde188>

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.

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.event_id and states.old_state_id are removed (Aria can’t handle them), but the indexes are remained,
  • states.entity_id and states.state column’s length is reduced from 255 to 128 char (they were too long for Aria keys),
  • events.event_type column’s length is increased from 32 to 64 char (it was too small for some events, causing SQL errors in recorder),
  • events.event_data and states.attributes TEXT columns got compressed.

Link & how to install:

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.

1 Like

Meantime I updated the documentation a little bit, there are better configuration examples, though the add-on binaries are the same, so there is no new version. If somebody started to use it, I suggest you to review some settings.

I’m using it in the past month, and it seems to be quite stable.