Forum topics in the guides section should be editable as a wiki by everyone. Feel free to add/update the guide to help out others.
Thanks for the guide!
I had to use mysql2sqlite with -h core-mariadb.
Any idea how to fix this?
2023-12-21 11:47:26 ERROR SQLite transfer failed inserting data into table events: table events has no column named data_id
Unfortunately step 5.1 now no longer works so we’re stuck:
when trying to pip install the conversion tool: pip refuses to install it with an error:
~ # pip install mysql-to-sqlite3
error: externally-managed-environment
× This environment is externally managed
╰─>
The system-wide python installation should be maintained using the system
package manager (apk) only.
If the package in question is not packaged already (and hence installable via
"apk add py3-somepackage"), please consider installing it inside a virtual
environment, e.g.:
python3 -m venv /path/to/venv
. /path/to/venv/bin/activate
pip install mypackage
To exit the virtual environment, run:
deactivate
The virtual environment is not deleted, and can be re-entered by re-sourcing
the activate file.
To automatically manage virtual environments, consider using pipx (from the
pipx package).
note: If you believe this is a mistake, please contact your Python installation or OS distribution provider. You can override this, at the risk of breaking your Python installation or OS, by passing --break-system-packages.
hint: See PEP 668 for the detailed specification.
Which means there’s no way now to do the actual conversion. I don’t know what the ramifications will be of using the --break-system-packages flag they mention either.
Thank you for your feedback. Can you provide some more details about your setup?
If anyone has encountered and/or solved this problem, please update the original post. There is an edit button at the top.
It’s a fairly bog-standard HAOS setup. Running HAOS 2023.12.4. The only real change is that about 2 years ago I moved my database to Mariadb since that was way faster than SQLite at the time. Now that I want to move back I can’t execute the steps as mentioned since the package won’t install, and the instructions don’t mention whether you need protection mode on or off for the SSH addon.
I’m guessing that you’d need to give HA more time to create the stub DB.
With the command
ha core start && sleep 5 && ha core stop
I also got the same error when trying to convert the data from mariadb (not on HA, I did the conversion on another host with plenty of memory).
but ha core start && sleep 10 && ha core stop did work. I guess HA is still building the tables even after it’s reported that it has been started. Just try giving it more time before stopping the core again.
MySQL password:
2023-12-28 16:19:43 INFO Transferring table event_types
100%|███████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 7913.78it/s]
2023-12-28 16:19:43 INFO Transferring table statistics_runs
100%|█████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 95.65it/s]
2023-12-28 16:19:43 INFO Transferring table events
100%|█████████████████████████████████████████████████████████████████████████████████| 1/1 [00:01<00:00, 1.64s/it]
2023-12-28 16:19:45 INFO Transferring table statistics
100%|█████████████████████████████████████████████████████████████████████████████████| 5/5 [00:06<00:00, 1.39s/it]
2023-12-28 16:19:58 INFO Transferring table states_meta
100%|███████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 1276.03it/s]
2023-12-28 16:19:58 INFO Transferring table states
100%|███████████████████████████████████████████████████████████████████████████████| 14/14 [00:36<00:00, 2.59s/it]
2023-12-28 16:20:51 INFO Transferring table schema_changes
100%|███████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 6615.62it/s]
2023-12-28 16:20:52 INFO Transferring table event_data
100%|████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 165.18it/s]
2023-12-28 16:20:52 INFO Transferring table state_attributes
100%|█████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 21.91it/s]
2023-12-28 16:20:52 INFO Transferring table statistics_short_term
100%|█████████████████████████████████████████████████████████████████████████████████| 2/2 [00:02<00:00, 1.22s/it]
2023-12-28 16:20:56 INFO Transferring table recorder_runs
100%|███████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 8192.00it/s]
2023-12-28 16:20:56 INFO Transferring table statistics_meta
100%|███████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00, 2228.64it/s]
2023-12-28 16:20:56 INFO Vacuuming created SQLite database file.
This might take a while.
2023-12-28 16:21:02 INFO Done!
The migration did succeed on my PC. No need for compaction either since with 64G of memory there’s no need to leave out stuff.
The guide describes what worked for me (and a number of others). If it is not mentioned in the guide, the settings were left to their default setting.
You might try the conversation on a PC since this seems also a popular and succesful route.
For more details and troubleshooting, you might find an answer here.
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 
2 Likes
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:
[wsl2]
swap=128GB
swapFile=D:\x_drive\wsl\swap\swap.vhdx
Use double \ or wsl will think \ is an escape and a drive with enough free space to hold the swapfile.
I’m running HA and Mariadb in 2 docker containers. Stopped the HA container, ran
mysql2sqlite -f ./home-assistant_v2.db -d home-assistant -u hass -h <ip_of_mariadb> -P 3306
-V --use-buffered-cursors --mysql-password somepasswordiused
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 followed the full tutorial, no errors at all. But after rebooting with the default db, I went to check the history and there was none.
I checked the file and this seems to be the reason:
config/home-assistant_v2.db.corrupt.2024-03-13T21:03:18.844016+00:00
What can I do to recover it?
Edit: tried this but as soon as HA rebooted it returned a couple errors and renamed it as corrupted again.
Undo step 6.4 and reboot. This should put you back to mariadb.
Delete the corrupt db and try again from the start.
I’m stuck here to! Did you solve this and finish the migrating procedure?
This happened for me too. I used pipx instead.
Do apk add pipx and then pipx install mysql-to-sqlite3. And then pipx ensurepath to add the path to the PATH environment variable.
1 Like
Well, without much hope I did so and it worked!
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)?
Edit: nvm, it’s too old data to bother.
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…
What is a database crash? SQLite is just a file.