MySQL多个大文本域异常

逃离我推掉我的手 2022-04-21 15:28 150阅读 0赞

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

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)

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

sb.append(" PRIMARY KEY (`id`)");     
    	       sb.append(") engine=MYISAM  DEFAULT CHARSET=utf8;");   
    	       // sb.append(") engine=InnoDB  DEFAULT CHARSET=utf8;");

\[b\]\[color=olive\]\[size=large\]至此，解决上面的异常问题，下面我们来详细的看下这两种存储引擎的具体区别：\[/size\]\[/color\]\[/b\]  
\[b\]\[size=large\]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，这样可以提高多用户并发操作的性能。\[/size\]\[/b\]  
  
\[b\]\[color=olive\]\[size=large\]修改表的存储引擎的DDL SQL语句\[/size\]\[/color\]\[/b\]

alter table tablename type=InnoDB