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.
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.
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.
)