MySQL Data truncation: Data too long for column

[rkgupta12]

I am doing a small POC that has Hibernate (Object Grid Mapper) -> Infinispan  -> MySQL as the data store.

It works, however, I am getting the below error. By default in the guides for Infinispan using org.infinispan.loaders.jdbc.binary.JdbcBinaryCacheStore , the data type is binary(1), I changed that to everything I could think of (varbinary, longtext, longclob), and everything results in the below error.

I don't think the data can be that long, so surprised.

<property name="connectionUrl" value="jdbc:mysql:///infinispan" />

                    <property name="userName" value="root" />

   <property name="password" value="root" />

                    <property name="driverClass" value="com.mysql.jdbc.Driver" />

               <property name="idColumnType" value="VARCHAR(255)"/>

               <property name="dataColumnType" value="LONGTEXT"/>

The error is (data does get stored FYI):

12:30:53,458 ERROR JdbcBinaryCacheStore:219 - ISPN008012: Sql failure while inserting bucket: Bucket{entries={RowKey{table='sequences', columnNames=[key], columnValues=[dog]}=ImmortalCacheEntry{key=RowKey{table='sequences', columnNames=[key], columnValues=[dog]}, value=ImmortalCacheValue {value=101}}}, bucketId='-164422656'}

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'DATA_COLUMN' at row 1

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)

    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)

    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)

    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)

    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)

    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)

    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)

    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)

    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)

    at org.infinispan.loaders.jdbc.binary.JdbcBinaryCacheStore.insertBucket(JdbcBinaryCacheStore.java:214)

    at org.infinispan.loaders.bucket.BucketBasedCacheStore.storeLockSafe(BucketBasedCacheStore.java:94)

    at org.infinispan.loaders.bucket.BucketBasedCacheStore.storeLockSafe(BucketBasedCacheStore.java:49)

    at org.infinispan.loaders.LockSupportCacheStore.store(LockSupportCacheStore.java:213)

    at org.infinispan.loaders.AbstractCacheStore.applyModifications(AbstractCacheStore.java:126)

    at org.infinispan.loaders.AbstractCacheStore.prepare(AbstractCacheStore.java:144)

    at org.infinispan.loaders.decorators.AsyncStore.applyModificationsSync(AsyncStore.java:340)

    at org.infinispan.loaders.decorators.AsyncStore$AsyncStoreProcessor.retryWork(AsyncStore.java:743)

    at org.infinispan.loaders.decorators.AsyncStore$AsyncStoreProcessor.run(AsyncStore.java:727)

    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

    at java.lang.Thread.run(Thread.java:662)

[tremes]

Hi ravi!

I've faced exacly the same problem, however I didn't precisely count the size of inserted data (can experiment with that), I run with VARBINARY(10000) and it works for me in MySQL.

[rkgupta12]

I tried VARBINARY(10000) .. got same error

then I added a 0

VARBINARY(100000)

and got.

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Column length too big for column 'DATA_COLUMN' (max = 65535); use BLOB or TEXT instead

    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

    at com.mysql.jdbc.Util.getInstance(Util.java:381)

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)

    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)

    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)

    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)

    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564)

    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)

    at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)

    at org.infinispan.loaders.jdbc.TableManipulation.executeUpdateSql(TableManipulation.java:167)

    ... 50 more

[tremes]

yes, 100 000 is really too much - see e.g http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html . It appears that you'll really need to count the size of your data. I used this cache configuration for http session and tried it with simple testing app, so 10kb was enough for me. Did you try suggested BLOB type?

[rkgupta12]

Seems to work now, I had to drop the database, just not alter the column

thank you