Mariadb persistence is not running - who finds a solution?

Tags: #<Tag:0x00007f616ee772d8>

Hello all,

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

  1. 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

  1. Install the JDBC Persistence in the Openhab Paper UI
    JDBC Persistence MariaDB
    persistence-jdbc-mariadb - 1.14.0

  2. Create the jdbc.service file
    with following content
    url=jdbc:mariadb://192.168.188.34:3306/OpenHAB
    user=openhab
    password=XXXXX

  3. Created the jdbc.persist file
    with following content
    Strategies {
    everyMinute : “0 * * * * ?”
    every15Minutes : “0 */15 * ? * *”
    everyHour : “0 0 * * * ?”
    everyDay : “0 0 0 * * ?”
    default = everyChange
    }
    Items {
    Temperatur_Garage : strategy = everyChange, restoreOnStartup
    Garagentorstatus_Garage : strategy = everyChange, restoreOnStartup
    }

  4. Added addons.cfg file
    persistence=mariadb, mysql, jdbc

  5. Restart Openhab
    sudo systemctl restart openhab2.service

  6. changed the values of the items

  7. check if the database has any values and tables
    MariaDB [(none)]> SHOW TABLES FROM OpenHAB;
    Empty set (0.001 sec)

Who can help me and find my issue?
I tried to use the other persistence

  • JDBC Persistence MySQL
  • MySQL Persistence

Thanks for your help

Marcel

Between step 7 and 8
you should enter in MariaDB
to use the database you named in step 1

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

thank you for the fast hint but I got the same result

MariaDB [(none)]> use OpenHAB;
Database changed
MariaDB [OpenHAB]> SHOW TABLES FROM OpenHAB;
Empty set (0.001 sec)

does anybody has an another hint for me

Marcel

If you change the door of your garage (Garagentorstatus_Garage)
can you check the log if there is an error in the persistence

and it might seem strange change your persistence in addons.cfg file to

Added addons.cfg file
persistence=mysql

MariaDB can easily be accessed with MySQL this how I have set it up

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.

On day later,

to

no solution :frowning:

I changed the file

no solution :frowning:

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.

In the current log there is an error


I hope I have time tomorrow to test it again.

Thank you for your hints

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;

no solution
I created the user and added the rights

in the log I got following information

It is a little bit strange because I do not know at which position I added the openhab.fritz.box user

In the cfg file I used the openhab user
url=jdbc:mariadb://localhost:3306/OpenHAB
user=openhab

and in the log is user root???
I will only a running database

In my mysql.cfg file are the following settings

# 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.

1 Like

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.

1 Like

Hello all,

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

Marcel

I have made a small step by step set up for MySQL persistence