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

[e-raser]

Results? Working?

[le_top]

I did an initial trial yesterday, and I find the import in mariadb to be extremely slow . My DB was 484491264 bytes, the sql dump 368643027 bytes (less!), the compressed sql for mariadb 44741761 bytes (1/10th).

FYI, I found another record of a conversion: Migrating Home Assistant from sqlite3 to MySQL 8.0 .

I do not want to have a long down time, I think we need to use features offered by sqlite3 that we see at How To Use The SQLite Dump Command and split up the process in steps to limit down time.

1. Copying the DB to a workstation (i.e. a faster computer with easy access).
   • while the system is live is doable - I’ld prefer rsync but I do not have it on my HAOS.
2. Prepare the import on the workstation:
   • Create SQL script to create the SCHEMA (all the tables);
   • Export the most recent data needed to continue regular functionnality
     • Use “.mode insert” and appropriate selects to create the inserts.
     • Most recent data to be inserted first.
     • use “INSERT OR UPDATE” rather then “INSERT”
   • Export all the data (without the SCHEMA) to another file.
     • Most recent data to be inserted first.
     • use “INSERT OR UPDATE” rather then “INSERT”
     • optimize by grouping the inserts/using transactions.
     • IGNORE FOREIGN KEYS.
     • set autoincrement numbers to biggest value + margin .
       The margin allows HA to continue adding some data after the import.
3. Perform a first import without bringing HA offline.
   • Apply the schema;
   • INSERT/UPDATE the most recent data.
   • This way the target database will already have an image.
   • One could test the data base with a test HA instance on the workstation to see everything is fine.
     (In that case, it may be needed to restart the entire import after testing because HA will add data).
4. • Stop HA
   • Do step 2 again, but skip step 3 and continue here.
   • Import the small file with the most recent data;
   • Update the HA configuration
   • start HA;
   • Import the big file while HA is online.
     Most of the data will already be imported, so the user already has the previously imported history.
     There may be a gap which corresponds to the delay between the first import and the final import.
     The insert/update will ensure only the changes are applied.

Automate all of the above.
I started a script here: https://gist.github.com/38854d24863c1081154cf08d75e6535a . It does not include my proposed procedure above.

[zagi988 (Bruno Negovec)]

For me it seems to be working. Energy dash was ok, everything was ok only that history was slooow.
mySQL is running on a server that holds the database on HDD’s that are not that fast and it was either unable to process that much at the time or it was the hard drives being too slow.
So I switched back to sqlite until SSD for that server arrives.

[le_top]

If history is still slow, the slow queries could be analysed to add indexes to speed them up.

[sudo]

I have been trying to migrate using the steps at the top but because its based on an old database schema and in 2022.4 things are a bit different people will have issues.

I am writing notes as I fix the issues I find but there are a few extra gotchas FYI all.

[WeterPeter]

@sudo Any update?

[e-raser]

Meanwhile there have been further changes related to the database scheme. According to release notes of 2022.4 to 2022.6 e. g. attributes and events have been outsourced from states to separate tables. All that needs to be considered to get a stable, working MySQL database after conversion.

So yes, double the question here Migrating home assistant database from sqlite to mariadb - #88 by WeterPeter

[Mario1 (Mariusz Pietrusa)]

Hi,
I have found quite simple solution to do that if you have already running somewhere mysql database

1. Stop home assistant and take backup

2. Take file home-assistant_v2.db and convert SqlLite to Mysql using sqlite3-to-mysql (python sqlite3-to-mysql · PyPI) - it transfer whole database to mysql database
   

   

3. Export data from mysql server - I used MySql Workench to export data to file (set Include Create Schema)
   

   

4. I had to replace in all files utf8mb4_unicode_ci to utf8mb4_general_ci because I had in first some errors during import related with different database versions.

5. Start fresh mariaDb addon in hass with exposed port to outside world

6. Import data to MariaDb - I used MySql Workench to import data from file.

7. Enable mariadb url in recorder in configuration.yaml

recorder:
  db_url: !secret mariadb_url

In the end I did not have to think about foreign key and my long term statistics works fine. I have not noticed any problem however I have finished my migration today few minutes ago

[bbccdd (Fred)]

@Mario1

I have not noticed any problem however I have finished my migration today few minutes ago

Hi Mariusz, we are a few minutes further ahead in time Did you notice any problems that you care to mention for those interested in migrating as well?

[e-raser]

Also very interested in this because… SQLite database s**ks a lot meanwhile.

Recorder-DB (SQLite): Unrecoverable sqlite3 database corruption detected: (sqlite3.OperationalError) locking protocol

I had few times issues with the default SQLite database already (meaning it was automatically marked as corrupted and HA created a new empty one - which of course is VERY bad when it comes to long-term statistics, energy dashboard etc.). Every time either immediately or after a bit of investigation I knew, what was the cause for this. Today (2nd time this year) a database corruption happened again, but this time I have no idea what caused it (like this colleague did: OperationalError in sqlite…

[mathias (Mathias Regotz)]

I had similar problems with purge of the database led to a corrupted db. With your excellent guide I was able to save all the long term data and move to maria db. Thank you very much!

[gerardsyd (Gerard)]

Hi - thanks for this. I tried to run sqlite3mysql and it threw an error saying
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘-assistant_v2’ at line 1
Any ideas on this? Wondering why I’m getting the error but assume you did not?
Thanks!

[ihr (Ignacio Hernandez-Ros)]

Hi, Yesterday I give this a try. I executed all steps indicated on message from @jr3us last Jan 11th but didn’t success because of this error:

(ERROR) components/recorder/run_history.py
Error executing query: (MySQLdb.IntegrityError) (1364, "Field 'run_id' doesn't have a default value") [SQL: INSERT INTO recorder_runs (start, end, closed_incorrect, created) VALUES (%s, %s, %s, %s)] [parameters: (datetime.datetime(2022, 8, 18, 19, 21, 18, 194665, tzinfo=datetime.timezone.utc), None, 0, datetime.datetime(2022, 8, 18, 19, 21, 18, 415689, tzinfo=datetime.timezone.utc))] (Background on this error at: https://sqlalche.me/e/14/gkpj)
21:21:18 – (ERROR) Recorder

I’ll take a look at this later today, I’m sending this notice in case anyone already knows what is happening and can help me.

Cheers

[ihr (Ignacio Hernandez-Ros)]

Well, I know what happened. I just Copy and Paste the SQL sentences to update the MySQL database just after loading new data and did not carefully read the documentation (replace N+1 with the maxid+1). So I’ll do it another try later tonight.

[ihr (Ignacio Hernandez-Ros)]

I succesfully migrated by database today but I’d to do additional steps not documented. More or less:

SELECT MAX(attributes_id) FROM state_attributes;
ALTER TABLE state_attributes MODIFY COLUMN attributes_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;

SELECT MAX(data_id) FROM event_data;
ALTER TABLE event_data MODIFY COLUMN data_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;

But the most problematic step in my case was that both statements require to remove a foreign key:

ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_3`;
ALTER TABLE `events` DROP FOREIGN KEY `events_ibfk_1`;

And had to create them again with

ALTER TABLE`states` ADD CONSTRAINT `states_ibfk_3` FOREIGN KEY (`old_state_id`)REFERENCES `states`(`state_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE events ADD CONSTRAINT events_ibfk_1 FOREIGN KEY (`data_id`) REFERENCES event_data (data_id);

The last foreing key “events_ibfk_1”, I created looking at the generated sql import because I’d no a copy of the definition.
Everything works fine now after a restart. No warnings and no errors and everything looks good.
I’ll appreciate some feedback from a database expert regarding the last alter table statement…

[jr3us]

If you would please take the instructions that you used and modify them with the steps you had to perform in addition and repost so all can benefit.

Regards!

[ihr (Ignacio Hernandez-Ros)]

@jr3us

update states set attributes = REPLACE(attributes, '', ‘"’);`

As requested by @jr3us, here is an updated script up till today. It might become obsolete as new versions are created and might not work for you! I offer absolute NO guarantee for any data lost!. You are warned.

# The first few steps here are typical install and configuration of mariadb.

sudo apt install mariadb-server mariadb-client

sudo apt install libmariadb-dev sqlite3

sudo mysql_secure_installation

I did the following 2 steps using phpmyadmin

Created a user hass with all permissions

Created a database hass

Stop the hass server process on your server.

sqlite3 home-assistant_v2.db .dump > hadump.sql

git clone https://github.com/athlite/sqlite3-to-mysql

copied sqlite3-to-mysql to same directory as hadump.sql

bash sqlite3-to-mysql hadump.sql > haimport.sql

mysql -u hass -p -h localhost hass < haimport.sql

## the above mysql statement imported about 55K records in both states and events, 
## 360 into recorder_runs, as well as 6 records into schema_changes for my 
## home-assistant with a 2 day history. Your mileage will vary.

## “Fix” the data and update tables:

mysql hass

update events set event_data = REPLACE(event_data, '`', '"'); 
update states set attributes = REPLACE(attributes, '`', '"');
update state_attributes set shared_attrs = REPLACE(shared_attrs, '`', '"');
update event_data set shared_data = REPLACE(shared_data, '`', '"');

# Remove Foreign Key Constraints

ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_1`;
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_2`;
ALTER TABLE `states` DROP FOREIGN KEY `states_ibfk_3`;
ALTER TABLE `statistics_short_term` DROP FOREIGN KEY `statistics_short_term_ibfk_1`;
ALTER TABLE `statistics` DROP FOREIGN KEY `statistics_ibfk_1`;
ALTER TABLE `events` DROP FOREIGN KEY `events_ibfk_1`;


# THIS IS NOT A SCRIPT TO COPY AND PASTE, PLEASE, READ THE FOLLOWING TWO LINES AND EXECUTE EACH STATEMENT REPLACING THE 'N+1' WITH THE VALUE FROM PREVIOUS SELECT STATEMENT
# alter the primary key in the tables to use auto_increment, starting from the last run id +1.
# MANUALLY run the following 'SELECT' statements and replace 'N+1' with the output of the select statment adding 1 (e.g., 1+1=2).

select max(run_id) from recorder_runs;
alter table recorder_runs modify column run_id INT NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1; 

select max(event_id) from events;
alter table events modify column event_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1; 

select max(state_id) from states;
alter table states modify column state_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;

SELECT MAX(change_id) FROM schema_changes;
ALTER TABLE schema_changes MODIFY COLUMN change_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;

SELECT MAX(id) FROM statistics;
ALTER TABLE statistics MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;

SELECT MAX(id) FROM statistics_meta;
ALTER TABLE statistics_meta MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;

SELECT MAX(run_id) FROM statistics_runs;
ALTER TABLE statistics_runs MODIFY COLUMN run_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;

SELECT MAX(id) FROM statistics_short_term;
ALTER TABLE statistics_short_term MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;

SELECT MAX(attributes_id) FROM state_attributes;
ALTER TABLE state_attributes MODIFY COLUMN attributes_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;

SELECT MAX(data_id) FROM event_data;
ALTER TABLE event_data MODIFY COLUMN data_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=N+1;

# Add back the foreign key constraints

ALTER TABLE `states` ADD CONSTRAINT `states_ibfk_1` FOREIGN KEY (`old_state_id`)REFERENCES `states`(`state_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `states` ADD CONSTRAINT `states_ibfk_2` FOREIGN KEY (`event_id`)REFERENCES `events`(`event_id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `states` ADD CONSTRAINT `states_ibfk_3` FOREIGN KEY (`attributes_id`)REFERENCES `state_attributes`(`attributes_id`);

ALTER TABLE `statistics` ADD CONSTRAINT `statistics_ibfk_1` FOREIGN KEY (`metadata_id`)REFERENCES `statistics_meta`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `statistics_short_term` ADD CONSTRAINT `statistics_short_term_ibfk_1` FOREIGN KEY (`metadata_id`) REFERENCES `statistics_meta`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `events` ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`data_id`) REFERENCES `event_data` (`data_id`);

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;
ALTER TABLE statistics_short_term MODIFY `start` datetime(6), MODIFY `created` datetime(6), MODIFY `last_reset` datetime(6), MODIFY `mean` double, MODIFY `min` double, MODIFY `max` double, MODIFY `state` double, MODIFY `sum` double;
ALTER TABLE statistics MODIFY `start` datetime(6), MODIFY `created` datetime(6), MODIFY `last_reset` datetime(6), MODIFY `mean` double, MODIFY `min` double, MODIFY `max` double, MODIFY `state` double, MODIFY `sum` double;

Important note added Oct-5th 2022. I found out today, the database model created by the script sqlite3-to-mysql is not creating the same model created by HA when starting a new database. I’ve added 4 SQL sentences to the end of the script to modify the datatypes for 4 tables that caused some troubles later so I hope there will be no more issues like this to others as well.

[jr3us]

I have updated the initial post to include a link to @ihr s comment with his updated information.

Regards!

[bbccdd (Fred)]

Hi Ignacio,

Worked just fine, thank you!

Can you please add a note to clarify something?

@ihr

# alter the primary key in the tables to use auto_increment, starting from the last run id +1 (replace N+1 with the maxid+1

Could be replaced by something along these lines to prevent people from simply copying and pasting since manual action (adding 1 to a number) is needed for each element.

# alter the primary key in the tables to use auto_increment, starting from the last run id +1.
# MANUALLY run the following 'SELECT' statements and replace 'N+1' with the output of the select statment adding 1 (e.g., 1+1=2).

Aditionally, if you bundle the SELECT statements so they can be copied and pasted at once, those wanting to migrate can calculate the right values and add them to the ALTER statements in a plain text editor (e.g., vim, notepad) before executing them.

I’ll post my learnings from migrating sqlite to mariadb on Home Assistant OS hereafter for those interested.

[bbccdd (Fred)]

Hi all,

I’ve also just migrated from sqlite3 to mariadb based on the latest instructions, but on Home Assistant Operating System (HAOS) at the latest versions[1].

Pointers for those wanting to do the same:

Place your family in maintenance mode for a day (took me about half a day of downtime, of which importing the database itself took around 1 hour on a fast PC).

1. Make & verify backups!

Loss of history between backup and working state after migration.

2. Gain privileged access to the host on port 22222 (hint: pull a pre-configured SSH authorized_keys file from the home assistant host in console mode).
3. Get the recorder working with mariadb.
4. Perform the sqlite3-to-mysql actions on a separate debian or ubuntu system since I see no way to get sqlite3 on the mariadb container nor on the HAOS host.
5. Copy the sqlite3 database into the host (I used a linux system with scp to pull the database from)
6. Use docker to copy the database file into the addon_core_mariadb container

Downtime from here on.

7. Stop all plugins but the SSH one (to free resources for the importing) and most imporantly, stop the core (log in using console or ssh into port 22 and execute ha core stop

8. Import (e.g., use docker exec -it addon_core_mariadb bash
   to gain access to the mariadb container (this took over an hour on a proxmox VM allocated 4 cores of a AMD Ryzen 7 5800X CPU, so take this into account please. You can check if it is still running based on higher than normal CPU load using a parallel SSH session running docker stats or docker stats addon_core_mariadb. This is for 2 years of active HASS use with 800k records in tables ‘events’ and ‘states’ combined.

9. Execute script as updated by Ignacio less than a week ago from this post.

WARNING: manually run the SELECT MAX statement for each table, add 1 to each number and replace ‘N+1’ with it before running the ALTER TABLE commands.

10. Reboot and check for anomalies in logfiles, and, confirm that the history goes back to before the migration!

End of downtime, all good.

NB I do not have time to write a full guide, but I think that if you are interested in similar outcome you have roughly two options:

1. Figure it out with the work and information from this post (which is perfectely doable with the information in this post if you have time and if you are interested in puzzling/learning).
2. Wait. At some point, hopefully, there will be a migration path in the GUI that allows for these steps to take place in an automated fashion. I mean, it is a reliable process just someone needs to automate it in a future proof way.

Also, I’ll post back in a few weeks also if all remains OK for those more conservative amongst us.

[1] versions used:
Home Assistant 2022.8.6
Supervisor 2022.08.3
Operating System 8.5
Frontend 20220802.0 - latest