nignac
October 22, 2023, 1:44pm
1
Hey there,
openHAB 3.4.5 running in docker with mariaDB 10.5 as persistence service.
I’ve had some Group items, which won’t update after a member of that group received an update. I found out if MariaDB was busy, then my Group items won’t update at all.
Therefore I’ve optimized my MariaDB Container (increased InnoDB Buffer size, etc.). Now everything works well.
I did some housekeeping to my database and if i’am running “openhab:jdbc schema check” and “openhab:jdbc schema fix” it is still complaining about some items.
What I’m missing?
openhab> openhab:jdbc schema check | head -n 5
Table Item Issue
------------------------------------------------------------- -------------------------------------------------------- ----------------------------------------------------------------
anwesenheit_0947 Anwesenheit Column 'time' expected to be NOT NULL, but is nullable
anwesenheit_guest_1944 Anwesenheit_Guest Column 'time' expected to be NOT NULL, but is nullable
anwesenheit_guest_sensor_2168 Anwesenheit_Guest_Sensor Column 'time' expected to be NOT NULL, but is nullable
openhab> openhab:jdbc schema fix | head -n 5
Fixed table 'anwesenheit_0947' for item 'Anwesenheit'
Fixed table 'anwesenheit_guest_1944' for item 'Anwesenheit_Guest'
Fixed table 'anwesenheit_guest_sensor_2168' for item 'Anwesenheit_Guest_Sensor'
openhab> openhab:jdbc schema check | head -n 5
Table Item Issue
------------------------------------------------------------- -------------------------------------------------------- ----------------------------------------------------------------
anwesenheit_0947 Anwesenheit Column 'time' expected to be NOT NULL, but is nullable
anwesenheit_guest_1944 Anwesenheit_Guest Column 'time' expected to be NOT NULL, but is nullable
anwesenheit_guest_sensor_2168 Anwesenheit_Guest_Sensor Column 'time' expected to be NOT NULL, but is nullable
laursen
October 22, 2023, 2:54pm
2
It looks like a possible bug. Can you try again with debug logging?
log:set debug org.openhab.persistence.jdbc
And when you are done:
log:set default org.openhab.persistence.jdbc
nignac
October 22, 2023, 4:46pm
3
laursen:
log:set debug org.openhab.persistence.jdbc
I’ve had a short look in phpmyadmin. The time column is NOT NULL, but schema fix shows nullable.
openhab:jdbc schema fix Anwesenheit:
2023-10-22 18:32:28.245 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTableColumns
2023-10-22 18:32:28.248 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doGetTableColumns sql=SELECT column_name, column_type, is_nullable FROM information_schema.columns WHERE table_schema='openhab3-db' AND table_name='anwesenheit_0947'
2023-10-22 18:32:28.252 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::alterTableColumn
2023-10-22 18:32:28.253 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doAlterTableColumn sql=ALTER TABLE anwesenheit_0947 MODIFY COLUMN time TIMESTAMP(3) NOT NULL
2023-10-22 18:32:28.286 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'Xiaomi_Temp_Sensor_Humidty' as '44300000000000000000000000000000000000 %' in SQL database at Sun Oct 22 18:32:28 CEST 2023 in 307 ms.
2023-10-22 18:32:28.454 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=Vacuum_CustomizedCleanMode (Type=StringItem, State="unknown_method", Label=Customized Clean Mode, Category=null, Tags=[Point], Groups=[Group_Vacuum, Group_Persistence]) state="unknown_method" date=null
2023-10-22 18:32:28.456 [DEBUG] [persistence.jdbc.internal.dto.ItemVO] - JDBC:ItemVO tableName=vacuum_customizedcleanmode_0105; newTableName=null;
2023-10-22 18:32:28.456 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'Vacuum_CustomizedCleanMode' as Type 'STRINGITEM' in 'vacuum_customizedcleanmode_0105' with state '"unknown_method"'
2023-10-22 18:32:28.457 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: itemState: '"unknown_method"'
2023-10-22 18:32:28.457 [DEBUG] [persistence.jdbc.internal.dto.ItemVO] - JDBC:ItemVO setValueTypes dbType=VARCHAR(16255); javaType=class java.lang.String;
2023-10-22 18:32:28.458 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: other: itemState: '"unknown_method"'
2023-10-22 18:32:28.458 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO vacuum_customizedcleanmode_0105 (time, value) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='"unknown_method"'
2023-10-22 18:32:28.780 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'Vacuum_CustomizedCleanMode' as '"unknown_method"' in SQL database at Sun Oct 22 18:32:28 CEST 2023 in 326 ms.
2023-10-22 18:32:28.929 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=Anwesenheit_Niklas (Type=SwitchItem, State=ON, Label=Anwesenheitsstatus Niklas, Category=null, Groups=[Group_Persistence, Anwesenheit]) state=ON date=null
2023-10-22 18:32:28.930 [DEBUG] [persistence.jdbc.internal.dto.ItemVO] - JDBC:ItemVO tableName=anwesenheit_niklas_0916; newTableName=null; ```
openhab:jdbc schema check:
```yaml
2023-10-22 18:36:36.908 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: itemState: '100'
2023-10-22 18:36:36.908 [DEBUG] [persistence.jdbc.internal.dto.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE; javaType=class java.lang.Double;
2023-10-22 18:36:36.909 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '100.0'
2023-10-22 18:36:36.909 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO aqara_motion_sensor2_battery_0480 (time, value) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='100.0'
2023-10-22 18:36:36.985 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTableColumns
2023-10-22 18:36:36.989 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doGetTableColumns sql=SELECT column_name, column_type, is_nullable FROM information_schema.columns WHERE table_schema='openhab3-db' AND table_name='anwesenheit_0947'
2023-10-22 18:36:36.994 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTableColumns
2023-10-22 18:36:36.995 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doGetTableColumns sql=SELECT column_name, column_type, is_nullable FROM information_schema.columns WHERE table_schema='openhab3-db' AND table_name='anwesenheit_guest_1944'
2023-10-22 18:36:37.000 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTableColumns
2023-10-22 18:36:37.001 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doGetTableColumns sql=SELECT column_name, column_type, is_nullable FROM information_schema.columns WHERE table_schema='openhab3-db' AND table_name='anwesenheit_guest_sensor_2168'
2023-10-22 18:36:37.005 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTableColumns
2023-10-22 18:36:37.005 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doGetTableColumns sql=SELECT column_name, column_type, is_nullable FROM information_schema.columns WHERE table_schema='openhab3-db' AND table_name='anwesenheit_guest_timer_1943'
2023-10-22 18:36:37.010 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTableColumns
laursen
October 22, 2023, 8:55pm
4
If you run:
SELECT column_name, column_type, is_nullable
FROM information_schema.columns
WHERE table_schema='openhab3-db' AND table_name='anwesenheit_0947'
what is the result?
