[Solved!] Cant figure out MySQL for the life of me

Setup, Configuration and Use Beginners
,
1

I’ve got MySQL set up as my persistence, but it’s continuing to fail. I’ve read through all the threads that have to do with MySQL and haven’t seen a solution yet that has helped me, hopefully you can help.

config:

mysql:url=jdbc:mysql://192.168.1.39:3306/openhab
mysql:user=openhab
mysql:password=xxxxxxx
mysql:reconnectCnt=2
mysql:waitTimeout=10

Persist:

Strategies {
    everyHour : "0 0 * * * ?"
    everyDay  : "0 0 0 * * ?"
    default = everyChange
}
items {
    * : strategy = everyChange, everyDay, restoreOnStartup
    Temperature*, Weather* : strategy = everyHour
}

Logs:

   2016-01-27 21:58:34.310 [ERROR] [.p.m.i.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://192.168.1.39:3306/openhab, user=openhab, password=xxxxx
    com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
2

It may be that your MySQL server is only listening for connections on the loopback address (127.0.0.1). On my setup, which is the default as it was installed, I have this in /etc/mysql/my.cnf:

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1

According to the manual, if you change it to 0.0.0.0, it will listen for connections on any interface, which will be needed if it’s running on a different machine. If instead it’s running on the same machine, you should be able to change openhab.cfg with this line:

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

Hi,
I found that the actual ip-adress was not working for me (I run mysql on the same machine as OpenHab).
I needed to change it to:
mysql:url=jdbc:mysql://localhost/OpenHab
(I even believe the input of the directory name was case sensitive).
Cheers
Björn

4

localhost is usually defined as 127.0.0.1, so that would be the same advice as I gave earlier. Good point about case sensitivity of the database name. My MySQL database is named openhab, so that is specific to one’s setup.

5

I actually started with 127.0.0.1/openhab and localhost (knowing they were the same thing, but sometimes it’s black magic), but that wasn’t working. I read some threads to include the port number, didn’t work. There was another thread indicating that sometimes if 127.0.0.1 doesn’t work, even if you’re on the same machine, specify the IP distinctly.

I used these instructions for creating the DB, so I’m betting it’s the case. Will try it and get back to you.

6

Case was a problem, still not being allowed to connect, I’m going to go back to localhost to see if that resolves it.

mysql> show databases
→ ;
±-------------------+
| Database |
±-------------------+
| information_schema |
| OpenHAB |
| mysql |
| performance_schema |
±-------------------+
4 rows in set (0.01 sec)

7

Moving back to localhost worked!!! Thank you @Extrabannies & @watou very much for helping figure this out!

1 Like
8

hi, i’ve been doing some tests and i think there’s a problem with openHAB (or maybe mysql…?):

  1. In mysql config, i set up the local IP address 192.168.x.x
    I did it because i need to connect to this mysql server from another IP in the same network (and that works OK).

  2. I can access from command line to mysql and test mysql user ‘openhab’ without problems. Queries OK to the database. Can see table Items, etc

  3. In openhab i changed from ‘localhost’ to the IP address, and still the same error:

mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://192.168.x.x:3306/openhab, user=openhab, password=mypassword

And:

mySQL: No connection to database. Can not persist item …

To resume (this are my tests, maybe yours are different):

Doesn’t Work:

openhab:
mysql:url=jdbc:mysql://localhost:3306/openhab
MySQL:
bind-address = 192.168.x.x

Doesn’t Work:

openhab:
mysql:url=jdbc:mysql://192.168.x.x:3306/openhab
MySQL:
bind-address = 192.168.x.x

ONLY WORKS:

openhab:
mysql:url=jdbc:mysql://localhost:3306/openhab
MySQL:
bind-address = 127.0.0.1

Apparently it just works if mysql is listening on localhost, so…

Does openhab cares about the mysql server IP?

Sorry if i messed someone … :wink: i’m already confused.

Thanks!!!
Max

9

Was there an error before this point in the scenario?

You’re sure you’re putting the local address, not the the subnet like 192.168.1.0?

When you have my.cnf set to 192.168.x.x, try netstan -an | grep LISTEN to see what port the MySQL server is really listening on.

10

thanks for your answer!

i solved it like this:
login to mysql as root and execute:

GRANT ALL PRIVILEGES ON openhab.* TO openhab@'192.168.100.%' IDENTIFIED BY 'mypassword';
FLUSH PRIVILEGES;
  • my.cnf is listening on the fixed IP address
  • openhab.cfg is pointing to the fixed IP address

i have to test the remote access, i suppose it will work flawlessly

thanks!!!
Max

1 Like