openHAB2 & MySQL persistence setup


(Hartmut Schwensen) #61

@Christian_V “Here, it will ask for a root password, saying that “While it is not necessary, it is suggested.” Of which it is always a good idea to include a root password. Make sure, of course, to remember this password as well.”

What can I do when I did not receive a popup for setting the password? Is there a default one? Thanks in advance


(Brian Aker) #62

The script mentioned above is missing the following:

FLUSH PRIVILEGES;

Eventually, after a restart, a connection to the database will work without the above statement.

If you want persistence you will also want to make Innodb the default storage engine.


(Udo Hartmann) #63

Saw this new behavior for MariaDB, when installing, there is no request for root password.
By default there is no way to login to mysql other than using

sudo mysql

This is due to the fact, that MariaDB by default is configured to use a plugin for root login. As you have to create a new user anyway…


(B0rner) #64

Is it realy needed to grant all * privileges to this user?


(Brian Chan) #65

Hi all,

I’m receiving the following error after upgrading openHAB:

2018-09-10 15:57:00.026 [ERROR] [jdbc.internal.JdbcPersistenceService] - JDBC::query: itemRegistry == null. Ignore and give up!

I’ve tried to google it but found nothing. Anyone know what this error means and how to fix it?

Thanks.


(Anders Olesen) #66

After upgrading the snapshot release 2 days ago, I also get this error. Value changes are being persisted, however, I get this error in the log when HabPanel (timeline and chart) is trying to retrieve values using jdbc. No data is shown. When enabling debug logging on jdbc I get no additional information.


(Brian Chan) #67

I think it’s caused by this bug: https://github.com/openhab/openhab-core/pull/390.

I downgraded openhab and now it’s working without error.


(Anders Olesen) #68

It has now been fixed in the snapshot release.


(Sid Amos) #69

I am using openHAB 2.3.0 with the jdbc-mysql persistence because the mysql persistence does not understand the config tableUseRealItemNames.

It is basically working, but cannot create the tables for some of the items, others work. Example for error:

java.sql.SQLException: Invalid default value for ‘value’ Query: CREATE TABLE IF NOT EXISTS unifiredminote5_lastseen_8 (time TIMESTAMP(3) NOT NULL, value TIMESTAMP(3), PRIMARY KEY(time)) Parameters: []

Any ideas? Thanks!

Edit:
This would be the correct SQL:
CREATE TABLE IF NOT EXISTS unifiredminote5_lastseen_8
(time TIMESTAMP NOT NULL, value TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(time))

Edit 2:
This would also be correct:
CREATE TABLE IF NOT EXISTS OpenHAB.unifiredminote5_lastseen_8
(time DATETIME NOT NULL, value DATETIME, PRIMARY KEY(time))

So, I was able to fix it by configuring this in jdbc.cfg:
sqltype.DATETIME = DATETIME

So, the default of TIMESTAMP(3) is not correct for MySQL, at least not for the current version.