Xiaomi BLE plant sensor entities unavailable

[nightmare1111]

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]]

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]

+1 Same problem. Core 2023.4.4

[123jjck]

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]

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

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

[VendettaPrime]

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]

Seems good now thanks

[nightmare1111]

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

[jip98]

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]

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]

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]

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

[Ernst79]

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]

+1 mismo problema...

[VendettaPrime]

+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]

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]

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]

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]

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

[dexif]

Same issue 3.3.5 firmware

[Ernst79]

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.

[Babidi92]

I have the same problem

[peteh]

I also have this issue, somewhen in February it stopped being detected

[Ernst79]

Please, post debug logging of the xiaomi-ble integration and of the Bluetooth integration. And from the xiaomi-ble pypi package.

without logs, I can’t help you.

[peteh]

config_entry-bluetooth-d701cf4b919c4d1a7bffd74bfe6c188b.json

Unfortunately I deleted the Xiaomi BLE Extension in the hope it would rediscover the device. I'm running on the latest Homeassistant running in docker on Ubuntu 22.04 LTS

It looks like the scanning does not pick up the flowercare sensor for some reason. (should have xx:76 in the address)

[Ernst79]

Did you try a new battery?

[peteh]

I got a bit closer to the issue:

• The integrated intel controller does not detect the Flowercare sensor anymore but it does detect other devices, e.g. Xiaomi Humidity sensors that someone in the neighborhood seems to have
• I added a TP Link usb bluetooth controller and the Flowercare sensor showed up immediately after configuring the new bt device in homeassistant bluetooth integration
• the battery is relatively fresh and it is working on another raspberry pi and the flowercare app

So I guess the issue is with the intel controller for some reason. Any ideas why it might not like my sensor anymore?

[Ernst79]

Most likely some interference of the Bluetooth radio and the WiFi radio or zigby radio. All act on 2.4 GHz and one can block the other.

[peteh]

I narrowed it down a bit more. It seems to be related to the linux kernel.

latest 6.6 and 6.7 branches seem not to detect Bluetooth LE devices with intel chips.

Downgrading to 6.1 makes it work again.

[Ernst79]

It sometimes helps to do a full system reboot, not only HA. Like power off, power on.

[peteh]

I had to do this with the different kernels anyway. So I think this is not related. It looks like Intel AX210 drivers are just broken after 6.5/6.6 kernels.