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

repiuk · August 28, 2022, 6:24am

I need to do the same migration. Events has 2 258 466 rows. The sqlite db is on schema_version 9. A new install is on schema_version 29. Recorder purge never worked apparently. Home assistant is running on the same NUC for 2 years…
importing haimport.sql is at 300000 rows after 2 hours. It would need several days to import the complete database
Tried restoring a backup on a new NUC with the latest Home Assistant 2022.8.7. The result is a complete disaster and a new(empty) home_assistant_v2.db

Dumped the statistics with
Sqlite3
sqlite> .output statistics.sql
sqlite> .dump statistics
and the other tables

Lets see if I can save the statistics

I really don’t understand why they didn’t opt for a second database for the statistics.

bbccdd · September 18, 2022, 9:59am

To those who have moved to mariadb, do you also experience the logbook failing to load events?

And, what could point into a troubleshooting direction please?

j54j6 · September 18, 2022, 12:54pm

Today I also moved my Home assistant 2022.9.4 to MariaDB - Migration went (besides a couple of columns had a wrong datatype or missing index) well, but I also have the Energy Dashboard problem. My Graph looks quite good, and the measurements are also correct (either on UI and in DB) but as mentioned above my costs are ridiculous… 1 kWh is 86.63€ (even for me, living in Germany is the price a bit high ^^)
But it seems that the prices don’t have anything in common with the consumption… Like in the picture now (a couple of hours later) - I now have consumed 3.96 kWh, but the price is only “a bit higher” (8€ for the next 2 kWh)…

Did anyone find a solution for this? - I’ve tried creating a new sensor, but there is also the same problem. Even a new Migration did not solve this .
I even noticed that the “Non-Fossil energy consumed” Chart may be a bit too high – normally I’he more like 50 - 60% but never before 76% Percent…

Hope anyone already solved this issue

e-raser · September 18, 2022, 1:24pm

I think the energy dashboard uses its own (hidden) sensors for keeping track of power usage and costs of provided input sensors. No idea where they are stored, just a wild guess…

drknow · September 30, 2022, 2:24am

I have a question for you - if the DB server is down, does HASS still work fine? I like your approach but if my main server dies (the one that would run the sql) i really don’t want the home to stop working well since I have a dedicated pi for HASS. Have you tested this? And if so, what happens when teh sql server comes back up?

ihr · September 30, 2022, 9:44am

@drknow if the DB server is down, the “recorder” service raises errors and some integrations might not work as expected, but other areas of HASS still works. For example, in my case I’m using mostly zigbee devices using the phoscon.de integration and hardware (in a separate RPi) that RPI is still sending events (via sockets connection) and the automations are fired so yes, they work. The problem is only that the “state” is not stored regularly to the database and the statistics will not collect all state changes

e-raser · September 30, 2022, 2:11pm

Taking the database apart from the same machine HA is running apparently introduces more dependencies and increases the possibility of things breaking. A half-working HA „just“ because the other machine hosting the database is gone for a moment (maybe due to network hiccups) is nothing I am looking forward, not speaking bout performance (network speed / latency etc.). Of course if you have perfect environment it might work really well, maybe even better than having everything in the same box.

Just my opinion, which is why I‘m personally not going to outsource my database but instead keep it running on the same host (HA OS).

But here it’s about migration (from SQLite to MySQL/MariaDB)

jressel01 · October 8, 2022, 5:48pm

Today i migrate my SQLite DB to Maria DB. The convert neet 10 hours.
Export and Import without Problem.
Nice tutorial.

Mario1 · October 8, 2022, 6:41pm

Hi,
I do not have any issue. Only one was utf8mb4_unicode_ci what I described.
Since I did migration I work on the same instance without any problem.

Mario1 · October 8, 2022, 6:42pm

Hi,
I do not have any issue. Only one was utf8mb4_unicode_ci what I described. Since I did migration I work on the same instance without any problem.

bbccdd · October 10, 2022, 4:59am

Same problem here. Please keep us posted on a solution for this home assistant specific energy crisis

perwah · October 12, 2022, 2:04pm

Yea, energy cost calculations got messed up for me after migration to MariaDB. It now just accumulates all the sums, instead of all the differences. It’s possible to recalculate the history in the database, but it still continues to calculate wrongly. I posted a more detailed reply here.

Now I’ve just let it be, since I’ve already spent a day or two trying to fix it.

e-raser · October 12, 2022, 2:19pm

Thank you all for sharing. That’s obviously a no-go for some users willing to migrate, but not accepting the loss of LTS / energy dashboard data. Hope you get it fixed somehow.

Maybe all of you affected can summarize what migration method you used so that @jr3us perhaps can add a disclaimer/warning sign in the original post. Nobody lucky to find these great migration how-tos will be happy finding out the energy dashboard in the end got messed up.

mchome · October 14, 2022, 4:00pm

This sounds like the type of data for longterm statistics
‘state_class: total_increasing’

got messed up during copy and maybe altered the type of state class .

nesang · October 18, 2022, 2:50am

do you know if there’s a way to update the state_class? these seem to be hidden entities

prvakt · October 25, 2022, 9:21am

For those having issues with wrong energy cost, just run those 4 queries (as already suggested by ihr) and restart HA:

ALTER TABLE `statistics` CHANGE `created` `created` DATETIME(6) NULL DEFAULT NULL, CHANGE `start` `start` DATETIME(6) NULL DEFAULT NULL, CHANGE `mean` `mean` DOUBLE NULL DEFAULT NULL, CHANGE `min` `min` DOUBLE NULL DEFAULT NULL, CHANGE `max` `max` DOUBLE NULL DEFAULT NULL, CHANGE `last_reset` `last_reset` DATETIME(6) NULL DEFAULT NULL, CHANGE `state` `state` DOUBLE NULL DEFAULT NULL, CHANGE `sum` `sum` DOUBLE NULL DEFAULT NULL;

ALTER TABLE `statistics_short_term` CHANGE `created` `created` DATETIME(6) NULL DEFAULT NULL, CHANGE `start` `start` DATETIME(6) NULL DEFAULT NULL, CHANGE `mean` `mean` DOUBLE NULL DEFAULT NULL, CHANGE `min` `min` DOUBLE NULL DEFAULT NULL, CHANGE `max` `max` DOUBLE NULL DEFAULT NULL, CHANGE `last_reset` `last_reset` DATETIME(6) NULL DEFAULT NULL, CHANGE `state` `state` DOUBLE NULL DEFAULT NULL, CHANGE `sum` `sum` DOUBLE NULL DEFAULT NULL;

ALTER TABLE events MODIFY `time_fired` datetime(6), MODIFY `event_data` longtext;

ALTER TABLE states MODIFY `last_updated` datetime(6), MODIFY `last_changed` datetime(6), MODIFY `attributes`longtext;

And in order to fix statistics in your DB, just create a trigger as suggested here (you will need 2 of them - 1 for long term and 1 for short term stats): https://community.home-assistant.io/t/recalculate-statistics-table-sum-to-fix-wrong-energy-data/371334/5

perwah · October 30, 2022, 1:59pm

Unfortunately, this did not solve the issue of runaway energy cost calculations.

Edit: Now a few days later, it looks like the cost calculation is ok again!

fireheadman · November 11, 2022, 11:58pm

Got a question on the mariadb migration… I performed all the steps (minus the secure installation — will go back and do this another day).

When I look at Events, I see every entry as NULL NULL

States also shows NULL NULL NULL and Unknown

Will this heal over time as things report in? Or am I missing something here?
PS, my energy stats look fine. (Statistics)

eddiewebb · November 13, 2022, 9:30pm

Solution

I took a simpler or lazier approach, and used a well supported library to quickly migrate home assistants sqlite database into MariaDB/MySQL with full history. There is no need to even transfer files - just a couple commands and done!

Prerequisites

install and activate MariaDB addon
Install and activate the Terminal & SSH addon

Maria DB Migration Steps

Stop the recorder service from developer tools
( ‘Developer Tools’ > ‘Services’ > Search for Recorder: Disable > Press the ‘Call service’ )
Start the Terminal & SSH addon (mariadb should be running as well)
Run the commands below to install python (already exists on host, super fast)
Run DB migration

Post-Migration (activation)

Configure HA to use new mariadb as recorder in configuration.yaml

Commands to migrate

# Install pre-req
apk add python3
apk add py3-pip
pip3 install sqlite3-to-mysql
# OR python3 -m pip install sqlite3-to-mysql

# run migration (CHANGE PASSWORD)
sqlite3mysql \
--sqlite-file /config/home-assistant_v2.db \
--mysql-database homeassistant \
--mysql-host core-mariadb \
--mysql-user homeassistant \
--mysql-password 'YOURPASSWORD'

(optional, include --chunk INTEGER to run the migration in chunks of records. This may help large datasets with lower resources.)

Arguments for script and more info → sqlite3-to-mysql · PyPI

FAQ

will it work with HAOS?
Yes. The installed tools only need to exist as long as the commands take, and the Terminal & SSH add-on works great.
I got an error from mysql (invalid column, duplicate key, etc) !
Backup mysql, purge all tables, and try again. MariaDB tables should not exist when we start migratration, will be fiully created from sqlite. ONly the database itself should exist.
```
apk add mysql-client
mysqldump -h core-mariadb -u homeassistant homeassistant -p > sqlbackup_DATE.sql
```
Can I use if database is on another host?
Yes, just change the ‘-h’ (hostname) argument to wherever it lives
Can I use with phpmysqladmin?
The python tool wants direct access to the MySQL database, just like phpmysql. You need access to the HA instance with ability to run python (hence Terminal addon)
What about Energy Dashboard (and statistics generally)?
Yep, all there. This migration should be everything in sqlite. (thanks @jusii, @malosaa)
My Energy costs are all messed up after migrating!
Giving Home Assistant a day to work through should correct this, (thanks @malosaa)
Are there size limits?
In theory any existing sqlite db can be migrated. only the resources of your machine limit that, but even little NUCs should get it done eventually.

And also…

Tremendous thanks to all the folks below who tested, questioned, and refined this method! We got a winner!

e-raser · November 13, 2022, 10:24pm

So on which setup did you / can one use this? Will it work on HAOS too (because installing stuff is - if at all - only temporary due to the dockerization which is reset on container recreation etc.)?