As indicated by the previous post, the process still works. People can update the original post in case of changes.
If you encounter a change, please update the guide to help others!
I suggest to read through the entire guide carefully and consider if you followed all the steps. Also check The original forum thread for possible solutions.
Hi all. I will provide a follow up to my post from almost a month ago. I finally got some time to implement the migration.
So, I ran the commands, and it took less than 10 minutes. My MariaDB was around 550 just before I did the migration, however the new SQLite is about 1.2GB, I didn’t purge the short-term statistics, so not sure if that is why its larger than expected.
Either way, it should be fine. I will monitor it for the next week and remove the MariaDB add on once I am happy with it.
I do remember back when I migrated to MariaDB probably 5 years ago, it seemed a lot faster than the SQLite, but so far, since I have migrated back, it seems almost the same…
Thanks everyone.
I just reverted back as well.
Running the conversion on my Windows machine with WSL was really fast (<5min for 3Gig database) and copying the file back via Samba share even easier.
Hopefully the new database remains healthy 
HI all.
I thought I would ask in this group before I make a new post
As we have seen, there has been some issues with the sqllite database in version 2024.07. Essentially when it does it purge at 4:12am, it freezes, and a reboot seems to fix it.
I know they did some fixes in later versions of 2024.07, but they also said some other fixes won’t be ready till 2024.08. (Due in a few days)
So, my question is are these issues likely to be in our sqllite which we migrated from MariaDB, or because it is essentially a new database, they legacy data won’t be there, and therefore won’t cause any issues.
Has anyone on this chat, who migrated had issues when moving to 2024.07
Why would you want to go back to sqlite anyway? Does mariadb works ok for you? Latest problems were, as i see, only in sqlite, not in mariadb.
But, then again, i’ve had my share of problems (and data losses) with sqlite, so i’m not going back (read above). Not a single problem with mariadb though…
I’d like to keep the discussion on the migration.
Feel free to make a new topic for the MariaDB issue. This way, other users will be able to find it (as explained in the forum rules).
As stated in the original guide, the reason why Nabu Casa recommends SQLite is because it is more stable and better supported these days. This wasn’t always the case but it is now.
The argument: “if it ain’t broke, don’t fix it” is always valid but most people are here because they are interested in the benefits and process of switching back to SQLite, so let’s focus the discussion on that.
Agreed, although my experience is quite contrary: sqlite is less stable, also i went back to mariadb exactly because sqlite DID broke (again), and it broke AFTER they said “now it’s stable”…
Thank you for this detailed description. I just was running the recorder DB on a different server (docker host with mariadb as container) but wanted to migrate back. This post saved me a lot of work and time!
I edited this post a little so that it is more up to date with the current version of HA. Also added alternative commands (pipx) in case people (like me) experience problems using pip install.
Thank you. Working perfect.
Convert the database on external Linux VM is running only some minutes for 3GB SQLite database.
Back to SQLite after many years on MariaDB my HA is a little bit more responsive.
So I think I go back to MariaDB after one day on SQLite.
After restart I must wait 10 Minutes to see data from recoder like graph and some statistic sensor do not work anymore. So I think with some more values a database is better choice.
Home Assistant does periodic optimizations of the database so there is a good chance the issue will disappear on their own. I didn’t dive into the details since I trust Home Assistant to know best (which works for me).
A few gigabytes of database file should not be a problem on a raspberry pi 4.
If MariaDB works better for you, don’t let me hold you back from doing what works for you.
Nice instruction, but from what I understood: you can just turn off the mariaDB and configs that point to that DB when long term data and history does not mean anything to me, right?
So Basically the “easy” version without that much overhead would be just turn off and the system (re)creates the sqlite db, wont it?
Correct.
If you don’t want to migrate for example energy data, you can remove the MariaDB config and HA will start using the default database again.
Thanks a lot for the tutorial! Spent a lot of time trying to get this to work because I can’t update home assistant anymore while using mariadb for some reason (waiting for recorder to start until it gives up, no error messages in 2024.11.4, no response in github issue). In the end it took ~60GB of memory to finish the conversion process but looks like it finally worked. 
celebrated too early… home assistant starts, but history (also long term statistics) are gone 
Did you keep the MariaDB add-on? If so, you can go back and forth until it works. There is link to another forum topic: https://community.home-assistant.io/t/migrating-from-mysql-mariadb-back-to-sqlite
Maybe you can find some tips on how to solve uour issue. Hope you figure it out.
I did, but the latest home assistant version doesn’t work with my MariaDB anymore (no error message besides “timeout waiting for recorder”) and other integrations don’t work on the previous version anymore so can’t easily go back.
Finally got it working again using this for conversion from mariadb to sqlite: GitHub - mysql2sqlite/mysql2sqlite: Converts MySQL dump to SQLite3 compatible dump
Not sure if this really made the difference or whether I made some mistake in the first export using the instructions above. I may have started the first export while home assistant was still using the database.
Hello,
With the help of Perplexity, i was able to install mysql2sqlite and make it works.
To install the mysql-to-sqlite3 package on your Debian 12 system hosting Home Assistant, you need to create a virtual environment. This is because Debian 12 uses an externally managed environment for system-wide Python packages. Here’s how to proceed:
sudo apt update
sudo apt install python3-venv python3-pip
python3 -m venv ~/mysql_to_sqlite_env
source ~/mysql_to_sqlite_env/bin/activate
pip install mysql-to-sqlite3
mysql2sqlite command from within this virtual environment. To deactivate the environment when you’re done, simply type:deactivate
Remember to activate the virtual environment each time you want to use mysql-to-sqlite3[1][6].
Feel free to add your instructions to the original post and give yourself the credits for it. It’s a community maintained topic. These replies are much harder to find.