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!
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
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.
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…
1 Like
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!
1 Like
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.
5 Likes
I have updated the initial post to include a link to @ihr s comment with his updated information.
Regards!
Hi Ignacio,
Worked just fine, thank you!
Can you please add a note to clarify something?
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.
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).
- Make & verify backups!
Loss of history between backup and working state after migration.
-
Gain privileged access to the host on port 22222 (hint: pull a pre-configured SSH
authorized_keysfile from the home assistant host in console mode). - Get the recorder working with mariadb.
- 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.
- Copy the sqlite3 database into the host (I used a linux system with scp to pull the database from)
- Use docker to copy the database file into the addon_core_mariadb container
Downtime from here on.
-
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 -
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 runningdocker statsordocker stats addon_core_mariadb. This is for 2 years of active HASS use with 800k records in tables ‘events’ and ‘states’ combined. -
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.
- 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:
- 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).
- 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
2 Likes
Thank you for your post. I updated my post following your suggestions.
Just one important note for every one else. I’ve HA on a RPI and I’ve put the database on a separate Linux Server. Why? because running a database like MariaDB on a SD card in a small RPI does not seems to be a good idea. For all the other tasks it is OK, except for influxDB that is another database I’ll migrate from the RPI to the Linux box.
Cheers
Ignacio
1 Like
Why? Does MariaDB or the addon perform more write cycles to the SD card than SQLite?
I doubt it. Using a SSD is in general a good idea. On the other hand: I run HA including InfluxDB on a Pi with a (Extreme) sd card for quite a while.
Well, that’s not exactly my point. I wanted to migrate the database out of the RPI for performance reasons. And due to the reason I will run the storage service in a separate server I’d no reason to keep using sqlite.
Okay that’s more an advanced or even semi-pro style of driving your data warehouse. Most of us are happy to run it on the same machine as HA. Less complexity is always a good thing.
/side discussion over 
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.
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?
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
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…
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?
1 Like
@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
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)
1 Like