New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Recorder - Unhandled database error while processing task (multiple types and occurrences after upgrade to 2023.4) #90974
Comments
Do you have the older MariaDB logs from when the migration happened? |
A backup was completed 8 hours ago, so maybe I can extract it from there? @bdraco I have a backup downloaded from 10AM today, but I'm not sure how to extract logs from the backup of if that's even possible. Any pointers on how to extract and read those without doing a restore? |
Also, it wasn't clear if your database is still dropping connections or if everything is working now. Dropped connection means that query too way too long, or there is actual database corruption from a disk/hardware/MariaDB bug which should show up in the log. |
Also the database size won't shrink after the migration until the 2nd Sunday of the month or when you manually reclaim space by calling the recorder.purge service to repack |
It looks like the connection is working now as each sensor history shows a gap from 8PM yesterday to 4:30PM today, and it appears to be writing new information. So I believe it's "working" now.... It seems like things just stopped at 8PM last night, but the reboot effectively kick started everything and then resulted in those logs I shared above. There haven't been more errors in the logs reported since the ones I shared above. Would there be logs in my 10AM Backup? If so, how do I extract it? |
Its a tarball so you can open it in most compression software. I expect you hit the memory circuit breaker that prevents the recorder from using all available memory during migration. The current design is a fixed limit but we are going to redesign it to increase the limit here #90894 |
Yeah, I've tried multiple apps to open it up, but each of the tarball files within give me an error indicating it's not an archive. And I'm not sure where the logs would be exactly....that's why I was asking if it was even possible to do this. Or would this memory circuit breaker item mean that the auto backup is not good and that's the error I'm getting? |
There should be another tarball inside the backup with
No its not related |
Yeah, I just keep getting errors on each of the tarballs inside like core_maridadb and homeassistant that it cannot be opened because it is not an archive. This is why I'm wondering if the system was hung up on the backup due to this schema migration freeze or whatever happened and as a result, this backup I have from 10AM is no good. |
Strike that - I just pulled a backup from yesterday and I'm getting the same thing. So this means I should have the logs that might be helpful to confirm your suspicion, but every app I try to use to open it tells me it can't and it's not an archive. I'll keep searching, but any pointers on how to get these logs would be helpful. |
Which operating system are you using to try to open it? |
Windows. I've tried several apps and all of them (including 7-Zip) tell me the individual tarballs within are not archives and cannot be opened. |
7zip should do it for sure. Can you post screenshots? |
I'm getting this same error no matter which file I try from either backup. It's the same if I first extract the large backup into a folder and try on the individual file OR if I just open the backup in Z-Zip and keep navigating down to the mariadb file. UPDATE - ahhh, I have a password on the backup...I bet you that's why I'm getting that error.
Would you recommend calling record.purge manually? I just don't want to mess this up any more than I have with the data I have missing from today... |
Yup thats it. You can't open it with traditional tools.
You can call it with something like this to ensure it won't remove any data unless its a year old: |
I'm trying some python script to decrypt it, but have not been successful so far.... |
Thanks, unfortunately I have no clue how to use that. I'm trying this script right now, but I now am running into this error...
This issue has convinced me to disable password protecting my HA backups....this is truly an unnecessary pain just to get the log files... |
I’ve hit the same issue with the database migration and only noticed it 24 hours later. If the migration stops because of memory issues maybe the user should be somehow informed about it. |
We don't know if that's the reason. If you have logs please post them |
This is the only entry I see in the log from yesterday, when I upgraded: 2023-04-07 15:02:14 28 [Warning] Aborted connection 28 to db: 'hassio' user: 'hassio' host: '192.168.10.32' (Got an error reading communication packets)
2023-04-08 18:15:49 0 [Note] mariadbd (initiated by: unknown): Normal shutdown |
Home Assistant logged the following:
|
Is mariadb still running in the container? |
It was. I have since restarted both containers (HA core + mariadb). |
Was there any indication about what caused mariadb to shutdown?
|
That was me. I restarted both containers after I had figured out that the
recorder was not working ok.
…On Sat, 8 Apr 2023 at 23:37, J. Nick Koston ***@***.***> wrote:
Was there any indication about what caused mariadb to shutdown?
2023-04-08 18:15:49 0 [Note] mariadbd (initiated by: unknown): Normal
shutdown seems to indicate something triggered it to shutdown, and since
there isn't code in Home Assistant core to shutdown the MariaDB server it
would be interesting to know where its coming from.
—
Reply to this email directly, view it on GitHub
<#90974 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AABJXH6Y7YW6A2SE4CZZDVTXAHLDLANCNFSM6AAAAAAWV4NCZM>
.
You are receiving this because you commented.Message ID:
***@***.***>
|
Per my post here in the HA Community forums on the release notes, since I haven't figured out how to extract my mariadb logs from an encrypted backup, I starting to believe my issue was caused by not having enough free space on my SSD. I believe I had only 20gb of free space at the time of migration, which sounds like it was not enough to perform the migration successfully. I didn't watch the video until today where this was mentioned and did not see anything about this requirement in the release notes before upgrading (or rebooting after I noticed the lack of data after 24 hours). If my mariadb logs would still be useful in determining root cause, I will gladly share those if I can some specific guidance on how to extract it from the encrypted backup without restoring my HA instance. |
I'm not sure if this was related, but at 10:23AM ET, my HA instance became unresponsive and automatically restarted. During the restart, it was waiting for Recorder to start and nothing else would load....after about 5 minutes of still waiting and no progress or information updates in the logs, I finally manually pulled the plug on my NUC to restart the device. Upon the next reboot, everything loaded, however the first item in the logs was this:
and MariaDB logs show this:
The only things I've changed since upgrading to 2023.4.2 is my purge time from 90 to 60 days, and then forced a repack to see what impact this had on my DB size (18GB to 15GB). My backups are still growing and are now 7.4GB and I've got 102GB of free space on my drive (I incorrectly said I had a 128GB SSD, but it's really a 240GB). The net for now is everything appears to be working except for the statics appear to have stopped around 10:10AM and then resumed around 10:30AM, so I lost 20 minutes of data readings during this time for some reason....similar to what happened during the 24 hours + of the database scheme update... |
And it just restarted again at 12:15 for no clear reason... Here's the first item in the logs now:
Here are the MariaDB logs:
@bdraco could this all be related to the update? I really don't want to lose all my data, but I'm wondering if I need to expedite my transition back to the native database as it appears I'm continuing to have issues following this update release. |
2023-04-12 12:15:42 5 [Warning] Aborted connection 5 to db: 'homeassistant' user: 'hass' host: '172.30.32.1' (Got an error reading communication packets) That looks like the communication between the mariadb server and core is somehow being interrupted. There isn't enough information to tell whats going on here but there haven't been any changes on how we talk to the database so I expect there is more going on with your system and it's probably the same reason the update had trouble as well. |
Ok - anything in particular I should do or enable to help gather more information? e.g. like a hidden debug mode or something? My MariaDB instance has been solid since 2021 and has not required me to do anything to it since 2023.4.0 last week, so I'm really at a loss to where to begin to figure this out. Ith's the official add-on installed on the same device. I have no reason to believe there's a hardware issue here. I've resolved the potential free space issue by reducing the number of local backups it keeps. If this continues, I might try to do a DB migration this weekend following this post given my limited SQL knowledge. |
You would likely need to watch the raw packets between the containers to see what's going on. That would be extremely difficult to debug and find the issue if you don't have previous experience doing it. Migrating seems like a good plan as it would avoid the communication issue entirely |
Right - I have no previous experience with that... As a sanity check, is there anything in the this post that looks odd to you for the migration? I'll try to carve out some time to do this soon. |
If you just want to keep statistics, koying's post looks like a pretty good solution. I haven't tested it myself but I don't see anything that seems like it would be an obvious issue https://community.home-assistant.io/t/migrating-from-mysql-mariadb-back-to-sqlite/545837/10?u=bdraco |
Thank you. Two more questions:
|
There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates. |
The problem
Almost 24 hours after upgrading to 2023.4, I noticed some odd behavior with my logs not loading for devices. The notification for the database schema change had already gone away, so I thought that meant it was done.
After rebooting my system, I now see several items in the logs with multiple occurrences related to recorder. I'm using MariaDB (version 2.5.2). I recognize this may be more complicated due to the add-on, but this appears to be an issue following the 2023.4 upgrade and schema change.
If I go to Logbook - I noticed before rebooting I could only see items until 8PM yesterday and nothing from today.
After rebooting, I'm now only able to see items from around that time of the reboot to now, but nbetween 8PM yesterday and 4:30PM today..... And my energy dashboard put everything at 4PM today for the reboot....it looks like it was frozen for hours and just now is starting to write data. Is all of that data gone?!??
Also, I noticed that my backup last night increased by 1GB (From 7GB to 8GB) from the previous day.
This is on an intel NUC i5 with 8GB of ram. I'm not finding any other performance changes like high CPU or RAM usage.
What version of Home Assistant Core has the issue?
2023.4
What was the last working version of Home Assistant Core?
2023.3.6
What type of installation are you running?
Home Assistant OS
Integration causing the issue
Recorder
Link to integration documentation on our website
https://www.home-assistant.io/integrations/recorder/
Diagnostics information
No response
Example YAML snippet
No response
Anything in the logs that might be useful for us?
Additional information
Here are my logs from MariaDB (version 2.5.2) following the last reboot and also restart of the add-on.
The text was updated successfully, but these errors were encountered: