mySQL Persistence - A new classic error: - mySQL: Unable to find table for query 'GROUP_NAME'

ohb1138 · December 21, 2018, 7:15pm

Hi there,

this is my last issue that I am carrying with me since the beginning of running OpenHAB 2.2. I read through all the existing topics but none seems to fit or solve my problem …

For all of my groups I get this error in openhab.log:

20:03:16.408 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempDB'.
20:03:16.440 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gWeather'.
20:03:16.458 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gFritzSHome'.
20:03:16.558 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gGeoFence'.
20:03:16.566 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gLDruckWD'.
20:03:16.620 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempEG'.
20:03:16.627 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gKontakte'.
20:03:16.653 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gZwave'.
20:03:16.899 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gAstro'.
20:03:16.920 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempWD'.
20:03:16.964 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gBudHeizDruck'.
20:03:16.985 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gHeating'.
20:03:17.034 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempBad'.

Here is my mysql.persist:

Strategies {
    default = everyChange
}

Items {
    * : strategy = everyUpdate, everyChange, restoreOnStartup
    gTempDB* : strategy = every10Minutes, restoreOnStartup
    gTempEG* : strategy = every10Minutes, restoreOnStartup
    gTempWD* : strategy = every10Minutes, restoreOnStartup
    gAstro* : strategy = every10Minutes, restoreOnStartup
    gHeating* : strategy = every10Minutes, restoreOnStartup
    gTempBad* : strategy = every10Minutes, restoreOnStartup
    gLDruckWD* : strategy = every10Minutes, restoreOnStartup
    gBudHeizDruck* : strategy = every10Minutes, restoreOnStartup
    gZwave* : strategy = every10Minutes, restoreOnStartup
    gGeoFence* : strategy = every10Minutes, restoreOnStartup
    gWeather* : strategy = every10Minutes, restoreOnStartup
    gFritzSHome* : strategy = every10Minutes, restoreOnStartup
    gKontakte* : strategy = every10Minutes, restoreOnStartup
}

I do not know why my groups are not recognized/considered/stored … whatever by mySQL persistence.

I found some reported bugs on that topic but only for very old versions and as you can see in the mysql.persist file, I tried to add them manually.

I found one topic where someone added the tables manually to the openhab database but I do not know what characteristics the table should have so I can not do that.

I would kindly appreciate any hint or suggestion how I can get rid of these error messages or if there is no solution and I should simply ignore them (which I would not prefer )

Thanks for your support!!!

vzorglub · December 21, 2018, 8:38pm

When you use thing persistence:

gTempDB* : strategy = every10Minutes, restoreOnStartup

The members of the groups get perststed, not the group itself.
If you want to persist the group as well, you need to do:

gTempDB, gTempDB* : strategy = every10Minutes, restoreOnStartup

ohb1138 · December 21, 2018, 9:29pm

Changing the mysql.persist file to that causes:

22:27:15.266 [INFO ] [del.core.internal.ModelRepositoryImpl] - Refreshing model 'mysql.persist'
22:27:15.284 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempDB'.
22:27:15.286 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempDB'.
22:27:15.288 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gWeather'.
22:27:15.290 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gWeather'.
22:27:15.292 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gFritzSHome'.
22:27:15.294 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gFritzSHome'.
22:27:15.295 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gGeoFence'.
22:27:15.297 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gGeoFence'.
22:27:15.301 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gLDruckWD'.
22:27:15.303 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gLDruckWD'.
22:27:15.305 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempEG'.
22:27:15.307 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempEG'.
22:27:15.310 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gKontakte'.
22:27:15.311 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gKontakte'.
22:27:15.313 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gZwave'.
22:27:15.315 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gZwave'.
22:27:15.317 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gAstro'.
22:27:15.319 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gAstro'.
22:27:15.320 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempWD'.
22:27:15.322 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempWD'.
22:27:15.323 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gBudHeizDruck'.
22:27:15.325 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gBudHeizDruck'.
22:27:15.327 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gHeating'.
22:27:15.328 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gHeating'.
22:27:15.331 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempBad'.
22:27:15.332 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempBad'.
22:27:15.334 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempDB'.
22:27:15.335 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempDB'.
22:27:15.337 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempEG'.
22:27:15.338 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempEG'.
22:27:15.340 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempWD'.
22:27:15.341 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempWD'.
22:27:15.343 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gAstro'.
22:27:15.345 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gAstro'.
22:27:15.347 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gHeating'.
22:27:15.348 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gHeating'.
22:27:15.350 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempBad'.
22:27:15.352 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempBad'.
22:27:15.353 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gLDruckWD'.
22:27:15.355 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gLDruckWD'.
22:27:15.357 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gBudHeizDruck'.
22:27:15.358 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gBudHeizDruck'.
22:27:15.360 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gZwave'.
22:27:15.361 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gZwave'.
22:27:15.362 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gGeoFence'.
22:27:15.364 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gGeoFence'.
22:27:15.365 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gWeather'.
22:27:15.367 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gWeather'.
22:27:15.368 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gFritzSHome'.
22:27:15.370 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gFritzSHome'.
22:27:15.371 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gKontakte'.
22:27:15.373 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gKontakte'.

It seems to me that mysql fails to create tables for groups in the openhab database. Because with your recommended change I get the error message now twice and I assume one is from the entries in the mysql.persist file and the other entry from the query of the config files.

So the issue lies from what I believe to understand in the missing tables in the database as there are only items and no groups?

ohb1138 · December 21, 2018, 9:46pm

Thinking about it … it actually doesn’t make any sense to store the group items as they contain no information and are just logical instances to group items …

If it would be smart the group would also group the data of the members in an array - probably to export this array to use it for external charts with data from this group of items.

But I believe that the persistence definition is not that smart and therefore the group items are for grouping only with no intention to store the data of a group in a dedicated group area.

Probably the error message for groups is actually right - was there are no item entries in the database but it may be should simply be suppressed for group items?

I don’t know if some one can bring light into this …

ohb1138 · December 21, 2018, 10:21pm

Same issue in 2015 …

@chris suggested to raise an issue on the issue tracker to solve this in the future but it seems that it either was not solved or it came back some how.

What is the process to raise an issue?

Bredmich · December 21, 2018, 11:39pm

Don´t you need to define your Strategies like the every10Minutes?
So i think none of your Group Members are currently stored in the mysql persistence.

Strategies
{
  everyMinute	: "0 * * * * ?"
  every5Minutes : "0 */5 * * * ?"
  everyHour   : "0 0 * * * ?"
  everyDay    : "0 0 0 * * ?"
  default = everyChange
}

ohb1138 · December 22, 2018, 1:32pm

Bredmich:

Don´t you need to define your Strategies like the every10Minutes ?
So i think none of your Group Members are currently stored in the mysql persistence.
Strategies
{
  everyMinute	: "0 * * * * ?"
  every5Minutes : "0 */5 * * * ?"
  everyHour   : "0 0 * * * ?"
  everyDay    : "0 0 0 * * ?"
  default = everyChange
}

True, I forgot that … I added it but the behaviour did not change. Even after letting it run for several hours I just get double error entries when adding the group items to the mysql.persist file:

09:03:09.163 [INFO ] [del.core.internal.ModelRepositoryImpl] - Refreshing model 'mysql.persist'
09:03:09.171 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempDB'.
09:03:09.173 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempDB'.
09:03:09.174 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gWeather'.
09:03:09.176 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gFritzSHome'.
09:03:09.178 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gGeoFence'.
09:03:09.179 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gLDruckWD'.
09:03:09.181 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempEG'.
09:03:09.182 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempEG'.
09:03:09.183 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gKontakte'.
09:03:09.185 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gZwave'.
09:03:09.187 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gAstro'.
09:03:09.188 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempWD'.
09:03:09.190 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempWD'.
09:03:09.191 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gBudHeizDruck'.
09:03:09.193 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gHeating'.
09:03:09.194 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempBad'.
09:03:09.196 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempBad'.
09:03:09.197 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempDB'.
09:03:09.199 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempDB'.
09:03:09.200 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempEG'.
09:03:09.201 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempEG'.
09:03:09.203 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempWD'.
09:03:09.204 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempWD'.
09:03:09.205 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempBad'.
09:03:09.207 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gTempBad'.