mysql文本域类型_MySQL多个大文本域异常

最近散仙在处理标注结果入库的结果数据时，发现再向MySQL插入text域的文本值，莫名其妙的总是报一个异常，信息如下：

java.sql.SQLException: Got error 139 from storage engine

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)

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 com.tools.OperaDataBase.insertData(OperaDataBase.java:191)

at com.ninemax.action.ToolsAction.showAllListDocument(ToolsAction.java:130)

at com.ninemax.action.ToolsAction.solveBatch(ToolsAction.java:241)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:601)

at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:452)

at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:291)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:254)

at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176)

at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:263)

at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)

at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:133)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:207)

at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:207)

at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:190)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:75)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:94)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:243)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:267)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:142)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:166)

at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:176)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:190)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:187)

at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)

at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)

at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:485)

at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)

at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)

at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:1852)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

at java.lang.Thread.run(Thread.java:722)

后经，查资料发现，原来跟MySQL的内部存储引擎有关系，散仙的数据库默认用的InnoDB的引擎，对text类型的文本域支持有限，不能超过15个text类型的，每条数据不能超过8000字节，散仙要存的有20多个字段，发现果然里面有超过15个的text类型，没办法了，这10多个text类型的数据是必须的，换成varchar可能会超出varchar的上限，所以最后散仙只好在动态建表时，指定MySQL的引擎为MyISAM，部分代码如下：

sb.append(" PRIMARY KEY (`id`)");

sb.append(") engine=MYISAM DEFAULT CHARSET=utf8;");

// sb.append(") engine=InnoDB DEFAULT CHARSET=utf8;");

至此，解决上面的异常问题，下面我们来详细的看下这两种存储引擎的具体区别：

MyISAM是MySQL的默认存储引擎，基于传统的ISAM类型，支持全文搜索，但不是事务安全的，而且不支持外键。每张MyISAM表存放在三个文件中：frm 文件存放表格定义；数据文件是MYD (MYData)；索引文件是MYI (MYIndex)。

InnoDB是事务型引擎，支持回滚、崩溃恢复能力、多版本并发控制、ACID事务，支持行级锁定(InnoDB表的行锁不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，如like操作时的SQL语句)，以及提供与Oracle类型一致的不加锁读取方式。InnoDB存储它的表和索引在一个表空间中，表空间可以包含数个文件。

主要区别：

MyISAM是非事务安全型的，而InnoDB是事务安全型的。

MyISAM锁的粒度是表级，而InnoDB支持行级锁定。

MyISAM支持全文类型索引，而InnoDB不支持全文索引。

MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。

MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。

InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表(alter table tablename type=innodb)。

应用场景：

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。

InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

修改表的存储引擎的DDL SQL语句

alter table tablename type=InnoDB

分享到：

2014-02-25 17:43

浏览 1760

论坛回复 / 浏览 (0 / 2248)

分类:数据库

评论