7

I have a simple EntityBean with a @Lob annotation. If I delete this annotation I get no errors on JBossAS 6.0.0.Final and MySQL5. But if I annotate it with @Lob (because mt contains about 100 to 5000 characters in my case) I get errors in my testing environment if I persist the entity.

  • without @Lob: mt is mapped to VARCHAR
  • with @Lob: mt is mapped to LONGTEXT (this is what I want, but I get errors)

This my entity:

@Entity
@Table(name = "Description")
public class Description implements Serializable
{ 
  public static final long serialVersionUID=1;

  @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
  private long id;  

  @Lob
  private String mt;
}  // ... getter/setter

The error are here:

...
Caused by: org.hibernate.exception.GenericJDBCException: could not insert
   [my.Description]
...
Caused by: java.sql.SQLException: Connection is not associated with a managed
     connection.org.jboss.resource.adapter.jdbc.jdk6.WrappedConnectionJDK6@3e4dd
...

I really don't know why I get this (reproduceable) error. The environment seems to be ok, many other tests are passed and it even works without the @Lob annotation.

This question is related to JPA: how do I persist a String into a database field, type MYSQL Text, where the usage of @Lob for JPA/MySQL is the accepted answer.

Update 1 The error above is OS specific. On a W7 machine I have no problems with @Lob, with OSX Lion always the error. I will try to update MySQL and the driver.

Update 2 The proposed workaround by Kimi with @Column(columnDefinition = "longtext") works fine, even on OSX. In both cases MySQL creates the same column: LONGTEXT.

Update 3 I updated MySQL to mysql-5.5.17-osx10.6-x86_64 and the connector to mysql-connector-java-5.1.18. Still the same error.

Community
  • 1
  • 1
Thor
  • 6,607
  • 13
  • 62
  • 96
  • 2
    What does your description table look like? If mt is a varchar in your database then why would you want to try to map it to a Lob? – mR_fr0g Oct 21 '11 at 14:10
  • Does the MySQL5 Supports the LONGTEXT as a correct value for creating an identifier? I'm not sure how the DB should do that... increment the text-value? – Piotr Nowicki Oct 21 '11 at 14:20
  • @PedroKowalski mt is not used as the identifier. The id field is. This is how i read it at first but it is just poor formatting of the code. – mR_fr0g Oct 21 '11 at 14:47
  • @mR_fr0g ok, sorry mate - my bad :-) – Piotr Nowicki Oct 21 '11 at 14:56
  • @Thor - just as mR_fr0g said - is the any purpose of using the Lob in your case? – Piotr Nowicki Oct 21 '11 at 15:19
  • I've reformatted the code, now it should be better readable. @mR_fr0g The `@Lob` is required (as far as I know) for text fields with a high number of characters (about 100 to 5000 in my case). – Thor Oct 21 '11 at 15:27
  • Just shooting here - did you try using both @Lob and @Column(columnDefinition=...) to force it to be a VARCHAR as defined in the DB? – Piotr Nowicki Oct 24 '11 at 08:06
  • The DB is created with the EJB annotation. I can perfectly live with a LONGTEXT, it does not have to be a VARCHAR. – Thor Oct 24 '11 at 10:16

2 Answers2

5

There is no need to annotate a String property with @Lob. Just set the column length with @Column(length = 10000).

EDIT:

Moreover, you can always set the length to your database specific maximum value, as well as define the column type with the columndefinition setting to whichever suits your needs.

For example, if you're using MySQL 5.0.3 or later, the maximum amount of data that can be stored in each data type is as follows:

  • VARCHAR: 65,535 bytes (~64Kb, 21,844 UTF-8 encoded characters)
  • TEXT: 65,535 bytes (~64Kb, 21,844 UTF-8 encoded characters)
  • MEDIUMTEXT: 16,777,215 bytes (~16Mb, ~5.5 million UTF-8 encoded characters)
  • LONGTEXT: 4,294,967,295 bytes (~4GB, ~1.4 billion UTF-8 encoded characters).

As i understand it, @Lob just sets the column type and length depending on the underlying database.

Kimi
  • 6,239
  • 5
  • 32
  • 49
  • What happens if the String has for some reasons more than 10000 characters? Please see, http://stackoverflow.com/questions/3868096 – Thor Oct 26 '11 at 09:01
  • 2
    Well, you can always set the length to your database specific maximum value, as well as define the column type with the `columndefinition` setting to whichever suits your needs. For example, if you're using MySQL 5.0.3 or later, the maximum length for a UTF-8 encoded VARCHAR is 21,844 characters (65,535 bytes), which is the same as the limit of TEXT type. MEDIUMTEXT can hold 16,777,215 bytes (~16Mb, ~5.5 million characters) and LONGTEXT 4,294,967,295 bytes (~4GB, ~1.4 billion characters). As i understand it, `@Lob` just sets the column type and length to some database specific values. – Kimi Oct 26 '11 at 10:02
  • So, if portability isn't a problem, I'd go with this. – Kimi Oct 26 '11 at 10:04
  • with this annotation I get a VARCHAR 10000, but if I want to store more characters the exception `Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'mt' at row 1` is thrown. (sorry, did't see your additional comments, this comment is related to your answer) – Thor Oct 26 '11 at 10:13
  • I'll add the comment to the answer. – Kimi Oct 26 '11 at 10:33
  • Thank your for the workaround with `(columnDefinition = "longtext")` it even works on OSX (see updated question). – Thor Oct 26 '11 at 10:40
  • the issue was not solved with the setup described above, but I can use a `@Lob` with `JBoss AS 7.1.0.CR1b "Flux Capacitor"` without problem. – Thor Jan 17 '12 at 13:57
2

Another way that worked for me is to update launch configuration of jBoss with

-Dhibernate.jdbc.use_streams_for_binary=true

I'm running jBoss6 with MySQL5

j0k
  • 22,600
  • 28
  • 79
  • 90
GeNeTiC
  • 21
  • 2