Merge old database with new (statistics)

Development
23

Hi,
open the page set up old DB and new DB click RUN but nothing happend.

image
image1151Ɨ871 18.3 KB

Can you help me?

24

Thanks for this tool. Has anyone recently tried the provided scripts?

My sqlite Home Assistant database is in a bad state and long-term statistics are not automatically calculated anymore. I would hate to loose a year of past data.

25

I havenā€™t used this tool for quite some time now since Iā€™ve made it as I didnā€™t need it anymore. @arough mentioned it worked for him a month ago so I assume it should still work as intended.
Hoever, Home Assistant did change a lot overtime so it could be that the script needs an update. Anyone is free to make a pull request for an update!

26

Thanks for your reply @jory! I managed to fix my problem in the meantime with a little bit beginner SQL fu. It was probably caused by a migration that did not finish properly. I will save this thread though and come back to it, should the need arise.

27

I tried the tool again (my db keeps getting corrupt :frowning_face:) and have the same problem as the others.
Clicking on run seemingly does nothing, but creates copies of the newer database and appends _new-${timestamp}.

image
image1508Ɨ456 101 KB

I get no output in the browser console or in the docker container.

I went back to the version of the PHP container I used when adding docker support to exclude docker as the error. Nothing changed.

Sadly Iā€™m no jQuery guru and canā€™t figure out why nothing happens when clicking ā€œRunā€.

1 Like
28

Just to give an update on the topic:
Iā€™m blind and do get an error in the network tab.
SQLSTATE[HY000]: General error: 11 database disk image is malformed.
I managed to fix it manually by dumping and reapplying the database.

Iā€™m now adding a check and fix for this particular error to the tool.
At least Iā€™m trying to :sweat_smile:

29

Hmm, when I try to merge I got this error for ā€œinit_statsā€:

SQL Exec: SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: statistics.metadata_id, statistics.start_ts

Any idea to fix this?
When I understood it correct, the error says, that in the columns metadata_id and start_ts are no unique data. But in my opinion the only unique data column should be id.

At the moment I have daily a corrupt database and do not want to lost my energy dashboard statistic. All other data does not matter.