Some users keep struggling with MySql persistence.
it are just a few steps to get it working in.
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 (you have a larger buffer, and it does ssh, so you don’t need putty)
Another helpful 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 (OH2)
[..]
# A comma-separated list of persistence services to install (e.g. "persistence = rrd4j,jpa")
persistence = mysql
[..]
In OH3 we use a different approach
go to settings
→ Other Add ons → JDBC Persistence MariaDB: add this
→ System Services → Persistence: Check JDBC
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 command line (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 each command in mariaDB a semicolon ; is needed
CREATE DATABASE OpenHAB;
To be able to use it, you need to add a 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 if you are using OH2 to allow openhab to communicate to mariaDB
adjust the timezone to your needs
In OH3 settings → Other Services → JDBC Persistence Service: Use the interface to add the information from below.
# the database url (127.0.0.1 is the same as localhost)
url=jdbc:mariadb://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 your file jdbc.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 which 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 persistencies, make sure one is working as you want, without any errors that continue with the next one.
The instructions have been altered for OH3 on January 21, 2022
