MySQL Persistence

[naron (Jean-Marc Roué)]

HI ,

New to openHab i am using OpenHab on ubuntu (64bit) . I want to store some data in database . I tried with MySQL but i dont have any success .

1. I have installed Mysql server . and I have added org.openhab.persistence.mysql-1.7.0.jar to addons folder.

2. I added following configuration to openhab.cfg file :

############################ SQL Persistence Service ##################################
/# the database url like ‘jdbc:mysql://:/’ (without quotes)
mysql:url=jdbc:mysql://127.0.0.1:3306/openhab

/# the database user
mysql:user=openhab

/# the database password
mysql:password=password

/# the reconnection counter
/#mysql:reconnectCnt=

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

3)I added mysql.persist file into openHab\configurations\persistence
// persistence strategies have a name and a definition and are referred to in the “Items” section
Strategies {
// if no strategy is specified for an item entry below, the default list will be used
default = everyChange
}
items {
// persist all items once a day and on every change and restore them from the db at startup
// careful, this might result in huge databases - that’s why it is in comment here ;
* : strategy = everyChange, everyDay, restoreOnStartup
// persist all temperature and weather values at every change and every hour
// * : strategy = everyChange, everyHour
}

This is from my log file :

10:39:58.791 [DEBUG] [sqlPersistenceServiceActivator:32 ] - mySQL persistence bundle has been started.
10:39:58.806 [DEBUG] [.p.internal.PersistenceManager:147 ] - Initializing mysql persistence service.
10:39:58.811 [DEBUG] [c.internal.ModelRepositoryImpl:64 ] - Configuration model ‘mysql.persist’ can not be found
10:39:58.812 [DEBUG] [.p.m.i.MysqlPersistenceService:462 ] - mySQL configuration starting
10:39:58.813 [DEBUG] [.p.m.i.MysqlPersistenceService:387 ] - mySQL: Attempting to connect to database jdbc:mysql://127.0.0.1:3306/openhab
10:39:59.408 [DEBUG] [.p.m.i.MysqlPersistenceService:390 ] - mySQL: Connected to database jdbc:mysql://127.0.0.1:3306/openhab
10:39:59.413 [DEBUG] [.p.m.i.MysqlPersistenceService:523 ] - mySQL configuration complete.
10:39:59.414 [DEBUG] [c.internal.ModelRepositoryImpl:64 ] - Configuration model ‘mysql.persist’ can not be found
10:40:02.417 [INFO ] [c.internal.ModelRepositoryImpl:80 ] - Loading model ‘home.sitemap’
10:40:02.763 [INFO ] [c.internal.ModelRepositoryImpl:80 ] - Loading model ‘mysql.persist’
10:40:02.890 [INFO ] [c.internal.ModelRepositoryImpl:80 ] - Loading model ‘home.rules’
10:40:03.292 [INFO ] [c.internal.ModelRepositoryImpl:80 ] - Loading model ‘home.items’

Idea for this problem???

Thanks

[JohnnyX (John)]

@naron

mysql:url=jdbc:mysql://127.0.0.1:3306/openhab

Your URL of the JDBC driver is not correct. If installed on same machine as your openhab you can use same IP.

My configuration below:

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
#jdbc:mysql:url=127.0.0.1:3306/openhab
mysql:url=jdbc:mysql://192.168.0.10: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)

[JohnnyX (John)]

@naron

10:39:58.811 [DEBUG] [c.internal.ModelRepositoryImpl:64 ] - Configuration model ‘mysql.persist’ can not be found

Also it seems to be that your mysql.persist file in folder configurations/persistence is not configured properly. As an example part of my configuration:

Strategies {
everyMinute : "0 * * * * ?"
everyHour : "0 0 * * * ?"
everyDay  : "0 0 0 * * ?"

default = everyChange
}

 		Items {
gSQL* : strategy = everyChange, everyHour, everyDay, restoreOnStartup

}

I also want to recommend you this post: Excluding an item from persistence

Excluding an item from persistence

Hi Micheal From my point the easiest way to exclude an item is to put all other items in a special group (like ‘persistance’) but let this item out. Then save only this group to database. I had some strange effects myself before I changed it that way. BTW: I use mySQL persistance, just in case… Regards Dieter

[naron (Jean-Marc Roué)]

Yes mysql server is on the same machine.

i can see openhab users already connected with one table items and two fields ItemId and ItemName.
i try to change my mysql.persist file

Thank

[naron (Jean-Marc Roué)]

Now it works well with only one item group

I will look to organize my file mysql.persit

thank you very much.