Mysql persistence complains it can't find tables

Setup, Configuration and Use Installation
1
  • Platform information:
    • Hardware: RaspPi 3
    • OS: Raspberian
    • Java Runtime Environment: Oracle 1.8_
    • openHAB version: 2.4.0-20181001 something…
  • Issue of the topic: mySQL persistence complains about SOME tables not being found
  • Please post configurations (if applicable):
    • Items configuration related to the issue
    • Sitemap configuration related to the issue
    • Rules code related to the issue
    • Services configuration related to the issue
  • If logs where generated please post these here using code fences:
018-10-02 09:20:02.860 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'MainHallManual_Mode'.
2018-10-02 09:20:03.154 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'ZWTaylorRoomTRV_Temperature'.
2018-10-02 09:20:03.160 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'ZWEnsuiteTRV_Temperature'.
2018-10-02 09:20:03.188 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'ZWBackroomTRV_SensorTemperature'.

Some tables get created… And populate fine. But some just spit errors about not being able to find the table. What gives? The persistence is set to automatically create tables. So why does it randomly decide not to and just complain?

Config for persistence is

oot@piman-301:/etc/openhab2/persistence# cat mysql.persist
// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
	// if no strategy is specified for an item entry below, the default list will be used
	everyMinute	: "0 * * * * ?"
	every5Minutes : "0 */5 * * * ?"
   	everyHour   : "0 0 * * * ?"
   	everyDay    : "0 0 0 * * ?"
   	default = everyChange
}

/*
 * Each line in this section defines for which item(s) which strategy(ies) should be applied.
 * You can list single items, use "*" for all items or "groupitem*" for all members of a group
 * item (excl. the group item itself).
 */

Items {
    // persist all items once a day and on every change and restore them from the db at startup
    * : strategy = everyChange, everyDay, restoreOnStartup

    // Get Temps every 5 minutes at least...
    ZW_Boiler* : strategy = every5Minutes

    // additionally, persist all temperature and weather values every hour
    gTemperatur* : strategy = every5Minutes, restoreOnStartup
}
root@piman-301:/etc/openhab2/persistence#
2

This is a worry too…

2018-10-02 09:37:39.580 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not create table for item 'MH_ThermoMode' with statement 'CREATE TABLE Item68 (Time DATETIME, Value VARCHAR(20000), PRIMARY KEY(Time));': Column length too big for column 'Value' (max = 16383); use BLOB or TEXT instead
2018-10-02 09:37:39.582 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Item 'MH_ThermoMode' was not added to the table - removing index
2018-10-02 09:37:39.594 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'MH_ThermoMode' in database with statement 'INSERT INTO Item68 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': Table 'OpenHAB.Item68' doesn't exist
3

OK… Solved both…

  1. The first issue. It’s HABPanel doing lookups for items that were previously configured (But deleted because I needed to cleanup the zWave). The frustrating thing about this is that there’s noway to know that the lookup was being performed for HABPanel, and not for updating it. (Apart from experience. I’m all for learning from experience, but frustrating the users isn’t a good way to build a product).

  2. The mySQL table creation problem. A simple fix. There’s a note around here on how to doit it, but sadly only for OH1.x. The way to doit for OH2.x is to add the same entry

mysql:sqltype.string=VARCHAR(1024)

into /etc/openhab2/services/mysql.cfg

An lo! mysql now works again…

6 Likes
4

@travellingkiwi Thanks for this solution. Works like a charm!
Just a minor note on your solution:
This looks weird compared to the rest of the setup of this file but adding this as e.g. line 3 works perfectly!