JDBC persistence puts everything in the same table

[DanielMalmgren]

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)

1. 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]

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

[DanielMalmgren]

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]

@lewie would you have any comments on this?

[DanielMalmgren]

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

1. 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.

2. 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]

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]

You should really upload the full log.

[DanielMalmgren]

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]

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]

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]

@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:

testItem-table -> Number

testSwitch-table -> Switch

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]

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]

@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:

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

Then everything should init right.

[DanielMalmgren]

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]

@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.