Ik heb wat problemen met de MariaDB database helaas... Zal komen door de update van gisteren naar 2
022.3
Ik draai HA met MariaDB en influxDB voor de lange termijn data.
Ik heb al e.e.a. geprobeerd, eerst zag ik deze fout voorbij komen:
09:16:56 – (WARNING) Recorder
TemplateError('ValueError: Template error: int got invalid input 'unknown' when rendering template '{{ states('sensor.sum_power_east_south_west') | int }}' but no default was specified') while processing template 'Template("{{ states('sensor.sum_power_east_south_west') | int }}")' for attribute '_attr_native_value' in entity 'sensor.solar_total'
Die heb ik opgelost, fout is verdwenen.
In de States table zat een fout, helaas werkt Repair table niet (Note The storage engine for the table doesn't support repair), maar na een optimize was de fout er ook uit: Status OK op de States table.
Ook kan ik niet de individuele entry's verwijderen van bovengenoemde sensor.sum_power_east_south_west waar de state unknown of unavailable is ipv een int. Dan krijg ik: #1451 - Cannot delete or update a parent row: a foreign key constraint fails (`homeassistant`.`states`, CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`)). Geen idee of dit uberhaupt een probleem kan vormen?
In 2e instantie voor de zekerheid maar allemaal ge-edit naar
0. Echter kon ik daarna geen optimize meer uitvoeren:
Note Table does not support optimize, doing recreate + analyze instead
Error Got error 1
06 "Socket is connected" from storage engine InnoDB
Status Operation failed
Dan maar weer een reboot, en krijg ik dit:
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
| Database is about to upgrade from schema version: 30 to: 33
Logger: homeassistant.components.recorder.migration
Source: components/recorder/migration.py:154
Integration: Recorder (documentation, issues)
First occurred: 11:46:04 (12 occurrences)
Last logged: 11:46:05
Adding index `ix_events_event_type_time_fired_ts` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
Index ix_events_event_type_time_fired_ts already exists on events, continuing
Adding index `ix_states_entity_id_last_updated_ts` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
Index ix_states_entity_id_last_updated_ts already exists on states, continuing
Adding index `ix_states_last_updated_ts` to database. Note: this can take several minutes on large databases and slow computers. Please be patient!
Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:130
Integration: Recorder (documentation, issues)
First occurred: 11:50:13 (1 occurrences)
Last logged: 11:50:13
Error executing query: (MySQLdb.OperationalError) (1034, "Index for table 'states' is corrupt; try to repair it") [SQL: UPDATE states set last_updated_ts=IF(last_updated is NULL,0,UNIX_TIMESTAMP(CONVERT_TZ(last_updated,'+00:00',@@global.time_zone)) ), last_changed_ts=UNIX_TIMESTAMP(CONVERT_TZ(last_changed,'+00:00',@@global.time_zone)) where last_updated_ts is NULL LIMIT 250000;] (Background on this error at: https://sqlalche.me/e/14/e3q8)
Logger: homeassistant.components.recorder.core
Source: components/recorder/migration.py:993
Integration: Recorder (documentation, issues)
First occurred: 11:50:13 (1 occurrences)
Last logged: 11:50:13
Database error during schema migration
Traceback (most recent call last):
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)
MySQLdb.OperationalError: (1034, "Index for table 'states' is corrupt; try to repair it")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 742, in _migrate_schema_and_setup_run
migration.migrate_schema(
File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 154, in migrate_schema
_apply_update(hass, engine, session_maker, new_version, current_version)
File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 856, in _apply_update
_migrate_columns_to_timestamp(session_maker, engine)
File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 993, in _migrate_columns_to_timestamp
result = session.connection().execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/future/engine.py", line 280, in execute
return self._execute_20(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
return connection._execute_clauseelement(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
ret = self._execute_context(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
util.raise_(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1034, "Index for table 'states' is corrupt; try to repair it")
[SQL: UPDATE states set last_updated_ts=IF(last_updated is NULL,0,UNIX_TIMESTAMP(CONVERT_TZ(last_updated,'+00:00',@@global.time_zone)) ), last_changed_ts=UNIX_TIMESTAMP(CONVERT_TZ(last_changed,'+00:00',@@global.time_zone)) where last_updated_ts is NULL LIMIT 250000;]
(Background on this error at: https://sqlalche.me/e/14/e3q8) |
Alle tabellen muv States geven een Status OK met een check table.
Wat kan ik nu het beste doen. Kan ik niet simpelweg de hele States tabel verwijderen? Wordt die automatisch weer aangemaakt? Of ben ik dan de long-term-history kwijt? Dat zou ik echt willen voorkomen indien mogelijk.
Ik heb dagelijkse backups beschikbaar, dus daar kan ik ook nog wat mee. Iemand die me weer op weg kan helpen?