I’m trying to plot the temperature provided by my Nest Thermostat, but I never get values.
Even on dummy indicators it doesn’t work. Dummy works fine for number data with ‘#’ icon, but do not work for number data with a ‘*’ icon…
Any idea ?
Can you post your items, persistence and sitemap, please
Hi,
persistance is set to jdbc and used with MariaDB.
At OpenHB server start, a single table has been created (items). Setting a graph in Habpanel added 1 entry into this unique table (itemid =1 ; item name is the name of the channel I want to plot (nest_thermostat_xxxxxxxxxxxxxxxxxxxxx_temperature)).
My persist file looks like this:
// persistence strategies have a name and definition and are referred to in the "Items" section
Strategies {
every15min : "15 * * * * ?"
everyDay : "0 0 0 * * ?"
// if no strategy is specified for an Item entry below, the default list will be used
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 the Item state of Heating_Mode and Notifications_Active on every change and restore them from the db at startup
//Heating_Mode, Notifications_Active: strategy = everyChange, restoreOnStartup
// additionally, persist all temperature and weather values every 15min
Temperature*, Humidity* : strategy = every15min
//every change
state* : strategy = everyChange
}
and my jdbc.cfg:
# D A T A B A S E C O N F I G
# Some URL-Examples, 'service' identifies and activates internally the correct jdbc driver.
# required database url like 'jdbc:<service>:<host>[:<port>;<attributes>]'
url=jdbc:mariadb://localhost:3307/openHAB
# required database user
user=HAB
# required database password
password=xxxxxxx
# E R R O R H A N D L I N G
# optional when Service is deactivated (optional, default: 0 -> ignore)
#errReconnectThreshold=
# I T E M O P E R A T I O N S
# optional tweaking SQL datatypes
# defaults:
#sqltype.CALL = VARCHAR(200)
#sqltype.COLOR = VARCHAR(70)
#sqltype.CONTACT = VARCHAR(6)
#sqltype.DATETIME = DATETIME
#sqltype.DIMMER = TINYINT
#sqltype.LOCATION = VARCHAR(30)
#sqltype.NUMBER = DOUBLE
#sqltype.ROLLERSHUTTER = TINYINT
#sqltype.STRING = VARCHAR(65500)
#sqltype.SWITCH = VARCHAR(6)
# For Itemtype "Number" default decimal digit count (optional, default: 3)
#numberDecimalcount=
# T A B L E O P E R A T I O N S
# Tablename Prefix String (optional, default: "item")
# for Migration from MYSQL-Bundle set to 'Item'.
#tableNamePrefix=Item
# Tablename Prefix generation, using Item real names or "item" (optional, default: false -> "item")
# If true, 'tableNamePrefix' is ignored.
tableUseRealItemNames=true
# Tablename Suffix length (optional, default: 4 -> 0001-9999)
# for Migration from MYSQL-Bundle set to 0.
#tableIdDigitCount=
# Rename existing Tables using tableUseRealItemNames and tableIdDigitCount (optional, default: false)
# USE WITH CARE! Deactivate after Renaming is done!
#rebuildTableNames=true
# D A T A B A S E C O N N E C T I O N S
# Some embeded Databases can handle only one Connection (optional, default: configured per database in packet org.openhab.persistence.jdbc.db.* )
# jdbc.maximumPoolSize = 1
# jdbc.minimumIdle = 1
# T I M E K E E P I N G
# (optional, default: false)
#enableLogTime=true
Anything weird ?
I think it is weird that no numeric values are visible into the mariadb databse, only this table items…
Some more info : into the log I see this:
java.sql.SQLException: Error preparing query: Incorrect table name 'nest_thermostat_xxxxxxx_temperature_0001' Query: SELECT time, value FROM nest_thermostat_b217c6fc_ljwxzcmasavycu4fa5ajldksfsdm1jek_temperature_0001 WHERE TIME>'2018-10-12 21:47:33' AND TIME<'2018-10-12 22:47:36' ORDER BY time ASC Parameters: []
So it expects a table with the name of the item (as stated into the jdbc.cfg), but obviously this table is not present…
The item is not being persisted
According to your persist file you are persisting the items belonging to the groups Temperature, Humidity every 15 minutes and the items belonging to the group state every change.
Does the item nest_thermostat_xxxxxxxxxxxxxxxxxxxxx_temperature belong to any of these groups?
Hi vzorglub,
You were right! So I fixed my jdbc.persist file to be like this:
// persistence strategies have a name and definition and are referred to in the "Items" section
Strategies {
every5min : "*/5 * * * * ?"
every15min : "*/15 * * * * ?"
everyDay : "0 0 0 * * ?"
// if no strategy is specified for an Item entry below, the default list will be used
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 the Item state of Heating_Mode and Notifications_Active on every change and restore them from the db at startup
//Heating_Mode, Notifications_Active: strategy = everyChange, restoreOnStartup
// additionally, persist all temperature and weather values every hour
nest_thermostat_aaaaaaaa_laaaaaaAsavaaaaaa5AJLdKSfsaaaaaa_temperature, nest_thermostat_aaaaaaaa_laaaaaaAsavaaaaaa5AJLdKSfsaaaaaa_humidity : strategy = every5min
//every change
samsungtv_tv_aaaaa202_aaaa_1aa0_aaaa_fc8faaaa9aaa_power : strategy = everyChange
}
It works fine for my samsung TV ! But not for the Nest.
So I went into the log and found this line:
2018-10-14 14:05:45.557 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!!
java.sql.SQLException: Error preparing query: Incorrect table name 'nest_thermostat_aaaaaaaa_laaaaaaAsavaaaaaa5AJLdKSfsaaaaaa_humidity_0002' Query: CREATE TABLE IF NOT EXISTS nest_thermostat_aaaaaaaa_laaaaaaAsavaaaaaa5AJLdKSfsaaaaaa_humidity_0002 (time TIMESTAMP(3) NOT NULL, value DOUBLE, PRIMARY KEY(time)) Parameters: []
So I tried the SQL Query myself into myPHPAdmin webinterface. And same thing, there is an error with the query.
In fact the error is pretty basic : samsung item identifier is 55 characters long, but Nest item identifiers are 66 characters long (71 characters for the table name because a counter is concatenated at the end to create the table name)! And this last number is problematic with MariaDB : table names cannot be longer than 64 characters (and cannot be extended on MariaDB side).
Any idea on how I can fix this ?
Set the tableUseRealItemNames as false in the jdbc.cfg
See:
Hi vzorglub,
I wanted to keep the item names ‘clear’ into the DB so I could query it easily, but I can handle joining data !
So it seems that it working fine… except for the strategy.
Nest values are reauired to follow the ‘every5min’ strategie.
This strategy is based on the following cron command : “*/5 * * * * ?”
But instead of logging values every 5 minutes, it logs data every 5 seconds.
Any toughts ?
"0 0/5 * * * ?"
Every 5 minutes starting at minute 0
It works !
But isn’t Cron suppose to follow this format: min hour day(month) month day(week) ?
In any case, thank you very much for your kind help !
openHAB uses cron with seconds too.
Please mark the thread as solved, thanks