JDBC persistence puts everything in the same table

DanielMalmgren commented 7 years ago

Expected Behavior

A database with one table per persisted Item.

Current Behavior

A database with one single table named item0000 where values from all persisted items seems to end up. No relevant errors in the log as far as I can find.

Possible Solution

Steps to Reproduce (for bugs)

Configure jdbc.cfg to write into a completely blank database

Context

I used MySQL persistence before, didn't have this problem there. Using the same database (did a complete wipe of it though). The only thing I changed in jdbc.cfg (except for url, user and password) was numberDecimalcount which I set to 1.

Your Environment

Version used: openhab 2.0.0 (installed from official repository), JDBC SQL Persistence 1.9.0
Environment name and version: Oracle Java 8u121
Operating System and version: Debian 8.7

watou commented 7 years ago

Could you add the contents of your jdbc.cfg, redacting any private information?

DanielMalmgren commented 7 years ago

This is the complete file (minus all empty rows and rows starting with #):

url=jdbc:mysql://127.0.0.1:3306/openhab user= password= numberDecimalcount=1

watou commented 7 years ago

@lewie would you have any comments on this?

DanielMalmgren commented 7 years ago

Two details after some investigation of the table (no idea if they're of any use at all):

The value of the table is of type double. Obviously that doesn't match all Item types, for example switches are always persisted as 0.
I said that all persisted Items get in the same table. It seems though that not every change is, only some of them. My settings are to persist on everyChange.

DanielMalmgren commented 7 years ago

Some snippets from debug logging:

`07:30:00.055 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=Motorvarmare_Switch (Type=SwitchItem, State=OFF) 07:30:00.057 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=item0000; newTableName=null; 07:30:00.059 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType SWITCHITEM for Item Motorvarmare_Switch 07:30:00.062 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'Motorvarmare_Switch' as Type 'SWITCHITEM' in 'item0000' with state 'OFF' 07:30:00.064 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: 'OFF' 07:30:00.066 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=VARCHAR(6); javaType=class java.lang.String; 07:30:00.067 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: other: item.getState().toString(): 'OFF' 07:30:00.069 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO item0000 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='OFF' 07:30:00.088 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'Motorvarmare_Switch' as 'OFF' in SQL database at Thu Feb 23 07:30:00 CET 2017 in 33 ms.

09:03:10.423 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=ZWaveMultiSensor_SensorTemperature (Type=NumberItem, State=4.6) 09:03:10.426 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=item0000; newTableName=null; 09:03:10.430 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item ZWaveMultiSensor_SensorTemperature 09:03:10.432 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'ZWaveMultiSensor_SensorTemperature' as Type 'NUMBERITEM' in 'item0000' with state '4.6' 09:03:10.434 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '4.6' 09:03:10.435 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE; javaType=class java.lang.Double; 09:03:10.437 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '4.6' 09:03:10.459 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO item0000 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='4.6' 09:03:10.476 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'ZWaveMultiSensor_SensorTemperature' as '4.6' in SQL database at Thu Feb 23 09:03:10 CET 2017 in 54 ms. `

9037568 commented 7 years ago

You should really upload the full log.

DanielMalmgren commented 7 years ago

Doh. I thought that in order to really be able to give you a clean good log I'd simply stop the persistence bundle, clear the database and start the bundle again to get exactly what happened when trying to create the tables in the log. Only this time it worked... I guess something must have gone wrong when it first tried to make the tables. Do you want to dig deeper into it or should we just close the issue?

9037568 commented 7 years ago

I think we should hold this open a while longer and see what the database/persistence expert can grok from it. He might have a theory as to what went wrong.

DanielMalmgren commented 7 years ago

Righty then. I did some log diggging and realized that I actually had debug logging on for jdbc when I first activated it, so there's quite much interesting in the log. My best guess is that (for some reason) it failed creating the "items" table which then led to the other problems. Pasted relevant part of log here: http://pastebin.com/raw/wZ5h2pqn

lewie commented 7 years ago

@DanielMalmgren, groked now... ;-) Will see if it helps.

What means

did a complete wipe of it though

Dir you delete all Tables? Or do you want to migrate from Mysql bundle with empty tables?

As I can see, from

JDBC::query: unable to find table for query, no data in database for item 'testItem'. Current number of tables in the database: 3

After that line anything is wrong in your Database Layout (see mine at the end)

Your configuration seams perfect. But a item0000-table never is generated by openHAB jdbc! First Table is a Table named items (where all Item names are stored). second table is named item0001 for testItem-values with ItemId 1 associated in items-table. (with tableUseRealItemNames=true in jdbc.cfg second table is named testitem_0001)

I installed a new OH2 with two Items used new Database with Collation utf8_general_ci:

items: Number testItem "testItem" and Switch testSwitch "testSwitch"
same jdbc.cfg
jdbc.persist with testItem,testSwitch : strategy = everyChange
rules witch changes these two items:
```
var Number initCount = 0
```

rule "testItem Count up" when Time cron "0/30 ?" then initCount = initCount +1 postUpdate(testItem, initCount) println("initCount = "+initCount) end

rule "testSwitch sw" when Time cron "0/35 ?" then if ( testSwitch.state == OFF) { postUpdate(testSwitch, ON ) }else{ postUpdate(testSwitch, OFF ) } println("testSwitch.state = "+testSwitch.state) end


*items*-table:
![grafik](https://cloud.githubusercontent.com/assets/5707426/23587473/118c30a4-01ad-11e7-9cae-2e7bfdd800a4.png)

*testItem*-table -> Number
![grafik](https://cloud.githubusercontent.com/assets/5707426/23587476/2be892bc-01ad-11e7-8715-4701db29099c.png)

*testSwitch*-table -> Switch
![grafik](https://cloud.githubusercontent.com/assets/5707426/23587479/3b4d1d9a-01ad-11e7-9f12-e0c1640c8950.png)

2017-03-05 14:05:20.384 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'testItem.items' 2017-03-05 14:05:20.490 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'jdbc.persist' 2017-03-05 14:05:21.350 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'testitem.rules' 2017-03-05 14:05:23.069 [INFO ] [.dashboard.internal.DashboardService] - Started dashboard at /start 2017-03-05 14:05:23.329 [INFO ] [basic.internal.servlet.WebAppServlet] - Started Basic UI at /basicui/app 2017-03-05 14:05:23.530 [INFO ] [arthome.ui.paper.internal.PaperUIApp] - Started Paper UI at /paperui 2017-03-05 14:05:23.554 [INFO ] [panel.internal.HABPanelDashboardTile] - Started HABPanel at /habpanel 2017-03-05 14:05:23.638 [DEBUG] [org.openhab.persistence.jdbc ] - BundleEvent STARTING - org.openhab.persistence.jdbc 2017-03-05 14:05:23.638 [DEBUG] [org.openhab.persistence.jdbc ] - BundleEvent STARTED - org.openhab.persistence.jdbc 2017-03-05 14:05:23.645 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::setItemRegistry 2017-03-05 14:05:23.646 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::activate: persistence service activated 2017-03-05 14:05:23.646 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::updateConfig 2017-03-05 14:05:23.649 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::JdbcConfiguration 2017-03-05 14:05:23.649 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: configuration size = 11 2017-03-05 14:05:23.652 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: user=lewie 2017-03-05 14:05:23.652 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: password exists? true 2017-03-05 14:05:23.653 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: url=jdbc:mysql://192.168.0.181:3306/ItemTypeTest4 2017-03-05 14:05:23.653 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: found serviceName = 'mysql' 2017-03-05 14:05:23.653 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: Init Data Access Object Class: 'org.openhab.persistence.jdbc.db.JdbcMysqlDAO' 2017-03-05 14:05:23.688 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::initSqlTypes: Initialize the type array 2017-03-05 14:05:23.702 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::initSqlQueries: 'JdbcMysqlDAO' 2017-03-05 14:05:23.704 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initSqlTypes: Initialize the type array 2017-03-05 14:05:23.704 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initSqlQueries: 'JdbcMysqlDAO' 2017-03-05 14:05:23.704 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: dBDAO ClassName=org.openhab.persistence.jdbc.db.JdbcMysqlDAO 2017-03-05 14:05:23.705 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: numberDecimalcount=1 2017-03-05 14:05:23.705 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableUseRealItemNames=false 2017-03-05 14:05:23.705 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: enableLogTime false 2017-03-05 14:05:23.713 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: load JDBC-driverClass was successful: 'com.mysql.jdbc.Driver' 2017-03-05 14:05:23.713 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: configuration complete. service=jdbc 2017-03-05 14:05:23.713 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB 2017-03-05 14:05:23.713 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true 2017-03-05 14:05:23.713 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource 2017-03-05 14:05:24.452 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::setDbConnected true 2017-03-05 14:05:24.452 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initAfterFirstDbConnection: Initializing step, after db is connected. 2017-03-05 14:05:24.561 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbMajorVersion = '5' 2017-03-05 14:05:24.562 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbMinorVersion = '7' 2017-03-05 14:05:24.564 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - driverMajorVersion = '5' 2017-03-05 14:05:24.564 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - driverMinorVersion = '1' 2017-03-05 14:05:24.564 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbProductName = 'MySQL' 2017-03-05 14:05:24.564 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbProductVersion = '5.7.17' 2017-03-05 14:05:24.565 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB asking db for name as absolutely first db action, after connection is established. 2017-03-05 14:05:24.705 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true 2017-03-05 14:05:24.707 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createItemsTableIfNot 2017-03-05 14:05:24.708 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doCreateItemsTableIfNot sql=CREATE TABLE IF NOT EXISTS items (ItemId INT NOT NULL AUTO_INCREMENT,itemname VARCHAR(500) NOT NULL,PRIMARY KEY (ItemId)) 2017-03-05 14:05:24.797 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getItemIDTableNames 2017-03-05 14:05:24.797 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetItemIDTableNames sql=SELECT itemid, itemname FROM items 2017-03-05 14:05:24.801 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service. 2017-03-05 14:05:24.802 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::updateConfig: configuration complete for service=jdbc. 2017-03-05 14:05:24.802 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service. 2017-03-05 14:05:24.804 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service. 2017-03-05 14:05:24.805 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service. 2017-03-05 14:05:24.805 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service. 2017-03-05 14:05:24.805 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service. 2017-03-05 14:05:24.805 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service. 2017-03-05 14:05:24.833 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service. 2017-03-05 14:05:24.834 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service. 2017-03-05 14:05:24.836 [DEBUG] [org.openhab.persistence.jdbc ] - ServiceEvent REGISTERED - {org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService}={url=jdbc:mysql://192.168.0.181:3306/ItemTypeTest4, component.name=org.openhab.persistence.jdbc, tableUseRealItemNames=false, service.config.category=persistence, service.config.description.uri=persistence:jdbc, user=lewie, service.pid=org.openhab.jdbc, service.config.label=JDBC Persistence, component.id=158, numberDecimalcount=1, password=VRCLq7ED9GQJ7xEy, service.id=287, service.bundleid=193, service.scope=bundle} - org.openhab.persistence.jdbc 2017-03-05 14:05:30.672 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=testItem (Type=NumberItem, State=1) 2017-03-05 14:05:30.673 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: no table found for item 'testItem' in sqlTables 2017-03-05 14:05:30.673 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createNewEntryInItemsTable 2017-03-05 14:05:30.673 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doCreateNewEntryInItemsTable sql=INSERT INTO items (ItemName) VALUES ('testItem') 2017-03-05 14:05:30.688 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: getTableName with rowId=1 itemName=testItem 2017-03-05 14:05:30.688 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item testItem 2017-03-05 14:05:30.689 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=item0001; newTableName=testItem; 2017-03-05 14:05:30.690 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createItemTable 2017-03-05 14:05:30.776 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: Table created for item 'testItem' with dataType DOUBLE in SQL database. 2017-03-05 14:05:30.776 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=item0001; newTableName=null; 2017-03-05 14:05:30.776 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item testItem 2017-03-05 14:05:30.777 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'testItem' as Type 'NUMBERITEM' in 'item0001' with state '1' 2017-03-05 14:05:30.777 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '1' 2017-03-05 14:05:30.777 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE; javaType=class java.lang.Double; 2017-03-05 14:05:30.777 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '1.0' 2017-03-05 14:05:30.777 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO item0001 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='1.0' 2017-03-05 14:05:30.790 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'testItem' as '1' in SQL database at Sun Mar 05 14:05:30 CET 2017 in 118 ms. 2017-03-05 14:05:35.009 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=testSwitch (Type=SwitchItem, State=OFF) 2017-03-05 14:05:35.009 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: no table found for item 'testSwitch' in sqlTables 2017-03-05 14:05:35.009 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createNewEntryInItemsTable 2017-03-05 14:05:35.009 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doCreateNewEntryInItemsTable sql=INSERT INTO items (ItemName) VALUES ('testSwitch') 2017-03-05 14:05:35.032 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: getTableName with rowId=2 itemName=testSwitch 2017-03-05 14:05:35.032 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType SWITCHITEM for Item testSwitch 2017-03-05 14:05:35.032 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=item0002; newTableName=testSwitch; 2017-03-05 14:05:35.032 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createItemTable 2017-03-05 14:05:35.103 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: Table created for item 'testSwitch' with dataType VARCHAR(6) in SQL database. 2017-03-05 14:05:35.104 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=item0002; newTableName=null; 2017-03-05 14:05:35.104 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType SWITCHITEM for Item testSwitch 2017-03-05 14:05:35.104 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'testSwitch' as Type 'SWITCHITEM' in 'item0002' with state 'OFF' 2017-03-05 14:05:35.104 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: 'OFF' 2017-03-05 14:05:35.104 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=VARCHAR(6); javaType=class java.lang.String; 2017-03-05 14:05:35.104 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: other: item.getState().toString(): 'OFF' 2017-03-05 14:05:35.104 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO item0002 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='OFF' 2017-03-05 14:05:35.110 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'testSwitch' as 'OFF' in SQL database at Sun Mar 05 14:05:35 CET 2017 in 101 ms. 2017-03-05 14:06:00.004 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=testItem (Type=NumberItem, State=2) 2017-03-05 14:06:00.005 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=item0001; newTableName=null; 2017-03-05 14:06:00.005 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item testItem 2017-03-05 14:06:00.005 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'testItem' as Type 'NUMBERITEM' in 'item0001' with state '2' 2017-03-05 14:06:00.005 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '2' 2017-03-05 14:06:00.005 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE; javaType=class java.lang.Double; 2017-03-05 14:06:00.005 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '2.0' 2017-03-05 14:06:00.005 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO item0001 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='2.0' 2017-03-05 14:06:00.017 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'testItem' as '2' in SQL database at Sun Mar 05 14:06:00 CET 2017 in 12 ms. 2017-03-05 14:06:00.021 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=testSwitch (Type=SwitchItem, State=ON) 2017-03-05 14:06:00.022 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=item0002; newTableName=null; 2017-03-05 14:06:00.022 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType SWITCHITEM for Item testSwitch 2017-03-05 14:06:00.022 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'testSwitch' as Type 'SWITCHITEM' in 'item0002' with state 'ON' 2017-03-05 14:06:00.022 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: 'ON' 2017-03-05 14:06:00.022 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=VARCHAR(6); javaType=class java.lang.String; 2017-03-05 14:06:00.024 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: other: item.getState().toString(): 'ON' 2017-03-05 14:06:00.024 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO item0002 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='ON' 2017-03-05 14:06:00.036 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'testSwitch' as 'ON' in SQL database at Sun Mar 05 14:06:00 CET 2017 in 15 ms.

DanielMalmgren commented 7 years ago

I did a drop database followed by a create database, so it was really completely empty.

What I'm thinking is that I might have been stupid enough to first change default persistence to jdbc and then wiping the database right when it was in the middle of initializing stuff. Could that have caused this?

I haven't looked at the code, but it kinda feels like an int variable was supposed to hold the number of the table to put values in and this variable never got initialized and thus was always zero (causing the item0000).

lewie commented 7 years ago

@DanielMalmgren,

What I'm thinking is that I might have been stupid enough to first change default persistence to jdbc and then wiping the database right when it was in the middle of initializing stuff. Could that have caused this?

Yes definitely!

Do this now:

Stop your OH2 or uninstall JDBC-Mysql binding over PaperUI. (No configuration is lost!)
Delete all Tables or re-create database
Start OH2 or reinstall JDBC-Mysql binding over PaperUI.

Then everything should init right.

DanielMalmgren commented 7 years ago

Well, if you read my comment (number eight from the top) that's about what I already did, so the problem is already solved for me :-) This issue is still open only because of the possibility of finding a bug somewhere...

lewie commented 7 years ago

@DanielMalmgren, OK yes,

hmm, to solve this, jdbc bundle would have to delete other tables if items-table does not exist. Think about to catch this. Thanks.

openhab / openhab1-addons