I have some trouble with the persistence. I already tried it several times with different manuals but it will not work.
I have done following steps
Install mariadb
sudo apt-get install mariadb-server mariadb-client
sudo mysql -u root -p
grant all on . to root@localhost identified by ‘XXXXXXX’ with grant option;
flush privileges;
quit;
mysql -u root -p
CREATE DATABASE OpenHAB;
CREATE USER ‘openhab’@‘localhost’ IDENTIFIED BY ‘XXXXX’;
GRANT ALL PRIVILEGES ON OpenHAB.* TO ‘openhab’@‘localhost’;
quit
sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
changed #bind-address = 0.0.0.0
Install the JDBC Persistence in the Openhab Paper UI
JDBC Persistence MariaDB
persistence-jdbc-mariadb - 1.14.0
Create the jdbc.service file
with following content
url=jdbc:mariadb://192.168.188.34:3306/OpenHAB
user=openhab
password=XXXXX
If I remember correctly it makes a difference if you grant the permissions on the external interface (192.168.188.34) or the internal one (localhost). Make sure the grants match your persistence configuration. Also check the openhab logs. If the persistence layer is not able to access the table I’d expect something to be in there.
the good message: openHAB2 and MariaDB is working. I’ve it running.
To see what’s going on as a first step it’s good to install and use ‘MySQL Workbench’.
With that you can see, what really happen on the data base side.
I am not very familar with the log files. I know, that should be the basics. I changed the user and some other things for testing and changed it back and restarted openhab again. But the root user and the other settings are displayed there.
The error tells you that you want to access as user openhab.fritz.box
So add the user openhab.fritz.box to the users
try this
CREATE USER ‘openhab.fritz.box’@‘localhost’ IDENTIFIED BY ‘XXXXX’;
GRANT ALL PRIVILEGES ON OpenHAB.* TO ‘openhab.fritz.box’@‘localhost’;
FLUSH PRIVILEGES;
# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://127.0.0.1:3306/OpenHAB?serverTimezone=Europe/Amsterdam
# the database user
user=openhab
# the database password
# root password is openhab login with sudo mysql -u root
password=openhabian
# the reconnection counter
reconnectCnt=3
sqltype.STRING = TEXT
localtime=true
If you want to show the logfile contents you can better copy and paste the text (CTLR-C or CMD-C) I am not able to read the image on my phone.
No, this is not true. The error tells you that the host openhab.fritz.box is not allowed to access the server.
That’s what I meant with my first comment: If you use the external IP address of your openhab box (192.168.188.34) in your persistence configuration file you have to create your database user using the user name ‘openhab’@‘192.168.188.34’.
If you use the internal IP address of you openhab box (127.0.0.1 or localhost) in your persistence config file, you have to create your database user using the user name ‘openhab’@‘localhost’. Same goes for the GRANT ALL PRIVILEGES … you have to use the matching host there - e. g. GRANT ALL PRIVILEGES ON OpenHAB.* TO ‘openhab’@‘localhost’;
I assume of course that your openhab box is the same as 192.168.188.34 and you did NOT install the database on another machine.
I assume further that you use the user name “openhab” in you persistence configuration.
The log output tells me that openhab tried to connect with the user name “root”, so something else is messed up in your configuration.
Try to go step by step. First of all, activate only one persistence module in your addons.cfg (not three of them!) and then configure that one. So if your tutorial tells you to edit the jdbc.persist use the jdbc persistence module, not the other two.
Always check the logs. Try to understand it, otherwise post the output here, so that someone trying to help you has a proper error message to work with. “No solution” doesn’t help.
thank you for all the hints. I am not a expert and I try my best (I know, that my answers or my information to solve this issue are not very satisfactory).
At the end
only one persistence module was active in the addons.cfg file
in the jdbc.cfg file the user=openhab was listed (I do not know where root or openhab.fritz.box was defined)
in the jdbc.cfg file I changed the url from IP to localhost
url=jdbc:mariadb://localhost:3306/OpenHAB?serverTimezone=Europe/Berlin
And I got an ERROR in the log file which was similar / like
[ERROR] [nal.common.AbstractInvocationHandler] - An error occurred while calling method ‘QueryablePersistenceService.query()’ on ‘org.openhab.core.persistence.internal.QueryablePersistenceServiceDelegate@1813401’: java.lang.Integer cannot be cast to java.lang.Long
This was mentioned in
A friend spend some time to check some things and he changed the log level.
Now we think that my jdbc version and the mariadb version are not compatiple
I deactivated all persistence files, uninstall the persistence and I got another error message
2020-06-12 20:07:54.090 [ERROR] [core.karaf.internal.FeatureInstaller] - Failed installing ‘openhab-persistence-jdbc’
But I got a lot of other errors in my log I think my installation is messed up so I will install it new.
The configuration files are saved it will be not so much work to install it new
In this request I have learnd to use the log (very important point) and write detaild requests answers