How to setup MySQL persistence in a few minutes. Step by step

Tags: #<Tag:0x00007f616d5973d0> #<Tag:0x00007f616d597290> #<Tag:0x00007f616d596f48>

Some users keep struggling with MySql persistence.
it are just a few steps to get it working.
This has been written for openhabian but will work on most linux based systems.

Be prepared be able to check your log files use for example frontail
If you are on a windows machine I suggest to use cmder instead of cmd (your have a larger buffer and it does ssh so you don’t need putty)
Another helpfull tool could be MySQL workbench. To check if you are able to make outside connections.
Install and use Visual Basic studio to edit your files.

First in your addons.cfg add the persistence

[..]
# A comma-separated list of persistence services to install (e.g. "persistence = rrd4j,jpa")
persistence = mysql

[..]

with cmder

ssh openhabian@openhab
openhabian@openhab's password:
Linux openhab 4.19.75-v7l+ #1270 SMP Tue Sep 24 18:51:41 BST 2019 armv7l

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
[...]

Next use the commandline (cmd or cmder) to install mariadb server
if prompted for the password you can leave it empty. However, if you add a password don’t forget it

sudo apt-get update
sudo apt-get install mariadb-server

Now you need to access the database with command line

sudo mysql -u root

While in mariaDB you have to set the database that you want to use.
After eacht command in mariaDB a semicolon ; is needed

CREATE DATABASE OpenHAB;

To be able to use it you need to add an user and password.
to keep it simple I use here

  • username = openhab
  • password = openhabian
CREATE USER 'openhab'@'localhost' IDENTIFIED BY 'openhabian';

Give the user the rights to use the database

GRANT ALL PRIVILEGES ON OpenHAB.* TO 'openhab'@'localhost';
FLUSH PRIVILEGES;

Then set up the mysql.cfg file to allow openhab to communicate to mariaDB
adjust the timezone to your needs

# the database url (127.0.0.1 is the same as localhost)
url=jdbc:mysql://127.0.0.1:3306/OpenHAB?serverTimezone=Europe/Berlin

# the database user
user=openhab

# the database password
password=openhabian

# the reconnection counter
reconnectCnt=3

sqltype.STRING   =   TEXT
# uncomment next line if you store texts in persistence
#mysql:sqltype.string=VARCHAR(20000)

# 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

Now you need to make a persist file in the persistence directory

Name you file what ever you like with the extension anyname.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
	everyHour   : "0 0 * * * ?"
   	everyDay    : "0 0 0 * * ?"
    every5Minutes : "0 0/5 * 1/1 * ? *"
   	default = everyChange
}
Items {
    // persist all items once a day and on every change and restore them from the db at startup
	Itemname : strategy = everyChange, everyDay, restoreOnStartup

    // additionally, persist all temperature and weather values every hour
    gTemperatur* : strategy = every5Minutes, restoreOnStartup
}

If you use an * in the Items sections it stores everything. Just select the Items wich you want to persist

To check if you are collecting data.

MariaDB [(none)]> use OpenHAB;
MariaDB [OpenHAB]> SHOW TABLES FROM OpenHAB;

You should see something like this

MariaDB [OpenHABdata]> show tables;
+-----------------------+
| Tables_in_OpenHAB |
+-----------------------+
| Items                 |                
| Item1                 |
| Item10                |
| Item11                |
| Item12                |
| Item13                |
+-----------------------+
7 rows in set (0.00 sec)

Select * from Items;
+--------+----------------------------+
| ItemId | ItemName                   |
+--------+----------------------------+
|      1 | Power_use                  |
|      2 | TemperatureGarden          |
|      3 | Switch Kitchen             |
|      4 | Co2Livingroom              |
|      5 | Motio                      |
+--------+----------------------------+
94 rows in set (0.00 sec)

By selecting each Item? you can see the data.

If you are using different persistences, make sure one is working as you want, without any errors that continue with the next one.

2 Likes

If using a persistence strategy named every5Minutes, I’m pretty sure you first have to create it in Strategies section. :wink:

1 Like

Just updated, thanks for your sharp eyes. :blush:

1 Like

So I hadn’t seen this thread yet, but brought up mariadb (from docker official image, so what I’d consider “standard configuration”), added the mysql persistence addon, configured it and added items and…got this error:

2020-07-03 06:33:03.798 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not create table for item 'EcobeeThermostatMainFloor_Program_CurrentClimateRef' with statement 'CREATE TABLE Item24 (Time DATETIME, Value VARCHAR(20000), PRIMARY KEY(Time));': Column length too big for column 'Value' (max = 16383); use BLOB or TEXT instead

Folks - if everybody has to modify mysql.cfg, maybe it’s time to change the default?

1 Like