JPA: "Data too long for column" does not change

Question

One of my entities Machinery has got a String property called notes. JPA 2-Hibernate generates the schema for me, in my case the RDBMS is a MySQL.

notes is created as a VARCHAR(255) column, which is right.

Users begin to create records and all works perfectly, but then some users get the infamous Data too long for column "notes" error.

That field hasn't enough room for user's machinery notes! Ok, no problem. Let's change the schema!

So, I open my entity class and change my property to:

@Column(length=1000000)
@Lob
private String notes;

By the way, my persistence.xml declares:

<property name="hibernate.hbm2ddl.auto" value="update" />

After an application restart, I'm glad that Hibernate is altering my notes column to a LONGTEXT (it's enough for me).

So I first try using my application to create a new "long-noted" record and I still the the error "Data too long" although is a LONGTEXT now.

Then, I try doing a raw INSERT from the MySQL command line and it works! I can insert long notes in that field!

Finally, I DROP my local/staging DB schema and change hibernate.hbm2ddl.auto in persistence.xml to create and it works.

Does JPA still think that it's a VARCHAR? Does it have some sort of cache or some place in which it stores schema's information?

I can't drop my production db, obviously. So, what can I do to reset or change the column type?

I am using JBossAS7 JPA 2-Hibernate.

Depew

Answer 1

NOTHING! I ended up with: - DB backup - hbm2ddl => CREATE-DROP - hbm2ddl => UPDATE - DB restore

Crazy! :(

Depew

Answer 2

The definitive Hibernate book "Java persistence with Hibernate" mentions this about the update value for hibernate.hbm2ddl.auto (bolds are mine)

An additional option for this configuration property, update, can be useful during development: it enables the built-in SchemaUpdate tool, which can make schema evolution easier. If enabled, Hibernate reads the JDBC database metadata on startup and creates new tables and constraints by comparing the old schema with the current mapping metadata. Note that this functionality depends on the quality of the metadata provided by the JDBC driver, an area in which many drivers are lacking. In practice, this feature is therefore less exciting and useful than it sounds.

Also Hibernate docs suggest the same here

The SchemaUpdate tool will update an existing schema with "incremental" changes. The SchemaUpdate depends upon the JDBC metadata API and, as such, will not work with all JDBC drivers.

I tried to replicate your use case and found it surprising that I too had this issue.

I have a user entity like this

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@Table( name = "usr" )

public class User {
  @Id
  @GeneratedValue
  private Long id;

  @Column( length = 40, unique = true )
  private String name;

  @Lob
  @Column( length = 100000 )
  private String text;

  public long getId() {
    return id;
  }

  public void setName( String name ) {
    this.name = name;
  }

  public String getName() {
    return name;
  }

  public String getText() {
    return text;
  }

  public void setText( String text ) {
    this.text = text;
  }

}

and my persistence xml is like this

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
             version="2.0">
   <persistence-unit name="jpatest" transaction-type="RESOURCE_LOCAL">
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
            <property name="hibernate.connection.username" value="root"/>
            <property name="hibernate.connection.password" value="root"/>
            <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/jpadatabase"/>
            <property name="hibernate.show-sql" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

If I change the value of hibernate.hbm2ddl.auto to create and change the text property of entity to this

.....

  @Column( length = 255 )
  private String text;
......

The schema generator generates following sql on startup

DEBUG SchemaExport:415 - drop table if exists usr
DEBUG SchemaExport:415 - create table usr (id bigint not null auto_increment, name varchar(40) unique, text varchar(255), primary key (id)) ENGINE=InnoDB
INFO SchemaExport:281 - schema export complete

Now changing the property in entity again

.....
@Lob
@Column( length = 100000 )
private String text;
.......

Now following correct sql is generated

DEBUG SchemaExport:415 - drop table if exists usr
DEBUG SchemaExport:415 - create table usr (id bigint not null auto_increment, name varchar(40) unique, text longtext, primary key (id)) ENGINE=InnoDB
INFO SchemaExport:281 - schema export complete

So far so good.

Now if I change the value hibernate.hbm2ddl.auto to update and repeat the above change in entity in the same order, no update column sql is generated inspite of the fact that I have updated the text column from varchar(255) to LONGTEXT

 INFO TableMetadata:65 - table found: jpadatabase.usr
 INFO TableMetadata:66 - columns: [id, text, name]
 INFO TableMetadata:68 - foreign keys: []
 INFO TableMetadata:69 - indexes: [name, primary]
 DEBUG DefaultIdentifierGeneratorFactory:90 - Setting dialect  [org.hibernate.dialect.MySQL5InnoDBDialect]
 INFO SchemaUpdate:217 - schema update complete

However if I am using update and instead of modifying a property, I add another property location then correct sql is generated again

DEBUG SchemaUpdate:203 - alter table usr add column location varchar(255)
INFO SchemaUpdate:217 - schema update complete

So in essence the create ( which first drops the table and then recreates) works correctly however the update does not if there is a modification in property metadata.

To me it looks like the issue of driver support for incremental updates is at play here. Also intuitively If I think about this, then it does not make sense to give support to update the datatype of columns. What will happens to the existing data if the modified column datatype is a scaled down version of earlier datatype.

Guayule

Thank you, it's a good answer but.... even if I alter "manually" my column, I still got the error! Datatype DOES change, but Hibernate keeps handling it like the old datatype. – Depew

When I alter the column manually then I don’t have any problem in persisting large text. The only time I get exception is when I exceed the packet size which mysql can handle. For e.g.,I get this exception when my packet size is 2500103 - “com.mysql.jdbc.PacketTooBigException: Packet for query is too large (2500103 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.” I could successfully persist texts of length 1000018 characters. I did't test above that. So you problems seems to be something different. – Guayule

I am guessing that as you are using JBossAS7 application server and when you change the column datatype externally, the connections held by connection pool might be stale in terms of table metadata. Try to restart your server and see if this could be the issue? – Guayule

Try turning the Hibernate log level to DEBUG or TRACE and track what's hibernate ( as JPA provider) is trying to do under the hood ! – Guayule

It does update the schema. now I have longtext but it keeps telling me data too long – Depew

Answer 3

I just had this problem, after read this I found the answer, is very logical if you think about it, is related with the audited tables of envers.

How to reproduce

• You are using hibernate envers
• You change the type of a column in the code adding the annotation @Lob.

Cause

Hibernate only updates the original table, not the audited one, this is what hibernate does:

ALTER TABLE piece_aud MODIFY notes LONGTEXT;

Symptoms

MysqlDataTruncation exception is raised with the infamous "Data too long for column 'x'".

Solution

Manually update the type of the audited table. Example:

ALTER TABLE piece_aud MODIFY notes LONGTEXT;

Alternatively you can also update the column definition like this (if you don't mind to delete an re-create your schema):

@Column(name="notes",columnDefinition="LONGTEXT")
private String notes;

This was very tricky because the exception only say the name of the column and not the name of the table!!, that's why drop and re-create schema also works, cause audited tables are regenerated.

Brag

totally forgot about the audit table! you saved the day – Szymanowski

Thanks a lot @ZooMMX. I forgot the audit table. – Pahari

Thank you so much for posting this - the problem was driving me crazy. This should really be marked as the correct answer. – Neom

Answer 4

I too had the same scenario, and below one is working smoothly for me

@Column(name="your_column_name",columnDefinition="LONGTEXT")
private String notes;

I know it was asked long back but still it might be of help to someone. :)

Fisherman

Answer 5

I had the same problem INSTER statement work perfectly direct on database but doing it via Hibernate did not work and produced Data truncation: Data too long for column. Everything worked when hbm2ddl was changed to create-drop.

But my real problem was due to me using Audit tables using the Hibernate build in Audit functionality. The main table was updated correctly to the increased size but not the audit table so all changes that was written to the Audit table caused this Data truncation error. Just manually updated the column in the Audit table to the correct size and everything worked fine.

Doane

The part with the Audit table was also my problem. Thanks for the answer. – Gandzha

The auditing was the hint I needed ;) Thanks so far! – Guttate

Answer 6

I think this solves the problem @Column(columnDefinition="LONGVARCHAR")

Peccant

This totally does the trick if you're using an H2/Hibernate database. – Cockatrice

Answer 7

@Column( length = 100000 )
private String text;

This works but you have to DELETE the table! Because hibernate doesn't update the table in this case. So you have to force hibernate to recreate the table and it works!

Chandless

Answer 8

As for me I exclude the following properties and problem goes away

<property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>

Bosom

Answer 9

Quite annoying problem, though. Instead of backing up the whole DB and change the hibernate.hbm2ddl.auto, dialect oriented ALTER TABLE SQL's can be used. For instance; for MySQL just update column type with

alter table your_table modify column your_column text

and voila!

PS: Not to forget to update audit tables!

Buddha

Answer 10

In Spring/JPA/Hibernate/Postgres,

@Type(type="org.hibernate.type.StringClobType")
String message;

Works like a charm for me!

Important Note: The column type in the database doesn't auto-update for me, so I need to either allow Hibernate to recreate the table, or manually change the type from varchar(255) to text, otherwise nothing appears to happen.

Megilp