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 ?