Always `Unable to find table for query` when viewing chart

Setup, Configuration and Use Beginners
1

I am running a minimal install of OpenHAB 1.8.0. I have configured the Weather Binding to use Wunderground.com, but I also want OpenHAB to be able to render e.g. a chart for the temperature and humidity.
However, whenever I attempt to load such a chart (by opening my “Dublin Weather” frame), I will see an empty one, and in the logs the following appears:

2016-01-16 15:50:48.044 [DEBUG] [.p.m.i.MysqlPersistenceService] - mySQL query: item is Weather_Temperature
2016-01-16 15:50:48.045 [ERROR] [.p.m.i.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Weather_Temperature’.
2016-01-16 15:50:48.047 [DEBUG] [.p.m.i.MysqlPersistenceService] - mySQL query: item is Weather_Temperature
2016-01-16 15:50:48.047 [ERROR] [.p.m.i.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Weather_Temperature’.
2016-01-16 15:50:48.048 [DEBUG] [.p.m.i.MysqlPersistenceService] - mySQL query: item is Weather_Humidity
2016-01-16 15:50:48.049 [ERROR] [.p.m.i.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Weather_Humidity’.
2016-01-16 15:50:48.050 [DEBUG] [.p.m.i.MysqlPersistenceService] - mySQL query: item is Weather_Humidity
2016-01-16 15:50:48.050 [ERROR] [.p.m.i.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Weather_Humidity’.

The only explanation I have seen so far is that this can happen if no data has been persisted yet. However, I have kept my OpenHAB running for many hours now (and weather data is being fetched every 15 minutes).
I have also verified that OpenHAB has read/write access to my MySQL database.

How can I make this work?

Below, I have added my configurations:

I have a weather.items containing the following:

Group   gWeather        "Weather"           <sun_clouds>    (All)
Group   gWeather_Chart  "Weather Charts"    <chart>         (gWeather)
/* Same as Weather_Temperature, but different label and icon */
Number      Weather_Summary         "Dublin Weather [%.1f °C]"     <sun_clouds>    (gWeather)                  {weather="locationId=dublin, type=temperature, property=current"}
Number      Weather_Temperature     "Temperature [%.1f °C]"            <temperature>   (gWeather,gWeather_Chart)   {weather="locationId=dublin, type=temperature, property=current"}
Number      Weather_Temp_Feel       "Felt Temperature [%.1f °C]"   <temperature>   (gWeather)                  {weather="locationId=dublin, type=temperature, property=feel"}
String      Weather_Condition       "Condition [%s]"                <sun_clouds>    (gWeather)                  {weather="locationId=dublin, type=condition, property=text"}
Number      Weather_Humidity        "Outside Humidity [%d %%]"                      (gWeather,gWeather_Chart)   {weather="locationId=dublin, type=atmosphere, property=humidity"}
Number      Weather_Rain_Chance     "Chance of Rain [%d %%]"        <rain>          (gWeather)                  {weather="locationId=dublin, type=precipitation, property=probability"}
Number      Weather_Rain_Total      "Amount of Rain [%d mm]"        <rain>          (gWeather)                  {weather="locationId=dublin, type=precipitation, property=total"}
String      Weather_Wind_Dir        "Wind Direction [%s]"           <wind>          (gWeather)                  {weather="locationId=dublin, type=wind, property=direction"}
Number      Weather_Wind_Speed      "Wind Speed [%d km/h]"          <wind>          (gWeather)                  {weather="locationId=dublin, type=wind, property=speed"}
Number      Weather_Pressure        "Pressure [%.2f mb]"                            (gWeather)                  {weather="locationId=dublin, type=atmosphere, property=pressure"}
Number      Weather_Chart_Period    "Chart Period"
DateTime    Weather_LastUpdate      "Last Update [%1$ta %1$tR]"     <clock>

The group gWeather_Chart exists solely to be able to create persistence rules for the Weather_Temperature and Weather_Humidity items, which brings me to my weather-mysql.persist file:

Strategies {
    everyHour   : "0 0 * * * ?"
    default = everyChange
}
Items {
    gWeather_Chart* : strategy = everyChange, everyHour
}

And finally, this is my default.sitemap; It defines a “Weather” section, underneath which I declared a chart for the gWeather_Chart group (which would also be accessible via chart?groups=gWeather_Chart&period=W&random=1&h=800&w=1200):

sitemap home label="Home"
{
    Frame label="Weather" {
        Text item=Weather_Summary valuecolor=[Weather_LastUpdate=="Uninitialized"="lightgray",Weather_LastUpdate>90="lightgray",>25="orange",>15="green",>5="orange",<=5="blue"] {
            Frame {
                Text item=Weather_Condition
                Text item=Weather_LastUpdate visibility=[Weather_LastUpdate>30] valuecolor=[Weather_LastUpdate>120="orange", Weather_LastUpdate>300="red"]
            }
            Frame {
                Switch item=Weather_Chart_Period label="Chart Period" mappings=[0="Hour", 1="Day", 2="Week"]
                Chart item=gWeather_Chart period=h refresh=6000 visibility=[Weather_Chart_Period==0, Weather_Chart_Period=="Uninitialized"]
                Chart item=gWeather_Chart period=D refresh=30000 visibility=[Weather_Chart_Period==1]
                Chart item=gWeather_Chart period=W refresh=30000 visibility=[Weather_Chart_Period==2]
            }
        }
    }
}

I enabled logging for the mysql persistence and weather bindings to get some more details, but nothing helpful for fixing this issue appeared in the logs yet:

<logger name="org.openhab.binding.weather" level="DEBUG" />
<logger name="org.openhab.persistence.mysql" level="DEBUG" />
2

Did you configure your openhab.cfg properly? That means:

    # The name of the default persistence service to use
    #persistence:default=rrd4j
    persistence:default=mysql


############################ SQL Persistence Service ##################################
# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
#jdbc:mysql:url=127.0.0.1:3306/openhab
mysql:url=jdbc:mysql://**yourIP**:3306/openhab

# the database user
mysql:user=**yourUserName**

# the database password
mysql:password=**yourPassword**

# the reconnection counter
mysql:reconnectCnt=1

# the connection timeout (in seconds)
#mysql:waitTimeout=

# optional tweaking of mysql datatypes
# example as described in https://github.com/openhab/openhab/issues/710
mysql:sqltype.string=VARCHAR(20000)

Additionally I added the service to use in the sitemap, e.g:

Text  	item=gTemperatur {
		Frame	{	
			Switch item=gTemperatur_ChartSwitch01 mappings=[0="D", 1="3D", 2="W", 3="2W", 4="M", 5="4M", 6="1J"]
			Chart item=gTemperatur		service="mysql" period=D 	refresh=360000 visibility=[gTemperatur_ChartSwitch01==0, gTemperatur_ChartSwitch01=="Uninitialized"]
			Chart item=gTemperatur		service="mysql" period=3D 	refresh=360000 visibility=[gTemperatur_ChartSwitch01==1]
			Chart item=gTemperatur		service="mysql" period=W 	refresh=360000 visibility=[gTemperatur_ChartSwitch01==2]
			Chart item=gTemperatur		service="mysql" period=2W 	refresh=360000 visibility=[gTemperatur_ChartSwitch01==3]
			Chart item=gTemperatur		service="mysql" period=M 	refresh=360000 visibility=[gTemperatur_ChartSwitch01==4]
			Chart item=gTemperatur		service="mysql" period=4M 	refresh=360000 visibility=[gTemperatur_ChartSwitch01==5]
			Chart item=gTemperatur		service="mysql" period=Y 	refresh=360000 visibility=[gTemperatur_ChartSwitch01==6]
				}

Does this work for you?

3

I tried adding the service="mysql" bit to my sitemap, but that didn’t change anything; I’m still getting the same errors when trying to retrieve a chart. (And I did make sure openHAB was running for a while, to give it a chance to collect some weather data).

The problem seems to be (at least in part) that openHAB does not create the required MySQL tables.

I have also verified that openHAB can read and write my MySQL database:

I have also verified that OpenHAB has read/write access to my MySQL database.

Not only have I checked that the credentials (which I put into my openhab.cfg - see below) work, but I also noticed that openHAB was able to create an Items table in said database:

mysql> USE `OpenHAB`;
Database changed
mysql> SHOW TABLES;
+-------------------+
| Tables_in_OpenHAB |
+-------------------+
| Items             |
+-------------------+
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE `Items`;
+-------+------------------------------+
| Table | Create Table                 |
+-------+------------------------------+
| Items | CREATE TABLE `Items` (
  `ItemId` int(11) NOT NULL AUTO_INCREMENT,
  `ItemName` varchar(200) NOT NULL,
  PRIMARY KEY (`ItemId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------+
1 row in set (0.00 sec)

However, this table is always empty:

mysql> SELECT COUNT(1) FROM `Items`;
+----------+
| COUNT(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

My openhab.cfg looks like this (excerpt):

persistence:default=mysql
mysql:url=jdbc:mysql://localhost:3306/OpenHAB
mysql:user=openhab
mysql:password=TotallySecret

There aren’t any log entries about MySQL during startup, but when I DROP TABLE `Items`;, restarting openHAB will recreate it; which confirms that openHAB can access my MySQL DB.

4

Is it possible that there is a kind of naming convention for persisting files?
Can you pls try to rename your file to “mysql.persist” only?

1 Like
5

That seems to have fixed it.