Re: [Hsqldb-user] data exception: string data, right truncation

[Fred T. <fr...@us...>]

The sql.enforce_strict_size=false is for backward compatibilty in
migration. We do not test the software with this setting.

This issue has been reported from time to time, especially with migrated
databases.

What happens is when you add / drop columns or indexes, data in existing
columns is copied into the new version of the table. It seems there is a
situation where a column has a type of (for example) VARCHAR(100) and
some of its values are longer than 100 characters.

You can use SELECT MAX(CHAR_LENGTH(COL)) FROM .. to find the required
size on the existing data and then use a statement such as ALTER TABLE
... ALTER COLUMN COL DATA TYPE VARCHAR(N) to increase the column size.

Use the INFORMATION_SCHEMA.COLUMNS table to check the existing sizes.

Fred

On Thu, 28 Oct 2010 16:38 +0200, "Christian Beil"
<chr...@we...> wrote:
> Am 28.10.2010 16:33, schrieb Christian Beil:
> > Hi,
> >
> > I hope that someone can give me some quick help.
> > One of our customers is always getting
> >           org.hsqldb.HsqlException: data exception: string data, right
> > truncation
> > on dropping a column
> >           ALTER TABLE table_name DROP column_name
> >
> > We're using HSQLDB 2.0, the database is not migrated from 1.8 and we're
> > setting the property sql.enforce_strict_size=false at startup.
> > Changing the column types from varchar to longvarchar didn't help.
> >
> > Thanks in advance.
> >
> > Christian
> >
> Here is the stacktrace:
> 
> Caused by: org.hsqldb.HsqlException: data exception: string data, right 
> truncation
> 
> at org.hsqldb.error.Error.error(Error.java:131)
> 
> at org.hsqldb.error.Error.error(Error.java:101)
> 
> at 
> org.hsqldb.types.CharacterType.convertToTypeLimits(CharacterType.java:451)
> 
> at org.hsqldb.Table.enforceTypeLimits(Table.java:2262)
> 
> at org.hsqldb.persist.RowStoreAVL.moveData(RowStoreAVL.java:349)
> 
> at org.hsqldb.TableWorks.moveData(TableWorks.java:1275)
> 
> at org.hsqldb.TableWorks.dropColumn(TableWorks.java:805)
> 
> at org.hsqldb.StatementSchema.getResult(StatementSchema.java:680)
> 
> at org.hsqldb.StatementSchema.execute(StatementSchema.java:271)
> 
> at org.hsqldb.Session.executeCompiledStatement(Session.java:1286)
> 
> at org.hsqldb.Session.executeDirectStatement(Session.java:1175)
> 
> at org.hsqldb.Session.execute(Session.java:968)
> 
> at org.hsqldb.jdbc.JDBCStatement.fetchResult(JDBCStatement.java:1805)
> 
> ... 27 more
> 
> 
> 
> ------------------------------------------------------------------------------
> Nokia and AT&T present the 2010 Calling All Innovators-North America
> contest
> Create new apps & games for the Nokia N8 for consumers in  U.S. and
> Canada
> $10 million total in prizes - $4M cash, 500 devices, nearly $6M in
> marketing
> Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store 
> http://p.sf.net/sfu/nokia-dev2dev
> _______________________________________________
> Hsqldb-user mailing list
> Hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>