MySQL error

mysql
openhab2
Tags: #<Tag:0x00007f0148290b58> #<Tag:0x00007f0148290a18>

(minnu maria) #1

2018-09-10 14:32:29.659 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://127.0.0.1:3306/openhab, user=root, password=root
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database ‘openhab’

I’ not sure what causes this error. When I start openhab , i get the following lines…

image


(Elias H.) #2

Did you create the Database? I case you did not, do the following and/or follow this tutorial openHAB2 & MySQL persistence setup

While we are at it… maybe consider using InfluxDB instead of MySQL because this => InfluxDB+Grafana persistence and graphing

  1. Use the normal shell/terminal not openhab-karaf
  2. Fire up this command to login into your mysql server mysql -h localhost -uroot -proot
  3. Fire up this command to list all databases mysql> show databases; (Note that "mysql> is the prompt from the shell)
  4. Check if there is an openhab database, should look something like this
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| openhab            |
| performance_schema |
+--------------------+
  1. If there is no openhab entry, create a Database with this command CREATE DATABASE openhab;

(minnu maria) #3

Thank you.

I will try it out .
I got openhab in the database, but when go see it it does not contain any of my items… Am I doing

When I checked my logs this is the error I’m getting…

2018-09-10 15:39:04.074 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.
2018-09-10 15:39:04.074 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.
2018-09-10 15:39:06.598 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.
2018-09-10 15:39:06.599 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.

office_temp is the item which receives temperature through mqtt and he graph is subsquently plotted…


(Vincent Regaud) #4

Did you restart OH since creating the DB?


(minnu maria) #5

Yes I just did, the error persists…

2018-09-10 15:59:49.178 [INFO ] [.dashboard.internal.DashboardService] - Started dashboard at http://172.16.73.36:8080
2018-09-10 15:59:49.179 [INFO ] [.dashboard.internal.DashboardService] - Started dashboard at https://172.16.73.36:8443
2018-09-10 15:59:49.693 [INFO ] [io.openhabcloud.internal.CloudClient] - Connected to the openHAB Cloud service (UUID = df470386-e60e-4e6b-81bd-05f7685, base URL = http://localhost:8080)
2018-09-10 15:59:51.491 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model ‘b.items’
2018-09-10 15:59:51.651 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model ‘mysql.persist’
2018-09-10 15:59:52.926 [INFO ] [el.core.internal.ModelRepositoryImpl] - Validation issues found in configuration model ‘b.rules’, using it anyway:
The import ‘java.util.concurrent.locks.ReentrantLock’ is never used.
The import ‘java.io.ByteArrayInputStream’ is never used.
The import ‘java.awt.image.BufferedImage’ is never used.
The import ‘javax.imageio.ImageIO’ is never used.
The import ‘java.lang.String’ is never used.
The import ‘java.io.File’ is never used.
2018-09-10 15:59:52.928 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model ‘b.rules’
2018-09-10 15:59:52.979 [INFO ] [thome.model.lsp.internal.ModelServer] - Started Language Server Protocol (LSP) service on port 5007
2018-09-10 15:59:53.089 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model ‘b.sitemap’
2018-09-10 15:59:53.687 [INFO ] [basic.internal.servlet.WebAppServlet] - Started Basic UI at /basicui/app
2018-09-10 15:59:53.700 [INFO ] [arthome.ui.paper.internal.PaperUIApp] - Started Paper UI at /paperui
2018-09-10 15:59:53.740 [INFO ] [panel.internal.HABPanelDashboardTile] - Started HABPanel at /habpanel
2018-09-10 15:59:53.791 [INFO ] [penhab.io.transport.mqtt.MqttService] - MQTT Service initialization completed.
2018-09-10 15:59:53.792 [INFO ] [t.mqtt.internal.MqttBrokerConnection] - Starting MQTT broker connection ‘mosquitto’
2018-09-10 15:59:54.341 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.
2018-09-10 15:59:54.438 [INFO ] [assic.internal.servlet.WebAppServlet] - Started Classic UI at /classicui/app
2018-09-10 15:59:54.659 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.
2018-09-10 15:59:54.659 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.
2018-09-10 15:59:56.664 [ERROR] [ui.internal.items.ItemUIRegistryImpl] - Cannot retrieve item for widget org.eclipse.smarthome.model.sitemap.Image
2018-09-10 15:59:56.664 [ERROR] [ui.internal.items.ItemUIRegistryImpl] - Cannot retrieve item ‘ImageURL’ for widget org.eclipse.smarthome.model.sitemap.Image
2018-09-10 15:59:56.665 [ERROR] [ui.internal.items.ItemUIRegistryImpl] - Cannot retrieve item ‘ImageURL’ for widget org.eclipse.smarthome.model.sitemap.Image
2018-09-10 15:59:56.685 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.
2018-09-10 15:59:56.688 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.
2018-09-10 15:59:59.678 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.
2018-09-10 15:59:59.679 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.
2018-09-10 16:00:02.677 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.
2018-09-10 16:00:02.679 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Office_temp’.


(Elias H.) #6

Could you post your mysql.persist?
Also did you create and setup the openhab user for mysql correctly?


(minnu maria) #7

mysql.persist
image

I followed the steps given in the link… I got openhab in my database but not the items…


(minnu maria) #8

Okay I did a mistake with username and password pardon me, it’s working now :wink: Thank you
I get the graph…

Msql looks like this… is it possible to get the temperature value in the database…


(Vincent Regaud) #9

There will be two more tables now
Item1 and Item2
Then you can do SELECT * FROM openhab.Item2;