SQL query interferes with other SQL queries

[lord_icon]

This is the current status. Here I query the DB size 2 times. (I will delete one later).

I use mariaDB. It currently looks like this.

recorder:
  db_url: mysql://homeassistant:homeassistant@core-mariadb/homeassistant?charset=utf8mb4

But as soon as I insert this code I get problems. The sensor is not created and the other SQL queries no longer show any data.

sensor:
  - platform: sql
    db_url: "mysql://homeassistant:homeassistant@core-mariadb/homeassistant?charset=utf8mb4"
    scan_interval: 30
    queries:
      - name: test_sql_sensor
        query: "SELECT 1 as count;"
        column: count

Logger: homeassistant.config_entries
Quelle: config_entries.py:637
Erstmals aufgetreten: 14:27:36 (2 Vorkommnisse)
Zuletzt protokolliert: 14:27:36

Error setting up entry MariaDB for sql
Error setting up entry Maria-DB Größe for sql
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/config_entries.py", line 637, in __async_setup_with_context
    result = await component.async_setup_entry(hass, self)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/sql/__init__.py", line 110, in async_setup_entry
    redact_credentials(get_instance(hass).db_url),
                       ~~~~~~~~~~~~^^^^^^
  File "/usr/src/homeassistant/homeassistant/helpers/recorder.py", line 76, in get_instance
    return hass.data[DATA_INSTANCE]
           ~~~~~~~~~^^^^^^^^^^^^^^^
KeyError: 'recorder_instance'

Can anyone explain this?

[deluxestyle]

@lord_icon

SELECT 1 as count;

could it be that your query is the problem
not sure if this is a valid query,

[lord_icon]

@lord_icon

SELECT 1 as count;

No… that shouldn’t be a problem.
And if it did, then this ONE sensor would not be present.

PHPMyAdmin also confirms this.

The query creates a single column called count, which contains the value 1. This query is often useful as a minimal test or as a placeholder in SQL sensors to check if the database connection and the query are basically working.

It seems to me that if you can’t create 2 connections… but that’s nonsense.

[deluxestyle]

hm, seems to be a problem

Error in SQL integration

Same issue here using MariaDB. Reloading integration resolved the error but after every reboot the error comes back.

[lord_icon]

no… I don’t think I have the same problem.

When I do a reload, nothing changes. The sensors are still not loaded. Only a complete removal of the SQL code solves the problem.

So it should be about something else.

[deluxestyle]

have you tried a different query?

[lord_icon]

Yes, of course. As a DB admin, quite a comprehensive one. But since this didn’t work, I reduced it more and more. Down to this minimal command. Ultimately, this only tests the DB structure.
No table or similar is queried. This command must work.

[igbor]

I have the same problem. The code is taken from the example in the documentation. If you manually reboot the integration, everything works until the next reboot.
SELECT table_schema “database”, Round(Sum(data_length + index_length) / POWER(1024,2), 1) “value” FROM information_schema.tables WHERE table_schema=“homeassistant” GROUP BY table_schema;

[PeteRage]

Looks like a bug to me. Is there a GitHub issue created?

[PeteRage]

SQL integration error at restart

Looks like it’s fixed and should be in 2025.3.0

[lord_icon]

@PeteRage

Looks like it’s fixed and should be in 2025.3.0

# https://github.com/IAsDoubleYou/homeassistant-mysql_query
# This addon supports INSERT per service. BUT unfortunately no SELECT.
mysql_query:
  mysql_host: core-mariadb
  mysql_username: homeassistant
  mysql_password: homeassistant
  mysql_db: solarprognosen_vergleich
  mysql_port: 3306 # Optional, Standard ist 3306
  mysql_autocommit: true # Optional, Standard ist true
  mysql_charset: utf8mb4 # Optional, Standard ist oft utf8mb4
  mysql_collation: utf8mb4_unicode_ci # Optional


!!!! MY !!!!! EXAMPLE

automation:
  - alias: "Solarwerte in DB eintragen (mit Differenzen, Prozentanzeige und Peakwerte)"
    trigger:
      #platform: time_pattern
      #seconds: "/10"
      platform: time
      at: "23:59:00"
    action:
      - service: mysql_query.query
        data:
          query: >
            INSERT INTO solarprognosen_vergleich 
            (datum, Solcast_Solar, Solcast_Solar_PEAK, solarprognose, solarprognose_peak, erreichte_tages_kWh, erreichter_tagesPEAK, 
             differenz1_kWh, differenz2_kWh, differenz1_percent, differenz2_percent, 
             differenz1_PEAK_kWh, differenz2_PEAK_kWh, differenz1_PEAK_percent, differenz2_PEAK_percent)
            VALUES (
              '{{ now().strftime("%d.%m.%Y") }}',
              '{{ states("sensor.solcast_pv_forecast_prognose_heute") }}',
              '{{ "{:.2f}".format(states("sensor.solcast_pv_forecast_prognose_spitzenleistung_heute")|float / 1000) }}',
              '{{ states("sensor.solarprognose_werte_heute") }}',
              '{{ states("sensor.solarprognose_werte_heutiger_peak") }}',
              '{{ states("sensor.pv_erzeugung_heute") }}',
              '{{ "{:.2f}".format(states("input_number.daily_pv_peak")|float / 1000) }}',
              '{{ "{:+.2f}".format(states("sensor.solcast_pv_forecast_prognose_heute")|float - states("sensor.pv_erzeugung_heute")|float) }}',
              '{{ "{:+.2f}".format(states("sensor.solarprognose_werte_heute")|float - states("sensor.pv_erzeugung_heute")|float) }}',
              '{{ "{:+.2f}%".format((states("sensor.solcast_pv_forecast_prognose_heute")|float - states("sensor.pv_erzeugung_heute")|float) / states("sensor.solcast_pv_forecast_prognose_heute")|float * 100) }}',
              '{{ "{:+.2f}%".format((states("sensor.solarprognose_werte_heute")|float - states("sensor.pv_erzeugung_heute")|float) / states("sensor.solarprognose_werte_heute")|float * 100) }}',
              '{{ "{:+.2f}".format((states("sensor.solcast_pv_forecast_prognose_spitzenleistung_heute")|float / 1000) - (states("input_number.daily_pv_peak")|float / 1000)) }}',
              '{{ "{:+.2f}".format((states("sensor.solarprognose_werte_heutiger_peak")|float) - (states("input_number.daily_pv_peak")|float / 1000)) }}',
              '{{ "{:+.2f}%".format(((states("sensor.solcast_pv_forecast_prognose_spitzenleistung_heute")|float / 1000) - (states("input_number.daily_pv_peak")|float / 1000)) / (states("sensor.solcast_pv_forecast_prognose_spitzenleistung_heute")|float / 1000) * 100) }}',
              '{{ "{:+.2f}%".format(((states("sensor.solarprognose_werte_heutiger_peak")|float) - (states("input_number.daily_pv_peak")|float / 1000)) / (states("sensor.solarprognose_werte_heutiger_peak")|float) * 100) }}'
            );
        response_variable: query_response

and then as the 2nd one:

#https://github.com/IAsDoubleYou/ha_mysql
#This addon supports SELECT per sensor. BUT unfortunately no INSERT.
ha_mysql:
  host: core-mariadb
  port: 3306
  username: homeassistant
  password: homeassistant
  database: solarprognosen_vergleich

!!!! MY !!!!! EXAMPLE

script:
  sende_solarprognose_email:
    sequence:
      - service: mysql_query.query
        data:
          query: >
            SELECT JSON_ARRAYAGG(
              JSON_OBJECT(
                'datum', datum,
                'Solcast_Solar', Solcast_Solar,
                'solarprognose', solarprognose,
                'erreichte_tages_kWh', erreichte_tages_kWh,
                'differenz1_kWh', differenz1_kWh,
                'differenz1_percent', differenz1_percent,
                'differenz2_kWh', differenz2_kWh,
                'differenz2_percent', differenz2_percent,
                'erreichter_tagesPEAK', erreichter_tagesPEAK,
                'Solcast_Solar_PEAK', Solcast_Solar_PEAK,
                'solarprognose_peak', solarprognose_peak,
                'differenz1_PEAK_kWh', differenz1_PEAK_kWh,
                'differenz1_PEAK_percent', differenz1_PEAK_percent,
                'differenz2_PEAK_kWh', differenz2_PEAK_kWh,
                'differenz2_PEAK_percent', differenz2_PEAK_percent
              )
            ) AS data
            FROM solarprognosen_vergleich
            WHERE STR_TO_DATE(datum, '%d.%m.%Y')
              BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01') AND NOW()
            ORDER BY STR_TO_DATE(datum, '%d.%m.%Y') ASC;
        response_variable: query_result

      - service: notify.email_prognosedaten
        data:
          title: >
            Solarprognose Bericht für {{ now().strftime('%B %Y') }} – Stand {{ now().strftime('%d.%m.%Y') }}
          message: "Solarprognose Tagesbericht"
          data:
            html: >