Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
One-liner to convert an existing Home-Assistant SQLite database to MySQL
# prerequisites:
## install software
apt install mariadb-server libmariadbclient-dev sqlite3
## install mysqlclient in virtualenv
su -c 'homeassistant/bin/pip3 install mysqlclient --upgrade' -l homeassistant
## create database
mysql -e 'CREATE SCHEMA IF NOT EXISTS `hass_db` DEFAULT CHARACTER SET utf8'
## create user (use a safe password please)
mysql -e "CREATE USER 'hass_user'@'localhost' IDENTIFIED BY 'hass_pw'"
mysql -e "GRANT ALL PRIVILEGES ON hass_db.* TO 'hass_user'@'localhost'"
mysql -e "GRANT usage ON *.* TO 'hass_user'@'localhost'"
# stop HA now
systemctl stop home-assistant # or whatever it is for you
# now edit the configuration to point hass to mysql
nano ....
# now start HA once and stop it right away, we only want it to create the tables:
systemctl start home-assistant # or whatever it is for you
sleep 20
systemctl stop home-assistant # or whatever it is for you
# now empty the tables
mysql hass_db -e 'delete from events;delete from recorder_runs; delete from schema_changes; delete from states;'
# this is the actual conversion:
sqlite3 home-assistant_v2.db .dump \
| sed -re 's/^PRAGMA .+OFF/SET FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0/' \
-e 's/^CREATE INDEX .+//' \
-e 's/^BEGIN TRANSACTION;$/SET autocommit=0;BEGIN;/' \
-e '/^CREATE TABLE .+ \($/,/^\);/ d' \
-e 's/^INSERT INTO "([^"]+)"/INSERT INTO \1/' \
-e 's/\\n/\n/g' \
| perl -pe 'binmode STDOUT, ":utf8";s/\\u([0-9A-Fa-f]{4})/pack"U*",hex($1)/ge' \
| mysql hass_db --default-character-set=utf8 -u hass_user -p
@MarMed
Copy link

MarMed commented Jun 23, 2019

Thanks for this!

Loading

@Pofilo
Copy link

Pofilo commented Aug 10, 2019

The first startup is a bit longer but it works like a charm, thanks!

Loading

@pve84
Copy link

pve84 commented Sep 25, 2019

Thanks, did need to change a few things for my setup. The conversion part was very helpful

Loading

@elbow
Copy link

elbow commented Dec 29, 2019

Why do you remove the indexes?

Loading

@seidler2547
Copy link
Author

seidler2547 commented Dec 29, 2019

Because the syntax is different and the indices are created by HA when it starts up.

Loading

@Pepetor
Copy link

Pepetor commented Jan 25, 2020

Why don't you use utf8mb4 instead utf8?

Loading

@BornTKill
Copy link

BornTKill commented May 14, 2020

mysql --binary-mode=1 if you get ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected

Loading

@JanUrlaub
Copy link

JanUrlaub commented Aug 12, 2020

Thanks :-)

Loading

@antoweb
Copy link

antoweb commented Nov 29, 2020

this command return error
mysql hass_db -e 'delete from events;delete from recorder_runs; delete from schema_changes; delete from states;'

Cannot delete or update a parent row: a foreign key constraint fails (hass_db.states, CONSTRAINT states_ibfk_1 FOREIGN KEY (event_id) REFERENCES events (event_id))

Loading

@kaitlinsm
Copy link

kaitlinsm commented Jan 17, 2021

This worked for me, however I had to use utf8mb4 encoding, and I also had to add two extra columns. Something like:

alter table states
	add context_id character(36) null;

alter table states modify context_id char(36) null after created;

alter table states
	add context_user_id character(36) null;

alter table states modify context_id char(36) null after context_id;

and the one-liner was

sqlite3 home-assistant_v2.db .dump \
| sed -re 's/^PRAGMA .+OFF/SET FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0/' \
       -e 's/^CREATE INDEX .+//' \
       -e 's/^BEGIN TRANSACTION;$/SET autocommit=0;BEGIN;/' \
       -e '/^CREATE TABLE .+ \($/,/^\);/ d' \
       -e 's/^INSERT INTO "([^"]+)"/INSERT INTO \1/' \
       -e 's/\\n/\n/g' \
| perl -pe 'binmode STDOUT, ":utf8mb4";s/\\u([0-9A-Fa-f]{4})/pack"U*",hex($1)/ge' \
| mysql homeassistant --default-character-set=utf8mb4 -u xxxx -p 

Aside from that, I had problems converting unicode characters - a lot of entries in states had unicode \u00b0C that was copied as ?C for example, but an update/replace query sorted that out..

Loading

@gregfroese
Copy link

gregfroese commented Jan 22, 2021

Thank you for this, very helpful.
I basically followed your guide, but I did have trouble with the degrees symbol from my temperature reporting.

To get past that, I replaced the last line calling myql with a redirect to a file something like this:
sqlite3 home-assistant_v2.db .dump \ | sed -re 's/^PRAGMA .+OFF/SET FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0/' \ -e 's/^CREATE INDEX .+//' \ -e 's/^BEGIN TRANSACTION;$/SET autocommit=0;BEGIN;/' \ -e '/^CREATE TABLE .+ \($/,/^\);/ d' \ -e 's/^INSERT INTO "([^"]+)"/INSERT INTO \1/' \ -e 's/\\n/\n/g' \ | perl -pe 'binmode STDOUT, ":utf8mb4";s/\\u([0-9A-Fa-f]{4})/pack"U*",hex($1)/ge' > output.sql

This is how I discovered the error was coming from the degrees symbol. I wasn't concerned about that data so I just grep'd those lines out into a cleansed version like this:
grep -v "C\"" output.sql > cleansed.sql
I was too lazy to figure out the encoding of the degrees symbol to use it on the command line, so I just looked for C" to find these lines

After that it was simply:
cat cleansed.sql | mysql -h 127.0.0.1 homeassistant --default-character-set=utf8mb4 -u xxxx -p

One other note, I did have to specify the hostname in the mysql command as shown above to get it to connect to MariaDB in a docker container.

Loading

@TerryE
Copy link

TerryE commented Mar 3, 2021

@kaitlinsm I've just done this approach for to migrate my recorder DB to MariaDB. I've got an current hass.io docker install. My states table doesn't include the fields context_id or context_user_id , so I guess that these have been added by some customisation.

UTF-8 encoding uses multiple bytes per UTF character with core ASCII taking one and emoticons 4. The MySQL / MariaDB implementation of the "utf-8" is actually an invalid implementation in that it can't handle the 4 byte UTF codes correctly; however the name has been retained for backwards compatibility. "utf8mb4" really means utf-8 correctly implemented. AFAIK, perl utf8 output loads correctly into utf8mb4 collated fields. I certainly couldn't find any probs with my field names (including ° C).

A couple of other points for HassOS users:

  • Don't move reporter to mariaDB if your data partition is on microSD. I use an RPi4 for my HA system, but my data partition is on a USB3 mapped M.2-SATA SSD which is fine for the InnoDB engine.
  • Doing this is a lot easier if you have SSH access to the kernel (port 22222) and the HA CLI (port 22) containers.
  • sqllite3 isn't available in the mariadb container so you need to do the unload by SSHing to the kernel. The /config folder is pathed at /mnt/data/supervisor/homeassistant in the kernel. So after stopping the supervisor, I did:
# CONFIG=/mnt/data/supervisor/homeassistant
# MARIADB=`docker ps | grep mariadb | cut 1-12`
# sqlite3 $CONFIG/home-assistant_v2.db .dump > $CONFIG/ha.sql
# docker cp $CONFIG/ha.sql $MARIADB:/ha.sql
# rm $CONFIG/ha.sql
# docker exec -it $MARIADB bash
  • This last command puts you into an interactive session in the MariaDB container with the unload file at /ha.sql. This container does have a proper sed and perl 5.30.
  • I added my DB pwd and DB connection URL to my secrets.yaml, installed the mariaDB container, configured the homeassistant password and started mariaDB to create the homeassistant DB. I did a ha core stop added the db_url: !secret mariadb_url to my recorder entry, and did a ha core start <wait> ha core stop to allow HA to create the reporter tables in mariaDB.
  • You can log onto the DB with mysql -u root homeassistant to truncate all 4 tables. (You will need to SET FOREIGN_KEY_CHECKS=0 first otherwise truncating states will fail.)
  • Edit the SQL to make mariaDB conformant; run though perl filter, and reload into mysql, then cleanup crap sql files and exit the container.
  • The recorder history should now be available once you restart the HA core.

Loading

@patrickdk77
Copy link

patrickdk77 commented Mar 3, 2021

I used this perl command and had no issues with the utf8 encoding of any symbols, degrees, cosine, ...

perl -pe 'use utf8; use open qw(:std :utf8); binmode STDOUT, ":utf8mb4";s/\\u([0-9A-Fa-f]{4})/pack"U*",hex($1)/ge'

Loading

@nathan951
Copy link

nathan951 commented Jul 15, 2021

Im very novice here...
I'm using HAOS and have MariaDB addon installed.
It looks like the instructions install mariaDB at HAOS root? Im not sure how to SSH into the addon.

Is this the correct way to migrate for HAOS users?
Is it possible for detailed steps for a real novice HAOS users, like myself?

Loading

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment