Migrating home assistant database from sqlite to mariadb.. ONLY if you are VERY familiar with Database administration

[spitf1r3]

apk add pv or sudo apt install pv or something else depending on your system

[malosaa (Hector)]

i found a new way that works great now and did not even mess up my cost device @fversteegen
You right they did do something so this is what i did after the old method did not work anymore.

What do you need:

Heidi sql (sql manager)
MariaDB or proxmox LXC Tteck scripts (AWSOME GUY!!!)
Home Assistant
Adminer (its ike phpmyadmin) i prefer this
or
Phpmyadmin

The set-up:

1. ssh into homeassistant and duplicated the .DB called homeassistant_v2.db.
2. Created a new database with HEIDI with
   Just open HEIDI and login to your mysql and create the DB
   
   

utf8mb4_general_ci and name homeassistant this is the database we gonna use later, now let homeassistant

Now we need to change something in the homeassistant.yaml
To do that just go into your homeassistant.yaml and be sure the line of recorder looks like this, ofcourse don’t forget to replace the ip username and password.

recorder:
  db_url: mysql://USERNAME:PASSWORD@YOURIP/homeassistant?charset=utf8mb4

After you done this, save and restart homeassistant and let it run for 5 minutes and shut it down.
or if you dont want to shut it down just change the recorder back that it uses the old default database file again.

3. open heidi and select the database named homeassistant and empty all rows/tables and delete all keys from all tables (not the orange ones!) using (heidi sql)
   just hover over the key and press remove
   

   
   
   

4. created a new database called (homeassistant_test) the old dump database
   

5. now we start using the script again to dump the same homeassistant.db we backed up before again into the new created database. homeassistant_test) c6de-

sqlite3mysql
–sqlite-file /config/home-assistant_v2.db
–mysql-database homeassistant
–mysql-host core-mariadb
–mysql-user homeassistant_test
–mysql-password ‘YOURPASSWORD’
–ignore-duplicate-keys

6. go to homeassistant_test database and check the data of homeassistant_test and look for the table statistics_short_term and open it, copy the (CREATED) row (im using heidi sql)
   

   

7. than open statistics_short_term in the database named homeassistant and paste the row
   it does not matter where
   

   

8. compare the rows from homeassistant_test with homeassistant and
   be sure in (events | recorder_runs | states,statistics )have the colum (created) if not just copy them over, (use the corresponding column from the other database)

9. also check if there are missing columns in homeassistant u can copy them to from homeassistant_test

10. now finally we run the script again and once more we use the homeassistant.db we created in the beginning and this time we gonna dump it in the database named homeassistant thats the one we edited and made it ready and compatible with mariadb.

sqlite3mysql
–sqlite-file /config/home-assistant_v2.db
–mysql-database homeassistant
–mysql-host core-mariadb
–mysql-user homeassistant
–mysql-password ‘YOURPASSWORD’
–ignore-duplicate-keys

if everything went ok and you open the DB u see that FLOAT changed to DOUBLE

see here my old homeassistant_test

new dumped homeassistant

Go in to the homeassistant YAML file and add it to the recorder and start HASS and BOOM it works.

I did a mix of everyone but none of that data went corrupted or lost during this migration, atleast not for me.
Also a big plus is that the COST entity won’t get messed up at all

So whoever wants to try this always make a BACKUP!

Thanks to @eddiewebb @spitf1r3 @SirACEair @Alex_Pupkin for theyr info and scripts so i could make a tutorial.

I tested this on my local server and proxmox HASS server, working great.
My Mariadb LXC is installed on Proxmox
Tteck scripts (AWSOME GUY!!!)

[malosaa (Hector)]

An update, well still working without an issue, and the migration went fine on all 3 systems.
1 had over 5gb of data, even the cost device did not mess up.

// 17-03-2023 Still working without an error or data problems.

[justone]

Qabout1: What’s a HSD?
Qabout2: How to create a database manually?
Qabout3: you ment the red ones when writing orange?
Qabout4: don’t get it whats a dump_database

I appreciate your work on that for a while and always tried to get the sense of it and it partly worked apart from the energy/price destaster but this time you completely lost me. It’s sort of workflow designed for graduated database engineers only I assume.
Will there be a “for the dumb dudes” version some day?

[malosaa (Hector)]

Qabout1: What’s a HSD?
sorry thats indeed a typo, it must be homeassistant.db

Qabout2: How to create a database manually?
i updated the tutorial

Qabout3: you ment the red ones when writing orange?
i updated the tutorial, it clearly shows all keys what need to be deleted but in the screenshot the red key was selected.

Qabout4: don’t get it whats a dump_database
thats the name i used for the tutorial database name, i updated the tutorial and change the name and made clear what is it.

if i have time i maybe can make a step by step video, but its a lot of work.

[justone]

@malosaa

but its a lot of work

Everything fine, won’t need a video at all. And yes summing up my luckless attempts to get things migrated wasn’t work at all, but eating up time like hell.
And since your hints from the past days/weeks did bring me most closest to the finishing line it was a bit strange since I got stuck at the starting grid (point 1)

Thanks a lot for you effort in making it more readable/understandable.

[fila612]

Thanks for that super howto, one question regarding step 8 & 9:
In some tables there aren’t the “created” row. so, it is important that all tables have a created row or it is important that the columns are equal?
In my case, there is no “created” row in the events table homeassistant_test:

therefore I don’t create one in the homeassistant db, right?

EDIT: it works without the created row, so I assume, that only the rows between the 2 db have to be equal.
Thanks for your Guide, that was the only one which helped in my case

[malosaa (Hector)]

Sorry for the late reply.

I’m glad i could help, for me everything still working on 3 servers.

About the CREATED row is only needed to be inserted if its missing in the new created database.
Just compare the old and new and its ok, it doesn’t have to be in every table

[malosaa (Hector)]

I recommand all people who have very less knowledge about the migration to MARIADB, just don’t do it!.
See what the developers have to say at the 2023.4 release party.

Here is the video at the correct timestamp

Home Assistant 2023.4 Release Party

[jr3us]

I have captured the above information regarding the recommendations to not use mysql / mariadb unless you KNOW what you are doing as a database administrator to the beginning post for this topic.

[Alex_Pupkin]

anybody moved back to sqlite after 2023.4 improvements?

[zadigre]

wondering the same if anyone switched back to SQLite.
Is it worth it if we have a good working setup with mariadb right now?
I don’t need external access to the database… I just want the best performance.

[mneuron]

Yes I switched back and succeeded. If it’s worth, is up to you.
The HA developers recommend using SQLite as the DB and made some really spectacular improvements on the performance.
See my ‘manual’ (no guarantee):

Migrating from MySQL (mariaDB) back to sqlite

Because the optimisation by the HA team for SQLite was huge I wanted to switch back from MAriaDB to SQLite… I finally succeeded in switching (back) from MariaDB to SQLite and keeping all states/events and statistics. (I’m on 2023.4.1 , the database scheme is 41 ) I used HeidiSQL, connected to the MariaDB and 1.export (with data: ‘replace existing data’) all seperate tables (only DATA, no table ). [image] change configuration.yaml, recorder: #db_url (now sqlite is the defaultDB) delet…

[m4v3r1ck]

I switched back to SQlite as well:

1. Finished - I was in the process of migrating during the announcement of 2023.4 - the migration to MariaDB.
2. Created a full backup of HA with the new migrated MariaDB.
3. Restored a previous day old full back up with SQlite 2023.3
4. Updated to the announced HA 2023.4 with SQLite Turbo
5. Still working with the updated SQLite, sailing very smooth.

I my experience the new 2023.4 SQLite is working really great, very snappy and light weighted. I realize this is not a scientifically approach to comparing DB’s.

All together a great revert. Moving on…

Great update HA Team.

[mpieru]

Hello,
are there any news or update about this topic ?
I tried to migrate (SQLite → MariaDB) following these notes but with core 2023.5 it seems that doesn’t work.
I’m going crazy because it would be very useful for me to have one steady database shared between a couple of homeassistant nodes (a sort of cluster, let’s say, only one node working at time, the second node only for redundancy/manteinance).

[Ha2379]

Thank you so much, appreciate the help!
Had to delete the database and igoner duplicate keys to get my energy data to transfer

[prom00]

I’m having issues on converting to Mariadb. I have an empty database ‘homeassisant’.

When I try to run sqlite3mysql:

Command:

sqlite3mysql --sqlite-file /config/home-assistant_v2.db --mysql-database homeassistant --mysql-host 192.168.1.x --mysql-user peter --mysql-password 'SECRET' --debug

Response:

Traceback (most recent call last):
File “/usr/bin/sqlite3mysql”, line 8, in
sys.exit(cli())
File “/usr/lib/python3.10/site-packages/click/core.py”, line 1130, in call
return self.main(*args, **kwargs)
File “/usr/lib/python3.10/site-packages/click/core.py”, line 1055, in main
rv = self.invoke(ctx)
File “/usr/lib/python3.10/site-packages/click/core.py”, line 1404, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/usr/lib/python3.10/site-packages/click/core.py”, line 760, in invoke
return __callback(*args, **kwargs)
File “/usr/lib/python3.10/site-packages/sqlite3_to_mysql/cli.py”, line 204, in cli
converter.transfer()
File “/usr/lib/python3.10/site-packages/sqlite3_to_mysql/transporter.py”, line 736, in transfer
self._create_table(table[“name”], transfer_rowid=transfer_rowid)
File “/usr/lib/python3.10/site-packages/sqlite3_to_mysql/transporter.py”, line 360, in _create_table
column_type = self._translate_type_from_sqlite_to_mysql(column[“type”])
File “/usr/lib/python3.10/site-packages/sqlite3_to_mysql/transporter.py”, line 287, in _translate_type_from_sqlite_to_mysql
raise ValueError(“Invalid column_type!”)
ValueError: Invalid column_type!

The connection is being made successfully, as I’ve deleted the homassistant database manually and it recreated it with sqlite3mysql.

Any idea on how to fight this?

[enboig]

I just want to add that I used dbeaver to migrate data without any problem (homeassistant in docker, mariadb installed locally):

1. create empty mariadb database
2. stop home-assistant
3. configure home-assistant to use mariadb
4. run home-assistant and stop it to create empty tables on mariadb
5. truncate mariadb tables (could be done at migration time)
6. configure on dbeaver both sqlite database and mariadb, and use it to migrate data

[spitf1r3]

Some data types may be different in MariaDB than in SQLite (and it may cause corruption of some states/other data), have you checked this?

[enboig]

I haven’t done rigorous check, just checked if “updates” from firsts posts were necessary and they weren’t.