Skip to content

Instantly share code, notes, and snippets.

@seidler2547
Last active January 8, 2023 06:03
Show Gist options
  • Star 41 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save seidler2547/93012edf3c7a2414ec1d9a8ebbc9c1a6 to your computer and use it in GitHub Desktop.
Save seidler2547/93012edf3c7a2414ec1d9a8ebbc9c1a6 to your computer and use it in GitHub Desktop.
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
@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))

@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..

@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.

@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.

@patrickdk77
Copy link

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'

@nathan951
Copy link

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?

@roflcoopter
Copy link

roflcoopter commented Apr 8, 2022

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..

@kaitlinsm Could you give an example of what this query looked like?

@wpte
Copy link

wpte commented Apr 29, 2022

I ended up using the sqlite3-to-mysql python script
So far it seems to work well.
Also utf8mb4 seems to be default on my ubuntu server setup

# prerequisites:
## install software
apt install mariadb-server
## create database
mysql -e 'CREATE SCHEMA IF NOT EXISTS `hass_db` DEFAULT CHARACTER SET utf8mb4'
## 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
docker stop homeassistant # or whatever it is for you
# now edit the configuration to point hass to mysql
nano ....
# convert the sqlite db to mysql
sqlite3mysql -f ./home-assistant_v2.db -d hass_db -u hass_user -p
# start HA
docker start homeassistant # or whatever it is for you

@asoretmadolell
Copy link

I ended up using the sqlite3-to-mysql python script So far it seems to work well. Also utf8mb4 seems to be default on my ubuntu server setup

# prerequisites:
## install software
apt install mariadb-server
## create database
mysql -e 'CREATE SCHEMA IF NOT EXISTS `hass_db` DEFAULT CHARACTER SET utf8mb4'
## 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
docker stop homeassistant # or whatever it is for you
# now edit the configuration to point hass to mysql
nano ....
# convert the sqlite db to mysql
sqlite3mysql -f ./home-assistant_v2.db -d hass_db -u hass_user -p
# start HA
docker start homeassistant # or whatever it is for you

This method worked for me. Awesome. You're a star.

@jansemrad
Copy link

If you are getting error:

2022-07-12 09:32:29 ERROR 2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)
2003: Can't connect to MySQL server on 'localhost:3306' (111 Connection refused)

Let´s try this way:

sqlite3mysql -f ./config/home-assistant_v2.db -d hass_db -u hass_user -h core-mariadb -p

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