@cweitkamp the driver seems to completely mistake the tables.
12/27/2021 11:05:00.526 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=K_TP_R_Current (Type=NumberItem, State=0.252 A, Label=Amperes, Category=null, Tags=[Measurement, Current], Groups=[group_Kitchen])
12/27/2021 11:05:00.531 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=Item23; newTableName=null;
12/27/2021 11:05:00.535 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item K_TP_R_Current
12/27/2021 11:05:00.539 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'K_TP_R_Current' as Type 'NUMBERITEM' in 'Item23' with state '0.252 A'
12/27/2021 11:05:00.543 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '0.252 A'
12/27/2021 11:05:00.547 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE; javaType=class java.lang.Double;
12/27/2021 11:05:00.551 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '0.252'
12/27/2021 11:05:00.555 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO Item23 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='0.252'
12/27/2021 11:05:00.569 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'K_TP_R_Current' as '0.252 A' in SQL database at Sun Dec 27 11:05:00 TRT 2020 in 42 ms.
12/27/2021 11:05:00.573 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=K_TP_R_Voltage (Type=NumberItem, State=224.439 V, Label=Voltage, Category=null, Tags=[Measurement, Voltage], Groups=[group_Kitchen])
12/27/2021 11:05:00.577 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=Item24; newTableName=null;
12/27/2021 11:05:00.581 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item K_TP_R_Voltage
12/27/2021 11:05:00.585 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'K_TP_R_Voltage' as Type 'NUMBERITEM' in 'Item24' with state '224.439 V'
12/27/2021 11:05:00.589 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '224.439 V'
12/27/2021 11:05:00.592 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE; javaType=class java.lang.Double;
12/27/2021 11:05:00.596 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '224.439'
12/27/2021 11:05:00.600 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO Item24 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='224.439'
12/27/2021 11:05:00.614 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'K_TP_R_Voltage' as '224.439 V' in SQL database at Sun Dec 27 11:05:00 TRT 2020 in 42 ms.
12/27/2021 11:05:00.619 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=K_TP_R_Power (Type=NumberItem, State=56.406 W, Label=Watts, Category=null, Tags=[Measurement, Power], Groups=[group_Kitchen])
12/27/2021 11:05:00.624 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=Item25; newTableName=null;
12/27/2021 11:05:00.628 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item K_TP_R_Power
12/27/2021 11:05:00.632 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'K_TP_R_Power' as Type 'NUMBERITEM' in 'Item25' with state '56.406 W'
12/27/2021 11:05:00.641 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '56.406 W'
12/27/2021 11:05:00.644 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE; javaType=class java.lang.Double;
12/27/2021 11:05:00.649 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '56.406'
12/27/2021 11:05:00.653 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO Item25 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='56.406'
12/27/2021 11:05:00.666 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'K_TP_R_Power' as '56.406 W' in SQL database at Sun Dec 27 11:05:00 TRT 2020 in 46 ms.
If you look at the first line, it’s trying to store item K_TP_R_Current, and according to line 2, the table for that item is Item23. However, the correct table is Item2. This is the mapping of tables from the Items table:
MariaDB [lake_home]> SELECT * FROM Items;
+--------+-----------------------------------------------------------+
| ItemId | ItemName |
+--------+-----------------------------------------------------------+
| 1 | K_TP_R_Power |
| 2 | K_TP_R_Current |
| 3 | K_TP_R_Voltage |
| 4 | K_TP_H_Switch |
| 5 | DR_Light_Switch |
| 6 | DR_Light_Color |
| 7 | Weather_OWM_Rain |
| 8 | Weather_OWM_Snow |
| 9 | Weather_OWM_Temperature |
| 10 | LR_Light_Color |
| 11 | LR_Light_Switch |
| 12 | Day_White_Lights_Switch |
| 13 | MBR_Light_Switch |
| 14 | MBR_Light_Color |
| 15 | Night_Yellow_Lights_Switch |
| 16 | systeminfo_computer_openHABianPi_storage_availablePercent |
| 17 | systeminfo_computer_openHABianPi_memory_availablePercent |
| 18 | systeminfo_computer_openHABianPi_cpu_load |
| 19 | Weather_OWM_Humidity |
| 20 | Wake_up_Eylem_Switch |
| 21 | Wake_up_Routine_Switch |
| 22 | Override_Sleep_Mode |
| 23 | Override_Evening_Routines |
| 24 | MBR_Sensor_Motion_LastUpdate |
| 25 | Override_Home_Away |
| 26 | Override_Vacation_Mode |
| 27 | Override_MBR_Sensor_Motion |
| 28 | systeminfo_computer_openHABianPi_cpu_load15 |
| 29 | systeminfo_computer_openHABianPi_network_DataSent |
| 30 | systeminfo_computer_openHABianPi_network_DataReceived |
| 31 | systeminfo_computer_openHABianPi_sensor_CPUtemp |
| 32 | Override_MBR_Sensor_Illuminance_LowThreshold |
| 33 | Override_MBR_Sensor_Illuminance |
| 34 | MBR_Temperature_Threshold_Min |
| 35 | MBR_Temperature_Threshold_Max |
| 36 | Override_Baby_Mode |
| 37 | Override_DR_Automated_Lights |
| 38 | Override_MBR_Automated_Lights |
| 39 | Override_LR_Automated_Lights |
| 40 | Override_Automated_Heating |
| 41 | NBR_Light_Switch |
| 42 | Override_NBR_Automated_Lights |
| 43 | Override_NBR_Sensor_Motion |
| 44 | Override_NBR_Sensor_Illuminance_LowThreshold |
| 45 | Override_NBR_Sensor_Illuminance |
| 46 | K_TP_Heater_Threshold_Min |
| 47 | K_TP_Heater_Threshold_Active |
| 48 | NBR_Light_Color |
| 49 | HW2_Light_Switch |
| 50 | Override_HW2_Automated_Lights |
| 51 | NBR_Temperature_Threshold_Min |
| 52 | NBR_Temperature_Threshold_Max |
| 53 | LR2_Light_Switch |
| 54 | LR2_Light_Color |
+--------+-----------------------------------------------------------+
54 rows in set (0.00 sec)
Next it tries to get K_TP_R_Voltage from Item24, but the correct table is Item3.
This is my jdbc.cfg:
############################ JDBC Persistence Service ##################################
# I N S T A L L J D B C P E R S I S T E N C E S E R V I C E
#
# https://github.com/openhab/openhab/wiki/JDBC-Persistence
#
# Tested databases/url-prefix: jdbc:derby, jdbc:h2, jdbc:hsqldb, jdbc:mariadb, jdbc:mysql, jdbc:postgresql, jdbc:sqlite
#
# derby, h2, hsqldb, sqlite can be embedded,
# If no database is available it will be created, for example the url 'jdbc:h2:./testH2' creates a new DB in OpenHab Folder.
#
# Create new database, for example on a MySQL-Server use:
# CREATE DATABASE 'yourDB' CHARACTER SET utf8 COLLATE utf8_general_ci;
# D A T A B A S E C O N F I G
# Some URL-Examples, 'service' identifies and activates internally the correct jdbc driver.
# required database url like 'jdbc:<service>:<host>[:<port>;<attributes>]'
# url=jdbc:derby:./testDerby;create=true
# url=jdbc:h2:./testH2
# url=jdbc:hsqldb:./testHsqlDb
url=jdbc:mariadb://localhost/lake_home
# url=jdbc:mysql://localhost/lake_home
# url=jdbc:postgresql://192.168.0.1:5432/testPostgresql
# url=jdbc:sqlite:./testSqlite.db
# url=
# required database user
user=XXXXXXX
# required database password
password=XXXXXXXXX
# E R R O R H A N D L I N G
# optional when Service is deactivated (optional, default: 0 -> ignore)
#errReconnectThreshold=
# I T E M O P E R A T I O N S
# optional tweaking SQL datatypes
# see: https://mybatis.github.io/mybatis-3/apidocs/reference/org/apache/ibatis/type/JdbcType.html
# see: http://www.h2database.com/html/datatypes.html
# see: http://www.postgresql.org/docs/9.3/static/datatype.html
# defaults:
#sqltype.CALL = VARCHAR(200)
#sqltype.COLOR = VARCHAR(70)
#sqltype.CONTACT = VARCHAR(6)
#sqltype.DATETIME = DATETIME
#sqltype.DIMMER = TINYINT
#sqltype.LOCATION = VARCHAR(30)
#sqltype.NUMBER = DOUBLE
#sqltype.ROLLERSHUTTER = TINYINT
#sqltype.STRING = VARCHAR(65500)
#sqltype.SWITCH = VARCHAR(6)
# For Itemtype "Number" default decimal digit count (optional, default: 3)
#numberDecimalcount=
# T A B L E O P E R A T I O N S
# Tablename Prefix String (optional, default: "item")
# for Migration from MYSQL-Bundle set to 'Item'.
tableNamePrefix=Item
# Tablename Prefix generation, using Item real names or "item" (optional, default: false -> "item")
# If true, 'tableNamePrefix' is ignored.
tableUseRealItemNames=false
# Tablename Suffix length (optional, default: 4 -> 0001-9999)
# for Migration from MYSQL-Bundle set to 0.
tableIdDigitCount=0
# Rename existing Tables using tableUseRealItemNames and tableIdDigitCount (optional, default: false)
# USE WITH CARE! Deactivate after Renaming is done!
#rebuildTableNames=true
# D A T A B A S E C O N N E C T I O N S
# Some embeded Databases can handle only one Connection (optional, default: configured per database in packet org.openhab.persistence.jdbc.db.* )
# see: https://github.com/brettwooldridge/HikariCP/issues/256
# jdbc.maximumPoolSize = 1
# jdbc.minimumIdle = 1
# T I M E K E E P I N G
# (optional, default: false)
#enableLogTime=true