I have a bad problem with some n3-line-charts and Habpanel.
I suspect it might be related with the MySQL server but I can’t figure out how.
What I have:
OH2.1 on an Orange Pi Zero with MySQL persistence using the MySQL server of a Drobo 5N NAS.
A series of Habpanel dashboards used for different rooms and some showing n3-line-charts of weather, indoor climate and energy consumption data. Some of them pull quite a few (about 12000) datapoints
What’s changed recently:
Until some days ago the persistence was on a local MySQL database stored on a flash drive. That was fairly unreliable and required frequent intervention. So instead of running my own, I added a database for openhab to the preexisting MySQL service of a Drobo 5N NAS on my network. That works like a charm - most of the time.
I also changed the way some of my persisted values are treated by rules, but I’ve not changed the charts, the format or quantity of data used, so I think that’s not related.
What goes wrong:
After some heavier chart use, e.g. opening the dashboard with the 12000 datapoint charts a few times, charts stop working altogether:
Other habpanel dashboards still work, but the ones containing charts just show blank chart axis.
I can also not edit the non-working dashboards anymore - clicking the pencil next to the dashboard name does nothing.
In the Karaf console I see nothing get logged with relation to Habpanel or MySQL, neither Info nor Warnings. MySQL persistence continues to receive data normally for my items. Only thing I see logged is the HABpanel stopped/started message if I edit one of the still functional dashboards.
Doing this does not restore the charts.
If however I restart OpenHAB2 completely, things will return to normal, charts show and dashboards can all be edited.
Does anybody have ideas what could be going on?
Shouldn’t I see errors if there’s something wrong querying the database? And how can HABpanel fail so seriously without logging anything?
Did you see anything on the client-side?
Try opening the Chrome developer tools (F12 on Windows), reload the page, and check for anything red in the Console and Network tabs. Could be a problem getting the data and clicking on the failed requests might give you the actual error message.
I noticed in the mysql.cfg I screwed up the mysql:reconnectCnt line, so I think Openhab lost the connection (why?) and then failed to reconnect.
However if I look at the log:tail in the Karaf console should I not get lots of errors about mysql every time a change fails to get persisted?? There’s absolutely nothing showing.
Let’s see if it stays stable now with the reconnect setting restored…
Today I managed to get the original broken state again, here’s what I saw:
The dashboards with charts show only empty axis.
The Chrome dev console shows none of the errors I saw yesterday, everything seems normal. In the network tab there’s nothing either.
The Karaf console shows no error messages about persistence.
The persistence link quoted in my previous post now does NOT throw a 400 error. It simply gets no result at all, the server does not respond to it.
At the same time I see that item updates come through on the Chome console and I see the updates are also continuing to be persisted into the MySQL database.
Any ideas what this could be or where to continue searching?
I experienced quite the same problem while using MySQL persistence.
After analyzing the Java-VM OpenHAB was running in I noticed a deadlock in the MySQL Java connector.
The deadlock seems related to this MySQL bug.
Since the connector JAR used in the persistence plugin is very old I tried to replace it with a current one where this bug should have been fixed. But then I read about the generic JDBC persistence addon which is using a more recent driver. I used it for a while and did not have the problem again. A few weeks ago I switched to MariaDB but for reasons not related to this problem.
But be very careful when migrating to the JDBC addon! There might be additional steps neccessary to make it work in addition to the steps described in the wiki. I had to rename some tables in the MySQL database (but I don’t know exactly which ones) due to different casing in the JDBC persistence addon.
Interesting! I tried migrating to the JDBC connection but it just threw lots of exceptions, presumably to mismatching data types. So I decided to just drop the database and start over rather than to try and figure out the differences. That worked.
Jury is still out on the stability, i’ll see that when it has enough data gathered.
Now I just have one issue left: my MySQL server runs in a different time zone (America/Los_Angeles) and I was using “localtime=true” so that timestamps would go by Openhab’s time. I tried adding “?serverTimezone=America/Los_Angeles” (server’s timezone) or “?serverTimezone=Europe/Madrid” (my timezone) to the connection string but the timestamps seem unimpressed
Stability with JDBC Mysql instead of Mysql is looking good - I can now graph even massive charts (tried >30k datapoints at once) without sending the whole system into limbo. Awesome!
Now I just need to figure out how to fix the timezone offset between Openhab and the database…
Hello, could you remember what exactly you had to change on the MySQL tables to get migration working? I do have now the same issue that the migration is not working and the existing DB cannot be used by the JDBC/MySQL binding any more…
Thanks
I remember that I had to replace ‘item’ with ‘Item’ or vice versa in some spots.
Just compare the database tables between the two connectors and you will clearly see the difference.
You can also make a view that simply refers to the other table. For some time I was using a tandem of writing with MySQL and reading JDBC (due to the stability issues) with this trick.
Anyway it seems like with OH 2.4.0 the MySQL instability is finally resolved and I can just use MySQL for all.
