[Solved] Persistence url keeps changing

Hi.
I can’t seem to get mysql persistence working in OH2. I thought I’d set it up correctly, but I just get “No suitable driver found for url=jdbc:mysql://127.0.0.1:3306/openhab” in the log. Now when I look in mysql.config and jdbc.config the url is “url=jdbc:mysql://127.0.0.1:3306/openhab”, like an extra url= has sneaked in there. I changed it back and restarted entire openhab, but I still get the same error and when I look in the config files the url is changed back. Is this saved somewhere else as well or where is it coming from?

Pasting entire file contents below.
mysql.config

password="0penhabPwd"
service.pid="org.openhab.mysql"
url="url=jdbc:mysql://127.0.0.1:3306/openhab"
user=“openhabdbuser”

jdbc.config

password="0penhabPwd"
service.pid="org.openhab.jdbc"
url="url=jdbc:mysql://127.0.0.1:3306/openhab"
user=“openhabdbuser”

Maybe this will help:

Check in karaf if there is any stable config parameters for mysql & jdbc

config:list "(service.pid=org.openhab.mysql)"
config:list "(service.pid=org.openhab.jdbc)"

If there are wrong stuff there, delete the stored config:

config:delete org.openhab.mysql
config:delete org.openhab.jdbc

Fix your .config and .persist files and let OH2 re-read them to populate the stored configs.

Oops… I missed that part:

You mean that your /etc/openhab2/services/mysql.cfg file is being updated by OH2? (this shouldn’t happen…)

Hi Daniel,

how does your mysql.cfg file look like? That’s mine:

cat conf/services/mysql.cfg

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://127.0.0.1:3306/openhab2

# the database user
user=openhab2

# the database password
password=xxxxxxxxxxxxxxxxxxxxx

# the reconnection counter
reconnectCnt=30

# the connection timeout (in seconds)
waitTimeout=7200

# 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

And the result is

>cat userdata/config/org/openhab/mysql.config

password="xxxxxxxxxxxxxxxxxxxxx"
reconnectCnt="30"
service.pid="org.openhab.mysql"
url="jdbc:mysql://127.0.0.1:3306/openhab2"
user="openhab2"
waitTimeout="7200"

I get the feeling I need to look up what this Karaf thingy that everyone is talking about really is. That’ll be a work for tomorrow, now I need sleep…

Is it possible that your conf/services/mysql.cfg file looks like this (maybe copy&paste mistake):

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

This would explain the outcome in your userdata/config/org/openhab/mysql.config file

Well, it did… And now I tried the following:

  1. Using the Karaf console, checked the settings according to your instructions. They were wrong (one url= too much), so I deleted them using config:delete.
  2. Fixed the correct urls in the config files
  3. Restarted openhab service

What happened is the faulty settings are back again, both in Karaf and config files. I don’t understand anything right now. Are there any third place where they’re also stored???

(anyway, your profile image got me inspired. Hasn’t listened to dm since Exciter somewhere. Listening up now…)

This is really strange… the flat text config files (in /etc) should not be updated by openHAB 2…

Even if there is a another place (yes it is here: /var/lib/openhab2/config/org/openhab/mysql.config but you should not modify this one manually… it should be updated when you modify the /etc one) where the settings are stored and pulled into OH2 config, the /etc/openhab2/services/mysql.cfg (not mysql.config) file is in theory read-only for OH2.

Try deleting the existing file, create a new one and use another text editor to edit it (maybe something is wrong with that… just guessing)

Everything Counts (in large amounts :wink: )

Oh… Sorry… All my stupidity… I actually modified the file in /var/lib all this time, the file in /etc was still wrong. No wonder it didn’t work. Guess I must have changed the /etc one the first time and then forgot about it. Still a bit confusing with all the places. Welcome to my world…

edit: Just like to confirm, tables were now created in my openhab database, so the connection seems to work. One table named “items” and one named “Items”, wonder what the difference is :slight_smile:

Yup, I agree :slight_smile:

In general, remember that these files (in /var/lib/openhab2/) are composing your $userdata which are important when you want to create a backup (along with the configuration files in /etc/openhab2). You need them, but OH2 manages them (OH2 is Behind The Wheel)

I haven’t used mysql.persist but from the OH1 wiki entry:

I don’t know why you have 2 item tables :frowning: check the contents (using phpmyadmin or a console command)

Ok, so what you’re saying basically is “don’t touch the files in /var/lib/openhab2 manually”, right?

Well, they’re both empty. I think the fact that I don’t currently have any items configured might be the problem behind that though :sunglasses:

Well… yes :slight_smile:

I am using OH2 for the past 6 months and I never had to manually modify something there (only for advanced cases of troubleshooting).

I do take a regular backup of that folder because all my things (mainly Z-Wave) are discovered from Paper UI and are stored in the new /var/lib/openhab2/jsondb. Also, other configurations that you do from Paper UI and/or HABmin will go in files stored in subdirs of /var/lib/openhab2/

1 Like