Unable to switch from MySQL to JDBC persistence

Hi,
I’m trying to switch from MySQL to JDBC using the tutorial in here: JDBC Persistence.
I did put the “mysql-connector-java-5.1.38.jar” into the /addons- folder and removed the one for mysql.
Changed the name of the persist-file from mysql.persist to jdbc.persist.
Change the openhab.config the have:
persistence:default=jdbc
and
jdbc:url=jdbc:mysql://localhost:3306/openhab
jdbc:user=openhab
jdbc:password=mypassword
jdbc:tableNamePrefix=Item
jdbc:tableUseRealItemNames=false
jdbc:tableIdDigitCount=0

Restarted openhab

I only get errors reported if I try do get something from the database (like a chart). A noticeable log entry states its missing the mysql persistence???
The database is not getting any data?

What am I missing?

Hi @opus,

I have tested new “mysql-connector-java-5.1.38.jar”, it works.
You configuration looks good!

please can you post the debug output of openHAB.
From starting to first error + about 20 Lines please.

Helmut

Hi,
Thanks for the feedback. I will do another test, however I won’t be able to do that for the next coupld of days.

Tried to do the test last night, while searching for the debug-log I realized that the debug-mode would be neccessary for that.
During my first Trials on a Windows-machine I used this mode, however on the actual RaspBerryPI I can’t start in Debug-Mode???
In Windows it was just using the “start_debug.bat” instead of “start.bat”.
I’m a bit lost, sorry.

Since my original problem (access the MySQL database, written by the persistence, from an android-device) is solved using another way (WebServer on the machine holding the database, using a PHP-script start the MySQLI commands). I don’t need JDBC anymore.
However I would do this test if I you could tell me a way to start the debug-mode, or is there another way to get the “debug output”?

Start openHAB 1.8 in debug mode:

Thanks fpor those hints.
I managed to start start_debug.sh using “sudo” (stupid me, but knowledge on Linux is about 3 weeks old)
.
The test in Debug did show NO errors???
The only intersting thing I found:

12:28:06.091 [INFO ] [ui.internal.chart.ChartServlet:251  ] - Illegal argument in chart: {}
java.lang.IllegalArgumentException: Persistence service not found 'mysql'.
    at org.openhab.ui.internal.chart.DefaultChartProvider.createChart(DefaultChartProvider.java:176) ~[na:na]
    at org.openhab.ui.internal.chart.ChartServlet.doGet(ChartServlet.java:245) ~[na:na]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:735) [javax.servlet_3.0.0.v201112011016.jar:na]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:848) [javax.servlet_3.0.0.v201112011016.jar:na]
    at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(ServletRegistration.java:61) [org.eclipse.equinox.http.servlet_1.1.300.v20120522-1841.jar:na]
    at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:128) [org.eclipse.equinox.http.servlet_1.1.300.v20120522-1841.jar:na]
    at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:60) [org.eclipse.equinox.http.servlet_1.1.300.v20120522-1841.jar:na]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:848) [javax.servlet_3.0.0.v201112011016.jar:na]
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:598) [org.eclipse.jetty.servlet_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:486) [org.eclipse.jetty.servlet_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1065) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:413) [org.eclipse.jetty.servlet_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:192) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:999) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:250) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:149) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:111) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.Server.handle(Server.java:350) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:454) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.AbstractHttpConnection.headerComplete(AbstractHttpConnection.java:890) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.headerComplete(AbstractHttpConnection.java:944) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:630) [org.eclipse.jetty.http_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:230) [org.eclipse.jetty.http_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:77) [org.eclipse.jetty.server_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:606) [org.eclipse.jetty.io_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:46) [org.eclipse.jetty.io_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:603) [org.eclipse.jetty.util_8.1.3.v20120522.jar:8.1.3.v20120522]
    at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:538) [org.eclipse.jetty.util_8.1.3.v20120522.jar:8.1.3.v20120522]
    at java.lang.Thread.run(Thread.java:744) [na:1.8.0]

I did check the database directly, no entries were made during the test.
I must be misundestanig something, but if there are no entries into the database during the test, I assume the persistence logging isn’t working.

No prob, we all began!!! :slightly_smiling:

We are speaking about a openHAB 1.8, right?

First, Database:
jdbc-Bundle throws many errors and warnings if it is not start-able!
If it was started, in openhab.log you can definitely find a line like:

[DEBUG] [o.o.p.jdbc.internal.JdbcMapper:41   ] - JDBC::pingDB

If not, something is missing!

Second, your Chart:
Don’t know why, but chart wants to use a not existing Persistence Service: (you told openHab to use ‘jdbc’ with persistence:default=jdbc

Persistence service not found 'mysql'.

So we tell the chart to use the right service:

Like described here:

Sitemap Example:
Chart item=CsBaBathTemperature service=“jdbc” period=12h refresh=300000 visibility=[selectChartIntervall==0, selectChartIntervall==“Uninitialized”]

Sitemap Example using Webview:
Webview url=“http://192.168.0.100:8080/chart?groups=TempOutside&period=H&service=jdbc&w=800&h=480” height=5

Hope this helps
Helmut

Yes , I’m using OpenHab 1.8

,and No, I don’t see any Debug-entry with “JdbcMapper”. So something is missing.

As for the use of mysql for charts, the charts in the sitemap are called without stating service. I have only a SavedLink for a chart which used that statement (removed, works without!)

Where should I look?

BTW:
My plan was to use the data in the SQL-database in an android-app(selfmade). I managed that (using an webserver an sql-injects in a php-file on that server), however the speed at which I can present the data in a “chart” is soooooooo slooooooooooooow.
I went the easy way, just show the openhab-chart in a “WebView”.
Make the long sory short: Thanks for OpenHAB!

If you want, zip or gz your [openHAB 1.8 folder]/addons and [openHAB 1.8 folder]/configurations.
Upload it anywhere and send me a PM with the link for download. I will have a look at it then.