JDBC Persistence Service missing in 1.7

gerryK · December 14, 2015, 7:32pm

I used the driver as you suggested already because jdbc persistence was complaining about the version. The database folder does also exsist in my openhab directory. Specified user has also rw access rights to folder and file. I also would assume that the log would tell me if either of those settings were wrong. Does the binding report if it creates a new database if it doesn’t exist?
Would I see a log entry if it wrote to the db?

I am running openhab on raspbian jessey with Java 8 and SQLite3 btw. At the moment I manually start openhab with sudo so access rights should be fine.

lewie · December 14, 2015, 8:58pm

22:26:41.233 [DEBUG] [o.o.p.jdbc.internal.JdbcMapper:33   ] - JDBC::pingDB

Then database is created/connected. Read works then. For writing in DB, see next paragraphs.

Later when an Item should be written how defined in ./configurations/persistence/jdbc.persist like

Items {    *     : strategy = everyChange, restoreOnStartup }

You should see debug messages like:

21:40:40.927 [DEBUG] [o.o.p.jdbc.internal.JdbcMapper:120  ] - JDBC::storeItemValue: Item=Mitte_Temperature (Type=NumberItem, State=23.12)
21:40:40.927 [DEBUG] [.persistence.jdbc.model.ItemVO:38   ] - JDBC:ItemVO tableName=mitte_temperature_0023; newTableName=null; 
21:40:40.927 [DEBUG] [ersistence.jdbc.db.JdbcBaseDAO:478  ] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item Mitte_Temperature
21:40:40.927 [DEBUG] [ersistence.jdbc.db.JdbcBaseDAO:341  ] - JDBC::storeItemValueProvider: item 'Mitte_Temperature' as Type 'NUMBERITEM' in 'mitte_temperature_0023' with state '23.12'
21:40:40.928 [DEBUG] [ersistence.jdbc.db.JdbcBaseDAO:348  ] - JDBC::storeItemValueProvider: getState: '23.12'
21:40:40.928 [DEBUG] [.persistence.jdbc.model.ItemVO:47   ] - JDBC:ItemVO setValueTypes dbType=DOUBLE PRECISION; javaType=class java.lang.Double;
21:40:40.928 [DEBUG] [ersistence.jdbc.db.JdbcBaseDAO:355  ] - JDBC::storeItemValueProvider: newVal.doubleValue: '23.12'
21:40:40.928 [DEBUG] [.o.p.jdbc.db.JdbcPostgresqlDAO:146  ] - JDBC::doStoreItemValue sql=INSERT INTO mitte_temperature_0023 (TIME, VALUE) VALUES( NOW(), CAST( ? as DOUBLE PRECISION) ) value='23.12'
21:40:40.933 [DEBUG] [o.p.j.i.JdbcPersistenceService:228  ] - JDBC: Stored item 'Mitte_Temperature' as '23.12' in SQL database at Mon Dec 14 21:40:40 CET 2015 in 6ms.

Every read/write is documented in debug mode.
If something goes wrong you get a mass of error messages!

“jessey with Java 8” my preferred environment, raspbian I don’t use yet.

Hope this helps.

lewie · December 14, 2015, 9:27pm

@watou, I am absolutely in your opinion!

I would move to debug:

22:26:41.227 [WARN ] [p.j.internal.JdbcConfiguration:236  ] - JDBC::updateConfig: try to load JDBC-driverClass: 'org.sqlite.JDBC'

I would move to INFO or let it stay in DEBUG: INFO, because it is only on startup and it shows, that the right driver could be loaded.

22:26:41.229 [DEBUG] [p.j.internal.JdbcConfiguration:239  ] - JDBC::updateConfig: load JDBC-driverClass was successful: 'org.sqlite.JDBC'

I would move to debug:

22:26:41.237 [WARN ] [o.o.p.jdbc.internal.JdbcMapper:155  ] - JDBC::openConnection: setupDataSource.

gerryK · December 16, 2015, 9:04am

I have still no luck. I only get the already posted messages. Nothing indicating a write to the database nor massive error messages.

jdbc.persist

Strategies {
	every10sec : "0/15 * * * * ?"
	default = everyChange
}

Items {
	* : strategy = everyChange, restoreOnStartup
	currentTempOutside : strategy = every10sec
}

currentTempOutside is a Yahoo binding which gets updated every 15s. And I can see those updates in the log.

Any other bindings I need to install? At the moment there are following in my addons folder:

org.openhab.binding.exec-1.8.0-SNAPSHOT.jar
org.openhab.binding.http-1.8.0-SNAPSHOT.jar
org.openhab.binding.ntp-1.8.0-SNAPSHOT.jar
org.openhab.binding.weather-1.8.0-SNAPSHOT.jar
org.openhab.binding.wemo-1.8.0-SNAPSHOT.jar
org.openhab.io.dropbox-1.8.0-SNAPSHOT.jar
org.openhab.io.gpio-1.8.0-SNAPSHOT.jar
org.openhab.persistence.exec-1.8.0-SNAPSHOT.jar
org.openhab.persistence.jdbc-1.8.0-SNAPSHOT.jar
sqlite-jdbc-3.8.11.2.jar

lewie · December 16, 2015, 10:06am

Did you set persistence:default=jdbc in openhab.cfg?

Did you test if runtime 1.8.0 with demo configuration 1.8.0 works on raspbian?

Do not see any problems.

gerryK · December 16, 2015, 10:48am

Interesting. I get following warning with the demo setup:

2015-12-16 23:41:00.230 [WARN ] [.o.c.p.e.PersistenceExtensions] - There is no queryable persistence service registered with the name 'jdbc'

The persistence file is at its place and also it is configured in openhab.cfg. Driver and binding are in addons.

Is it a problem if openhab is not in its default dir /opt/openhab ?
I will try a vanilla raspbian tomorrow. Maybe there is a pack missing.

lewie · December 17, 2015, 5:42am

If you get this line, jdbc-bundle is definitely started.
If openHAB normally asks for its name, it will be registered as persistence service with the name ‘jdbc’.

Do you really get

22:26:41.233 [DEBUG] [o.o.p.jdbc.internal.JdbcMapper:33   ] - JDBC::pingDB

AND in the same start-log

2015-12-16 23:41:00.230 [WARN ] [.o.c.p.e.PersistenceExtensions] - There is no 
queryable persistence service registered with the name 'jdbc'

I guess now is a different problem.

gerryK · December 17, 2015, 10:21am

So, tested it on a freshly installed pi with jessey and downloaded OH 1.8 snapshot and installed the demo setup. Same as before. Nothing gets written to database nor is a database created if there isn’t any. And yes I get

22:26:41.233 [DEBUG] [o.o.p.jdbc.internal.JdbcMapper:33 ] - JDBC::pingDB

And the new warning with no query able persistence service is also showing up with the demo setup.

I zipped the setup if you want to take a look. Maybe I did a mistake in the config:

watou · December 17, 2015, 12:01pm

Sounds like this is possibly a critical bug, @lewie? Could you investigate and open a critical issue against it if you find that’s the case?

lewie · December 18, 2015, 3:45pm

How much RAM does your raspian have?
You want to start over 180 Bundles! I think about 4-16 GB should be right for that.

In your demo Setup, please delete Bundles and keep only:

default in standard demo Setup:
org.openhab.binding.http-1.8.0-SNAPSHOT.jar
org.openhab.binding.ntp-1.8.0-SNAPSHOT.jar
org.openhab.persistence.exec-1.8.0-SNAPSHOT.jar
org.openhab.persistence.logging-1.8.0-SNAPSHOT.jar
org.openhab.persistence.rrd4j-1.8.0-SNAPSHOT.jar

additions:
org.openhab.persistence.jdbc-1.8.0-SNAPSHOT.jar
sqlite-jdbc-3.8.11.2.jar

And please add Strategies Section in jdbc.persist.
Without Strategies, persistence does not save anything, I learned too.
Please for example to test, use:

Strategies {
        default = everyChange
}
Items {
    * : strategy = everyChange, restoreOnStartup
}

Now, it works like a charm!

My fear, only one connection can be opened to SQLite is wrong!
As I have tested yet, SQlite can hold multiple connections simultaneously.

@watou,
It is not a Bug.

watou · December 18, 2015, 4:56pm

Thanks for that, @lewie.

lewie · December 18, 2015, 5:42pm

@watou, moved too early… gone more deeply, there is a Problem!

Without sitewhere, openhab overall and Persistence Services starts up with all over 180 Bundles in addons folder.
I could hardly believe it

gerryK · December 18, 2015, 10:46pm

@lewie thanks for testing my pack. I left the add ons as they were from the installation on purpose not that I missed an important binding.
I changed it to your proposed add ons and also added the jdbc.persist file but no success on my side
I will test it now on my mac instead of the pi.

Update:
Works like a charm on my mac. So it has something to do with my pi and probably jessey. I will test it with wheezy now.

Update 2
Wheezy shows the exact same behavior. -> doesn’t work with my pi

Update 3
I switched to mongodb persistent binding for now. If there is a new version for JDBC and sqlite ping me and I will test it on my system.

charlie · December 30, 2015, 4:15pm

Hello everyone,

I am a newcomer to the world of OpenHAB, and I installed the application that works wonders.
But I vainly sought the org.openhab.persistence.jdbc-1.7.1.jar plugin
Could someone tell me where to find it ?

watou · December 30, 2015, 4:34pm

It didn’t exist when openHAB 1.7.1 was released. You can find the almost-1.8 version of it in the ZIP file here:

https://openhab.ci.cloudbees.com/job/openHAB/lastSuccessfulBuild/artifact/distribution/target/distribution-1.8.0-SNAPSHOT-addons.zip

charlie · December 30, 2015, 4:43pm

@watou Thanks,

If I take the 1.8.0 version of JDBC, will it work with openHAB 1.7.1 ?
or should I install openHAB 1.8.0 ?

watou · December 30, 2015, 5:02pm

I am pretty sure it will work on openHAB 1.7.1 as well as it does in 1.8. Just follow the wiki page (that I just updated to note its 1.8 availability).

charlie · December 30, 2015, 5:14pm

@watou Thank you very much,
I Will try that.

c_r · January 29, 2016, 7:28pm

See JDBC Persistence with SQLite not working in openHAB 1.8.0
for a continued discussion of the issue with JDBC persistence with SQLite not working

hjfabius · February 6, 2016, 11:24pm

Hi,
I’ve exactly the same problem on my RPi installation. The service does not raise any error but the file is not created.

Here my config:

org.openhab.persistence.jdbc-1.8.1.jar
sqlite-jdbc-3.8.11.2.jar
java 1.8.0_65

My config:

jdbc:url=jdbc:sqlite: /opt/openhab/mydb.db (I also tried with ./mydb.db)
jdbc:user=openhab
jdbc:password=*******
jdbc:tableUseRealItemNames=true

jdbc.persist:

Strategies
{
// if no strategy is specified for an item entry below, the default list will be used
default = everyChange
}

Items
{

: strategy = everyChange, restoreOnStartup
}

Starting in debug I’ve

22:57:08.081 [DEBUG] [o.p.j.i.JdbcPersistenceService:95 ] - JDBC::unsetItemRegistry
22:57:08.343 [DEBUG] [o.p.j.i.JdbcPersistenceService:90 ] - JDBC::setItemRegistry
22:57:08.345 [DEBUG] [o.p.j.i.JdbcPersistenceService:60 ] - JDBC::activate: persistence service activated
22:57:08.347 [DEBUG] [o.p.j.i.JdbcPersistenceService:214 ] - JDBC::updateConfig
22:57:08.349 [DEBUG] [p.j.internal.JdbcConfiguration:64 ] - JDBC::JdbcConfiguration
22:57:08.351 [DEBUG] [p.j.internal.JdbcConfiguration:72 ] - JDBC::updateConfig: configuration.size = 8
22:57:08.353 [DEBUG] [p.j.internal.JdbcConfiguration:77 ] - JDBC::updateConfig: url=jdbc:sqlite:/opt/openhab/domusSilicea.db
22:57:08.355 [DEBUG] [p.j.internal.JdbcConfiguration:87 ] - JDBC::updateConfig: found serviceName = ‘sqlite’
22:57:08.357 [DEBUG] [p.j.internal.JdbcConfiguration:98 ] - JDBC::updateConfig: Init Data Access Object Class: ‘org.openhab.persistence.jdbc.db.JdbcSqliteDAO’
22:57:08.359 [DEBUG] [ersistence.jdbc.db.JdbcBaseDAO:101 ] - JDBC::initSqlTypes: Initialize the type array
22:57:08.362 [DEBUG] [ersistence.jdbc.db.JdbcBaseDAO:74 ] - JDBC::initSqlQueries: ‘JdbcSqliteDAO’
22:57:08.364 [DEBUG] [o.o.p.jdbc.db.JdbcSqliteDAO :42 ] - JDBC::initSqlQueries: ‘JdbcSqliteDAO’
22:57:08.366 [DEBUG] [p.j.internal.JdbcConfiguration:105 ] - JDBC::updateConfig: dBDAO ClassName=org.openhab.persistence.jdbc.db.JdbcSqliteDAO
22:57:08.370 [DEBUG] [p.j.internal.JdbcConfiguration:144 ] - JDBC::updateConfig: user=openhab
22:57:08.372 [DEBUG] [p.j.internal.JdbcConfiguration:157 ] - JDBC::updateConfig: password= password.length=7
22:57:08.373 [DEBUG] [p.j.internal.JdbcConfiguration:182 ] - JDBC::updateConfig: tableUseRealItemNames=true
22:57:08.375 [DEBUG] [p.j.internal.JdbcConfiguration:216 ] - JDBC::updateConfig: enableLogTime false
22:57:08.377 [WARN ] [p.j.internal.JdbcConfiguration:237 ] - JDBC::updateConfig: try to load JDBC-driverClass: ‘org.sqlite.JDBC’
22:57:08.379 [DEBUG] [p.j.internal.JdbcConfiguration:240 ] - JDBC::updateConfig: load JDBC-driverClass was successful: ‘org.sqlite.JDBC’
22:57:08.380 [DEBUG] [p.j.internal.JdbcConfiguration:266 ] - JDBC::updateConfig: configuration complete. service=jdbc
22:57:08.382 [DEBUG] [o.o.p.jdbc.internal.JdbcMapper:33 ] - JDBC::pingDB
22:57:08.383 [DEBUG] [o.o.p.jdbc.internal.JdbcMapper:153 ] - JDBC::openConnection isDriverAvailable: true
22:57:08.385 [WARN ] [o.o.p.jdbc.internal.JdbcMapper:155 ] - JDBC::openConnection: setupDataSource.

No file is created. I read the old post but I’ve no idea how to progress. No error in the log that give me any direction for investigation.

Is there anyone that have any suggestion?

Thanks
hjfabius