So about one year ago, my HA install slowed to a crawl and it was related to the sqlite database, everything making queries took forever.
At the time, I moved my database to an external MySQL (well MariaDB) hosted on an external ubuntu machine.
So far so good, but upgrading from 2023.2.x to 2023.3.x was a nightmare, upgrade got stuck with both HA and mariadb using over 100% CPU time forever.
Turned out to be related to invalid timestamps.
He mentioned that today using SQLite wouldnāt cause such performance issue, and the only thing to worry about would be SD card space.
Having the SQL database be hosted on a different machine makes it a pain to backup, and in particular it doesnāt automatically happen when you upgrade HA.
So I was wondering if anyone has ever successfully migrated from MySQL/MariaDB back to sqlite ?
This doesnāt answer your question, but if you use the MariaDB addon (assuming youāre using HAOS / Supervised), then it is running on the same machine and is included in the backup.
Iām using it because itās a requirement for the NGINX Proxy Manager addon, which is more flexible and easier to set up (imho) than the NGINX Home Assistant SSL Proxy addon.
The link is the NGINX Proxy Manager addon needs to store its own data somewhere, and the author decided to do so in the same DB server as HA and require the use of MariaDB. It just happens to be a very convenient way to setup external access via SSL using certificates from DuckDNS, including their renewal. Thatās what prompted me to use it, way back when.
And no, the integration does not perform a migration. This was less of an issue before the advent of long-term statistics, as everything else rolls off the database pretty quickly. Hence I never attempted a manual migration, and never had the related timestamp issues you referenced. But obviously if you were going MariaDB-external to MariaDB-addon, you could move the databases.
The only inconvenience is applying updates to the addon occasionally (Iām not a fan of auto-update).
I switched to Mariadb (via the MariaDB Addon) long time ago. But now I want to switch back to SQlite without losing the data.
The first step is clear:
Create a new empty SQLite database first. Let HA do this for you by removing the recorder mariadb settings from the configuration.yaml. This is a very important step as explained in the Home Assistant 2023.4 Release Party.
The second step is the migration of the (statistics and other) data.
What is the suggested way to do that?
Because the optimisation by the HA team for SQLite was huge I wanted to switch back from MAriaDB to SQLiteā¦
I finally succeeded in switching (back) from MariaDB to SQLite and keeping all states/events and statistics. (Iām on 2023.4.1 , the database scheme is 41 )
I used HeidiSQL, connected to the MariaDB and
1.export (with data: āreplace existing dataā) all seperate tables (only DATA, no table ).
create in table EVENTS a key named ācreatedā (without quotes) with datatype ādatetimeā, in table STATES create a key named ācreatedā with datatype ādatetimeā and a key named ādomainā with datatype āvarcharā and length 64.
in notepad++ (which I used) edit the file states.sql: and find&replace every _binary 0x018725fccf354f12f37776356bc7c8b5 (a MariaDB binary blob, this is just an example hex number) to a SQLite blob literal: Xā018725fccf354f12f37776356bc7c8b5ā
use in notepad++ search mode= regular expression, in the find field: (_binary\s0x)(\w*) and this in the replace field: Xā\2ā
and choose replace all.!
import all previously exported and in NP++ changed files in HeidiSQL with āFileā¦Run SQL fileā.
If everything was succesfull (REMEMBER that you start with all data removed from all the tables) you will now see all the tables filled again with the same data as was contained in MariaDB.
The keys created in step 8 can now be deleted (SQLite doesnāt use them) but I didnāt and everything went fine.
close HeidiSQL
(make sure the home-assistant_v2.db is the newly filled one in the config folder of home assistant.
In SSH: ha core start
All statistiscs/history/states are back & SQLite is database again.
NB. If the exported tables āstates.sqlā or āevents.sqlā are very big then NP++ may take a long time to find&replace.
Thank you very much! I was about to abandon all my history data because of a lot of errors using MariaDB after updating home assistant. But now i can enjoy my energy dashboard and look at past data after this migration.
The only thing i did not get to work was the conversion from MariaDB blob to SQLite blob. I received an Error when i tried to import the converted .sql into āhome-assistant_v2.dbā using HeidiSQL. Instead of replacing existing blob with Xā\2ā i replaced it with NULL because the blob was what gave me the error during import. This seems to work fine.
Donāt know if i lost any significant data by replacing the blob with NULL, but the only thing i really cared about was the energy dashboard, and this works like it did. Looks like the history tab works with the older data too, so i am pleased with the result.
My states.sql file were a couple of millions lines (1 GB) and NotePad++ had to work maybe 5 mins to complete the replacement of text. It will say ānot respondingā, but just sit back and relax. I used the task-manager in windows to see when it was finished (it drops to ā 0% CPU load).
NOTE: Be sure to include --complete-insert. It took me a while to understand this was not working because the order of fields was different between my grand-fathered mariadb and a brand new sqlite.
comment out the db_url of the recorder in configuration.yaml
recorder:
# db_url: !env_var DB_URL
remove or move any existing home-assistant_v2.db
Start HA once for an empty sqlite database to be created
Immediately after HA has started successfully, shut it down again
import the statistics in the newly created sqlite db:
$ echo "BEGIN TRANSACTION;
DELETE from statistics_meta;
DELETE from statistics;
DELETE from statistics_runs;
DELETE from statistics_short_term;
$(cat ha_statistics_sql_dump.sql)
COMMIT;
" | sqlite3 home-assistant_v2.db
Take a quick coffee break waiting for the import to be completed
Iāve run into issues with MariaDB following 2023.4, so Iām looking into options to migrate back.
This is probably a dumb question - but does this mean your steps will not work for those on HAOS? If so, how would these steps change to migrate on HAOS?
Like others, I moved to MariaDB a long time ago (and the performance improvements were massive at the time), but Iām also intrigued in whether itās worth moving back. For those that have moved back, do you have any quantitive data on the relative performance with sqlite vs MariaDB that would justify the move?
Personally speaking, MariaDB is working OK for me and I probably wonāt consider moving unless MariaDB becomes unsupported, or there is a really significant performance uplift from switching.
(My hardware: RPi4 4GB with SD boot, and USB SSD for data. HA 2023.4.3)
Iām in the same boat. SQLite was causing me massive issues, especially if you needed to ever restore that. Has the restore issue been fixed? I mean, if you have to restore and lose the entire database anyway then what is the point? So I moved to MariaDB and have been pretty happy until recently.
Iāve noticed that recently that about 50% of the time if I simply restart HA it will complain the recorder canāt be started and history is broken and that MariaDB is the problem, but if I reboot the entire Pi then itās fine. I added a button on my dashboard to show me the DB size and itās pretty big, about 5GB, but I run my Pi 100% from an SSD and sometimes it may be slow starting, itās mostly been OK. You might be thinking this is an insane DB size, but Maria allowed me to track stats far longer than SQLite ever did so I kind of went crazy with it.
I think this is a great topic to bookmark in case I feel the need to go back.
