JDBC persistence with tableUseRealItemNames and tableIdDigitCount=0

I’m rebuilding my openHAB installation after a catastrophic failure on the weekend and I’m attempting to build in a way that reduces the effort to rebuild in the future. Part of what I want to do is keep the DB server and the openHAB server separate. My goal is to allow me to completely rebuild the openHAB server and also maintain the history of items that I persist. The history I’m mainly concerned with is the thermostat setpoints I have in each room, together with the actual temperature and the radiator valve opening in percent. As I introduce more insulation into the house I like to analyse the difference with graphs.

If I use the default DB table naming convention that most people seem to use (and that I’ve used up to now) in which the data for each persisted item is stored in a sequentially numbered table then if I rebuild the server I would have to ensure that each item I create is created in the same order that it was created during the previous build. This would ensure that the LivingRoomTemp item was assigned to table item0001, the setpoint to item0002, etc. However, in reality this is completely impractical, data is sent from various thermostats at random so the order in which the item tables are created is almost random. I could probably edit the values in the items table but there’s scope for error and there would be a window in which data would be written to the wrong tables. All fixable by manually editing the DB but that’s an arduous task.

However, I noticed that there are two options in the jdbc service configuration that should solve the problem: tableUseRealItemNames and tableIdDigitCount. If I set tableUseRealItemNames to true and tableIdDigitCount to zero then the items tables should be set to the name of the item. As long as I follow my build notes and name each persisted item consistently everything should fall in to place. Except, it doesn’t quite work that way. If I set:

tableUseRealItemNames=true
tableIdDigitCount=4

I get the following table names in the items table:

mysql> show tables;
+-----------------------------------------------+
| Tables_in_openHAB                             |
+-----------------------------------------------+
| housefeedelectricitymeter_batterylevel_0001   |
| housefeedelectricitymeter_instantpower_0004   |
| housefeedelectricitymeter_lowbattery_0003     |
| housefeedelectricitymeter_signalstrength_0005 |
| housefeedelectricitymeter_totalusage_0002     |
| items                                         |
+-----------------------------------------------+

mysql> select * from items;
+--------+------------------------------------------+
| ItemId | itemname                                 |
+--------+------------------------------------------+
|      1 | HouseFeedElectricityMeter_BatteryLevel   |
|      2 | HouseFeedElectricityMeter_TotalUsage     |
|      3 | HouseFeedElectricityMeter_LowBattery     |
|      4 | HouseFeedElectricityMeter_InstantPower   |
|      5 | HouseFeedElectricityMeter_SignalStrength |
+--------+------------------------------------------+

as you might expect. However, if I set:

tableUseRealItemNames=true
tableIdDigitCount=0

I get:

mysql> show tables;
+-----------------------------------------------+
| Tables_in_openHAB                             |
+-----------------------------------------------+
| housefeedelectricitymeter_batterylevel_1      |
| housefeedelectricitymeter_instantpower_4      |
| housefeedelectricitymeter_lowbattery_3        |
| housefeedelectricitymeter_signalstrength_5    |
| housefeedelectricitymeter_totalusage_2        |
| items                                         |

+-----------------------------------------------+

mysql> select * from items;
±-------±-----------------------------------------+
| ItemId | itemname |
±-------±-----------------------------------------+
| 1 | HouseFeedElectricityMeter_BatteryLevel |
| 2 | HouseFeedElectricityMeter_TotalUsage |
| 3 | HouseFeedElectricityMeter_LowBattery |
| 4 | HouseFeedElectricityMeter_InstantPower |
| 5 | HouseFeedElectricityMeter_SignalStrength |
±-------±-----------------------------------------+

Not what I was expecting. Each item table has the itemid from the items table appended to it. This means that the items still need to be created in the same order. Or am I missing an important detail? Why do the table names have the itemid appended to them? Item names must be unique so there is no need for anything to be appended to them for use as a unique table name, correct?

Thanks,

Steve.

1 Like

Same here, I consider this a bug in JDBC persistence.

A bumb from me. I have the same issue.
And since I want to use a Garbage collection mechanism, I need the item-name to be the same as the table name.

Any ideas?

has anyone worked around this problem? thanks.

Still same problem, tables are named with _# as suffix although tableIdDigitCount is set to 0.
Is there any solution in the meantime?

see report that was made in december 2021 on github but not fixed/changed yet although a proposal was made:

To my untrained eye that looks like a good solution. How would one go about raising that issue to the attention of devs who can make and sign off the fix?

I have created a draft PR with a fix:

The fix implements the logic proposed by @weberjn in the mentioned issue which also corresponds to the existing documentation. However, I have not yet tested this fix, so please try it carefully on a test database.

There is a link to a JAR in the PR - please make sure to also copy the corresponding driver into the same directory, for example “mysql-connector-java.jar” - see current versions here:

1 Like

Brilliant! I’ll build a new VM on the weekend to test.

How would I go about testing this as stated in the PR:

Check allowed characters in item names and make sure two items cannot collide.

Does this mean that an item names with special characters have the special characters stripped out when the DB tables are created? E.g., item opénhab and opènhab are both changed to opnhab or something similar?

It’s something for me to verify. Your example is exactly to the point, so if those item names are allowed in openHAB, it would cause problems for the current implementation.

@higgers - for your information, I now consider my changes stable, and I have therefore marked the PR as ready for review:

There is a link to the latest JAR in the bottom of the description. Please note that your underlying operating system and database server must support case sensitive table names in order for this feature to work.

I’ve built a new Debian VM for testing and have set up persistence using MariaDB (Debian uses mariaDB instead of Mysql) so I’ve can compare old and new behaviour. At the bottom of the description in github there’s the following note:

Note: Place the corresponding database driver into the addons directory. For example, download the MySQL connector and rename it to mysql-connector-java.jar.

I couldn’t find a MariaDB connector jar there. Do you know if the mysql one will work with MariaDB?

I think you can go to https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client

See JDBC - Persistence Services | openHAB

So here: https://repo1.maven.org/maven2/org/mariadb/jdbc/mariadb-java-client/1.4.6/mariadb-java-client-1.4.6.jar

You probably need to remove the version number from the filename like described for MySQL.

The version is pretty old, so if you’re up for it, next we could try upgrading it. However, one thing at a time. :slight_smile:

Thanks, I’ve got your version working and have table names in MariaDB that are the same as the item names! :slight_smile:

Now to try and break it… :wink:

Is there anything in particular you’d like me to test? I’ve set up a remoteopenhab connection to my live OH system so I can create items in the test system and link them to items in the live system to get real data.

1 Like

I did some quite extensive tests myself and also added unit tests for all the corner cases I could think of. So actually I’m currently out of ideas - knock yourself out. :slight_smile: I already appreciate the confirmation that it works with MariaDB as I limited myself to testing only with MySQL using my existing setup.

Out of curiosity: On which OS are you running MariaDB? You could try to create two similar items to check if case sensitivity is working correctly in your setup: MyItem and myItem (where only case differs).

I just created two switch items: myItem and MyItem which resulted in two new tables on the DB with the same names. I toggled the switches a few times and the correct data was written to each table. Great! :slight_smile:

I’m running MariaDB on Debian 11 Bullseye. The version currently installed is mariadb Ver 15.1 Distrib 10.5.15-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper.

2 Likes

Now, perhaps you could also try this version:
https://drive.google.com/file/d/1acdWkRdB8R0aPCOtRWVEW2xB3nPXa6oz/view?usp=sharing

with MariaDB Java Client 3.0.8?

You will need to replace mariadb-java-client.jar in the addons directory with this:
https://repo1.maven.org/maven2/org/mariadb/jdbc/mariadb-java-client/3.0.8/mariadb-java-client-3.0.8.jar

It would be nice to test compatibility with the newer version now that you’re at it, since the previous version is more than six years old.

@higgers - in case you would find time to resume testing with the new MariaDB driver, I have now created a draft PR for this upgrade: