org.hibernate.exception.GenericJDBCException: could not insert:

解决MySQL表满异常

最新推荐文章于 2022-12-29 17:31:09 发布

原创最新推荐文章于 2022-12-29 17:31:09 发布 · 2.8k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#insert #table #session #variables #buffer #mysql

本文介绍了解决Hibernate在插入数据时遇到的表已满异常的多种方法，包括调整MySQL配置参数tmp_table_size和max_heap_table_size，以及通过迁移ibdata1文件来释放空间。

org.hibernate.exception.GenericJDBCException: could not insert: [com.wellsynergy.kmatrix.db.TbDocument]
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1777)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2178)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:34)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:240)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:160)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:95)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:481)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:476)
at com.wellsynergy.kmatrix.grasper.http.kmatrix.KMatrixBaseAction.saveDocument(KMatrixBaseAction.java:277)
at com.wellsynergy.kmatrix.grasper.http.kmatrix.KMatrixBaseAction.saveList(KMatrixBaseAction.java:121)
at com.wellsynergy.kmatrix.grasper.http.kmatrix.KMatrixBaseAction.doAction(KMatrixBaseAction.java:54)
at com.wellsynergy.kmatrix.grasper.http.command.HttpGrasperDoAction.processCommand(HttpGrasperDoAction.java:20)
at com.wellsynergy.kmatrix.grasper.http.AbstractHttpGrasperCommand.process(AbstractHttpGrasperCommand.java:213)
at com.wellsynergy.kmatrix.grasper.http.AbstractHttpGrasperCommand.runGrap(AbstractHttpGrasperCommand.java:111)
at com.wellsynergy.kmatrix.grasper.http.AbstractHttpGrasperCommand.executeChild(AbstractHttpGrasperCommand.java:171)
at com.wellsynergy.kmatrix.grasper.http.command.HttpGrasperGenNextPage.processCommand(HttpGrasperGenNextPage.java:38)
at com.wellsynergy.kmatrix.grasper.http.AbstractHttpGrasperCommand.process(AbstractHttpGrasperCommand.java:213)
at com.wellsynergy.kmatrix.grasper.http.AbstractHttpGrasperCommand.runGrap(AbstractHttpGrasperCommand.java:117)
at com.wellsynergy.kmatrix.grasper.http.AbstractHttpGrasperCommand.executeChild(AbstractHttpGrasperCommand.java:171)
at com.wellsynergy.kmatrix.grasper.http.command.HttpGrasperGenNextPage.processCommand(HttpGrasperGenNextPage.java:38)
at com.wellsynergy.kmatrix.grasper.http.AbstractHttpGrasperCommand.process(AbstractHttpGrasperCommand.java:213)
at com.wellsynergy.kmatrix.grasper.http.AbstractHttpGrasperCommand.runGrap(AbstractHttpGrasperCommand.java:117)
at com.wellsynergy.kmatrix.grasper.http.AbstractHttpGrasperCommand.executeChild(AbstractHttpGrasperCommand.java:171)
at com.wellsynergy.kmatrix.grasper.http.command.HttpGrasperStartHTTPGrasper.processCommand(HttpGrasperStartHTTPGrasper.java:63)
at com.wellsynergy.kmatrix.grasper.http.AbstractHttpGrasperCommand.process(AbstractHttpGrasperCommand.java:213)
at com.wellsynergy.kmatrix.grasper.http.HttpGrasperJob.runGrasper(HttpGrasperJob.java:128)
at com.wellsynergy.kmatrix.grasper.http.HttpGrasper.grasp(HttpGrasper.java:48)
at com.wellsynergy.kmatrix.grasper.http.test.GrasperThread.run(GraspWebForums.java:225)
Caused by: java.sql.SQLException: The table 'tb_document' is full
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1085)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:670)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1159)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1076)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1061)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:104)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1759)

... 33 more

（以下来自 http://zuzong.iteye.com/blog/1071738）

tmp_table_size

如果内存内的临时表超过该值，MySQL自动将它转换为硬盘上的MyISAM表。如果你执行许多高级GROUP BY查询并且有大量内存，则可以增加tmp_table_size的值。

max_heap_table_size

该变量设置MEMORY (HEAP)表可以增长到的最大空间大小。该变量用来计算MEMORY表的MAX_ROWS值。在已有的MEMORY表上设置该变量没有效果，除非用CREATE TABLE或TRUNCATE TABLE等语句重新创建表。

如何设置：
方法一，修改my.ini或my.cnf
tmp_table_size=64M
max_heap_table_size=32M
此方法最大的缺点，是一旦mysql重启，一切恢复默认值

方法二，mysqld命令行
mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
--tmp_table_size=64M --max_heap_table_size=32M &

方法三，动态修改
因为tmp_table_size和max_heap_table_size，都有session作用域，所以，一般情况下，尽量这样做吧
SET SESSION tmp_table_size=64*1024*1024
SET SESSION max_heap_table_size=32*1024*1024

修改好后如何查看：
查看服务器配置信息
SHOW VARIABLES WHERE Variable_name LIKE '%tmp_table_size%'

查看服务器运行信息
SHOW STATUS

如何使用JDBC获得他们的值：
SELECT @@tmp_table_size
SELECT @@max_heap_table_size