Fill MySQL database with OpenHAB

Setup, Configuration and Use Beginners
,
1

  • Platform information:

    • OS: Linux - Ubuntu
    • openHAB version: 2

  • Issue of the topic:
    I’m working on OpenHAB and I’m looking to save all items values for each value change. I have already followed a tutorial (openHAB2 & MySQL persistence setup): MySQL is well installed on the PC, the database is well created. Nevertheless, it remains completely empty. Does anyone have a solution?

  • Configurations:
    ADD-ONS:
    MySQL Persistence
    JDBC Persistence MySQL

mysql.cfg

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
#mysql:url=jdbc:mysql://192.168.0.102/OpenHAB
#:3306
url=jdbc:mysql://147.252.237.45:3306/OpenHAB

# the database user
user=openhab

# the database password
password=YOURPASSWORD

# the reconnection counter
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=true

# CODE DU SITE: https://community.openhab.org/t/cant-get-persistence-with-mysql-up-help/13273/5
#persistence:default=jdbc

# for Migration from MYSQL-Bundle set to 'Item'.
tableNamePrefix=Item

#
# for Migration from MYSQL-Bundle do not use real names.
tableUseRealItemNames=true

#
# for Migration from MYSQL-Bundle set to 0.
tableIdDigitCount=0

mysql.persist

// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
	// if no strategy is specified for an item entry below, the default list will be used
	//everyMinute	: "0 * * * * ?"
	//every5Minutes : "0 */5 * * * ?"
   	//everyHour   : "0 0 * * * ?"
   	//everyDay    : "0 0 0 * * ?"
   	default = everyChange
}

/* 
 * Each line in this section defines for which item(s) which strategy(ies) should be applied.
 * You can list single items, use "*" for all items or "groupitem*" for all members of a group
 * item (excl. the group item itself).
 */

//Items {
    // persist all items once a day and on every change and restore them from the db at startup
    //* : strategy = everyChange, everyDay, restoreOnStartup
    //* : strategy = everyChange, everyDay


    // additionally, persist all temperature and weather values every hour
    //   gTemperatur* : strategy = every5Minutes, restoreOnStartup
  • Errors:
    Error: Could not get valid data from REST API
    at Request.request [as _callback] (/root/.vscode/extensions/openhab.openhab-0.5.1/serverJS/src/ItemCompletion/ItemCompletionProvider.js:168:18)
    at Request.self.callback (/root/.vscode/extensions/openhab.openhab-0.5.1/serverJS/node_modules/request/request.js:185:22)
    at Request.emit (events.js:182:13)
    at Request. (/root/.vscode/extensions/openhab.openhab-0.5.1/serverJS/node_modules/request/request.js:1161:10)
    at Request.emit (events.js:182:13)
    at IncomingMessage. (/root/.vscode/extensions/openhab.openhab-0.5.1/serverJS/node_modules/request/request.js:1083:12)
    at Object.onceWrapper (events.js:273:13)
    at IncomingMessage.emit (events.js:187:15)
    at endReadableNT (_stream_readable.js:1090:12)
    at process._tickCallback (internal/process/next_tick.js:63:19)
2

Are you sure about the IP? seems your MySQL Database machine has a public ip address, which is fine, but at least unconventional in question of openHAB installations.

Did you change anything other than url, user and password in mysql.cfg? If yes, please consider to change it back to ensure the binding is working with default values.
Maybe switch to jdbc persistence, which is the newer persistence service.

You’ve disabled all items in mysql.persist, so there will be no persistence at all. To log all changes, the mysql.persist file should look like this:

Strategies {
	everyMinute	: "0 * * * * ?"
   	default = everyChange
}

Items {
    // persist all items on every change
    * : strategy = everyChange
}

You could shorten this file to

Strategies {
   	default = everyChange
}
Items {
    *
}

So as the default strategy is everyChange, this should suffice to persist every change, but the former file is more clear :wink: