Merkwürdiges Persistence verhalten

[PhiGi]

Hallo Zusammen,

ich betreibe seit einer gefühlten Ewigkeit mein OpenHab2 auf einem PI in verbindung mit meinem Buffalo NAS als MySQL Server.
Bisher hatte ich absolut keine Probleme. Nach dem letzten Update funktionierte das nicht mehr. Es gab nur noch Connection Problems. Ich nach vielen Tests und einer Neuinstallation von den OpenHab Addons funktionierte es scheinbar wieder, zumindest die DB füllte sich weiter.

Nun schau ich in meine Charts und sehe ... NICHTS. Ich bin mächtig verwirrt. Laut Log:

Code: Alles auswählen

2019-04-21 02:29:45.465 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://192.168.XX.XXX/openhab, user=XXX

java.sql.SQLNonTransientConnectionException: CLIENT_PLUGIN_AUTH is required

	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:79) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207) ~[234:org.openhab.persistence.mysql:1.13.0]

	at java.sql.DriverManager.getConnection(DriverManager.java:664) ~[?:?]

	at java.sql.DriverManager.getConnection(DriverManager.java:247) ~[?:?]

	at org.openhab.persistence.mysql.internal.MysqlPersistenceService.connectToDatabase(MysqlPersistenceService.java:521) [234:org.openhab.persistence.mysql:1.13.0]

	at org.openhab.persistence.mysql.internal.MysqlPersistenceService.query(MysqlPersistenceService.java:599) [234:org.openhab.persistence.mysql:1.13.0]

	at org.openhab.core.persistence.internal.QueryablePersistenceServiceDelegate.query(QueryablePersistenceServiceDelegate.java:47) [202:org.openhab.core.compat1x:2.4.0]

	at org.eclipse.smarthome.ui.internal.chart.defaultchartprovider.DefaultChartProvider.addItem(DefaultChartProvider.java:329) [149:org.eclipse.smarthome.ui:0.10.0.oh240]

	at org.eclipse.smarthome.ui.internal.chart.defaultchartprovider.DefaultChartProvider.createChart(DefaultChartProvider.java:205) [149:org.eclipse.smarthome.ui:0.10.0.oh240]

	at org.eclipse.smarthome.ui.internal.chart.ChartServlet.doGet(ChartServlet.java:333) [149:org.eclipse.smarthome.ui:0.10.0.oh240]

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:687) [29:javax.servlet-api:3.1.0]

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) [29:javax.servlet-api:3.1.0]

	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:865) [85:org.eclipse.jetty.servlet:9.4.11.v20180605]

	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:535) [85:org.eclipse.jetty.servlet:9.4.11.v20180605]

	at org.ops4j.pax.web.service.jetty.internal.HttpServiceServletHandler.doHandle(HttpServiceServletHandler.java:71) [186:org.ops4j.pax.web.pax-web-jetty:7.2.3]

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:146) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548) [82:org.eclipse.jetty.security:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:257) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1317) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.ops4j.pax.web.service.jetty.internal.HttpServiceContext.doHandle(HttpServiceContext.java:293) [186:org.ops4j.pax.web.pax-web-jetty:7.2.3]

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473) [85:org.eclipse.jetty.servlet:9.4.11.v20180605]

	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1219) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.ops4j.pax.web.service.jetty.internal.JettyServerHandlerCollection.handle(JettyServerHandlerCollection.java:80) [186:org.ops4j.pax.web.pax-web-jetty:7.2.3]

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.Server.handle(Server.java:531) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:352) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:281) [75:org.eclipse.jetty.io:9.4.11.v20180605]

	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102) [75:org.eclipse.jetty.io:9.4.11.v20180605]

	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118) [75:org.eclipse.jetty.io:9.4.11.v20180605]

	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:762) [87:org.eclipse.jetty.util:9.4.11.v20180605]

	at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:680) [87:org.eclipse.jetty.util:9.4.11.v20180605]

	at java.lang.Thread.run(Thread.java:745) [?:?]

Caused by: com.mysql.cj.exceptions.UnableToConnectException: CLIENT_PLUGIN_AUTH is required

	at sun.reflect.GeneratedConstructorAccessor161.newInstance(Unknown Source) ~[?:?]

	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:?]

	at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[?:?]

	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[?:?]

	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85) ~[?:?]

	at com.mysql.cj.protocol.a.NativeAuthenticationProvider.connect(NativeAuthenticationProvider.java:205) ~[?:?]

	at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1442) ~[?:?]

	at com.mysql.cj.NativeSession.connect(NativeSession.java:165) ~[?:?]

	at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:955) ~[?:?]

	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:825) ~[?:?]

	... 41 more

2019-04-21 02:29:45.502 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://192.168.1.43/openhab, user=admin

java.sql.SQLNonTransientConnectionException: CLIENT_PLUGIN_AUTH is required

	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:79) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240) ~[234:org.openhab.persistence.mysql:1.13.0]

	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207) ~[234:org.openhab.persistence.mysql:1.13.0]

	at java.sql.DriverManager.getConnection(DriverManager.java:664) ~[?:?]

	at java.sql.DriverManager.getConnection(DriverManager.java:247) ~[?:?]

	at org.openhab.persistence.mysql.internal.MysqlPersistenceService.connectToDatabase(MysqlPersistenceService.java:521) [234:org.openhab.persistence.mysql:1.13.0]

	at org.openhab.persistence.mysql.internal.MysqlPersistenceService.query(MysqlPersistenceService.java:599) [234:org.openhab.persistence.mysql:1.13.0]

	at org.openhab.core.persistence.internal.QueryablePersistenceServiceDelegate.query(QueryablePersistenceServiceDelegate.java:47) [202:org.openhab.core.compat1x:2.4.0]

	at org.eclipse.smarthome.ui.internal.chart.defaultchartprovider.DefaultChartProvider.addItem(DefaultChartProvider.java:345) [149:org.eclipse.smarthome.ui:0.10.0.oh240]

	at org.eclipse.smarthome.ui.internal.chart.defaultchartprovider.DefaultChartProvider.createChart(DefaultChartProvider.java:205) [149:org.eclipse.smarthome.ui:0.10.0.oh240]

	at org.eclipse.smarthome.ui.internal.chart.ChartServlet.doGet(ChartServlet.java:333) [149:org.eclipse.smarthome.ui:0.10.0.oh240]

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:687) [29:javax.servlet-api:3.1.0]

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) [29:javax.servlet-api:3.1.0]

	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:865) [85:org.eclipse.jetty.servlet:9.4.11.v20180605]

	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:535) [85:org.eclipse.jetty.servlet:9.4.11.v20180605]

	at org.ops4j.pax.web.service.jetty.internal.HttpServiceServletHandler.doHandle(HttpServiceServletHandler.java:71) [186:org.ops4j.pax.web.pax-web-jetty:7.2.3]

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:146) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548) [82:org.eclipse.jetty.security:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:257) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1317) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.ops4j.pax.web.service.jetty.internal.HttpServiceContext.doHandle(HttpServiceContext.java:293) [186:org.ops4j.pax.web.pax-web-jetty:7.2.3]

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473) [85:org.eclipse.jetty.servlet:9.4.11.v20180605]

	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1219) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.ops4j.pax.web.service.jetty.internal.JettyServerHandlerCollection.handle(JettyServerHandlerCollection.java:80) [186:org.ops4j.pax.web.pax-web-jetty:7.2.3]

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.Server.handle(Server.java:531) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:352) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:260) [84:org.eclipse.jetty.server:9.4.11.v20180605]

	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:281) [75:org.eclipse.jetty.io:9.4.11.v20180605]

	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102) [75:org.eclipse.jetty.io:9.4.11.v20180605]

	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:118) [75:org.eclipse.jetty.io:9.4.11.v20180605]

	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:762) [87:org.eclipse.jetty.util:9.4.11.v20180605]

	at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:680) [87:org.eclipse.jetty.util:9.4.11.v20180605]

	at java.lang.Thread.run(Thread.java:745) [?:?]

Caused by: com.mysql.cj.exceptions.UnableToConnectException: CLIENT_PLUGIN_AUTH is required

	at sun.reflect.GeneratedConstructorAccessor161.newInstance(Unknown Source) ~[?:?]

	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:?]

	at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[?:?]

	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[?:?]

	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85) ~[?:?]

	at com.mysql.cj.protocol.a.NativeAuthenticationProvider.connect(NativeAuthenticationProvider.java:205) ~[?:?]

	at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1442) ~[?:?]

	at com.mysql.cj.NativeSession.connect(NativeSession.java:165) ~[?:?]

	at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:955) ~[?:?]

	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:825) ~[?:?]

	... 41 more

Merkwürdig ist dabei halt das die Inserts funktionieren.

Ich habe es via MySQL Persistence und JDBC MySQL Persistence versucht. Bei beidem das selbe Problem.

Ich bin sehr dankbar für Ideen zur Problemlösung. Vielen dank im voraus!

[udo1toni]

Die erste Frage wäre, was hast Du upgedatet? Die Meldung
CLIENT_PLUGIN_AUTH is required
ist ja einigermaßen eindeutig. Wobei es für mich nach dem, was Du beschreibst, so aussieht, dass nur beim Lesen ein Problem besteht.

Eine Möglichkeit wäre, die aktuelle Konsiguration zu sichern, danach mit apt purge openhab2 die software komplett zu deinstallieren, erneut zu installieren (bitte nicht die Nightly Version, sondern entweder stable oder alternativ die testing Version) und zum Schluss die Konfiguration wieder einzuspielen.
Das Backup kann man bequem mittels openhab-cli erledigen. Das entstehende Archiv solltest Du vor dem purge natürlich an einen sicheren Ort auslagern openhab-cli führt auch den restore durch.

Eine Erfolgsgarantie gibt es aber nicht...