Friday, March 25, 2011

Insert LONGTEXT in Database using Hibernate

I would like to share my experience while inserting a Big XML file to the Database, The column dataType is LONGTEXT. The XML File sine is about 1.2MB, when i tried to insert in database using the following Hibernate Code i got the Exception as follows.
Code

Transaction transaction =  sessionFactory.getCurrentSession().beginTransaction();
transaction.begin();
sessionFactory.getCurrentSession().persist(xmlData);
transaction.commit();


Where xmlData is as follows

public class XMLData {
 private int id;
 private String xml;

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getXml() {
  return xml;
 }

 public void setXml(String xml) {
  this.xml = xml;
 }
}
I got the following Exception.

Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not insert: [org.cgi.model.Server]
 at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
 at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:40)
 at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2093)
 at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2573)
 at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:47)
 at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
 at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:290)
 at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:180)
 at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:108)
 at org.hibernate.event.def.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:131)
 at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:87)
 at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:38)
 at org.hibernate.impl.SessionImpl.firePersist(SessionImpl.java:618)
 at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:592)
 at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:596)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
 at $Proxy0.persist(Unknown Source)
 at org.cgi.dao.manager.ServerDAO.persist(ServerDAO.java:44)
 at org.cgi.dao.manager.test.TestManager.main(TestManager.java:23)
Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1094608 > 177152). You can change this value on the server by setting the max_allowed_packet' variable.
 at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3285)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1970)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)
 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
 at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
 at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
 at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
 at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
 at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:73)
 at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:33)
 ... 21 more

Solution:
As it is mentioned in the Exception we need to increase the max_allowed_packet variable of MySQL server.
By Default MySQL provides the size of 1 MB. you can read the value by the following Command in MySQL Command Prompt.

show variables like '%max_allowed_packet';
Now you can Set the Value by using the following Command.
set global max_allowed_packet=16777216;

For Reference
The maximum allowed packet size is 1GB.



No comments: