MySQL Persist failed after upgrade OH2 to 2.1 snapshot

xefil · February 22, 2017, 8:58am

Hello,

I’ve upgraded Openhab2 to SNAPSHOT version: openhab2 2.1.0~20170221002559-1

I get:

Configuration model ‘mysql.persist’ has errors, therefore ignoring it: [8,1]: missing RULE_ID at ‘}’

Here the content:

// Persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
        everyMinute     : "0 * * * * ?"
        every5Minutes   : "0 0/5 * * * ?"

        // If no strategy is specified for an item entry below, the default list will be used.
        default = 
}

/*
 * 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 {
        node21_DHT11_humid : strategy = everyUpdate, restoreOnStartup
        node21_DHT11_temp : strategy = everyUpdate, restoreOnStartup
        node21_DHT22_humid : strategy = everyUpdate, restoreOnStartup
        node21_DHT22_temp : strategy = everyUpdate, restoreOnStartup
        node20_DHT11_humid : strategy = everyUpdate, restoreOnStartup
        node20_DHT11_temp : strategy = everyUpdate, restoreOnStartup
        node20_sw1 : strategy = everyUpdate, restoreOnStartup
        node21_ONEWIRE1_temp : strategy = everyUpdate, restoreOnStartup
        node20_sw2 : strategy = everyUpdate, restoreOnStartup
        node20_sw1_status : strategy = everyUpdate, restoreOnStartup
        node20_sw2_status : strategy = everyUpdate, restoreOnStartup
        node22_DHT11_temp : strategy =  everyUpdate, every5Minutes, restoreOnStartup
        node22_DHT11_humid : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node22_ONEWIRE1_temp : strategy =  everyUpdate, every5Minutes, restoreOnStartup
        node22_lux_level : strategy = everyUpdate, every5Minutes, restoreOnStartup
        alarm_switch : strategy = everyUpdate, restoreOnStartup
        alarm_switch_node25_motion01 : strategy = everyUpdate, restoreOnStartup
        node20_gate01 : strategy = everyUpdate, restoreOnStartup
        node20_door01 : strategy = everyUpdate, restoreOnStartup
        node23_door01 : strategy = everyUpdate, restoreOnStartup
        node24_watt : strategy = everyUpdate, restoreOnStartup
        node26_DHT11_temp : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node26_DHT11_humid : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node26_ONEWIRE1_temp : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node27_bmp180_temp : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node27_bmp180_hpa : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node27_bmp180_forecast : strategy = everyMinute, everyUpdate, restoreOnStartup
        node27_rainTrigger : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node27_rainRaw : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node27_ONEWIRE1_temp : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node27_DHT11_humid : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node27_DHT11_temp : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node27_luxLevel : strategy = everyUpdate, every5Minutes, restoreOnStartup
        sketch20 : strategy = everyUpdate, restoreOnStartup
        sketch20_ver : strategy = everyUpdate, restoreOnStartup
        sketch20ChangedTime : strategy = everyUpdate, restoreOnStartup
        sketch21 : strategy = everyUpdate, restoreOnStartup
        sketch21_ver : strategy = everyUpdate, restoreOnStartup
        sketch21ChangedTime : strategy = everyUpdate, restoreOnStartup
        sketch22 : strategy = everyUpdate, restoreOnStartup
        sketch22_ver : strategy = everyUpdate, restoreOnStartup
        sketch22ChangedTime : strategy = everyUpdate, restoreOnStartup
        sketch23 : strategy = everyUpdate, restoreOnStartup
        sketch23_ver : strategy = everyUpdate, restoreOnStartup
        sketch23ChangedTime : strategy = everyUpdate, restoreOnStartup
        sketch24 : strategy = everyUpdate, restoreOnStartup
        sketch24_ver : strategy = everyUpdate, restoreOnStartup
        sketch24ChangedTime : strategy = everyUpdate, restoreOnStartup
        sketch25 : strategy = everyUpdate, restoreOnStartup
        sketch25_ver : strategy = everyUpdate, restoreOnStartup
        sketch25ChangedTime : strategy = everyUpdate, restoreOnStartup
        sketch26 : strategy = everyUpdate, restoreOnStartup
        sketch26_ver : strategy = everyUpdate, restoreOnStartup
        sketch26ChangedTime : strategy = everyUpdate, restoreOnStartup
        sketch27 : strategy = everyUpdate, restoreOnStartup
        sketch27_ver : strategy = everyUpdate, restoreOnStartup
        sketch27ChangedTime : strategy = everyUpdate, restoreOnStartup
        sketch28 : strategy = everyUpdate, restoreOnStartup
        sketch28_ver : strategy = everyUpdate, restoreOnStartup
        sketch28ChangedTime : strategy = everyUpdate, restoreOnStartup
        sketch29 : strategy = everyUpdate, restoreOnStartup
        sketch29_ver : strategy = everyUpdate, restoreOnStartup
        sketch29ChangedTime : strategy = everyUpdate, restoreOnStartup
        sketch30 : strategy = everyUpdate, restoreOnStartup
        sketch30_ver : strategy = everyUpdate, restoreOnStartup
        sketch30ChangedTime : strategy = everyUpdate, restoreOnStartup
        sketch31_ver : strategy = everyUpdate, restoreOnStartup
        sketch31ChangedTime : strategy = everyUpdate, restoreOnStartup
        node30_ONEWIRE1_temp : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node30_DHT11_temp : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node30_DHT11_humid : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node30_sw1_isTriggered : strategy = everyUpdate, restoreOnStartup
        node30_sw1_setpoint : strategy = everyUpdate, restoreOnStartup
        node30_sw1_setpointIcon : strategy = everyUpdate, restoreOnStartup
        node30_sw1_hitpoint : strategy = everyUpdate, restoreOnStartup
        node31_ONEWIRE1_temp : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node31_DHT11_temp : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node31_DHT11_humid : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node31_SoilLevel : strategy = everyUpdate, every5Minutes, restoreOnStartup
        node31_SoilTrigger : strategy = everyUpdate, every5Minutes, restoreOnStartup
}

What’s missing/changed from 2.0 to 2.1?

Thanks,

Simon

xefil · February 22, 2017, 9:07am

Update:

It was enough to comment out the “default”.
BTW, my upgrade was because, after upgrading from Openhab1 to Openhab2, new tables for specified items are NOT created:

2017-02-22 10:05:25.834 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘sketch22’.
2017-02-22 10:05:25.840 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘sketch22_ver’.
2017-02-22 10:05:25.846 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘sketch22ChangedTime’.
2017-02-22 10:05:25.854 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘sketch30’.
2017-02-22 10:05:25.861 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘sketch30ChangedTime’.
2017-02-22 10:05:25.869 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘sketch31ChangedTime’.
2017-02-22 10:05:25.877 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘node31_SoilTrigger’.

How to solve/debug it?

Thanks!

Simon

orc.fork.org · April 7, 2018, 5:14pm

This is an old question, but I’m answering it in case anyone else stumbles upon it, because this has been bugging me for a while.

Tables using MySQL persistence aren’t created until an Item receives an update (as defined in your persistence strategies). So a good way to generate tables for all your items would be to (temporarily) change your Items strategy to something like this:

Items {
    *: strategy = everyUpdate
}

The wildcard [*] is shorthand for ‘all Items.’

Doing this will cause a table to be created for your Item any time it receives an update. (Note that your logs will complain about being unable to find tables until it receives one!) But this is a poor strategy, long-term, because it will populate the database for each and every update an Item receives.

Once you’ve generated all your tables (which you can confirm by looking at the Items table in your database, and ensuring that each entry has a corresponding ItemN table (e.g., Item1, Item2, Item3), you should fine-tune your strategies. Groups work nicely for this, and you can find strategy syntax for groups here.

–

Another note: before I understood how to trigger table creation, I thought I could just create them manually, following the table structure in already-existing tables. Not sure why, but this doesn’t work. Manually populating the Items table with your Item name and creating a corresponding ItemN table will not be recognized by the system, and your data will not be populated.