[jdbc] Creating too wide columns

DanielMalmgren · December 15, 2020, 9:42am

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?

mhilbush · December 15, 2020, 2:53pm

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.

DanielMalmgren · December 15, 2020, 3:02pm

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

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?

mhilbush · December 15, 2020, 3:13pm

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?

DanielMalmgren · December 15, 2020, 3:18pm

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?

mhilbush · December 15, 2020, 3:26pm

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.

DanielMalmgren · December 15, 2020, 5:31pm

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

cweitkamp · December 15, 2020, 6:39pm

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.

cweitkamp · December 15, 2020, 7:48pm

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) [?:?]

cweitkamp · December 15, 2020, 8:07pm

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

mhilbush · December 15, 2020, 8:23pm

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

mhilbush · December 15, 2020, 8:29pm

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…

mhilbush · December 15, 2020, 8:31pm

BTW, thanks for the fix!

cweitkamp · December 15, 2020, 8:36pm

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.

mhilbush · December 15, 2020, 8:43pm

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

DanielMalmgren · December 16, 2020, 8:10am

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.

cweitkamp · December 16, 2020, 10:25pm

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.

nelson.aponte · May 31, 2024, 8:10am

Hi, guys.

I wasn’t sure if I shoud re-open this ticket after so long, but I’m started having the same issue with my MySQL after I tried to persist some new items. As far as I understand, the problem is with the image item.

I’m running OH 4.1.2, MySQL 8.0.26, and JDBC.

The error message I get is

05/31/2024 09:43:31.245 [WARN ] [.jdbc.internal.JdbcPersistenceService] - JDBC::store: Unable to store item
org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException: Error in SQL query!!!; Column length too big for column 'value' (max = 16383); use BLOB or TEXT instead Query: CREATE TABLE IF NOT EXISTS Item8505 (time TIMESTAMP(3) NOT NULL, value VARCHAR(21717), PRIMARY KEY(time)) Parameters: []; Pool Name= yank-default; SQL= CREATE TABLE IF NOT EXISTS Item8505 (time TIMESTAMP(3) NOT NULL, value VARCHAR(21717), PRIMARY KEY(time))
	at org.openhab.persistence.jdbc.internal.db.JdbcBaseDAO.doCreateItemTable(JdbcBaseDAO.java:414) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.createItemTable(JdbcMapper.java:202) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.getTable(JdbcMapper.java:392) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:217) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.internalStore(JdbcPersistenceService.java:174) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.lambda$2(JdbcPersistenceService.java:151) ~[?:?]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
	at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) [?:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
	at java.lang.Thread.run(Thread.java:833) [?:?]
05/31/2024 09:43:31.266 [WARN ] [.jdbc.internal.JdbcPersistenceService] - JDBC::store: Unable to store item
org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException: Error in SQL query!!!; Column length too big for column 'value' (max = 16383); use BLOB or TEXT instead Query: CREATE TABLE IF NOT EXISTS Item8506 (time TIMESTAMP(3) NOT NULL, value VARCHAR(21717), PRIMARY KEY(time)) Parameters: []; Pool Name= yank-default; SQL= CREATE TABLE IF NOT EXISTS Item8506 (time TIMESTAMP(3) NOT NULL, value VARCHAR(21717), PRIMARY KEY(time))
	at org.openhab.persistence.jdbc.internal.db.JdbcBaseDAO.doCreateItemTable(JdbcBaseDAO.java:414) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.createItemTable(JdbcMapper.java:202) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.getTable(JdbcMapper.java:392) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:217) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.internalStore(JdbcPersistenceService.java:174) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.lambda$2(JdbcPersistenceService.java:151) ~[?:?]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
	at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) [?:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
	at java.lang.Thread.run(Thread.java:833) [?:?]

The “warning” repeats itself over and over, just increasing the tables name Item85XX

This the configuration of the jdbc driver

# D A T A B A S E  C O N F I G
# Some URL-Examples, 'service' identifies and activates internally the correct jdbc driver.
# required database url like 'jdbc:<service>:<host>[:<port>;<attributes>]'
# url=jdbc:derby:./testDerby;create=true
# url=jdbc:h2:./testH2
# url=jdbc:hsqldb:./testHsqlDb
#url=jdbc:mariadb://localhost/YYYYY
 url=jdbc:mysql://localhost/YYYYY
# url=jdbc:postgresql://192.168.0.1:5432/testPostgresql
# url=jdbc:sqlite:./testSqlite.db
# url=

# required database user
user=home_data_manager

# required database password
password=XXXXX

# E R R O R   H A N D L I N G
# optional when Service is deactivated (optional, default: 0 -> ignore) 
#errReconnectThreshold=

# I T E M   O P E R A T I O N S
# optional tweaking SQL datatypes
# see: https://mybatis.github.io/mybatis-3/apidocs/reference/org/apache/ibatis/type/JdbcType.html   
# see: http://www.h2database.com/html/datatypes.html
# see: http://www.postgresql.org/docs/9.3/static/datatype.html
# defaults:
#sqltype.CALL          =   VARCHAR(200)
#sqltype.COLOR         =   VARCHAR(70)
#sqltype.CONTACT       =   VARCHAR(6)
#sqltype.DATETIME      =   DATETIME
#sqltype.DIMMER        =   TINYINT
#sqltype.LOCATION      =   VARCHAR(30)
#sqltype.NUMBER        =   DOUBLE
#sqltype.ROLLERSHUTTER =   TINYINT
#sqltype.STRING        =   VARCHAR(65500)
#sqltype.SWITCH        =   VARCHAR(6)

# For Itemtype "Number" default decimal digit count (optional, default: 3) 
#numberDecimalcount=

# T A B L E   O P E R A T I O N S
# Tablename Prefix String (optional, default: "item") 
# for Migration from MYSQL-Bundle set to 'Item'.
tableNamePrefix=Item

# Tablename Prefix generation, using Item real names or "item" (optional, default: false -> "item") 
# If true, 'tableNamePrefix' is ignored.
tableUseRealItemNames=false

# Tablename Suffix length (optional, default: 4 -> 0001-9999) 
# for Migration from MYSQL-Bundle set to 0.
tableIdDigitCount=0

# Rename existing Tables using tableUseRealItemNames and tableIdDigitCount (optional, default: false)
# USE WITH CARE! Deactivate after Renaming is done!
#rebuildTableNames=true

# D A T A B A S E  C O N N E C T I O N S
# Some embeded Databases can handle only one Connection (optional, default: configured per database in packet org.openhab.persistence.jdbc.db.* )
# see: https://github.com/brettwooldridge/HikariCP/issues/256
# jdbc.maximumPoolSize = 1
# jdbc.minimumIdle = 1

# T I M E K E E P I N G
# (optional, default: false) 
#enableLogTime=true

Seems to be related to this ticket: MySQL String Persistence too big · Issue #5957 · openhab/openhab1-addons · GitHub

Is there anything I can do besides not storing the image?