What is a database crash? SQLite is just a file.
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.
1 Like
It worked. Thank you
Sorry, not trying to be picky.
When did the corruptness occur? After the migration or a few hours/days later?
Maybe this helps: Trying to migrate from MariaDB back to default SQLite. Migration completed but corrupted db
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… 
)
1 Like
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.
I’m running on a core i5 NUC
It was slow when looking at large data sets for example the energy dash
made a intermediate db and then renamed that to the V2 db (naming the original _old as a backup)
HI All
I am thinking of migrating my MariaDB back to the original SQLite,
I have been using HA for 7 years, and used to have occasional issue with the SQLite, which is probably why I moved to MariaDB, however I feel it time to move back
My database is only 525MB, so I assume this is small enough to not be worried,
Also is the process still the same as the original post.
Thanks
Yup. The process is still the same. 
hi, i have all steps doin exactly but after 2 hours terible stress i delete all and destroy my system. everytime is coming this:
mysql2sqlite version 2.1.12 Copyright (c) 2019-2024 Klemen Tusar
2024-06-01 16:04:14 ERROR 2003: Can’t connect to MySQL server on ‘homeassistant.local:3306’ (111 Connection refused)
2003: Can’t connect to MySQL server on ‘homeassistant.local:3306’ (111 Connection refused)
where is the problem ? i have test all procedure nothing is working. is this prodedure to old and not working anymore because anybody have chance a formel or Koma or Dopelpunkt oder geschweifte klammer? ich hab den kanal so voll …
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.
- Reducing the database size is critical if you perform the migration on HA system.
- If you’re limited in ram, disable some add-ons.
1 Like
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!