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
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';
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:
Post a Comment