How to convert Home Assistant database to MariaDB

This video is a tutorial on how to convert the default Home Assistant database to MariaDB.

Here are a few links to get you started.....

As always, make sure your system is up to date...

sudo apt-get update
sudo apt-get upgrade

Lets start installing everything.....

sudo apt-get install software-properties-common

Now lets run update again and then install MariaDB...

sudo apt-get update
sudo apt-get install mariadb-server

It will prompt you for a default admin password to use with MariaDB

Once thats done, lets install a few more pieces needed for this to work with HA....

sudo apt-get install libmariadb-client-lgpl-dev libssl-dev
pip3 install mysqlclient

In order to convert the existing db over we need to install sqlite3

sudo apt-get install sqlite3

Now we are ready to stop Home Assistant to start working with the db...

First we need to do a dump of the db......

Now we need to download the sqlite to mysql converter....

cd sqlite3-to-mysql/

Now we are ready to create the db in Mariadb...

mysql -u -root -p
create database homeassistant;

grant all privileges on homeassistant.* to 'hass' identified by 'PASSWORD'
exit

Now we are ready to import the converted db into our newly created db.....

Once that is done importing, we need to make a few tweaks....

mysql -u hass -p
use homeassistant
select max(run_id) from recorder_runs;
(IT SHOULD RETURN A NUMBER)

alter table recorder_runs modify column run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=106;(NUMBER RETURNED PREVIOUSLY **INCREASED BY 1)

alter table states drop foreign key states_ibfk_1;

select max(event_id) from events;
(IT SHOULD RETURN A NUMBER)

alter table events modify column event_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=183522; (NUMBER RETURNED PREVIOUSLY **INCREASED BY 1)

recorder:

Now that all the changes have been made, start HA back up....

That should be it, you should now be up and running on the mariadb database.

If you like the video, please subscribe to my channel. If you have any questions, hit me up in the comments below. Also, if there are any videos you would like me to put out here, let me know as well.

[samholton]

You didn't add the foreign key back. I see there is a typo in the original article, should be:

ALTER TABLE states ADD CONSTRAINT states_ibfk_1 FOREIGN KEY (event_id) REFERENCES events (event_id);

[SquareHI]

Can this procedure be done running supervisor on an Odroid that comes with the new Blue system released last month? I really need to make the switch but I don't know follow the procedure using 2020.12.0 Home Assistant Supervised

[hassanabid]

hey man thanks for the video, at the start of your videos can you give a brief explanation of what are the advantages of doing this and why somebody would do it? thanks again

[stonewallace]

I looked over the documentation for MariaDB after watching your video. Would this allow HA to be configured for multiple logins for different users and have the UI customized per user? Or at least hide some things from a guest user? - and maybe a follow up tutorial to show how?

[marine]

Im having this error after the database is implemented "Error converting row to state: any idea why?

[aaaafaaaaf]

Adrian, as always great tutorial. What are the advantages of using Maiadb (Mysql or Percona), vs sqllite. Is there a significant advantage to doing the migration.

As far as Im concerned, you can say, "Cause I say SO" and that is good enough for me.
Thanks,
LL

[foduuweb]

How can maintain the ON and OFF switch status in Hassbian? Once I restart my raspberry pi 3 then I lost all the lights / switch status. Is there any way to keep in as it was before restarting?

[James-qrmo]

Does this still work for others? I keep receiving this error message:

Unable to locate package libmariadb-client-lgpl-dev

[jayharbeston]

greetings, It looks mostly good after performing the update, and home assistant does indeed connect. I did find a problem though with JSON Decoder errors indicating that for example, "Expecting property name enclosed in double quotes" . I noticed that the shell script sqlite3-to-mysql converts all double quotes to backquotes(`) for the conversion and the data in the tables events and states reflect this. I am thinking that to fix this, there needs to be something like the following to fix it:
update events set event_data = REPLACE(event_data, '`', '"');
update states set attributes = REPLACE(attributes, '`', '"');

UPDATE:

I found that the index current value needs to also be set for the states table. I needed to add the following to my instance:

elect max(state_id) from states; (IT SHOULD RETURN A NUMBER)
alter table states modify column state_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=183522; (NUMBER RETURNED PREVIOUSLY **INCREASED BY 1)

 would you want to add this to the instructions above?

Regards

[marine]

Nice video pip3 install mysqlclient give me 1 error i cant make it work! Im using virtual env in hassbian!

[samuismarthome]

Please make the video How to convert Home Assistant database to MariaDB for Hassbian as well.

[WiDeIvan]

Can I delete a home-assistant_v2.db file after that?

[larrycasey]

Why are all of your videos in a car? Thanks for the video

[nogetfx]

Great video
On hassbian (Strech) got following error:
gpg: failed to start the dirmngr '/usr/bin/dirmngr': No such file or directory
gpg: connecting dirmngr at failed: No such file or directory
gpg: keyserver receive failed: No dirmngr

Fixed it with:
sudo apt remove gnupg
sudo apt install --reinstall gnupg2
sudo apt install dirmngr