I have to following problem: I got my MySQL persistence running well with openHAB on my PC and MySQL / MariaDB on the synology. All tables were created well, values stored, all fine.
When I brought this config to my openHAB on the Synology all data is stored well in existing tables, but no new tables are created. Error like this:
11:51:52.146 ERROR o.o.p.m.i.MysqlPersistenceService[:256]- mySQL: Could not create table for item 'PM_EG_Kueche' with statement 'CREATE TABLE Item52 (Time DATETIME, Value null, PRIMARY KEY(Time));': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null, PRIMARY KEY(Time))' at line 1
A kind of this was created well with running it on the PC…
I have the same error on my Freenas system (FreeBSD) running openhab in a jail.
The error is related to a calculated item (in a rule). Other items are stored fine with mysql.
Number power_actual_usage "Actual usage [%.2f kW]" <energy> (BG_Meterkast) {mqtt="<[broker1:power/actual-usage:state:default]"}
Error message:
2015-09-01 04:22:00.323 [ERROR] [.p.m.i.MysqlPersistenceService] - mySQL: Could not create table for item 'power_actual_usage' with statement 'CREATE TABLE Item1 (Time DATETIME, **Value null**, PRIMARY KEY(Time));': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null, PRIMARY KEY(Time))' at line 1
I was wrong in my previous post that it was a calculated item.
However, the same value is stored using rrd4j and that seems to work well.
rrd4j.persist
// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
everyMinute : "0 * * * * ?"
everyHour : "0 0 * * * ?"
everyDay : "0 0 0 * * ?"
// if no strategy is specified for an item entry below, the default list will be used
default = everyChange, everyUpdate
}
/*
* 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 = everyDay, restoreOnStartup
power_actual_usage: strategy = everyMinute;
gas_cumulative_usage: strategy = everyMinute;
power_cumulative_usage_1:strategy = everyHour;
power_cumulative_usage_2:strategy = everyHour;
}
mysql.persist
// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
everyMinute : "0 * * * * ?"
everyHour : "0 0 * * * ?"
everyDay : "0 0 0 * * ?"
// if no strategy is specified for an item entry below, the default list will be used
default = everyChange, everyUpdate
}
Items {
// persist all items once a day and on every change and restore them from the db at startup
* : strategy = everyDay, restoreOnStartup
power_actual_usage: strategy = everyMinute;
gas_cumulative_usage: strategy = everyMinute;
power_cumulative_usage_1: strategy = everyHour;
power_cumulative_usage_2: strategy = everyHour;
}
Its a bit strange that it’s a Number type. The null in the error message indicates that it hasn’t correctly picked up the data type. Is something else setting the data type incorrectly (eg in a rule)?
Can you take a look in the log and see if there’s an entry just before this error that starts with the following -:
I cannot find mySQL: Use Itemtype in the log. Therefor I ran openhab again with debugging on but I do not see the error message while creating the table (and table Item31 is created). Also with debugging off it works.
I cannot explain why it works now and not before.
