Xiaomi BLE plant sensor entities unavailable

nightmare1111 commented 1 year ago

The problem

I have a Xiaomi BLE plant sensor . I had it working then this week some entities became unavailable, temp still works but no moisute fertility or lux. All these sensors are still broadcasting to the flower care android app.

What version of Home Assistant Core has the issue?

2023.4.2

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant OS

Integration causing the issue

Xiaomi ble

Link to integration documentation on our website

https://www.home-assistant.io/integrations/xiaomi_ble/

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

Logger: homeassistant.components.recorder.util
Source: components/recorder/table_managers/statistics_meta.py:315
Integration: Recorder (documentation, issues)
First occurred: 4:25:40 PM (1 occurrences)
Last logged: 4:25:40 PM

Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id [SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?] [parameters: ('sensor.hanging_basket_temperature', 'sensor.plant_sensor_5d9a_temperature', 'recorder')] (Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 748, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: statistics_meta.statistic_id

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 129, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 695, in update_statistics_metadata
    statistics_meta_manager.update_statistic_id(
  File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 315, in update_statistic_id
    ).update({StatisticsMeta.statistic_id: new_statistic_id})
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 3284, in update
    self.session.execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2229, in execute
    return self._execute_internal(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2124, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1524, in orm_execute_statement
    return super().orm_execute_statement(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 253, in orm_execute_statement
    result = conn.execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1414, in execute
    return meth(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 486, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1638, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    return self._exec_single_context(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2326, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 748, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.hanging_basket_temperature', 'sensor.plant_sensor_5d9a_temperature', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Additional information

The entities were unavailable after deleteing the integration and reinstaling it the unavailable entities are no longer visible at all

home-assistant[bot] commented 1 year ago

Hey there @jc2k, @ernst79, mind taking a look at this issue as it has been labeled with an integration (xiaomi_ble) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of `xiaomi_ble` can trigger bot actions by commenting: - `@home-assistant close` Closes the issue. - `@home-assistant rename Awesome new title` Renames the issue. - `@home-assistant reopen` Reopen the issue. - `@home-assistant unassign xiaomi_ble` Removes the current integration label and assignees on the issue, add the integration domain after the command.

_{^{(message by CodeOwnersMention)}}

xiaomi_ble documentation xiaomi_ble source _{^{(message by IssueLinks)}}

VendettaPrime commented 1 year ago

+1 Same problem. Core 2023.4.4

123jjck commented 1 year ago

Same issue when upgraded from Core 2023.3.6 to Core 2023.4.x (still occurs on 2023.4.4) but with BLE Mi Temperature and Humidity Monitor 2

Jc2k commented 1 year ago

Do you all have the SQL error seen in the first post?

If you do, what database are you using? SQLite? Maria?

VendettaPrime commented 1 year ago

I use MariaDB. In my case - I don't see any errors - just the message that the device is unavailable%. Comes after upgrade Core form 2023.3.5 to 2023.4.2 and after 2023.4.4

nightmare1111 commented 1 year ago

Seems good now thanks

nightmare1111 commented 1 year ago

now i have fertility but all other sensors are off, the mi flora app still shows all entities working

jip98 commented 1 year ago

I have the same problem since today. Around 9 am I lost the connection, it came back around 1 pm and lost it again at 3 pm. Does anyone have a solution?

Ernst79 commented 1 year ago

Did you install Home Assistant OS 10 recently? There are some Bluetooth issues reported with the 10.0 release. If so, you might try to move back to 9.5.

https://github.com/home-assistant/operating-system/issues/2485

jip98 commented 1 year ago

Yes, I did. I hope that's the problem. I will wait for a fix and if it takes too long go back to 9.5. Thank you!

kocicky commented 1 year ago

I tried it at 10.1 and problem persist. At 9.5 (2023.4.6) is all working well.

Ernst79 commented 1 year ago

That is because https://github.com/home-assistant/operating-system/issues/2485 isn’t solved yet. Please post all relevant info in that topic, as this has to be solved in Home Assistant OS.

apalau88 commented 1 year ago

+1 mismo problema...

VendettaPrime commented 1 year ago

+1 Same problem. Core 2023.4.4

I'm not using Home Assistant OS, I use Home Assistant Supervisor install on rpi4. Host OS - Debian. Core 2023.5.2 - problem still here

Ernst79 commented 1 year ago

Without further info it will be inpossible to tell what is wrong. If you did not install HA OS 10.1, it could be wifi interference, bad range, failing Bluetooth dongle. Try to enable debug logging for the Bluetooth integration to see if you receive data of the sensor at all.

gotaslug commented 1 year ago

I think I face the same error but with a different device and a newer version of Home Assistant 2023.6.3 with Supervisor 2023.06.2 and Operating System 10.3 on a Raspberry Pi 4:

2023-06-24 10:41:43.083 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.schalter_schlafzimmer_bett_battery_3', 'sensor.philips_rwl021_battery_3', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1968, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: statistics_meta.statistic_id

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 130, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/statistics.py", line 680, in update_statistics_metadata
    statistics_meta_manager.update_statistic_id(
  File "/usr/src/homeassistant/homeassistant/components/recorder/table_managers/statistics_meta.py", line 316, in update_statistic_id
    ).update({StatisticsMeta.statistic_id: new_statistic_id})
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 3275, in update
    self.session.execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2232, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2127, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/bulk_persistence.py", line 1619, in orm_execute_statement
    return super().orm_execute_statement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1413, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1637, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1987, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2344, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1968, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 920, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics_meta.statistic_id
[SQL: UPDATE statistics_meta SET statistic_id=? WHERE statistics_meta.statistic_id = ? AND statistics_meta.source = ?]
[parameters: ('sensor.schalter_schlafzimmer_bett_battery_3', 'sensor.philips_rwl021_battery_3', 'recorder')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

MonsterD1 commented 10 months ago

similar issue ... out of 10 HHCCJCY01 flower sensors 3 are showing up in HA Xiaomi BLE but 7 not.

all sensors use the 3.3.5 firmware alle sensors placed next to same ESPHome Bluetooth Proxie

canedje commented 9 months ago

same problem for flood sensor OC29 It is only creating a signal strength sensor.

dexif commented 7 months ago

Same issue 3.3.5 firmware

Ernst79 commented 7 months ago

Is there anything in the logs? Try to enable debug logging for the Bluetooth integration and check if you receive data of the sensor. If you do, post the logs here, such that I can check it.