SQLAlchemyError error processing task CommitTask #132573
Comments
|
Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration ( Code owner commandsCode owners of
(message by CodeOwnersMention) recorder documentation |
|
It looks like your SD card might be failing. |
|
I have the same problem. I have a 256GB SSD. The problem occurs at least once a day. Then I can't restart in menu and I have to disconnect the power supply. |
bdraco
added
problem in device
|
Same problem here, RPI5 with nvMe SSD 240GB. It occurs at least once a day, cant reset it via Developer Tools, it finds no config.file , no history data,no addons etc! The only thing that helps is power off and on again! |
|
From the Logs: Logger: homeassistant.components.recorder.core SQLAlchemyError error processing task CommitTask() |
|
Next error log: Logger: homeassistant.components.recorder.core Unrecoverable sqlite3 database corruption detected: (sqlite3.DatabaseError) database disk image is malformed [SQL: SELECT states_meta.metadata_id, states_meta.entity_id FROM states_meta WHERE states_meta.entity_id IN (?)] [parameters: ('sensor.voltage_phase_1',)] (Background on this error at: https://sqlalche.me/e/20/4xp6) The above exception was the direct cause of the following exception: Traceback (most recent call last): |
|
Hi, problem revealed. The error is in the NVMe memory card. I restored the HA on the microSD card and now I've been running for more than 24 hours without a recorder error. I bought a new 512GB ADATA NVMe card, today I'm going to try to run HA from a new memory card. I'll give you information here after the weekend. |
|
Ok thnks keep posted pls, its getting worse here, every few hrs i have to power it off and on again! HW configuration: |
|
Please try to migrate the DB system to MariaDB. There can be a problem here too, so you need to rule out a bug on the SW side (easy DB system SQLite). MariaDB is recommended by many HA users. |
|
I use an aftermarket power supply,this can be also the issue, This weekend i will test it with the official power supply of 27Watts |
|
I use original Raspberry power supply 27W and I have this problem. RPi5 would give information if there was a problem with the power supply. |
|
an update for those are interested in this problem:
24hrs later no issues, the RPI5 works very fast, low in cpu resources and about 25% of its RAM (4GB) if this is an nvMe hardware issue is still not clear (this was an brand nieuw drive) or maybe something else |
|
@sadyxa |
|
Hi, I have an update and final information. The bug is in the NVMe drive from the manufacturer PATRIOT. I bought an ADATA 512GB, did a system restore from backup and everything is OK. |
|
I tried system restore on the microSD card as well, and there was no error for 12 hours. Then I migrated to a new ADATA NVMe drive. |
|
thnks for the updates |
|
nearly 1 week later runing latest HAOS on the RPI5 with the SD card and everything works fine |
|
I got the same thing. |
|
Hello everyone! My HA is on a Raspberry Pi 4b, which is perfectly suited for my automations. It uses an aluminum heatsink and never exceeds 46°C in temperature, as I created an automation that keeps it below that (it uses an external fan). I use a generic M.2 SSD (not NVMe) with an aluminum heatsink on the USB 3.0 port, as the recommendation would be not to use an SD card. The SSD is on an adapter that is also generic (I removed one side of the case to install the heatsink). The power supply is from a Raspberry Pi 5, which means it has plenty of power. Despite all the care I take, I suffer from the same problem as you. At least once a week, an SQLAlchemy error occurs, which causes a series of cascading errors and I am forced to restart the Raspberry Pi. The hardware analysis does not point to a single problem, but from what I saw in the reports above, did you only stop having problems when you installed an SD card? Is that it? Could the problem be with the SSD adapter? Has anyone had a problem with an SSD changed the manufacturer and solved it? Below are the accessories for my hardware to make the analysis easier. M.2 SSD Adapter: Power Supply power: |
|
Yes indeed, going back to SD card ( from the nvme ssd) has been the solution for me. Our hardware setup is a bit different but so as you say, maybe is the SSD adaptor the issue and not the drive self. When i get another nvme drive i will thorougly test it. |
|
Same problem for me (It happens about once a day). I have the following setup :
Has anyone found a solution? :( |
Switching back to SD card from m.2 ssd has worked for me, up and running allready 10days with no issues at all! |
Thanks for the info, but I wish I'd used my SSD for reliability :( |
|
I was only helped by replacing the NVMe drive. |
Not able to quickly change to USB 2.0 so I decided to reverted to HAOS 13.2 - system is rock solid since then. |
|
I've switched today my nmve with HAT to an external usb3 adapter ..fresh install, restore back up and we will see. |
|
Again, there was a problem and the system crashed once a day. I found out that it occurs at the time of backup. I am now testing the backup settings. I have dual backup -> HA native backup and OneDrive addon. |
|
I have also seen the same error after upgrading to the latest 14.1 OS. And after reading this thread, I booted from the old version 12.4 and everything is working fine again. I went to terminal and send |
|
No SQL crash for me since i moved my nmve on usb3 |
|
No isuue for me since i went back to SD card from NvNe, allready 6weeks ago. system is RPi5 with all latest updates . |
In my configuration, even downgrade to HAOS 12.4 did not help. This weekend I successfully migrated the entire Home Assistant system to PROXMOX. |
|
it doesnt make sense anymore, is this a software or hardware issue? sadyxa migrates to proxmox after testing all possible combinations RPI5, while issue persists, with different NvMes and different HAOS versions! |
It doesn't make sense, but unfortunately it happens. |
|
Ok thnls, i wonder . Do Nabu Casa know about this issue?
Op vr 24 jan 2025 18:27 schreef [W.Fox] ***@***.***>:
… it doesnt make sense anymore, is this a software or hardware issue?
sadyxa migrates to proxmox after testing all possible combinations RPI5,
while issue persists, with different NvMes and different HAOS versions!
It doesn't make sense, but unfortunately it happens.
From history, it seems that those who use M.2 NVMe SSDs suffer more.
In my case, my M.2 SSD is a regular one (not NVMe). So, instead of having
daily crashes, they occur more randomly, taking a maximum of a week to
occur.
It seems to be a software problem, since some users migrated to the SSD
card and the problem stopped occurring.
Some users downgraded the version and had more stability, although it did
not solve the problem 100%.
—
Reply to this email directly, view it on GitHub
<#132573 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BEGET73UCIYHUSAQBGINFJ32MJZX5AVCNFSM6AAAAABTGOP6TOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMMJTGA2DGMZTGU>
.
You are receiving this because you commented.Message ID:
***@***.***>
|
|
in other words, RPI5 with HA + nvme HAT + NvMe SSD, DONT DO IT |
Personally, I don't see any advantage in using NVMe. But that doesn't justify this problem that people are having. It's an error that needs to be analyzed and corrected, after all, NVMe SSDs are quite common now. |
|
I have absolutely the same issue with RP4. I am using a portable SSD. Rolling back to the previous OS version didn't help me. I guess it is not a pure OS regression but a mix of different updates. The only idea i have for now is to use an SD card instead of SSD. |
I confirm. No problem with a nmve adaptater on USB 3. With a HAT, don't do that. Performance are equal. |
|
I've reinstalled RPi 5 on RPi OS where I run VPN and Wireguard. A week without a single problem. It will be a problem of HAOS and HA itself. |
|
This problem is a real pain! It appears out of the blue!
|
|
Hi all,
HAOS 14.2 has just been released and I am curious if it fixes the issues of 14.0 and 14.1. Please share your observations. |
Some considerations:
|
|
Thnks for sharing, i got exactly the same errors when connected the RPI5
via HDMI to my display. I have stopped experimenting, i moved everything on
a SD card and got peace of mind. Maybe someone who has the mood still for
testing this hardware setup with the latest HAOs
Op wo 29 jan 2025 18:49 schreef wnawr0t ***@***.***>:
… Hi all,
Let me share my investigation results:
1. The SQL issues are closely related to
https://community.home-assistant.io/t/home-assistant-os-14-breaks-nvme-ssd-usage-on-rpi5/817499
2. Once I/O errors for the NVME drive show up on the HDMI display (see
the 1st screenshot from the above link), the DB errors start to appear in
the HA log and some time later HA gets unresponsive (RPI5 hard reboot is
required)
3. I first downgraded HAOS from 14.1 to 14.0 and the NVME errors
seemed to be gone for the first 24 hours, but they got back.
4. Finally, I downgraded to HAOS 13.2. After that, all the NVME errors
vanished and there are no SQL errors anymore (my RPI is up for the last 2
weeks).
HAOS 14.2 has just been released and I am curious if it fixes the issues
of 14.0 and 14.1. Please share your observations.
Best
Wojciech
—
Reply to this email directly, view it on GitHub
<#132573 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BEGET7Y4B47UDS4JXEOS3VD2NEIENAVCNFSM6AAAAABTGOP6TOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMMRSGQZTONRSHE>
.
You are receiving this because you commented.Message ID:
***@***.***>
|
|
This will be my last attempt (or not, since I'm persistent) to get around the problem while it's still being solved. I created an automation to capture the system's error messages.
Now I just have to wait for the problem to happen again.
If anyone wants to replicate the experiment, here's the code for reference:alias: HA - Capturar SQLAlchemyError e reiniciar sistema
|
|
Nice workaround, thanks for sharing! |
|
A nice idea indeed, thnks for sharing |
|
I made important adjustments to the code. alias: HA - Capturar SQLAlchemyError e reiniciar sistema
|
|
Exaclty same issue on nvme ssd m.2 running on HAT of an RPi5. Digged a lot without success. I just tried @wnawr0t solution that seems to be the more reliable:
I will update if any news after that. Thanks a lot for this thread |
|
Fellow users, I am pleased to inform you that my automatic reboot routine when the damned "SQLAlchemyError" occurs worked! As I explained previously, my routine does the following:
This is not a solution to the problem, but it is a palliative that, for me, is acceptable, because at least now I will not be frustrated because some automation stopped happening because the system froze and I did not know about it. I will only add a small refinement so that the voice message does not play in the middle of the night, because the sound is loud and I do not want to wake up scared, I just want the system to start working again.
** The print is from the notification I received on the phone. (I speak Brazilian Portuguese.)
|
|
Anyone tried with the update 14.2 ? From my side everything is running fine since one week on the 13.2 but I get errors as soon as I switch to 14.x |
I don't remember which version of the OS I started with, but the error already existed. I've been using Raspberry Pi for less than 9 months. I always keep my system up to date (I back it up beforehand to go back if necessary), so I'm already on 14.2. However, since I did the update, the error hasn't occurred yet, but it hasn't been a week yet. One thing I did differently was to run a data purge, leaving only 1 day in the database. The impression I had (and it may just be an impression) is that this made the system more stable. If the errors stop (and only time will tell), I'll make this a standard, because I'm not interested in analyzing how many times a light was turned on or off, or anything like that. I'm currently running a weekly purge, keeping 5 days of data in the database.
|
|
I haven't received the SQLAlchemyError error for a few days. However, since there have been several updates, both to the core and to the addons, I have restarted the system several times. This may have prevented the error from occurring, but it may also have been my database purge routine. Now it runs twice a week and I only keep two days of data. However, another problem that really bothers me is when HA has no internet access. I use this system precisely because it can work offline, but obviously, if there is internet available, I want the system to benefit from it. But this isn't the first time that the system has CRASHED when it has no internet access. Today I was without electricity for 20 minutes. My Raspberry Pi where HA runs has a UPS with a huge battery, which ensures that it will work for many hours. The telephone operator's modem/router also has its own UPS, but now I distribute the signal through a more powerful router and this one doesn't have power support yet. Because of this, HA was without internet access for 20 minutes. It was enough to end up crashing. In addition to HA itself, which starts to display errors when it is disconnected, I also have the following integrations and add-ons that access the internet:
I don't understand this weakness of HA in relation to the absence of internet and it bothers me a lot. For applications developed by the community, a document would be issued instructing on the updates necessary to meet this operating standard. This would guarantee the stability of the system. I have no way of knowing if it was HA itself that crashed due to lack of internet or if it was a third-party integration. The log shows warnings and errors from everyone without anyone being flagged as responsible for the problem. Has anyone else experienced this? |
|
Fellows, Some change they made did not allow my routine to restart the machine automatically because every time it tried to notify me before restarting, it generated a new error and entered a loop. This damned error is directly related to an attempt to write to the database when, for some reason, it becomes unavailable. To try (again) to solve this, I parameterized HA to not record anything anymore. Yes, there will be no more event history and I don't care. I just want the system to be stable. And if the error occurs again for some inexplicable reason, the restart routine will simply restart the entire system without giving any notification. This stopped being a mere challenge and became a personal issue. |
|
Fellows, Some change they made did not allow my routine to restart the machine automatically because every time it tried to notify me before restarting, it generated a new error and entered a loop. This damned error is directly related to an attempt to write to the database when, for some reason, it becomes unavailable. To try (again) to solve this, I parameterized HA to not record anything anymore. Yes, there will be no more event history and I don't care. I just want the system to be stable. And if the error occurs again for some inexplicable reason, the restart routine will simply restart the entire system without giving any notification. This stopped being a mere challenge and became a personal issue. Inclusions I made in configuration.yaml:
System restart automation:
The "conditions" are parameters I created using helper entities. For you, this condition doesn't even need to exist or it can be different. |
The problem
Logger: homeassistant.components.recorder.core
Quelle: components/recorder/core.py:882
Integration: Recorder (Dokumentation, Probleme)
Erstmals aufgetreten: 19:46:04 (1 Vorkommnisse)
Zuletzt protokolliert: 19:46:04
SQLAlchemyError error processing task CommitTask()
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 882, in _process_one_task_or_event_or_recover
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 295, in run
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1181, in _commit_event_session_or_retry
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1207, in _commit_event_session
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/session.py", line 2362, in execute
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/session.py", line 2237, in _execute_internal
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/session.py", line 2106, in _connection_for_bind
File "", line 2, in _connection_for_bind
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/state_changes.py", line 103, in _go
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/session.py", line 996, in _raise_for_prerequisite_state
sqlalchemy.exc.InvalidRequestError: This session is in 'prepared' state; no further SQL can be emitted within this transaction.
**I have no idea with this Errorcode. This is the first ErrorCode of many and Everything includes the recorder. What can i do? It is the 3rd time i started from the beginning and everytime this code comes up.
This is the second Errorcode**
Logger: homeassistant.components.recorder.core
Quelle: components/recorder/core.py:882
Integration: Recorder (Dokumentation, Probleme)
Erstmals aufgetreten: 19:46:04 (1 Vorkommnisse)
Zuletzt protokolliert: 19:46:04
Unrecoverable sqlite3 database corruption detected: (sqlite3.DatabaseError) database disk image is malformed [SQL: UPDATE states SET last_reported_ts=? WHERE states.state_id = ?] [parameters: [(1733597135.6587698, 8740), (1733597062.7175126, 8592), (1733597110.6194406, 8689), (1733597110.6196215, 8690), (1733597110.9234178, 8691), (1733597110.923583, 8692)]] (Background on this error at: https://sqlalche.me/e/20/4xp6)
Traceback (most recent call last):
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1936, in _exec_single_context
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/default.py", line 938, in do_executemany
sqlite3.DatabaseError: database disk image is malformed
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 882, in _process_one_task_or_event_or_recover
File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 295, in run
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1181, in _commit_event_session_or_retry
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 1207, in _commit_event_session
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/session.py", line 2362, in execute
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/session.py", line 2247, in _execute_internal
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1627, in orm_execute_statement
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/bulk_persistence.py", line 357, in _bulk_update
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/orm/persistence.py", line 912, in _emit_update_statements
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1936, in _exec_single_context
File "/usr/local/lib/python3.13/site-packages/sqlalchemy/engine/default.py", line 938, in do_executemany
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
[SQL: UPDATE states SET last_reported_ts=? WHERE states.state_id = ?]
[parameters: [(1733597135.6587698, 8740), (1733597062.7175126, 8592), (1733597110.6194406, 8689), (1733597110.6196215, 8690), (1733597110.9234178, 8691), (1733597110.923583, 8692)]]
(Background on this error at: https://sqlalche.me/e/20/4xp6)
and the 3rd one:
Logger: homeassistant.components.recorder.util
Quelle: components/recorder/util.py:314
Integration: Recorder (Dokumentation, Probleme)
Erstmals aufgetreten: 19:46:04 (1 Vorkommnisse)
Zuletzt protokolliert: 19:46:04
The system will rename the corrupt database file //config/home-assistant_v2.db to //config/home-assistant_v2.db.corrupt.2024-12-07T18:46:04.908274+00:00 in order to allow startup to proceed
**Everytime this comes up i need to unplug the Powercable. It runs for maybe 1 hour. After then the Home assistant stuck and they say YAML. Data not found. And the Errorcodes above come up.
Please Help :(**
What version of Home Assistant Core has the issue?
2024.12.1
What was the last working version of Home Assistant Core?
/
What type of installation are you running?
Home Assistant OS
Integration causing the issue
I dont know
Link to integration documentation on our website
No response
Diagnostics information
No response
Example YAML snippet
No response
Anything in the logs that might be useful for us?
Rasberry Pi 5 512GB SSD Card
Additional information
No response
The text was updated successfully, but these errors were encountered: