I eventually set it to a purge of 1 day. Because I didn’t have the time to test which setting worked best.
5 Days was too much in my case. Perhaps 3 would do.
My advice….put the purge to 1 day. Execute the purge manually. Wait….and then migrate.
After that you can put the purge back to 30 days if you like.
I kept lowering the retention days and finally got it to work at 3 days, where it put my MariaDB Size at 1.65GB (from 9GB+). Now I’m up and running…and will need to figure out how to translate my Grafana queries from MariaDB to MySQL…on to the next adventure!
Thanks again for this awesome information and help here. @Robbin @Nardol @mneuron @HA_n00b ,and everyone else that contributed to this.
UPDATE - one interesting note, which I have yet to figure out. While I decreased my database size to 1.65GB as reported by MariaDB, the MySQL database is reporting as 3.0GB…weird. I wonder if another purge is required or something.
mysql2sqlite needs quite a lot of RAM in order to work, so if it gets killed while shovelling large tables, that’s simply because the system ran out of memory.
If running within a virtual machine, simply increase available ram to the VM. My instance killed mysql2sqlite with 2GB of RAM, but finished successfully after increasing it to 8GB.
It took some trial and error, but all the information was already in this post.
I want astray on my impatience and stopped Home Assistant too soon after triggering the purge. @Panzer_V 's advise to wait made me realize this mistake 
.
Since it was a lot of reading, I took the time to summarize this topic in a guide
Please let me know if any improvements are needed. I’ll be expending the guide to contain all variations for anyone to migrate
has anyone encountered:
2023-09-12 15:09:54.771 WARNING (MainThread) [homeassistant.components.http.security_filter] Filtered a request with a potential harmful query string: /magmi/web/ajax_pluginconf.php?file=../../../../../../../../../../../etc/passwd&plugintype=utilities&pluginclass=CustomSQLUtility
2023-09-12 15:09:54.801 WARNING (MainThread) [homeassistant.components.http.security_filter] Filtered a request with a potential harmful query string: /magmi/web/magmi.php?configstep=2&profile=%3C%2Fscript%3E%3Cscript%3Ealert%28document.domain%29%3C%2Fscript%3E
before?
It suddenly showed (once) and the only thing I can think of is the add-on SQLite Web, the I had installed for this migration.
it wasnt even running, it never is, I just left it in the system for you never know…
took it out now, but might be good to check if anything else needs to be done.
hey alex put it this way:
I have just done the migration, on my side from PostgreSQL, basically following the procedure from @koying described above. That said I migrated all the tables including the states and the events. To make it faster I did the migration on my laptop, as SQLite guarantees the binary compatibility of the databases between architectures. I noticed that SQLite seems to trigger a disk write for every INSERT clause, which makes the import quite slow. I therefore used eatmydata to avoid those writes and make the conversion way faster (around 2 minutes for a ~1GB SQLite database). Warning: don’t use this tool to run home assistant, there is a high risk of data corruption in case of power failure. Overall the Home Assistant downtime was less than 10 minutes.
Following the migration, I haven’t noticed any speed difference, but that was not my goal. I migrated to PostgreSQL many years ago to improve the performance, I am glad that nowadays we can get the same performance without additional complexity. The SQLite database uses roughly half of the disk space of the PostgreSQL one. In terms of I/O, the SD card reads have been divided by 3 (because the database is smaller and can now fit entirely in cache?), the SD card writes are identical, however the average write sizes have increased by 40%. Overall that is still low enough, I don’t think it will have a significant impact on the SD card lifetime.
So i have a decently big home assistant installation. 1 year ago i decided to move to mariadb cause i wanted to keep more than 30days of states and events data.
A couple of months ago i noticed home assistant restarting its own for no particoular reason. Logs were also even better (no entries). I was able to pull some log from supervisor which only showed. “Watchdog found a problem with Home Assistant API!” followed by “Restarting homeassistant”.
After some investigation I lurked in the DB and the states table had 88M entries (18Gig table). I purged that and was happy (no restarts) for a week.
when the states table reached 10M rows again I started having the random restarts once again.
For those of you reading this it’s also worth noting that home assistant runs on decent hardware. (4c8t amd ryzen7 2700u + 32G ram + 1TB nvme).
That said i decided to pull off from mariadb and switch back to sqlite. migration took 10m and i can tell that the energy data loads faster (for some reason) than previously on mariadb.
Obviously i’d need to wait at least 7-10 days to check performance wise AND “restart wise”. but at a first glance it seems that sqlite improved perf for my usecase.
I also considered running mariadb in an external container but in the end it would result in an extra thing to maintain.
The system was redesigned specifically targeting sqlite databases. Mariadb is slightly different and it is known that it will be slower than an sqlite database. If you can migrate, you should.
I’m not interested in preserving current contents of the Mariadb. Is this as simple as changing the Recorder db_URL target to sqlite:////PATH/TO/HOMEASSISTANT, restarting HA Core, and deleting the Mariadb add-on? If so, what is the default path?
Just fully remove the db_url: ... line and it will go to the default location, which is your config folder.
Thank you petro.
ok, i used your guide and feel like a hacker (i dont use root, ssl, pip, etc often)
my system is the standard homeassistant on raspi (hassos?)
lessons i learned:
-use newest putty version. ( i had an old version that could not connect)
-i commented the db_url out before the core stop
-i ignored the purge via dev tools recorder to 1 day, because i dont understand this
thank you very much
Today overnight something happened with my sqlite database, so a new blank was created. Now i’m without ANY history…
i used MariaDB quite a while, then i went back to sqlite since it was said that quite a lot was done to be stable, but i see that it’s not true… it still breaks for no reason.
So, i guess back to MariaDB… at least it’s stable.
I’m trying to migrate from standalone container with sidecar mysql to all HAOS.
Tried the suggestion here by @Nardol
but recieve:
[homeassistant.components.recorder.util] The system could not validate that the sqlite3 database at //config/home-assistant_v2.db was shutdown cleanly
On start up…
Any ideas what can cause this and how to fix it?
thanks! it workied for me to on a 56G Mysql DB to Sqlite. Also reduced it to 38G