search

MyElectricalData / myelectricaldata_import

129 stars 35 forks source link

[BUG] - ERROR : (sqlite3.DatabaseError) database disk image is malformed #567

Open ste69008 opened 1 month ago

ste69008 commented 1 month ago

Erreur SQL lors de la récupération de données

Le tableau de bord MyElectricalData affiche un message d'erreur à l'ouverture : DataTables warning: table id=dataTableConsommationDetail - Ajax error. For more information about this error, please see https://datatables.net/tn/7

Au chargement du container, j'ai l'impression que la récupération fonctionne mais l'ajout dans la base provoque une erreur. Je viens d'essayer la version myelectricaldata_dev, mais sans succès => même message d'erreur. Je peux fournir un log plus complet en PM si besoin (difficile de nettoyer toutes les infos perso).

2024-08-02 20:46:35.180 -     INFO : ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ ◦ ❖ ◦ ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
2024-08-02 20:46:35.180 -     INFO : [**MONPDL***] RÉCUPÉRATION DE LA CONSOMMATION JOURNALIÈRE :
2024-08-02 20:46:35.180 -     INFO : ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ ◦ ❖ ◦ ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
2024-08-02 20:46:35.196 -     INFO : Récupération des données : 2021-08-05 => 2024-08-04
2024-08-02 20:46:37.625 -     INFO : 127.0.0.1:59676 - "GET / HTTP/1.1" 200
2024-08-02 20:46:41.460 -     INFO :  Chargement des données depuis MyElectricalData 2021-08-05 => 2024-08-04
2024-08-02 20:46:42.828 -     INFO : 127.0.0.1:59684 - "GET / HTTP/1.1" 200
2024-08-02 20:46:48.078 -     INFO : 127.0.0.1:59028 - "GET / HTTP/1.1" 200
2024-08-02 20:46:53.323 -     INFO : 127.0.0.1:59044 - "GET / HTTP/1.1" 200
2024-08-02 20:46:55.937 -     INFO : Récupération des données : 2021-08-04 => 1933-05-04
2024-08-02 20:46:55.937 -     INFO :  => Toutes les données sont déjà en cache.
2024-08-02 20:46:55.938 -     INFO : ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ ◦ TERMINE ◦ ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
2024-08-02 20:46:55.939 -     INFO : ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ ◦ ❖ ◦ ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
2024-08-02 20:46:55.939 -     INFO : [**MONPDL***] RÉCUPÉRATION DE LA CONSOMMATION DÉTAILLÉE :
2024-08-02 20:46:55.940 -     INFO : ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ ◦ ❖ ◦ ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
2024-08-02 20:46:55.954 -     INFO : Récupération des données : 2024-07-28 => 2024-08-04
2024-08-02 20:46:55.965 -    ERROR : (sqlite3.DatabaseError) database disk image is malformed
[SQL: SELECT consumption_detail.id, consumption_detail.usage_point_id, consumption_detail.date, consumption_detail.value, consumption_detail.interval, consumption_detail.measure_type, consumption_detail.blacklist, consumption_detail.fail_count 
FROM usage_points JOIN consumption_detail ON usage_points.usage_point_id = consumption_detail.usage_point_id 
WHERE consumption_detail.usage_point_id = ? AND consumption_detail.date <= ? AND consumption_detail.date >= ? ORDER BY consumption_detail.date ASC]
[parameters: ('**MONPDL***', '2024-08-04 23:59:59.999999', '2024-07-28 00:00:00.000000')]
(Background on this error at: https://sqlalche.me/e/14/4xp6)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlite3.DatabaseError: database disk image is malformed

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

Traceback (most recent call last):
  File "/app/models/query_detail.py", line 84, in run
    current_data = self.db.get_detail(self.usage_point_id, begin, end, self.measure_type)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/app/models/database.py", line 1298, in get_detail
    query_result = self.get_detail_all(
                   ^^^^^^^^^^^^^^^^^^^^
  File "/app/models/database.py", line 1188, in get_detail_all
    return self.session.scalars(
           ^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 2, in scalars
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1778, in scalars
    return self.execute(
           ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1717, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) database disk image is malformed
[SQL: SELECT consumption_detail.id, consumption_detail.usage_point_id, consumption_detail.date, consumption_detail.value, consumption_detail.interval, consumption_detail.measure_type, consumption_detail.blacklist, consumption_detail.fail_count 
FROM usage_points JOIN consumption_detail ON usage_points.usage_point_id = consumption_detail.usage_point_id 
WHERE consumption_detail.usage_point_id = ? AND consumption_detail.date <= ? AND consumption_detail.date >= ? ORDER BY consumption_detail.date ASC]
[parameters: ('***MONPDL***', '2024-08-04 23:59:59.999999', '2024-07-28 00:00:00.000000')]
(Background on this error at: https://sqlalche.me/e/14/4xp6)
2024-08-02 20:46:55.981 -    ERROR : (sqlite3.DatabaseError) database disk image is malformed
[SQL: SELECT consumption_detail.id, consumption_detail.usage_point_id, consumption_detail.date, consumption_detail.value, consumption_detail.interval, consumption_detail.measure_type, consumption_detail.blacklist, consumption_detail.fail_count 
FROM usage_points JOIN consumption_detail ON usage_points.usage_point_id = consumption_detail.usage_point_id 
WHERE consumption_detail.usage_point_id = ? AND consumption_detail.date <= ? AND consumption_detail.date >= ? ORDER BY consumption_detail.date ASC]
[parameters: ('***MONPDL***', '2024-08-04 23:59:59.999999', '2024-07-28 00:00:00.000000')]
(Background on this error at: https://sqlalche.me/e/14/4xp6)
Traceback (most recent call last):
  File "/app/models/jobs.py", line 280, in get_consumption_detail
    run(self.usage_point_config)
  File "/app/models/jobs.py", line 269, in run
    Detail(headers=self.header_generate(), usage_point_id=usage_point_id).get()
  File "/app/models/query_detail.py", line 199, in get
    if "exit" in response:
       ^^^^^^^^^^^^^^^^^^
TypeError: argument of type 'NoneType' is not iterable
2024-08-02 20:46:55.991 -    ERROR : Erreur lors de la récupération de la consommation détaillée
2024-08-02 20:46:55.992 -    ERROR : argument of type 'NoneType' is not iterable
Configuration (config.yaml) ```yaml cycle: 14400 # 4H debug: true log2file: false wipe_influxdb: false # Work only with influxdb > 2.X # Par défaut le backend en local dans le /data/cache.db du conteneur. # Mais il est possible de basculer sur une base de données externe de type SQLite ou PostgreSQL # Exemple pour Postgres : # storage_uri: postgresql://myelectricaldata:myelectricaldata@postgres:5432/myelectricaldata mqtt: enable: true hostname: **** port: 1883 username: *** password: *** prefix: myelectricaldata client_id: myelectricaldata # DOIT ETRE UNIQUE SUR LA TOTALITE DES CLIENTS CONNECTE AU SERVEUR MQTT retain: true qos: 0 home_assistant: # MQTT est obligaoire pour Home Assistant enable: true discovery: true discovery_prefix: homeassistant home_assistant_ws: enable: true ssl: true token: **** url: *.*.*.*:8123 influxdb: enable: false hostname: influxdb port: 8086 token: myelectricaldata org: myelectricaldata bucket: myelectricaldata # ATTENTION, L'activation de l'importation asynchrone va réduire fortement le temps d'importation dans InfluxDB # mais va augmenter la consommation mémoire & CPU et donc à activer uniquement sur un hardware robuste. method: synchronous # Mode disponible : synchronous / asynchronous / batching # batching_options permet uniquement de configurer la methode `batching`. # Pour plus d'information : https://github.com/influxdata/influxdb-client-python#batching batching_options: batch_size: 1000 flush_interval: 1000 jitter_interval: 0 retry_interval: 5000 max_retry_time: 180_000 max_retries: 5 max_retry_delay: 125_000 exponential_base: 2 myelectricaldata: "****": enable: "true" token: **** name: "Mions" addresses: "true" cache: "true" consumption: "true" consumption_detail: "true" consumption_price_base: "0.2018" consumption_price_hc: "0.1668" consumption_price_hp: "0.2161" consumption_max_date: "2021-06-01" consumption_detail_max_date: "2021-06-01" offpeak_hours_0: 22H38-6H38 # LUNDI offpeak_hours_1: 22H38-6H38 # MARDI offpeak_hours_2: 22H38-6H38 # MERCREDI offpeak_hours_3: 22H38-6H38 # JEUDI offpeak_hours_4: 22H38-6H38 # VENDREDI offpeak_hours_5: 22H38-6H38 # SAMEDI offpeak_hours_6: 22H38-6H38 # DIMANCHE plan: BASE production: "false" production_detail: "false" production_price: "0.0" production_max_date: "2021-06-01" production_detail_max_date: "2021-06-01" refresh_addresse: "false" refresh_contract: "false" ```

Type d'installation

Version : 0.13.2

cvalentin-dkt commented 2 weeks ago

Il semblerais que ta base soit corrumpu, essaie d'effacer (déplacer) le cache.db et reboot le service.

ste69008 commented 2 weeks ago

Salut, merci pour ton retour. Cela fonctionne en supprimant la base !

Je suis surpris car j'avais réalisé l'opération sans succès ; j'avais même installé la version _dev et j'avais eu le même code d'erreur.

Cdt Steph