[jdbc] Creating too wide columns

Add-ons Persistence Services
,
1

Hi.
I just stumbled upon this error in my log when OH tried to persist a value that hasn’t been persisted before:

10:19:54.104 [ERROR] [org.knowm.yank.Yank                  ] - Error in SQL query!!!
java.sql.SQLException: Column length too big for column 'value' (max = 16383); use BLOB or TEXT instead Query: CREATE TABLE IF NOT EXISTS ups_status_0069 (time TIMESTAMP NOT NULL, value VARCHAR(21717), PRIMARY KEY(time)) Parameters: []

I don’t know if it’s just my mysql settings or a more general problem? Apparently my database doesn’t allow wider varchar than 16383.

I manually created a table (knowing that this value will never exceed 10 characters) so the problem is solved for me, but I don’t know if it needs better handling somewhere?

2

The jdbc binding can use different column lengths depending on the database and item type.

So, it would be helpful to know the following:

  • which database (and version number) you’re using
  • the item type
  • version of OH3 you’re running.
3

Sorry, didn’t know really which information would be relevant :slight_smile:

It is OH3RC1, Mariadb 10.3.27 and a string Item.

edit: I guess it’s hard for the persistence to know how wide columns are available in the database, right? Wouldn’t it be better to simply create the column as varchar(max) then?

4

Hmm. The only place where I see 21717 showing up is in the mysql-specific settings here.

Is your jdbc.cfg specifying mariadb or mysql in the URL string?

5

Ah! Of course you are right! This is an installation which I moved from one server to another, missed the fact that the old one had mysql and the new has mariadb. Could I just change that straight off in jdbc.cfg and restart the jdbc bundle?

6

I suppose you could try it. Unfortunately, I don’t use Mariadb and I’m only marginally familiar with the jdbc persistence addon.

And, I’m not sure it will fix the issue. It looks like the JdbcMariadb sqlTypes config here just uses the settings from JdbcBase here, and it looks like that’s using 65500.

But I know @cweitkamp uses Mariadb, so maybe he can shed more light on the subject. :wink:

7

Actually, this is the first problem I’ve encountered, I guess the difference between mysql and mariadb is kinda marginal :stuck_out_tongue:

8

Yes, exactly. I am using MariaDB. Not sure which version. Can have a look later if you are interested. I never faced a problem when the binding tried to create a table with given default values.

Changing the configuration file should result in a reset of the connection, thus I think it should work like suggested.

9

Ouch, … I have to take back my statement. Looks like I never tried to persist a String Item. I will submit a fix for it.

2020-12-15 20:46:24.258 [ERROR] [org.knowm.yank.Yank                 ] - Error in SQL query!!!
java.sql.SQLException: Column length too big for column 'value' (max = 16383); use BLOB or TEXT instead Query: CREATE TABLE IF NOT EXISTS astrotimeofday_0070 (time TIMESTAMP(3) NOT NULL, value VARCHAR(65500), PRIMARY KEY(time)) Parameters: []
	at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[bundleFile:?]
	at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[bundleFile:?]
	at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[bundleFile:?]
	at org.knowm.yank.Yank.execute(Yank.java:194) [bundleFile:?]
	at org.knowm.yank.Yank.execute(Yank.java:177) [bundleFile:?]
	at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doCreateItemTable(JdbcBaseDAO.java:319) [bundleFile:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.createItemTable(JdbcMapper.java:136) [bundleFile:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.getTable(JdbcMapper.java:274) [bundleFile:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:143) [bundleFile:?]
	at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:140) [bundleFile:?]
	at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:152) [bundleFile:?]
	at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:473) [bundleFile:?]
	at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) [bundleFile:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:?]
	at java.lang.Thread.run(Unknown Source) [?:?]
10

@mhilbush One more question. Did you check if persisting a Image Item does work for MySQL?

11

Yes, I verified that Image items and Player items were being stored for mysql.

12

I also should add that VARCHAR probably is not the best way to store an Image item, as the size of an image can very easily exceed the maximum size of VARCHAR. BLOB is probably the way to do it for mysql, but I’m not sure of how to do it for the other databases. Therefore, that change probably is best left for another day…

13

BTW, thanks for the fix!

14

Oh, I have to thank you for the analysis.

I was just asking because creating the table for an Image Item failed for MariaDB too. With the same error message as above. And MySQL only defines a different VARCHAR size for String Items.

But I agree. VARCAHR for Images might be error prone too. We will see. :wink:

15

I just double-checked. It’s creating the table successfully.

image-db
image-db894×75 16 KB

16

Just for the record: I have now changed persistence engine from mysql to mariadb in my jdbc.cfg. It immediately gave tons of errors in the log, but after that everything seems to work fine, both persisting new values and fetching values.

1 Like
17

Thanks for the feedback. Glad to hear. My patch has been merged too and it looks bloke way will have a lot of fun with jdbc persistence in OH 3. :wink: