Two days ago i did an OS upgrade from version 7.something (I think it was 7.6) to 8.5. Everything seemed to be working fine. But the day after I realized that all my sensors history had stopped working. I have about 8 sensors for temperature and humidity and they all work for the current actual value. But if i check the history, I have that data up until the upgrade. After that the graph is just a flat line (the same value).
I also keep getting a message that performance may be degraded becuase there is a database upgrade running. I can also hear that the harddrive on my server is working more then usual. But thje upgrade has now been running for almost 50 hours. Is there a way to see the status or progress of this upgrade? Will the histor in the graphs start working again after the upgrade is complete?
The DB i have is standard SQLite and the file was about 2,5GB in size. Its running on a VM on a physical server with “ok” hardware. But not like a Raspberry Pi och similar.
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:209
Integration: Recorder (documentation, issues)
First occurred: 12:54:08 (1 occurrences)
Last logged: 12:54:08
The system could not validate that the sqlite3 database at //config/home-assistant_v2.db was shutdown cleanly
Logger: homeassistant.components.recorder.migration
Source: components/recorder/migration.py:96
Integration: Recorder (documentation, issues)
First occurred: 12:54:26 (1 occurrences)
Last logged: 12:54:26
Database is about to upgrade. Schema version: 23
Logger: homeassistant.components.recorder.migration
Source: components/recorder/migration.py:105
Integration: Recorder (documentation, issues)
First occurred: 12:54:26 (3 occurrences)
Last logged: 12:54:26
Failed to drop index ix_statistics_statistic_id_start from table statistics. Schema Migration will continue; this is not a critical operation
Failed to drop index ix_statistics_short_term_statistic_id_start from table statistics_short_term. Schema Migration will continue; this is not a critical operation
Adding index `ix_statistics_statistic_id_start` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:95
Integration: Recorder (documentation, issues)
First occurred: 12:55:18 (1 occurrences)
Last logged: 12:55:18
Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics.metadata_id, statistics.start [SQL: CREATE UNIQUE INDEX ix_statistics_statistic_id_start ON statistics (metadata_id, start)] (Background on this error at: https://sqlalche.me/e/14/gkpj)
I would like to keep it of course. There is over a year worth of sensor data in that database.
Still running btw. Getting the above errors about every other minute. Looks like its in an endless loop trying to upgarde the databse. What to do?
The way to keep long term data is to have a reliable database for storing such data. sqlite was designed to … work during the duration of a missile launch and as such has as a design goal that it works at least 90 seconds or so.
Transfer the data somewhere else (as for how, plenty of people have documented this, since it’s a FAQ).
Well it seems that I “should have known better” then to try to upgrade to a new version then…
So how do i solve this? Im fine with keeping the data as is and start fresh in a new database.
Is there a way to stop the database migration? Or at least stop the loop that is currently running?
Just restart the VM?
Ok let me rephrase. Im fine with starting from scratch, if i can keep the old database and get data from if i need. Maybe i could just make a copy of the DB-file? And the delete it?
If i delete it, do I need tro setup all my cards, entitys and integrations from scratch?
Sure you can do that. Not sure how you are going to access the data again. The Home Assistant table schema is optimised at best, cryptic at worst (I know little about SQL so it may be quite normal).
No you do not. All you lose is the past history data. All your history graphs, long term statistics and energy dashboard will be blank until new data comes in.
Alright. I actually found a backup of the DB so i should be able to read some histor data from that.
So if o would like to recreate the database and maybe use mariadb or something similar. How would i do that?
So, google database migration sqlite to mariadb and pray something comes up.
In the best case it just works, it the worst case, you need to document all the tables of everything you are using and then write a program for the conversion.
Database migrations can be a pain, which is why people try to do everything to avoid them.