database plugin: issues when deleting unused entries

[onkelandy]

Feb 12 03:14:23 WARNING  plugins.database  remove_older_than_maxage skipped because db is not connected
Feb 12 03:14:58 ERROR    lib.db            Database [Database]: Could not connect to the database using 'pymysql': (2003, "Can't connect to MySQL server on '10.0.0.100' ([Errno 111] Verbindungsaufbau abgelehnt)")
Feb 12 03:14:58 CRITICAL plugins.database  Database: Initialization failed: (2003, "Can't connect to MySQL server on '10.0.0.100' ([Errno 111] Verbindungsaufbau abgelehnt)")
Feb 12 03:15:55 WARNING  plugins.database  remove_older_than_maxage skipped because db is not connected
Feb 12 03:16:04 ERROR    lib.db            Database [Database]: Could not connect to the database using 'pymysql': (2003, "Can't connect to MySQL server on '10.0.0.100' ([Errno 111] Verbindungsaufbau abgelehnt)")
Feb 12 03:16:04 CRITICAL plugins.database  Database: Initialization failed: (2003, "Can't connect to MySQL server on '10.0.0.100' ([Errno 111] Verbindungsaufbau abgelehnt)")
Feb 12 03:17:27 ERROR    lib.db            Can not execute query: DELETE FROM log WHERE item_id = %(id)s LIMIT %(maxrecords)s; (args OrderedDict([('id', 3236), ('maxrecords', 20000)])): (0, '')
Feb 12 03:17:27 ERROR    plugins.database  Database: Error for query DELETE FROM log WHERE item_id = 3236 LIMIT 20000;: (0, '')
Feb 12 03:17:28 ERROR    lib.scheduler.tasks Method plugins.database.Remove old exception: (0, '')
Traceback (most recent call last):
  File "/usr/local/smarthome/lib/scheduler.py", line 707, in _task
    obj()
  File "/usr/local/smarthome/plugins/database/__init__.py", line 1578, in remove_older_than_maxage
    self.remove_orphan_items()
  File "/usr/local/smarthome/plugins/database/__init__.py", line 1042, in remove_orphan_items
    if not self._delete_orphan(item):
  File "/usr/local/smarthome/plugins/database/__init__.py", line 1025, in _delete_orphan
    self._execute(self._prepare("DELETE FROM {log} WHERE item_id = :id LIMIT :maxrecords;"), {'id': item_id, 'maxrecords': self.delete_orphan_chunk_size}, cur=cur)
  File "/usr/local/smarthome/plugins/database/__init__.py", line 1774, in _execute
    self._query(self._db.execute, query, params, cur)
  File "/usr/local/smarthome/plugins/database/__init__.py", line 1804, in _query
    raise e
  File "/usr/local/smarthome/plugins/database/__init__.py", line 1801, in _query
    tuples = func(self._prepare(query), params, cur=cur)
  File "/usr/local/smarthome/lib/db.py", line 337, in execute
    result = cur.execute(stmt, args)
  File "/usr/local/smarthome/venvs/py_310/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/usr/local/smarthome/venvs/py_310/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/local/smarthome/venvs/py_310/lib/python3.10/site-packages/pymysql/connections.py", line 562, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/usr/local/smarthome/venvs/py_310/lib/python3.10/site-packages/pymysql/connections.py", line 843, in _execute_command
    raise err.InterfaceError(0, "")
pymysql.err.InterfaceError: (0, '')
Feb 12 03:18:59 ERROR    lib.db            Can not execute query: DELETE FROM log WHERE item_id = %(id)s LIMIT %(maxrecords)s; (args OrderedDict([('id', 3237), ('maxrecords', 20000)])): (0, '')
Feb 12 03:18:59 ERROR    plugins.database  Database: Error for query DELETE FROM log WHERE item_id = 3237 LIMIT 20000;: (0, '')
Feb 12 03:18:59 ERROR    lib.scheduler.tasks Method plugins.database.Remove old exception: (0, '')
Traceback (most recent call last):
  File "/usr/local/smarthome/lib/scheduler.py", line 707, in _task
    obj()
  File "/usr/local/smarthome/plugins/database/__init__.py", line 1578, in remove_older_than_maxage
    self.remove_orphan_items()
  File "/usr/local/smarthome/plugins/database/__init__.py", line 1042, in remove_orphan_items
    if not self._delete_orphan(item):
  File "/usr/local/smarthome/plugins/database/__init__.py", line 1025, in _delete_orphan
    self._execute(self._prepare("DELETE FROM {log} WHERE item_id = :id LIMIT :maxrecords;"), {'id': item_id, 'maxrecords': self.delete_orphan_chunk_size}, cur=cur)
  File "/usr/local/smarthome/plugins/database/__init__.py", line 1774, in _execute
    self._query(self._db.execute, query, params, cur)
  File "/usr/local/smarthome/plugins/database/__init__.py", line 1804, in _query
    raise e
  File "/usr/local/smarthome/plugins/database/__init__.py", line 1801, in _query
    tuples = func(self._prepare(query), params, cur=cur)
  File "/usr/local/smarthome/lib/db.py", line 337, in execute
    result = cur.execute(stmt, args)
  File "/usr/local/smarthome/venvs/py_310/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/usr/local/smarthome/venvs/py_310/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/local/smarthome/venvs/py_310/lib/python3.10/site-packages/pymysql/connections.py", line 562, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/usr/local/smarthome/venvs/py_310/lib/python3.10/site-packages/pymysql/connections.py", line 843, in _execute_command
    raise err.InterfaceError(0, "")
pymysql.err.InterfaceError: (0, '')

Hatte gegen 22:00 das Löschen verwaister Items angestoßen. Hatte das Gefühl, da tut sich nichts, aber vermutlich liegt das einfach an der Menge der Daten. Jedenfalls kam dann nach einer Neuverbindung mit der Datenbank im 90-Sekunden-Rhythmus immer diese Meldung im Log. Für jedes der zu löschenden Items...
Die Verbindung zur Datenbank war gegen 3 Uhr für 15 Minuten off (weil dort ein Backup ausgeführt wird). Und seither gibt's den Fehler.
Das Problem bestand später auch ohne Datenbankabbruch, möglicherweise waren schon einige Einträge gelöscht und beim Versuch, die erneut zu löschen, gab es obigen Fehler..?

[Morg42]

Ich hatte mal einen ähnlichen Fehler, das lag bei mir daran, dass die DB zu groß geworden war und sich ständig und überall zu viel Zeit genommen hat; dabei ist dann der Connector immer in den Timeout gerannt und musste jedes Mal neu verbinden. PhpMyAdmin hat auch nur noch Fehler angezeit. (Lösung war, per MySQL-Client manuell aufzuräumen...)

Weiß aber nicht, ob das hier das gleiche ist. Die Fehlermeldung ist ja nicht aussagekräftig. Hast du mal geschaut, ob du manuell problemlos (und verzögerungslos) auf die DB zugreifen kannst?

[bmxp]

Im Prinzip müsste man hier mal eine Warteschlange implementieren die auch einen Neustart überlebt. Also per Textanweisung, Yaml oder wie auch immer (menschenlesbar!) der Art "lösche von Item xyz alles bis zum 31.12.2023" oder "lösche verwaistes Item abc"
Diese Warteschlange könnte man dann in einem Item als Text darstellen und in die Visu einbinden. Dann weiß man genau was im Hintergrund gerade abläuft.

[Morg42]

Das Hauptproblem scheint mir hier erstmal, dass viele DBs ab einer gewissen Größe (eigentlich) für die verwendete Hw zu groß und damit letztlich zu langsam sind (ja, auch bei mir ;) ). Insofern müsste man
a) das timeout-Problem gelöst bekommen (ist schon im mysql-Client schwierig!) und
b) auf Performance verzichten und eher kleine Pakete (limit 100 bis max. 1000) verarbeiten. Dazu müsste es entweder einen extra Worker-Thread geben (nur bei MySQL, mag SQLite nicht) oder das DB-Plugin blockiert den Plugin-Thread, das geht dann nicht, während shng läuft. Daraus folgt für mich, dass spätestens mit SQLite eh "offline", also ohne laufendes shng, gearbeitet werden müsste...?

Der zweite Punkt wäre deine Warteschlange...

Da wäre zu überlegen, ob. das tatsächlich eine echte Warteschlange/Queue sein soll, die shng abarbeitet, oder das nur eine Statusanzeige sein soll.

Im ersten Fall wäre "menschenlesbar" ungünstig, weil immer aufwändiger zu parsen und ggf. nicht eindeutig, im zweiten Fall menschenlesbar in ein Item schreiben.

Variante 1 könnte man auch in einem Item speichern (cache, nicht (!) db :D ) Alternativ in einer Datei (müsste dann wahrscheinlich in var/foo/ liegen) und entweder
a) beim Start vom DB-Plugin im Hauptthread abarbeiten (Start könnte lange!! dauern) oder
b) im Standalone-Modus ausführen.

Wenn wir nur MySQL betrachten und einen Worker-Thread nutzen, könnte man (mit entsprechender WebIf-Unterstützung) den Worker anweisen (auch über Queue), Operation foo, bar und baz auszuführen und das läuft dann in kleinen Happen nebenher vor sich hin. Je nach DB-Größe und Hw kann das ggf. trotzdem das System blockieren, wenn das DB-Plugin auf Abschluss von Lese-/Schreibvorgängen warten muss...

Wenn wir SQLite betrachten, kann ich mir mit der "one thread only"-Problematik ausschließlich ein Standalone-Tool vorstellen...