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

[malosaa (Hector)]

For me i never had this problem, so i don’t think this will happen to everyone… ,

i have a system still running from the day i posted my work around, and the database is still working correct, that day the database had data from over a year and titll today still running.

So thats why i decided today to full migrate with my main system, as my test system is a duplicate of my main system.

The main thing was a cost entity what got messed up, but found a solution for it.

But i can ofcourse try your way and see if the cost entity does not get messed up, if not i will use your method.

//Update
You right something did break the conversion, 2 months ago i could convert without an issue.

[fversteegen (Frank)]

I didn’t do anything else; migrated the DB 2 months ago with problems with the cost entity. Without any changes I tried to put a cost entity in place again and poof: it worked as expected. I suspect that the recent db upgrade (as part of 2023.3 release) has something to do with it.

[malosaa (Hector)]

@spitf1r3

pv home-assistant_v2.sql

im getting at this step

pv home-assistant_v2.sql | mariadb

this error

pv command not found

[malosaa (Hector)]

For me i can’t convert as my data gets full messed up right now. i do try the method @spitf1r3 describes.

[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