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

spitf1r3 · March 2, 2023, 1:13pm

How can I use this? Do I need to dump sqlite to .sql file and then patch it somehow?

madcoder · March 2, 2023, 3:36pm

It’s only for information. I used it to make changes to the tables to bring them back up to match HA.

The specific problems in logbook timestamps is the float->double change in the comments I replied to. The fix is to modify the column data type.

The diff shows you the table and column, what the PyPI script migrated its data type to, and what HA wants its data type to be.

Frank_Howell · March 4, 2023, 11:03am

Hi,

Why doesn’t home assistant provide a normal migration to MariaDB? I don’t want to loose my data (a few years of statistics) and for what I read, it can be pretty risky migrating ourselves. There are so many good developers in the HA community.

Regards,

Frank

bdraco · March 5, 2023, 8:00am

When you migrate your data to MariaDB you are signing up to be the database administrator for your instance.

You’ll need to know how to handle backup and recovery, InnoDB corruption, general database maintenance tasks, cleaning up temporary storage, optimizing the my.cnf and other settings for your instance, and sometimes resizing data storage at some point. Unless you are comfortable with the above, you may be setting yourself up for a bad day in the future.

If you don’t have a specific need to access your data via MariaDB you may still be better off with using sqlite. The sqlite setup you get out of box is highly optimized for HA. If you leave everything with the defaults, HA is going to handle most of the above for you and you’ll primarily need to worry about backups.

spitf1r3 · March 5, 2023, 8:23pm

Here are the steps I’ve used to migrate my sqlite db to MySQL. Use at you own risk.
It took me a few tries/days (and a very helpful colleague) to figure this out based on various guides, each of which was outdated to some degree. Biggest issue with the method of using sqlite3mysql is that it ends up with a DB where many values are of the wrong type (FLOAT), and when you convert them to the right one (DOUBLE) you lose data.

wget https://gist.github.com/PattaFeuFeu/c4475457854f42f64f21268777d64d87/raw/66450ceee6ce8371aafacb5623fd06537ebd4881/sqlite3-to-mysql.sh

chmod +x sqlite3-to-mysql.sh

sqlite3 /config/home-assistant_v2.db .dump | ./sqlite3-to-mysql.sh > home-assistant_v2.sql

sed -i 's/FLOAT/DOUBLE/g' home-assistant_v2.sql

sed -i '1 i\SET autocommit=0;' home-assistant_v2.sql

sed -i 's/PRAGMA foreign_keys=OFF;/SET foreign_key_checks = 0;/g' home-assistant_v2.sql

sed -i 's/BEGIN TRANSACTION/BEGIN/' home-assistant_v2.sql

echo "commit;" >> home-assistant_v2.sql

mariadb --host=localhost --protocol=tcp --port=3306 --user=<mysql user> --password=<your mysql password> -e "drop database homeassistant;"

mariadb --host=localhost --protocol=tcp --port=3306 --user=<mysql user> --password=<your mysql password> -e "create database homeassistant;"

pv home-assistant_v2.sql | mariadb --host=localhost --protocol=tcp --port=3306 --user=<mysql user> --password=<your mysql password> homeassistant

mariadb --host=localhost --protocol=tcp --port=3306 --user=<mysql user> --password=<your mysql password> homeassistant

SET autocommit=0;

SET foreign_key_checks = 0;

SELECT MAX(event_id) FROM events;

ALTER TABLE events MODIFY COLUMN event_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = <number from previous step +1>;

SELECT MAX(state_id) FROM states;

ALTER TABLE states MODIFY COLUMN state_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = <number from previous step +1>;

SELECT MAX(run_id) FROM recorder_runs;

ALTER TABLE recorder_runs MODIFY COLUMN run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = <number from previous step +1>;

SELECT MAX(change_id) FROM schema_changes;

ALTER TABLE schema_changes MODIFY COLUMN change_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = <number from previous step +1>;

SELECT MAX(attributes_id) FROM state_attributes;

ALTER TABLE state_attributes MODIFY COLUMN attributes_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = <number from previous step +1>;

SELECT MAX(id) FROM statistics_short_term;

ALTER TABLE statistics_short_term MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = <number from previous step +1>;

SELECT MAX(run_id) FROM statistics_runs;

ALTER TABLE statistics_runs MODIFY COLUMN run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = <number from previous step +1>;

SELECT MAX(id) FROM statistics;

ALTER TABLE statistics MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = <number from previous step +1>;

SELECT MAX(data_id) FROM event_data;

ALTER TABLE event_data MODIFY COLUMN data_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = <number from previous step +1>;

SELECT MAX(id) FROM statistics_meta;

ALTER TABLE statistics_meta MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = <number from previous step +1>;

ALTER TABLE states DROP FOREIGN KEY states_ibfk_1;

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

COMMIT;

spitf1r3 · March 5, 2023, 8:31pm

I understand the sentiment, but I’m a pretty fresh user of HA (6 months), and I have outgrown sqlite quite quickly. It took me quite a few days/tries and a lot of help from someone to migrate my DB and make sure HA logs were silent (by correcting any wrong formatting of DB). Which leads me to believe there should be a better way to do that (migrate DB, back it up, etc.) built into HA. It’s not hard to end up with a 5-6GB sqlite DB that becomes pretty unmaintainable and slow.

bdraco · March 5, 2023, 9:09pm

SQLite can handle much larger database sizes than 6GB. I have SQLite databases larger than 100GB in production for other projects without any issues.

If you have that much data in the database with the default settings you likely have an integration that isn’t following standards or you are using a custom component that needs some design help to get its use of the state machine under control.

Its not just the database that it’s going to have to process all of that data. What you are describing is a symptom of a larger problem.

stephanvierkant · March 6, 2023, 3:52pm

Thanks for everyone with sharing their useful scripts!

I’ve created a feature request because I think this should be doable without running these scripts: MySQL migration tool

fversteegen · March 7, 2023, 5:48am

After being busy with other things for a while I decided to try adding cost to the energy dashboard again. This without using the tips from @malosaa and doing it simply within the energy dashboard.

To my surprise it looks as if this is working as expected again. Even after a few days the values still look normal without going through the roof again.

Anyone else experiencing the same? Maybe it has something to do with the database upgrade when moving tot 2023.3 release

Alex_Pupkin · March 7, 2023, 8:52pm

Thanks for sharing this - made a couple of improvements:

Removed old patterns taht no longer occur in export
Added removal of non-ascii characters
Automated auto-increment updates
Changed pv to cat since I dont have pv installed and it is hard to get into mariadb container

I run supervised so I run it first on HA core to export Sqlite3 DB into SQL (where i have sqlite3 client) and then in mariadb container where i have mariadb client.

Migrated 1Gb database today using this… so far so good

#/bin/bash

USER=homeassistant
PASSWORD=*****your password here*****

SQL_SQLITE3="home-assistant_v2.sql.sqlite3"
SQL_MARIADB="home-assistant_v2.sql.mariadb"
SQL_TEMP="home-assistant_v2.sql.temp"

if [ ! -f "home-assistant_v2.sql.sqlite3" ]; then
    echo "Dumping sqlite3 database to sql file"
    if [ ! -f home-assistant_v2.db ]; then
        echo "No database found at /config path. Try tunning this part of the script where HA is installed"
        exit 1
    fi
    sqlite3 home-assistant_v2.db .dump > "SQL_SQLITE3"
    echo "Done dumping sqlite3 database to sql file. Now run this script again where mariadb client is available"
    exit 0
fi

echo "Cleaning up sql file"
cp home-assistant_v2.sql.sqlite3 "$SQL_MARIADB"
sed -i \
-e 's/PRAGMA foreign_keys=OFF;/SET foreign_key_checks = 0;/g' \
-e 's/BEGIN TRANSACTION/BEGIN/' \
-e 's/FLOAT/DOUBLE/g' \
-e '1 i\SET autocommit=0;' \
-e 's/"end"/`end`/g' \
-e 's/event_type VARCHAR(32)/event_type VARCHAR(64)/g' \
"$SQL_MARIADB"
tr -cd '\11\12\15\40-\176' < "$SQL_MARIADB" > "$SQL_MARIADB".tmp
mv "$SQL_MARIADB".tmp "$SQL_MARIADB"

echo "Resetting auto increment values"
touch "$SQL_TEMP"
echo "SET autocommit=0;" > "$SQL_TEMP"
echo "SET foreign_key_checks = 0;" >> "$SQL_TEMP"
echo "START TRANSACTION;" >> "$SQL_TEMP"
echo "SET @m = (SELECT MAX(event_id) + 1 FROM events);" >> "$SQL_TEMP"
echo "SET @s = CONCAT('ALTER TABLE events MODIFY COLUMN event_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=', @m);" >> "$SQL_TEMP"
echo "PREPARE stmt1 FROM @s;" >> "$SQL_TEMP"
echo "EXECUTE stmt1;" >> "$SQL_TEMP"
echo "DEALLOCATE PREPARE stmt1;" >> "$SQL_TEMP"
echo "SET @m = (SELECT MAX(run_id) + 1 FROM recorder_runs);" >> "$SQL_TEMP"
echo "SET @s = CONCAT('ALTER TABLE recorder_runs MODIFY COLUMN run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=', @m);" >> "$SQL_TEMP"
echo "PREPARE stmt1 FROM @s;" >> "$SQL_TEMP"
echo "EXECUTE stmt1;" >> "$SQL_TEMP"
echo "DEALLOCATE PREPARE stmt1;" >> "$SQL_TEMP"
echo "SET @m = (SELECT MAX(change_id) + 1 FROM schema_changes);" >> "$SQL_TEMP"
echo "SET @s = CONCAT('ALTER TABLE schema_changes MODIFY COLUMN change_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=', @m);" >> "$SQL_TEMP"
echo "PREPARE stmt1 FROM @s;" >> "$SQL_TEMP"
echo "EXECUTE stmt1;" >> "$SQL_TEMP"
echo "DEALLOCATE PREPARE stmt1;" >> "$SQL_TEMP"
echo "SET @m = (SELECT MAX(state_id) + 1 FROM states);" >> "$SQL_TEMP"
echo "SET @s = CONCAT('ALTER TABLE states MODIFY COLUMN state_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=', @m);" >> "$SQL_TEMP"
echo "PREPARE stmt1 FROM @s;" >> "$SQL_TEMP"
echo "EXECUTE stmt1;" >> "$SQL_TEMP"
echo "DEALLOCATE PREPARE stmt1;" >> "$SQL_TEMP"
echo "SET @m = (SELECT MAX(run_id) + 1 FROM statistics_runs);" >> "$SQL_TEMP"
echo "SET @s = CONCAT('ALTER TABLE statistics_runs MODIFY COLUMN run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=', @m);" >> "$SQL_TEMP"
echo "PREPARE stmt1 FROM @s;" >> "$SQL_TEMP"
echo "EXECUTE stmt1;" >> "$SQL_TEMP"
echo "DEALLOCATE PREPARE stmt1;" >> "$SQL_TEMP"
echo "SET @m = (SELECT MAX(id) + 1 FROM statistics_meta);" >> "$SQL_TEMP"
echo "SET @s = CONCAT('ALTER TABLE statistics_meta MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=', @m);" >> "$SQL_TEMP"
echo "PREPARE stmt1 FROM @s;" >> "$SQL_TEMP"
echo "EXECUTE stmt1;" >> "$SQL_TEMP"
echo "DEALLOCATE PREPARE stmt1;" >> "$SQL_TEMP"
echo "SET @m = (SELECT MAX(id) + 1 FROM statistics);" >> "$SQL_TEMP"
echo "SET @s = CONCAT('ALTER TABLE statistics MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=', @m);" >> "$SQL_TEMP"
echo "PREPARE stmt1 FROM @s;" >> "$SQL_TEMP"
echo "EXECUTE stmt1;" >> "$SQL_TEMP"
echo "DEALLOCATE PREPARE stmt1;" >> "$SQL_TEMP"
echo "SET @m = (SELECT MAX(id) + 1 FROM statistics_short_term);" >> "$SQL_TEMP"
echo "SET @s = CONCAT('ALTER TABLE statistics_short_term MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=', @m);" >> "$SQL_TEMP"
echo "PREPARE stmt1 FROM @s;" >> "$SQL_TEMP"
echo "EXECUTE stmt1;" >> "$SQL_TEMP"
echo "DEALLOCATE PREPARE stmt1;" >> "$SQL_TEMP"
echo "SET @m = (SELECT MAX(attributes_id) + 1 FROM state_attributes);" >> "$SQL_TEMP"
echo "SET @s = CONCAT('ALTER TABLE state_attributes MODIFY COLUMN attributes_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=', @m);" >> "$SQL_TEMP"
echo "PREPARE stmt1 FROM @s;" >> "$SQL_TEMP"
echo "EXECUTE stmt1;" >> "$SQL_TEMP"
echo "DEALLOCATE PREPARE stmt1;" >> "$SQL_TEMP"
echo "SET @m = (SELECT MAX(data_id) + 1 FROM event_data);" >> "$SQL_TEMP"
echo "SET @s = CONCAT('ALTER TABLE event_data MODIFY COLUMN data_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=', @m);" >> "$SQL_TEMP"
echo "PREPARE stmt1 FROM @s;" >> "$SQL_TEMP"
echo "EXECUTE stmt1;" >> "$SQL_TEMP"
echo "DEALLOCATE PREPARE stmt1;" >> "$SQL_TEMP"
echo "ALTER TABLE states DROP FOREIGN KEY states_ibfk_1;" >> "$SQL_TEMP"
echo "ALTER TABLE states ADD CONSTRAINT states_ibfk_1 FOREIGN KEY (event_id) REFERENCES events(event_id);" >> "$SQL_TEMP"
echo "SET foreign_key_checks = 1;" >> "$SQL_TEMP"
echo "COMMIT;" >> "$SQL_TEMP"

echo "Dropping and recreating database"
mariadb --host=localhost --protocol=tcp --port=3306 --user=$USER --password=$PASSWORD -e "drop database homeassistant;"
mariadb --host=localhost --protocol=tcp --port=3306 --user=$USER --password=$PASSWORD -e "create database homeassistant;"
echo "Importing sql file to database"
mariadb --host=localhost --protocol=tcp --port=3306 --user=$USER --password=$PASSWORD homeassistant < "$SQL_MARIADB"
mariadb --host=localhost --protocol=tcp --port=3306 --user=$USER --password=$PASSWORD homeassistant < "$SQL_TEMP"

spitf1r3 · March 7, 2023, 10:53pm

Thanks for cleaning this up, I didn’t have enough skill to validste/automate some of this stuff.
I ran it from core_ssh addon, then from supervised Debian, so installing pv (which shows a very useful progress bar for the longest operation) was not an issue.
Did I miss any essential steps during my migration (I tired it multiple times, until I was able to eliminate all errors/warnings from core log)?

Alex_Pupkin · March 7, 2023, 11:13pm

i dont think u missed much - the big difference between this version and yours is that several tables were omitted in yours (statistics_short_term, state_attributes, event_data) in altering their auto_increment - this can cause issues IF these tables are still relevant and you had data in them and new data is inserted (IDs will duplicate). I am not certain which version of HA these tables came from (i am running HA for years now so this might just be old tables….). You can just run ALTER statements like you did for other tables to make sure you are ok.

spitf1r3 · March 8, 2023, 9:06am

No, I did all of those in mine.
I guess I’m good then, thanks.

malosaa · March 9, 2023, 9:10am

Hi did you try one of the methods mentioned by @Alex_Pupkin or @spitf1r3 ?
Maybe that would give no cost entity problem.

Actually today i did migrate from my new server 1gb+ .hsd to the mariaDB on my proxmox server.
The conversion went normal but the cost entity got screwed up like before, just for the day of the conversion only.
If i did know there where other methods i would have tried them.

So i re-added the total cost entity in the energy DB and works again.

Still this method in powershell windows works as a charm.

sqlite3mysql  --sqlite-file home-assistant_v2.db  --mysql-database homeassistant  --mysql-host 10.XX.XX.XXX  --mysql-user XXX --mysql-password XXX --ignore-duplicate-keys

malosaa · March 9, 2023, 10:07am

to not get an memory error copy the file to windows or an other pc with enough ram and run the command there, it worked out for me.

spitf1r3 · March 9, 2023, 10:43am

Sqlite3mysql will mess up your db because all of the columns that should be DOUBLE are set to FLOAT. This will mess up data on import and it will affect your future data too.

malosaa · March 9, 2023, 10:55am

~~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 · March 9, 2023, 1:48pm

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 · March 9, 2023, 8:45pm

im getting at this step

pv home-assistant_v2.sql | mariadb

this error

pv command not found

malosaa · March 9, 2023, 8:47pm

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