(Solved) OH2 mysql persistence not working (as expected)

Setup, Configuration and Use Beginners
1

Hello,

  • I have a clean package install on ubuntu 14.04 LTS with mysql persistence
  • I used Paper UI to select mysql for persistence
  • I created the database and database user with all privileges on the openhab database with grant option

create user ‘openhabuser’@’%’ identified by ‘XAZABC1;
grant all privileges on openhab.* to ‘openhabuser’@’%’ identified by ‘XAZABC1’ with grant option;
flush privileges;

  • I modified ./services/mysql.cfg:

the database url like ‘jdbc:mysql://:/’ (without quotes)

url=jdbc:mysql://localhost:3306/openhab
user=openhabuser
password=XAZABC1
reconnectCnt=5
waitTimeout=10
localtime=true

  • I created ./persistence/mysql.persist

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
}

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

// additionally, persist sm_ping every 5 minutes
   sm_ping : strategy = every5Minutes, restoreOnStartup

}

  • Starting openhab2 created two tables called Items and Item1 (ok)
  • One record is persisted in each table (ok)
  • Then there are a lot of errors logged and only every third record seems to be persisted:

2017-03-28 12:16:12.244 [INFO ] [arthome.ui.paper.internal.PaperUIApp] - Stopped Paper UI
2017-03-28 12:16:12.253 [INFO ] [panel.internal.HABPanelDashboardTile] - Stopped HABPanel
2017-03-28 12:16:12.260 [INFO ] [.dashboard.internal.DashboardService] - Stopped dashboard
2017-03-28 12:19:03.563 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'mysql.persist’
2017-03-28 12:19:07.307 [INFO ] [.dashboard.internal.DashboardService] - Started dashboard at /start
2017-03-28 12:19:07.657 [INFO ] [basic.internal.servlet.WebAppServlet] - Started Basic UI at /basicui/app
2017-03-28 12:19:07.726 [INFO ] [arthome.ui.paper.internal.PaperUIApp] - Started Paper UI at /paperui
2017-03-28 12:19:07.803 [INFO ] [panel.internal.HABPanelDashboardTile] - Started HABPanel at /habpanel
2017-03-28 12:20:07.985 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-03-28 12:20:07.986 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-03-28 12:20:07.988 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not create entry for ‘network_device_192_168_56_101_time’ in table ‘Items’ with statement ‘INSERT INTO Items (ItemName) VALUES (?)’: No operations allowed after connection closed.
2017-03-28 12:20:07.988 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: tableName was null
2017-03-28 12:20:07.988 [ERROR] [sql.internal.MysqlPersistenceService] - Unable to store item ‘network_device_192_168_56_101_time’.
2017-03-28 12:21:07.939 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-03-28 12:21:07.940 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-03-28 12:21:07.940 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Error count exceeded 5. Disconnecting database.
2017-03-28 12:21:07.940 [WARN ] [sql.internal.MysqlPersistenceService] - mySQL: No connection to database. Can not persist item ‘network_device_192_168_56_101_time (Type=NumberItem, State=0.27998800000000001464428578401566483080387115478515625)’! Will retry connecting to database when error count:5 equals errReconnectThreshold:5
2017-03-28 12:22:07.952 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Error count exceeded 5. Disconnecting database.
2017-03-28 12:23:07.970 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-03-28 12:23:07.970 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-03-28 12:23:07.971 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item ‘network_device_192_168_56_101_time’ in database with statement ‘INSERT INTO Item2 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;’: No operations allowed after connection closed.
2017-03-28 12:24:07.939 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-03-28 12:24:07.940 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-03-28 12:24:07.940 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Error count exceeded 5. Disconnecting database.
2017-03-28 12:24:07.940 [WARN ] [sql.internal.MysqlPersistenceService] - mySQL: No connection to database. Can not persist item ‘network_device_192_168_56_101_time (Type=NumberItem, State=0.246319000000000010164313835048233158886432647705078125)’! Will retry connecting to database when error count:5 equals errReconnectThreshold:5

What is going wrong?

Best regards

2

Hi,
I’m running more or less the same setup on 16.04. There are two differences; iirc these values are advised from a mysql server point of view:

# the reconnection counter
reconnectCnt=1
# the connection timeout (in seconds)
waitTimeout=28800

As far as i remember, if the timeout value is too low the DB might “lock” which looks like your issue.
HTH,
-OLI

2 Likes
3

Try to change to:

url=jdbc:mysql://127.0.0.1:3306/openhab
4

a reconnectCnt of 1 and a waitTimeout of 28800 means:

  • one reconnect after 8 hours of database offline
    a reconnectCnt of 5 and a waitTimeout of 30 should means:
  • 5 reconnect attempts with 30 seconds of database offline, maybe this means after 30 seconds the first reconnect attempt and after 3 minutes the last reconnect attempt.

I can’t believe that a nearly idle database is blocking one write attempt every minute. The same database server is running under FHEM thousands of writes without any problems.

But as a matter of fact raising the waitTimeout up to 120 seconds seems to work better. But in my eyes there must be something wrong here (I’m working for about 20 years as a database designer for industrial and public solutions with oracle, mssql and currently cassandra).

By the way, I’m missing something like a queue for events that cant be persisted in time so that they are persisted later when the database is accessible (like FHEM does)? I.e. when OH starts or the database is temporary down events are not logged into the database.

5

Altering localhost to the local ip-address 127.0.0.1 (127.0.1.1) didn’t fix the problem.

6

In openhab waitTimeout means: disconnect from DB if idle for longer than x, not reconnect after x if DB offline.
I read several threads (unfortunately in german) stating that a value too low results in exactly what you are seeing. 28800 was referred to as the “default value recommended for mysql server” so i went for that(even though it really seems kinda high) and i haven’t had any problems since. YMMW, you can always raise the value slowly until OK.

7

Thanks! I had the same problem and now it’s solved.