@nelson.aponte Can you provide a DEBUG log? Would be nice to know which value the service tries to cast and to which item type it belongs to. Thanks.
@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=trueShould I fill an issue for this?
Sry, I was completely stumped. Now I got it. You migrated from old MYSQL persistence bundle to JDBC/MariaDB persistence addon. Is that correct? And I have to admit that I have no clue how that is working except from what I can see from the config file.
One thing I notices is that your table is called “Items” (notice the capital “I” at the beginning) where as the JDBC binding tries to read the item table matching from “items” (notice the lower “i” at the beginning). This value/name is hard-coded and cannot be changed like prefix for the table name for the items themselves. Can you try to rename your table?
I’m just driving by this post & a novice, but a long time ago I downloaded the mariaDB because the mysql was no longer available (as I recall) and had problems. The trick turned out to be not use jdbc:mariadb:, but stick with jdbc:mysql as you had before.
More recently on the upgrade from 2.5 to 3.0, the items in my tables were renumbered, so you might want to check that also. I use the mysql workbench. Edit for clarity Item1 became item0001. Could be the capitalization mentioned above
Hope that helps.
That’s right. I wasn’t using the JDBC persistence add-on on OH 2.5, I was using the standard MySQL one, and I was forced to use JDBC on OH 3.0.
I never meant to install MariaDB, I don’t remember if I used the official MySQL repository or some default repository on OpenHABian, but somewhere along OH 2.x I realized that I didn’t have exactly MySQL installed, but MariaDB. Nonetheless, I decided to stay with the MySQL persistence add-on, instead of implementing JDBC-MariaDB.
On OH 3.0 I tried both but JDBC-MySQL couldn’t connect to the DB, so I had to stick to JDBC-MariaDB.
This is my list of tables;
MariaDB [lake_home]> SHOW TABLES;
+---------------------+
| Tables_in_lake_home |
+---------------------+
| Item1 |
| Item10 |
| Item11 |
| Item12 |
| Item13 |
| Item14 |
| Item15 |
| Item16 |
| Item17 |
| Item18 |
| Item19 |
| Item2 |
| Item20 |
| Item21 |
| Item22 |
| Item23 |
| Item24 |
| Item25 |
| Item26 |
| Item27 |
| Item28 |
| Item29 |
| Item3 |
| Item30 |
| Item31 |
| Item32 |
| Item33 |
| Item34 |
| Item35 |
| Item36 |
| Item37 |
| Item38 |
| Item39 |
| Item4 |
| Item40 |
| Item41 |
| Item42 |
| Item43 |
| Item44 |
| Item45 |
| Item46 |
| Item47 |
| Item48 |
| Item49 |
| Item5 |
| Item50 |
| Item51 |
| Item52 |
| Item53 |
| Item54 |
| Item6 |
| Item7 |
| Item8 |
| Item9 |
| Items |
| items |
+---------------------+
Apparently JDBC created a table called “items”.
Where did you see that? I still can’t see it in any of the things that I shared.
I will try to purge all the trash JDBC created, change the name of the items table for Items and see if it works. I’ll keep you posted.
This thread explained a lot of things for me, so thanks.
I’m running OH3 next to OH2.5 where I wanted to check if connection is still working.
OH2.5 is read/write, OH3 only read.
But I see that new tables are created (without capital i) and populated.
Are all items now persisted? I must have missed that part of information.
That explains why your item / table mapping is broken. When you compare the datasets in both tables (“Items” and “items”) you probably will see all your items but with different table ids.
In the code for the JDBC persistence service. The name is hard-nosed and cannot be changed by the user.
@BeanzBE Yes, OH3 ships a default persistence for all items (RRD4J).
Happy New Year guys.
Yes, all the items had a completely different order in the “items” table, so I gave it a different name and then renamed the “Items” table to “items” and it works now. I can even see graphs of the data from OH 2.5 on the MainUI.
Of course, I did the renaming after deleting all the trash data JDBC could persist in the wrong tables.
All Items are being correctly persisted now.
Nontheless, this seems like a bug to me. Typo? Maybe users should be able to define the name of the table in the jdbc.config file. What do you think @cweitkamp?
I think that might be a good idea. At least for user like you who have to switch from MySQL to JDBC persistence. Let’s file an issue on GitHub to not forget it.
Beside that it would be very nice to provide a short summary for others to wrap up how the migration works step by step. May I ask you to write a short how-to?
Happy New Year guys.
I don’t want some items to be saved in mysql. how can I do that?
Define your own persistence strategies and exclude whatever item you do not want to persist. JDBC by default persists all values in change.
Meaning that if there is no .persist file, everything is saved.
When the file is present, only that is applied?
If so, good to know 
Can I help by adding this to documentation somewhere?
I have a file called mysql.persist but the others are kept. Mysql is also stored for those not on this list.
Let’s specify this a little bit. If there is no textual configuration file for a persistence service it delivers its own default strategy. If you do not like it you have to define you own strategies in separate files - one file for each persistence service installed.
I apologize, I’m a little inexperienced in this matter. I will be glad if you explain in detail.
@haybolat Before we try to dig deeper please tell us which OH version you are using and which persistence service. MySQL or JDBC? If your are using JDBC than you .persist file has a wrong name.
I hope I answered your question.
Yes, everything. Thanks.
… and rename
persistence/mysql.persisttopersistence/jdbc.persist…
Can you give it a try?
Yes, thank you very much.