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

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

eddiewebb · November 13, 2022, 11:16pm

Yes, that is my setup. ONly needs to be temporary to run the ~45 second migration.

e-raser · November 14, 2022, 8:28am

Nice. That seems to be the most hassle-free Migration how-to seen so far

Most important question: what about your energy dashboard…

malosaa · November 14, 2022, 1:30pm

eddiewebb:

sqlite3mysql \
--sqlite-file /config/home-assistant_v2.db \
--mysql-database homeassistant \
--mysql-host core-mariadb \
--mysql-user homeassistant \
--mysql-password 'YOURPASSWORD' \
--ignore-duplicate-keys

i did try this on my test system getting an error

// update, deleting the database and adding again fixed it

malosaa · November 14, 2022, 1:37pm

So is it possible to use the phpmyadmin in homeassistant with an installed version of mariaDB on an other server ?

and how do i set it up

regards

eddiewebb · November 14, 2022, 1:38pm

Yes, as long as HA can connect, just change the -h (host) argument to point to wwhereever that machine is.

malosaa · November 14, 2022, 1:42pm

can i do this within the gui of phpmyyadmin ?

not possible:

Known issues and limitations

This add-on needs the core MariaDB add-on version 2.0 or higher.

This add-on is created to allow management of the official Home assistant MariaDB add-on. It cannot connect to other MySQL or MariaDB servers.

eddiewebb · November 14, 2022, 1:55pm

I dont understand why you are concerned with the Phpmysql GUI though, the python script eliminates any need to interact with MySQL/MariaDB, it does all the commands directly to the database, whereever it may live.

eddiewebb · November 14, 2022, 2:00pm

Edit: YEP , Sure does!

Can’t say, I disabled my Sense monitor integration months ago cause it was so damn noisy i was sure it would kill my SD.

HOWEVER

I monitor a greenhouse with stats and those are showing up going way back.

Would love for soemone with active energy stats to confirm though!

EDIT (I went further back an indeed its there as others have now confirmed)

malosaa · November 14, 2022, 3:49pm

I have active energy stats and they reporting good in the energy tab.
just tested it on my test system

Jusii · November 14, 2022, 3:50pm

Just did a quick test with this method and it actually looks pretty promising, even with the energy dashboard data! I have kinda spare HA-installation which only collects total energy stats and tested with that. All energy dashboard history looks to be there!

malosaa · November 14, 2022, 3:52pm

same here, it’s all there.

eddiewebb · November 14, 2022, 10:05pm

We did it!

Thanks all for the rapid feedback and testing, I updated my post with some FAQs from the knowledge gained.

Anyway we can sticky or pin a specific post?

e-raser · November 14, 2022, 10:25pm

Great. Thank you

Not sure bout that, I think topics can be marked as sticky by the mods. But you could ask @jr3us to link your how-to in the original/first post so people don’t need to waste much time reading 130 posts to find your guide

We now have I think two to three really good approaches here, while I personally think your how-to is the „best“ in terms of easiest one (and when energy dashboard / LTS is working that’s also a big pro versus few other guides which seem to struggle with that).

jr3us · November 14, 2022, 11:33pm

If someone can give me a block from the first step to the last that I can just paste into my original post as a text block similar to what I did originally, I will add it as the current favored method for migrating with credit to the user. I will leave the original for historic purposes.

I do see mentioned that a new instance of home assistant is in the steps involved. I don’t think this will necessarily work for an already established HA instance with lots of data.

Regards.

eddiewebb · November 15, 2022, 12:05am

my steps to migrate sqlite to mariadb posted above works for established instances. mine has been running for 18 months, the process has no size limit. It will work for an HA instance with N data, where N is any amount possible by sqlite and resources available on device.