Mysql logging

Mike_Klein · August 15, 2017, 11:41pm

Hi all,

I have some problems with the logging into a mysql db.
A items table is created by openhab, but he is not logging anything in there.

I already tried jdbc mysql, mysql persistence… both in parallel, etc. But it is not working. All the threats I found so far, people advised to install/deinstall one of the persistence … people didn’t knew why but suddenly it had been working for them… unfortunately not for me.

my persist file:

Strategies {
  default = everyUpdate
}

Items {
  * : strategy = default, restoreOnStartup
}

Dim · August 16, 2017, 4:37am

Items {
  * : strategy = everyUpdate, restoreOnStartup
}

You may want to use everyChange instead of everyUpdate (less persistence (not logs) entries).

Post also your cfg file from the conf/services folder.

http://docs.openhab.org/configuration/persistence.html

Mike_Klein · August 16, 2017, 10:00pm

mysql.cfg

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://127.0.0.1:3306/OpenHAB

# the database user
user=openhab

# the database password
password=*****

# the reconnection counter
#reconnectCnt=

# the connection timeout (in seconds)
#waitTimeout=

# Use MySQL Server time to store item values (=false) or use openHAB Server time (=true).
# For new installations, its recommend to set "localtime=true".
# (optional, defaults to false)
#localtime=true

jdbc.cfg



url=jdbc:mysql://127.0.0.1:3306/OpenHAB
user=openhab
password=***
tableNamePrefix=Items
tableUseRealItemNames=false
tableIdDigitCount=0

Dim · August 17, 2017, 3:52am

Use 1 method to connect to the MySQL db. I recommend the JDBC Persistence MySQL addon (not the MySQL Persistence).

Uninstall the persistence-mysql addon and remove the mysql.cfg file.

With these changes (and the corrected jdbc.persist) you should have a working solution.

Mike_Klein · August 17, 2017, 6:45am

nothing happening in mysql. when I go to:

items0 will be created in the mysql and I see this in the console:

08:43:37.993 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::query: unable to find table for query, no data in database for item 'Wohnzimmer_Schrank'. Current number of tables in the database: 4
08:43:37.994 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::query: try to generate the table for item 'Wohnzimmer_Schrank'
08:43:37.995 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: no table found for item 'Wohnzimmer_Schrank' in sqlTables
08:43:37.995 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createNewEntryInItemsTable
08:43:37.997 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doCreateNewEntryInItemsTable sql=INSERT INTO items (ItemName) VALUES ('Wohnzimmer_Schrank')
08:43:38.005 [ERROR] [org.knowm.yank.Yank                 ] - Error in SQL query!!!
java.sql.SQLException: Table 'OpenHAB.items' doesn't exist Query: INSERT INTO items (ItemName) VALUES ('Wohnzimmer_Schrank') Parameters: []
	at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392)[200:org.openhab.persistence.jdbc:1.11.0.201708100110]
	at org.apache.commons.dbutils.QueryRunner.insert(QueryRunner.java:610)[200:org.openhab.persistence.jdbc:1.11.0.201708100110]
	at org.apache.commons.dbutils.QueryRunner.insert(QueryRunner.java:534)[200:org.openhab.persistence.jdbc:1.11.0.201708100110]
	at org.knowm.yank.Yank.insert(Yank.java:123)[200:org.openhab.persistence.jdbc:1.11.0.201708100110]
	at org.knowm.yank.Yank.insert(Yank.java:106)[200:org.openhab.persistence.jdbc:1.11.0.201708100110]
	at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doCreateNewEntryInItemsTable(JdbcBaseDAO.java:276)[200:org.openhab.persistence.jdbc:1.11.0.201708100110]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.createNewEntryInItemsTable(JdbcMapper.java:75)[200:org.openhab.persistence.jdbc:1.11.0.201708100110]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.getTable(JdbcMapper.java:245)[200:org.openhab.persistence.jdbc:1.11.0.201708100110]
	at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.query(JdbcPersistenceService.java:192)[200:org.openhab.persistence.jdbc:1.11.0.201708100110]
	at org.openhab.core.persistence.internal.QueryablePersistenceServiceDelegate.query(QueryablePersistenceServiceDelegate.java:47)[189:org.openhab.core.compat1x:2.2.0.201708091334]
	at org.eclipse.smarthome.ui.internal.chart.DefaultChartProvider.addItem(DefaultChartProvider.java:280)[136:org.eclipse.smarthome.ui:0.9.0.201708041325]
	at org.eclipse.smarthome.ui.internal.chart.DefaultChartProvider.createChart(DefaultChartProvider.java:175)[136:org.eclipse.smarthome.ui:0.9.0.201708041325]
	at org.eclipse.smarthome.ui.internal.chart.ChartServlet.doGet(ChartServlet.java:255)[136:org.eclipse.smarthome.ui:0.9.0.201708041325]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)[24:javax.servlet-api:3.1.0]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)[24:javax.servlet-api:3.1.0]
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:812)[81:org.eclipse.jetty.servlet:9.2.19.v20160908]
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:587)[81:org.eclipse.jetty.servlet:9.2.19.v20160908]
	at org.ops4j.pax.web.service.jetty.internal.HttpServiceServletHandler.doHandle(HttpServiceServletHandler.java:71)[171:org.ops4j.pax.web.pax-web-jetty:4.3.0]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)[80:org.eclipse.jetty.server:9.2.19.v20160908]
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)[79:org.eclipse.jetty.security:9.2.19.v20160908]
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)[80:org.eclipse.jetty.server:9.2.19.v20160908]
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)[80:org.eclipse.jetty.server:9.2.19.v20160908]
	at org.ops4j.pax.web.service.jetty.internal.HttpServiceContext.doHandle(HttpServiceContext.java:287)[171:org.ops4j.pax.web.pax-web-jetty:4.3.0]
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)[81:org.eclipse.jetty.servlet:9.2.19.v20160908]
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)[80:org.eclipse.jetty.server:9.2.19.v20160908]
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)[80:org.eclipse.jetty.server:9.2.19.v20160908]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)[80:org.eclipse.jetty.server:9.2.19.v20160908]
	at org.ops4j.pax.web.service.jetty.internal.JettyServerHandlerCollection.handle(JettyServerHandlerCollection.java:80)[171:org.ops4j.pax.web.pax-web-jetty:4.3.0]
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)[80:org.eclipse.jetty.server:9.2.19.v20160908]
	at org.eclipse.jetty.server.Server.handle(Server.java:499)[80:org.eclipse.jetty.server:9.2.19.v20160908]
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)[80:org.eclipse.jetty.server:9.2.19.v20160908]
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)[80:org.eclipse.jetty.server:9.2.19.v20160908]
	at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)[71:org.eclipse.jetty.io:9.2.19.v20160908]
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)[83:org.eclipse.jetty.util:9.2.19.v20160908]
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)[83:org.eclipse.jetty.util:9.2.19.v20160908]
	at java.lang.Thread.run(Thread.java:748)[:1.8.0_144]
08:43:38.017 [ERROR] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: Creating table for item 'Wohnzimmer_Schrank' failed.
08:43:38.020 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: getTableName with rowId=0 itemName=Wohnzimmer_Schrank
08:43:38.022 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType SWITCHITEM for Item Wohnzimmer_Schrank
08:43:38.026 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=Items0; newTableName=Wohnzimmer_Schrank; 
08:43:38.029 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createItemTable
08:43:38.035 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: Table created for item 'Wohnzimmer_Schrank' with dataType VARCHAR(6) in SQL database.
08:43:38.037 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getHistItemFilterQuery filter='true' numberDecimalcount='3' table='Items0' item='Wohnzimmer_Schrank (Type=SwitchItem, State=OFF)' itemName='Wohnzimmer_Schrank'
08:43:38.040 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getHistItemFilterQueryProvider filter = org.openhab.core.persistence.FilterCriteria@128b804, numberDecimalcount = 3, table = Items0, simpleName = Wohnzimmer_Schrank
08:43:38.043 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value FROM Items0 WHERE TIME<'2017-08-17 07:43:37' ORDER BY time DESC  LIMIT 0,1
08:43:38.046 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT time, value FROM Items0 WHERE TIME<'2017-08-17 07:43:37' ORDER BY time DESC  LIMIT 0,1
08:43:38.051 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: query for Wohnzimmer_Schrank returned 0 rows in 14 ms
08:43:38.054 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: item is Wohnzimmer_Schrank
08:43:38.055 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getHistItemFilterQuery filter='true' numberDecimalcount='3' table='Items0' item='Wohnzimmer_Schrank (Type=SwitchItem, State=OFF)' itemName='Wohnzimmer_Schrank'
08:43:38.058 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getHistItemFilterQueryProvider filter = org.openhab.core.persistence.FilterCriteria@19057c1, numberDecimalcount = 3, table = Items0, simpleName = Wohnzimmer_Schrank
08:43:38.059 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value FROM Items0 WHERE TIME>'2017-08-17 07:43:37' AND TIME<'2017-08-17 08:43:37' ORDER BY time ASC
08:43:38.060 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT time, value FROM Items0 WHERE TIME>'2017-08-17 07:43:37' AND TIME<'2017-08-17 08:43:37' ORDER BY time ASC
08:43:38.065 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: query for Wohnzimmer_Schrank returned 0 rows in 10 ms