The issue seems to be caused by the pypi sqlite-to-mysql tool. It does not recreate the new tables with the correct data types needed for mysql.
I created a new empty database in the mariadb addon, and then set HA to that and restarted, just to get the correct expected table schema from HA. Then I altered all the incorrect table schemas and switched HA back to my database, and that resolved the issues. There were other columns as well that were different: datetime → datetime(6), int(11) → smallint(6), text → longtext, _ts columns from float → double, and changing table collation to utf8mb4_unicode_ci
Full diff of the schema as created by pypi’s sqlite-to-mysql compared to the schema as created by Home Assistant directly on an empty db: schema.diff · GitHub
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.
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.
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;
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.
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.
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
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"
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)?
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.
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 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.
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.
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.