[SOLVED] Persistence problems with MySql

I’m trying to get persistence going with MySql. Ive been working on it for a few days now and all I can get it to do is create the Item table.

I have enabled logging of queries on MySql and all I see is the following

>                    16 Query     /* mysql-connector-java-5.1.26 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
>                    16 Query     /* mysql-connector-java-5.1.26 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
>                    16 Query     SHOW COLLATION
>                    16 Query     SET NAMES latin1
>                    16 Query     SET character_set_results = NULL
>                    16 Query     SET autocommit=1
>                    16 Query     SET sql_mode='STRICT_TRANS_TABLES'
>                    16 Query     SHOW TABLES LIKE 'Items'
>                    16 Query     SELECT ItemId, ItemName FROM Items

persistence/mysql.persistence is below

Strategies {
        everyMinute     : "0 * * * * ?"
        every5Minutes   : "0 */5 * * * ?"
        everyHour       : "0 0 * * * ?"
        everyDay        : "0 0 0 * * ?"

    default = everyChange
}

Items {

    * : strategy = everyChange, everyDay, restoreOnStartup
    mqttPIRHall,mqttPIRKitchen,mqttPIRFront : strategy = everyChange

}

services/mysql.cfg is below

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
#url=
url=jdbc:mysql://127.0.0.1/openhab

# the database user
#user=
user=******

# the database password
#password=
password=******

# the reconnection counter
#reconnectCnt=
reconnectCnt=1

# the connection timeout (in seconds)
#waitTimeout=

# Use MySQL Server time to store item values (=false) or use openHAB Server time (=true).
# For new installations, its recommend to set "localtime=true".
# (optional, defaults to false)
#localtime=truei

sqltype.string=VARCHAR(20000)

if I drop the item table and restart openhab, it’s recreated but no other tables are created and no entries exist in the item table.

I have a number of devices generating state changes (mostly PIRs) and I would have expected them to write data into the persisten tables

After a restart, this is whats in my openhab2.log file

2018-01-28 11:56:47.682 [INFO ] [er.internal.HomeBuilderDashboardTile] - Started Home Builder at /homebuilder
2018-01-28 11:56:47.943 [INFO ] [.dashboard.internal.DashboardService] - Started dashboard at http://192.168.1.4:8080
2018-01-28 11:56:47.944 [INFO ] [.dashboard.internal.DashboardService] - Started dashboard at https://192.168.1.4:8443
2018-01-28 11:56:49.250 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'house.items'
2018-01-28 11:56:49.293 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'test.items'
2018-01-28 11:56:50.111 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'location.rules'
2018-01-28 11:56:50.521 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'test.rules'
2018-01-28 11:56:50.535 [WARN ] [el.core.internal.ModelRepositoryImpl] - Configuration model 'location.rules' is either empty or cannot be parsed correctly!
2018-01-28 11:56:50.576 [INFO ] [thome.model.lsp.internal.ModelServer] - Started Language Server Protocol (LSP) service on port 5007
2018-01-28 11:56:50.693 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'myhouse.sitemap'
2018-01-28 11:56:51.127 [INFO ] [basic.internal.servlet.WebAppServlet] - Started Basic UI at /basicui/app
2018-01-28 11:56:51.145 [INFO ] [arthome.ui.paper.internal.PaperUIApp] - Started Paper UI at /paperui
2018-01-28 11:56:51.247 [INFO ] [panel.internal.HABPanelDashboardTile] - Started HABPanel at /habpanel
2018-01-28 11:56:51.303 [INFO ] [penhab.io.transport.mqtt.MqttService] - MQTT Service initialization completed.
2018-01-28 11:56:51.304 [INFO ] [t.mqtt.internal.MqttBrokerConnection] - Starting MQTT broker connection 'broker'

I have tried using jdbc for mysql and mariadb (I’m using Mariadb) but that hasnt worked for me either.

Any suggestions on where else I should look ?

I would start by eliminating the duplication in your persistence strategies.
Ignore that line above. I misread your config.

But, you should enable DEBUG logging for the persistence service and check your logs again.

Good idea… I hadnt used karaf before so not sure if I got it right.

After connecting to karaf using ./client after a fresh restart, I entered

log:set DEBUG org.openhab.persistence.mysql
log:tail

The only mysql entries I saw were

16:07:31.219 [DEBUG] [ysql.internal.MysqlPersistenceService] - mySQL: Connected to database jdbc:mysql://127.0.0.1/openhab
16:07:31.222 [DEBUG] [ysql.internal.MysqlPersistenceService] - mySQL configuration complete.
16:07:31.225 [DEBUG] [org.openhab.persistence.mysql        ] - ServiceEvent REGISTERED - {org.openhab.core.persistence.PersistenceService}={service.pid=org.openhab.mysql, user=*****, url=jdbc:mysql://127.0.0.1/openhab, password=******, sqltype.string=VARCHAR(20000), reconnectCnt=1, component.name=org.openhab.persistence.mysql, component.id=197, service.id=323, service.bundleid=231, service.scope=bundle} - org.openhab.persistence.mysql

so it looks like its connected.

I’m seeing data being changed as follows

15:55:32.602 [INFO ] [smarthome.event.ItemStateChangedEvent] - mqttPIRHall changed from OPEN to CLOSED
15:55:32.991 [INFO ] [smarthome.event.ItemStateChangedEvent] - mqttPIRHall changed from CLOSED to OPEN
15:55:35.850 [INFO ] [smarthome.event.ItemStateChangedEvent] - mqttPIRHall changed from OPEN to CLOSED
15:55:36.111 [INFO ] [smarthome.event.ItemStateChangedEvent] - mqttPIRHall changed from CLOSED to OPEN
15:55:39.818 [INFO ] [smarthome.event.ItemStateChangedEvent] - mqttPIRKitchen changed from CLOSED to OPEN
15:55:44.113 [INFO ] [smarthome.event.ItemStateChangedEvent] - mqttPIRHall changed from OPEN to CLOSED

I would have expected to see the data triggering mysql operations from the persistence, but I dont see any.

Also, I forgot to mention in my inital post that I had selected mysql in Configuration/System/Persistence/Default Service in the paperui

I found one other error in my persistence script. It was mentioned in another thread also about persistence issues with mysql. In the mysql.persistence file I had Items spelt with a capitol I and not a lower case i

I now see the following in the logs

16:37:59.529 [DEBUG] [org.openhab.persistence.mysql        ] - BundleEvent STARTING - org.openhab.persistence.mysql
16:37:59.531 [DEBUG] [rnal.MysqlPersistenceServiceActivator] - mySQL persistence bundle has been started.
16:37:59.531 [DEBUG] [org.openhab.persistence.mysql        ] - BundleEvent STARTED - org.openhab.persistence.mysql
16:37:59.538 [DEBUG] [ysql.internal.MysqlPersistenceService] - mySQL: Attempting to connect to database jdbc:mysql://127.0.0.1/openhab
16:37:59.768 [DEBUG] [ysql.internal.MysqlPersistenceService] - mySQL: Connected to database jdbc:mysql://127.0.0.1/openhab
16:37:59.772 [DEBUG] [ysql.internal.MysqlPersistenceService] - mySQL configuration complete.
16:37:59.773 [DEBUG] [org.openhab.persistence.mysql        ] - ServiceEvent REGISTERED - {org.openhab.core.persistence.PersistenceService}={service.pid=org.openhab.mysql, user=****, url=jdbc:                                              mysql://127.0.0.1/openhab, password=*****, sqltype.string=VARCHAR(20000), reconnectCnt=1, component.name=org.openhab.persistence.mysql, component.id=197, service.id=323, service.bundleid=231                                              , service.scope=bundle} - org.openhab.persistence.mysql

so its looking better, but still nothing getting into the tables.

SUCCESS !

There were 2 errors remaining.

  1. I had named the persistence file mysql.persistence when it should have been mysql.persist
  2. It turned out the item (with lower case i) needed to be Item (with upper case I)

I now have data populating the mysql tables.