Can I log messages and info to MySQL?

I have been running a basic OpenHAB server for 6 months odd with 5 Lifx Lights and a load of rules to turn on/off the lights depending on who is awake/in/out of the house. We started getting issues with lights randomly coming on/off as the rules got complex and the console logging was/is hard to get enough info to debug (also limited in size and often gone by the time I get time to look) so I have decided to start again but this time logging all of the commands and states into a MySQL DB.

#What I have managed:
I have managed to install and setup a MySQL DB, install the “MySQL Persistance” (persistance-mysql - 1.10.0) Add-on from Paper-UI and get it to log all items state changes using the following ‘mysql.persist’ Persistence file:

// 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
	everyMinute	: "0 * * * * ?"
	every5Minutes : "0 */5 * * * ?"
   	everyHour   : "0 0 * * * ?"
   	everyDay    : "0 0 0 * * ?"
   	default = everyChange

 * Each line in this section defines for which item(s) which strategy(ies) should be applied.
 * You can list single items, use "*" for all items or "groupitem*" for all members of a group
 * item (excl. the group item itself).

Items {
    // persist all items once a day and on every change and restore them from the db at startup
    * : strategy = everyUpdate, restoreOnStartup

The console thew some errors (and still seems to now and then) that:

21:45:56.097 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'mysql.persist'
21:45:56.116 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query '##ITEM NAME##'.
21:45:56.119 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query '##GROUP NAME##'.
... etc

BUT then I have tables like ‘Items’ that seems to contain a list of all items that have had a value change since the persistent stuff was setup and a corresponding table ‘Item1’ for example that has the time and a string representing the new value…

This is great (or a good start) but I can’t find any information on how to take this further and push my own messages from the rules into my own tables? :confused:

So can someone point me in the right direction… I am kind of assuming it’s possible! If this assumption is wrong I guess can someone break the bad news and maybe offer an alternative way of permanently logging what functions ran at what time and any information and data I want to dump out…

Thanks :slight_smile:

By “messages” you mean commands?

Something like what is logged under events.log ?

2017-07-02 16:34:59.158 [ItemCommandEvent          ] - Item 'E08_LED' received command ON
2017-07-02 16:50:42.035 [ItemCommandEvent          ] - Item 'E08_LED' received command OFF

A persistence service will store only the state values (e.g. ON, OFF, etc) of the items but will not log the command that triggered the state update.

If you are interested to store the commands that are being generated, you could try using the log4j service.
You could tweak the log4j service to channel the log output to a custom destination.
See a similar example here:

Now… instead of a tcp output from log4j you could use a jdbc:mysql appender to channel the logs to mysql.

Of course, when you will use a custom log entry in your rule, like:

logInfo("My_Log", "My custom message")

It will show up in the openhab.log and you will be able to manage this from the log4j settings (loggers and appenders)
See more on customizing logs here:

Thank you Dim, Not quite what I wanted to hear BUT I’m sure it will do. :slight_smile: I will have a look at your suggestions and I’m sure will be back with more questions! :wink:

1 Like