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.
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…
Is it realy needed to grant all * privileges to this user?
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.
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.
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.
It has now been fixed in the snapshot release.
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.
Hi I followed this guide 1:1 but I don’t think that my mysql persistence service is starting since it’s not persisting anything and not logging anything.
services/mysql.cfg:
url=jdbc:mysql://127.0.0.1:3306/OpenHAB
user=openhab
password=password
persistence/mysql.persist:
Strategies {
every5Minutes : "0 */5 * * * ?"
default = everyChange, restoreOnStartup
}
Items {
InsideTemp, InsideHumidity, BarTrend, Barometer : strategy = every5Minutes, restoreOnStartup
}
logs on restart of openhab2:
2019-06-27 11:47:27.400 [INFO ] [ebuilder.internal.HomeBuilderServlet] - Started Home Builder at /homebuilder
2019-06-27 11:47:27.479 [INFO ] [panel.internal.HABPanelDashboardTile] - Started HABPanel at /habpanel
2019-06-27 11:47:35.724 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'davis.items'
2019-06-27 11:47:36.551 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'mysql.persist'
2019-06-27 11:47:39.886 [INFO ] [thome.model.lsp.internal.ModelServer] - Started Language Server Protocol (LSP) service on port 5007
2019-06-27 11:47:40.531 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'weatherenvoy.sitemap'
2019-06-27 11:47:40.598 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'test.sitemap'
2019-06-27 11:47:41.792 [INFO ] [.dashboard.internal.DashboardService] - Started Dashboard at http://10.3.0.13:8080
2019-06-27 11:47:41.795 [INFO ] [.dashboard.internal.DashboardService] - Started Dashboard at https://10.3.0.13:8443
2019-06-27 11:47:42.369 [INFO ] [arthome.ui.paper.internal.PaperUIApp] - Started Paper UI at /paperui
2019-06-27 11:47:42.847 [INFO ] [b.core.service.AbstractActiveService] - Davis Refresh Service has been started
I’ve also made sure to set the log level to trace of the persistence mysql service.
Output of log:list in karaf console:
org.openhab.persistence.mysql │ TRACE
The Addon is marked as installed in Paper UI
And I’ve made sure to manually insert the addon as the default persistence service in Paper UI:
Can someone here help me?
I’ve got the persistence addon to actually start now by reinstalling it through the Paper UI. But I ran into a new problem. The Addon can not connect to my database:
2019-06-27 12:11:43.361 [DEBUG] [sql.internal.MysqlPersistenceService] - mySQL: Attempting to connect to database jdbc:mysql://127.0.0.1:3306/OpenHAB
2019-06-27 12:11:44.024 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://127.0.0.1:3306/OpenHAB, user=openhab
java.sql.SQLException: The server time zone value 'CEST' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
Fixed thanks to this so link: https://stackoverflow.com/a/40245885
In PaperUI (and in Rules) just use “mysql”, not the full org.openhab…
That isn’t the cause of any of your problems but it might cause problems down the road.
A time zone (?serverTimezone=(your time zone) from here ) can be appended to the connection string in mysql.cfg, like this:
url=jdbc:mysql://127.0.0.1:3306/OpenHAB?serverTimezone=America/New_York