Migrating from MySQL (mariaDB) back to sqlite

[rdekruyf (Robbin)]

Today I did a succesful migration from mariaDB to sqlite after a lot of errors I found the way to go.
HA: 2023.6.3
OS: 10.3

Lessons learned:

• Use a old version of mysql2sqlite (v1.4.17)
• Purge via dev tools recoder to 1 day
• Set the mariadb add-on port setting hardcoded / manual on 3306
• Delete existing home-assistant_v2.db

Then run this in a external ssh client:

apk add python3
apk add py3-pip
pip install mysql-to-sqlite3==1.4.17 
ha core stop
mysql2sqlite -f /config/home-assistant_v2.db -d *databasename-probably-homeassistant* -u *usernameforthedatabase* -p -V --use-buffered-cursors -h *iphomeassistant* -P 3306

Then the process start and takes a few minutes. After that edit configuration.yaml and remove the db_url line. After that start home assistant again with the command ha core start

[djalexnl (Alexander de Boer)]

Problem was the HASSOS version… 9.5 … After I upgraded to 10.3 it worked fine… DB size went frome 910 to 580 mb…

Migration went smooth toolk about 17 minutes… rasbPI-4

Happy Camper

[Luitertje (Lesley)]

Thanks! The old version for mysql2sqlite did the trick for me!

[ziptbm]

@Panzer_V

I had the same issue today. It was related to a too big database.

What size is too big? I’ve run into this as well and have a 30 day purge set.

@Mariusthvdb

I noticed half way stops too (no kills though…), when not starting without a .db file. I had to let the script re-create a file, and not overwrite an existing one.

fwiw, I had a 10 day MariaDB DB, and it took less than 6 min.

I started mine without a .db file and the transfer “killed” after a minute. I then tried it again (where a .db had been started) and it again, “killed” after a minute. So should an existing .db be in the folder or not?

I’m going to purge my db down and try again, so hopefully this works. I’m following the steps in post 98 on HAOS 10.3 with HA Core 2023.7.3.

[Panzer_V]

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.

[ziptbm]

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.

[robi (Robert)]

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.

[AJediIAm (Jaap)]

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

[Mariusthvdb (Marius)]

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:

New interactive history explorer custom card

I don’t have write access to my github from the office, so I’ll reply here. Ah it’s the resize observer one. Just ignore it, it’s normal. It was there from the very first HE card. It’s completely harmless, but would be a huge amount of work to remove (so not worth it basically). As long as the cachesize error is gone, all good. [Offtopic] Just a quick offtopic note about your log warnings posted above. This here: ajax_pluginconf.php?file=../../../../../../../../../../../etc/passwd&pluginty…

[aurel32 (Aurelien)]

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.

[vekexasia (Andrea)]

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.

[petro]

@vekexasia

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.

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.

[GaryK (Gary Kelley)]

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?

[petro]

Just fully remove the db_url: ... line and it will go to the default location, which is your config folder.

[GaryK (Gary Kelley)]

Thank you petro.

[propi62]

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

[Protoncek (Pavel)]

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.

[Nornode]

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?

[knologys]

thanks! it workied for me to on a 56G Mysql DB to Sqlite. Also reduced it to 38G

[erkr (Eric)]

Thanks, these instructions where life saving. back on sqlLite
I used Windows WSL2 ubuntu to migrate back,

That required some preparations in WSL:

sudo apt-get update
sudo apt-get upgrade
sudo apt install python3-pip
pip install mysql-to-sqlite3

After enabling the port 3306 in the mariaDB add-on, I could run the migration with this path included:

/home/<user>/.local/bin/mysql2sqlite -f home-assistant_v2.db -h 192.168.178.47 -P 3306 -d homeassistant -u homeassistant -p -V --use-buffered-cursors

Size reduced from 1.1GB to 0.5GB