search

openhab / openhab1-addons

Add-ons for openHAB 1.x
Eclipse Public License 2.0
3.43k stars 1.71k forks source link

[mysql] persistance isn't working / couldn't be reset #4866

Closed binderth closed 7 years ago

binderth commented 7 years ago

Within openHAB2 I used the mySQL persistance for the first time, unfortunately the collation of my database lead to diverse errors with tablespace (varchar(20000) isn't available within UTF-8, only 16438). Since then, I'm not able to reset the persistance, e.g. start fresh again.

Expected Behavior

  1. stop openHAB2
  2. drop existing database in MySQL-Server
  3. start openHAB2
  4. openHAB2 should create new Items-table
  5. populate Items-table with all the items
  6. openHAB2 should create referenced Itemxx-tables for each item

Current Behavior

  1. stop openHAB2
  2. drop existing database in MySQL-Server
  3. start openHAB2
  4. openHAB2 should creates new Items-table
  5. Items-table stays empty,
  6. no Itemxxx are created

For reference there's my config: (1) addons.cfg 

# A comma-separated list of persistence services to install (e.g. "rrd4j,jpa")
persistence = mysql

(2) mysql.cfg

############################ mySQL Persistence Service ##################################

# the database url like 'jdbc:mysql://<host>:<port>/<database>'
url=jdbc:mysql://192.168.xx.xx:3306/openHAB

# the database user
user=xxx

# the database password
password=xxx

# the reconnection counter
#reconnectCnt=

# the connection timeout (in seconds)
#waitTimeout=

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

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

(3) mysql.persist

Strategies {
    everyMinute : "0 * * * * ?"
    every5Minutes   : "0 */5 * * * ?"
    everyHour   : "0 0 * * * ?"
    everyDay    : "0 0 0 * * ?"

    default = everyChange
}

Items {
    * : strategy = everyMinute, everyHour, everyDay, restoreOnStartup
}

After restart, I've got a new table in MySQL "Items" like this, but it stays empty

CREATE TABLE `Items` (
  `ItemId` int(11) NOT NULL AUTO_INCREMENT,
  `ItemName` varchar(200) COLLATE latin1_german2_ci NOT NULL,
  PRIMARY KEY (`ItemId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

openhab.log throws these ERRORS:

(...my .items)
2016-12-05 08:17:22.234 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'mysql.persist'
2016-12-05 08:17:28.182 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'astro.rules'
(...my .rules)
2016-12-05 08:17:32.967 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'binder.sitemap'
2016-12-05 08:17:33.774 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'binder.things'
2016-12-05 08:17:37.342 [INFO ] [.dashboard.internal.DashboardService] - Started dashboard at /start
2016-12-05 08:17:45.734 [INFO ] [basic.internal.servlet.WebAppServlet] - Started Basic UI at /basicui/app
2016-12-05 08:17:45.915 [INFO ] [arthome.ui.paper.internal.PaperUIApp] - Started Paper UI at /ui
2016-12-05 08:17:47.416 [INFO ] [ding.astro.handler.AstroThingHandler] - Scheduled astro DailyJob at midnight for thing astro:moon:home
2016-12-05 08:17:47.424 [INFO ] [ding.astro.handler.AstroThingHandler] - Scheduled astro DailyJob at midnight for thing astro:sun:home
2016-12-05 08:17:47.428 [INFO ] [ding.astro.handler.AstroThingHandler] - Scheduled astro PositionalJob with interval of 600 seconds for thing astro:moon:home
2016-12-05 08:17:47.433 [INFO ] [ding.astro.handler.AstroThingHandler] - Scheduled astro PositionalJob with interval of 600 seconds for thing astro:sun:home
2016-12-05 08:17:53.060 [INFO ] [panel.internal.HABPanelDashboardTile] - Started HABPanel at /habpanel
(...)
2016-12-05 08:18:05.978 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gGarten'.
2016-12-05 08:18:05.984 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gJalousien'.
2016-12-05 08:18:05.989 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gWetter'.
2016-12-05 08:18:05.994 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gStatus'.
2016-12-05 08:18:06.000 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'EG_WoZi'.
2016-12-05 08:18:06.005 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'EG_Kueche'.
2016-12-05 08:18:06.011 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'EG_WC'.
2016-12-05 08:18:06.016 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'EG_Flur'.
2016-12-05 08:18:06.020 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'OG_Bad'.
2016-12-05 08:18:06.025 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'OG_Schlafen'.
2016-12-05 08:18:06.029 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'OG_Flur'.
2016-12-05 08:18:06.034 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'UG_Flur'.
2016-12-05 08:18:06.038 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'UG_Heizung'.
2016-12-05 08:18:06.043 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'UG_Waschen'.
2016-12-05 08:18:06.048 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'UG_Party'.
2016-12-05 08:18:06.052 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Garten_N'.
2016-12-05 08:18:06.058 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Lights'.
2016-12-05 08:18:06.062 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Shutters'.
2016-12-05 08:18:06.067 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Heating'.
2016-12-05 08:18:06.071 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Temperature'.
2016-12-05 08:18:06.078 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Windows'.
2016-12-05 08:18:06.082 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Light_EG_WoZi_LG1'.
2016-12-05 08:18:06.087 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Light_EG_WoZi_LG2'.
2016-12-05 08:18:06.091 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Light_EG_WoZi_LG3'.
2016-12-05 08:18:06.095 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Light_EG_WoZi_LG4'.
2016-12-05 08:18:06.100 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Light_EG_WoZi_WandEss'.
2016-12-05 08:18:06.105 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Light_EG_WoZi_WandWoZi'.
(... more items to come, seems like all my items)

Please see thread https://community.openhab.org/t/persistence-isnt-working/15695/18?u=binderth for further details

Possible Solution

I think, there is some kind of persistance for persistances! ;) idk, but I think the binding still knows, there should be tables or something?

Steps to Reproduce (for bugs)

  1. using openHABian on a Raspberry Pi
  2. see steps above

Context

Trying to persist my items for further use

Your Environment

binderth commented 7 years ago

My Workaround for this was to name the new database differently from the first tries. The first ones where named "openHAB", at my last try I named it "openHAB2".

Still, I believe I found a bug, because:

  1. if you use "UTF-8" or "UTF-16" as collation, VARCHAR(20000) is too big.
  2. if you trying to reset your mysql-persistance you have to rename the database at present.

Thanks! Thomas.

binderth commented 7 years ago

short update on this: If I add some new Item, which should be persisted, at first I get some ERROR:

2016-12-05 19:31:41.792 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Shutter_OG_ges'.
2016-12-05 19:31:41.796 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Shutter_OG_KiZiWest'.
2016-12-05 19:31:41.800 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'Shutter_OG_KiZiOst'.

but after some time (I can't say how Long, but I guess at least since 20:30 - an hour later?) the Items appear both in the Items-Table and get Itemxxx-tables, which are then populated with values.

edit: it's not time-related (that was coincidence), it's related, when the first strategy fires. At least, after I added some items with "everyChange" and changed the item, it showed in the Items-table and got populated in the Itemxxx-table.

9037568 commented 7 years ago

Can you please provide a full debug log, @binderth ?

binderth commented 7 years ago

Hi Chris,

after some more testing, I did not encounter the original problem of not creating tables anymore. At present I use build #643. So I guess some changes since then did the trick!

Regarding my VARCHAR(20000) issue, I did find out, that you could add a line mysql:sqltype.string=VARCHAR(20000) to your mysql.cfg, which then could be defined lower chars for UTF encoding of the database.

So, I close this issue. Thomas.