Tables are created but they are empty

cwinter · November 17, 2021, 8:33pm

Hello,
I have setup a new OH3 system and one of my first things is to configure the persistence.

Infos:

openHAB 3.1.0 in Docker @ Synology NAS
MariaDB @ Synology NAS
Homematic CCU3
Philips Hue

The issue:
Tables with “item names” (Tablename Realname Generation : enabled) were only created after I press the “analyse” button and one table “Item” with item names was created.
But the item tables are empty. Did I forget to setup something? Probably…

First I had no jdbc.persist file and now I have tested it with different content like:

Strategies {
  everyMinute : "0 * * * * ?"
  default = everyMinute

}

Items {
    * : strategy = everyMinute
    }

After every change I have restarted the container. In the log I can only find INSERTs for the “items” table.

Thanks for any help!!!

cwinter · November 18, 2021, 9:43am

Now I got my first error message:

openhab> log:display org.openhab.persistence.jdbc                                                                   
                                                                                                                    
openhab> log:set org.openhab.persistence.jdbc                                                                       
Error executing command: No enum constant org.apache.karaf.log.core.Level.ORG.OPENHAB.PERSISTENCE.JDBC       
openhab> log:log org.openhab.persistence.jdbc                                                                       
openhab> log:tail org.openhab.persistence.jdbc                                                                      
09:35:01.023 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::query: item is ogBuroHeizungsregler_Ac         
tualTemperature                                                                                                     
09:35:01.025 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::getHistItemFilterQuery filter='true' n         
umberDecimalcount='3' table='ogburoheizungsregler_actualtemperature_0002' item='ogBuroHeizungsregler_Actual         
Temperature (Type=NumberItem, State=22.1 °C, Label=Actual Temperature, Category=Temperature, Tags=[Measurem         
ent, Temperature], Groups=[ogBuroHeizungsregler, gruppe_persistenz_10m_speichern])' itemName='ogBuroHeizung         
sregler_ActualTemperature'                                                                                          
09:35:01.027 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getHistItemFilterQueryProvider filter          
= org.openhab.core.persistence.FilterCriteria@1566a674, numberDecimalcount = 3, table = ogburoheizungsregle         
r_actualtemperature_0002, simpleName = ogBuroHeizungsregler_ActualTemperature                                       
09:35:01.032 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value         
 FROM ogburoheizungsregler_actualtemperature_0002 WHERE TIME>'2021-11-17 09:35:01' AND TIME<'2021-11-18 10:         
35:01' ORDER BY time ASC                                                                                            
09:35:01.032 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT ti         
me, value FROM ogburoheizungsregler_actualtemperature_0002 WHERE TIME>'2021-11-17 09:35:01' AND TIME<'2021-         
11-18 10:35:01' ORDER BY time ASC                                                                                   
09:35:01.098 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::query: query for ogBuroHeizungsregler_         
ActualTemperature returned 0 rows in 74 ms                                                                          
09:35:02.952 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::query: item is ogBuroHeizungsregler_Ac         
tualTemperature                                                                                                     
09:35:02.953 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::getHistItemFilterQuery filter='true' n         
umberDecimalcount='3' table='ogburoheizungsregler_actualtemperature_0002' item='ogBuroHeizungsregler_Actual         
Temperature (Type=NumberItem, State=22.1 °C, Label=Actual Temperature, Category=Temperature, Tags=[Measurem         
ent, Temperature], Groups=[ogBuroHeizungsregler, gruppe_persistenz_10m_speichern])' itemName='ogBuroHeizung         
sregler_ActualTemperature'                                                                                          
09:35:02.959 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getHistItemFilterQueryProvider filter          
= org.openhab.core.persistence.FilterCriteria@57183976, numberDecimalcount = 3, table = ogburoheizungsregle         
r_actualtemperature_0002, simpleName = ogBuroHeizungsregler_ActualTemperature                                       
09:35:02.971 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value         
 FROM ogburoheizungsregler_actualtemperature_0002 WHERE TIME>'2021-11-17 10:35:02' AND TIME<'2021-11-18 10:         
35:02' ORDER BY time ASC                                                                                            
09:35:02.974 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT ti         
me, value FROM ogburoheizungsregler_actualtemperature_0002 WHERE TIME>'2021-11-17 10:35:02' AND TIME<'2021-         
11-18 10:35:02' ORDER BY time ASC                                                                                   
09:35:02.984 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::query: query for ogBuroHeizungsregler_         
ActualTemperature returned 0 rows in 32 ms

cwinter · November 18, 2021, 3:04pm

What I have also observed is that I see no log ( log:tail org.openhab.persistence.jdbc) when a item value changes. When I try to analyse a value I get this log, what is fine because no data is stored so far.

openhab> log:tail org.openhab.persistence.jdbc                                                                      
14:59:59.020 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::query: item is Sensortest_Motion               
14:59:59.029 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::getHistItemFilterQuery filter='true' numberDeci
malcount='3' table='sensortest_motion_0016' item='Sensortest_Motion (Type=SwitchItem, State=ON, Label=Motion, Catego
ry=Motion, Tags=[Point], Groups=[Sensortest])' itemName='Sensortest_Motion'                                         
14:59:59.029 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getHistItemFilterQueryProvider filter = org.ope
nhab.core.persistence.FilterCriteria@4775b48d, numberDecimalcount = 3, table = sensortest_motion_0016, simpleName = 
Sensortest_Motion                                                                                                   
14:59:59.030 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value FROM sen
sortest_motion_0016 WHERE TIME>'2021-11-17 15:59:59' AND TIME<'2021-11-18 15:59:59' ORDER BY time ASC               
14:59:59.030 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT time, value
 FROM sensortest_motion_0016 WHERE TIME>'2021-11-17 15:59:59' AND TIME<'2021-11-18 15:59:59' ORDER BY time ASC      
14:59:59.032 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::query: query for Sensortest_Motion returned 0 r
ows in 4 ms

rossko57 · November 18, 2021, 3:26pm

You haven’t asked for that in your persist file.

cwinter · November 18, 2021, 5:02pm

That’s true!
But adding “everyChange” does not fix the issue that no "INSERT"s are in the log.
My new persit file:

Strategies {
  everyMinute : "0 * * * * ?"
  default = everyMinute

}

Items {
    * : strategy = everyMinute, everyChange
    }

rossko57 · November 18, 2021, 5:09pm

In OH3 there is a default strategy that should persist lots. This kicks in the absence of a suitable .persist file.
If you ‘delete’ your persist file (rename to blah.persist.xxxx or something) then reboot, see what happens.
If it persists then, you have a persist config problem. These files are picky about character encoding.
If not, it’s something more basic about the service, connectivity or something.

You can also persist at will from rules
someItem.persist("jdbc")
and observe your DEBUG logging.

cwinter · November 18, 2021, 6:55pm

Thank you for your efforts!

I have executed the command with and without my jdbc.persist file:

openhab> Sensortest_LastUpdated.persist("jdbc")                                 
Sensortest_LastUpdated.persist(jdbc)
openhab>

and I got NO output with this command:

log:tail org.openhab.persistence.jdbc

Any other idea?

cwinter · November 18, 2021, 10:06pm

After two nights investigating, I have reinstalled OH3. And the persistence works.

I don’t know what went wrong in my first setup.