Thank you for this writeup. Unfortunately I had the same issue where I was not able to install the pip module. Because of that I have opted for the remote conversion. To get this working I did the following (very basic, but I figure anyone else will manage with these easy steps).
1 Spin up a linux machine (in my case it was an Ubuntu 22.04 VM).
2 install python3 (apt install python3)
3 install python3-pip (apt install python3-pip)
4 install the pip module (pip install mysql-to-sqlite3)
5 install the phpmyadmin addon if you do not have this yet installed
6 using phpmyadmin create a new user with remote rights on the homeassistant database
7 Stop home assistant (ha core stop)
8 on the linux machine enter the conversion command with the new username (mysql2sqlite -f /config/home-assistant_v2.db -d -u -h homeassistant.local -P 3306 -p -V --use-buffered-cursors)
9 rename the database file on your home assistant machine
10 modify the configuration.yaml file and remove (or comment out) the database connection.
11 from the conversion machine copy the file using scp (scp ./home-assistant_v2.db [email protected]:/config/)
12 start home assistant (ha core start)
In my case these were all the steps taken. I didn’t clean up the short term statistics at the start cause I had offloaded the conversion to another machine (a VM with 8 cores and 32GB of memory) and the whole process didn’t take me longer than 15 minutes to execute from start to finish.
After the migration went well I have stopped the mariadb addon (and I will remove it after a last round of inspection).
I must say everything indeed feels more snappy now (even tough I’m running ha on a intel n100 with 8GB of mem).
I hope this little writup will help people that are not able to use the above method, but if there are any questions, just ask
Thanks for the recipe @AJediIAm it got me going as well. I took the remote route too and used WSL2 on my windows 10 pc. As I had a 15 Gb database, the memory limitation needed to be overcome by configuring a large swap file for WSL in .wslconfig file in the windows user homefolder:
from a wsl terminal. It created the database file locally in the wsl2 folder on my PC. It took 2.5 hours, required up to 50Gb of swap space, grew the swapfiel to 96Gb, but made it through without memory failure. Result was a 12 Gb sqlite DB.
Then copied the file to the /config folder of a cloned test container, removed the mariadb entry from /config/configuration.yaml of the container and restarted the container.
The cloned test container was quicker to respond, most notably in the year overview of the energy dashboard. Though I ran it on a different platform as the original one so that may play a role.
Just another way of migrating back, and an option to overcome large db limitations if needed. (I’ll clean before the final production migration.)
It seems the approach I used no longer works.
Would you (or someone else) be willing to update the guide to a remote conversation? The community will benefit from your experience and contributions.
Feel free to list yourself as author and take the credit.
Sure. I’ll try to do that later today. I just did the migration for my own setup. From stopping HA to starting it with the new DB took me 5 minutes. I had a 380Mb MySQL database and now have a 225Mb SQLite DB. Works really well…
Worked, with some tinkering. Creating a new user in phpmyadmin didn’t work for me, but the user created for the mariadb worked out fine. Getting mysql-to-sqlite3 to install was the hardest part. Pip install didn’t work on Ubuntu 23.10 so downloaded the files manually and then something like “sudo apt install python3-mysql-to-sqlite3”. After that it was clean sailing, db conversion in less than 5 minutes, didn’t bother with compacting the database or purging the history. My database is rather small (600mb) as I only keep a history of the important stuff like energy or security related stuff. The resulting db is 350mb and no errors or loss of data as far as I can detect.
Originally had mariadb installed because of nginx proxy manager. Last update of npm, which removed the need for mariadb, caused a serious unfixable error in mariadb resulting in a annoying memory leak of HAOS. Looks like it’s fixed now.
I was also surprised to see that this 2nd time around the size was much lower (670MB) than the first (1GB), but I think I know the reason.
The first time I migrated the original db back from my HA beginning was still there with a few months of data before I migrated to MariaDB. I manually had switched to that SQLite before and was happy to see the data was still there, so I was hoping the MariaDB data would get merged with that one. But that’s the one that got corrupted.
Is there I could I could manually recover the data from that date range that’s still there (in the 1st migration file)?
Just my 5 cents: i went back to sqlite, too. Only to experience database crash a few weeks after that. MariaDB never crashed during few years of work…
I guess i don’t have to say that i’m back on Maria…
Ok, if you’re picky… file crash
All of the sudden all history was gone, and “***.corrupted” file appeared and new blank database file was created. This happened to me before, that’s why i went to MariaDB a few years ago.
But, since lately word came out that “many things were done on sqlite to improve stability” i said “let’s go and try”. Sadly, no luck, form my point of view not much has been done to eliminate these random crashes.
Migration went perfect, and all worked great (history, too). It happened randomly, out of the blue, after a few weeks from migrating. The point is that it happened overnight, so i surely didn’t do anything, neither PC/HA wasn’t restarting (i double-checked!).
From i’ve read one of “bad” things of sqlite is that only one user can be logged in at the same time, so i guess that if HA comes to a “glitch” and tries to do a double-write all goes bye-bye.
Same will happen if you login with standalone sql app into a current HA sqlite database file as a user for writing - since HA in this case won’t be able to login it will report corrupt database and create a new one. (i did that…, not once, until i learned what’s the cause…). So, in this case if you want to play with anything but official sqlite addon you must make a copy of database and then login into that copy.
As said, MariaDB never crashed on me yet (hopefully devil won’t hear me and crash it today… )
Corrupted on my twice now as well since I switched back to sql lite. It seems to happen around 1 month weirdly enough and it is during the purge. Cant remove some statistic and then it goes kaput and I get a fresh DB empty.
Seriously thinking of going back to MariaDB, it was slow yes, but it never crashed on me
I used MariaDB on my Synology first, but now i just use HA add-on and i really don’t see any speed difference. In fact, back then when i first went to MariaDB it was said that it’s faster than Sqlite, but even then i didn’t see and difference…
But, i guess that it depends on machine HA is installed on… i guess it can be slower on pi.
)