4 Replies Latest reply on Aug 9, 2013 9:33 AM by ravi gupta

    MySQL Data truncation: Data too long for column

    ravi gupta Newbie

      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)

        • 1. Re: MySQL Data truncation: Data too long for column
          Tomas Remes Expert

          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.

          • 2. Re: MySQL Data truncation: Data too long for column
            ravi gupta Newbie

            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

            • 3. Re: MySQL Data truncation: Data too long for column
              Tomas Remes Expert

              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?

              • 4. Re: MySQL Data truncation: Data too long for column
                ravi gupta Newbie

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

                thank you