Kettle产生的奇异误差

Question

1. 它只是在Kettle 5.4中开发的一个最简单的tran。
2. tran从MSSQL中提取数据并插入到另一个MSSQL中，而不进行任何其他操作。
3. 我启用了“为插入使用批处理更新”复选框。
4. 通常，它与任何错误消息一起成功运行。
5. 此错误偶尔发生，不能通过重新运行tran来复制。

下面是错误日志：

2016/09/18 08:00:36 - tb_dim_innchangelog.0 - SQL query : SELECT
2016/09/18 08:00:36 - tb_dim_innchangelog.0 -   StatID
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , ChainID
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , ChainName
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , SaleDepartID
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , AcivmntRegion
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , Category
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , Stage
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , BeginDate
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , EndDate
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , createtime
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , ChainFlag
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , LastUpdateDate
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - FROM Dim_InnChangeLog
2016/09/18 08:00:36 - load_dim_innchangelog.0 - Prepared statement : INSERT INTO Dim_InnChangeLog (StatID, ChainID, ChainName, SaleDepartID, AcivmntRegion, Category, Stage, BeginDate, EndDate, createtime, ChainFlag, LastUpdateDate) VALUES ( ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?)
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - Finished reading query, closing connection.
2016/09/18 08:00:36 - BussinessDW_Source - Connection to database closed!
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - 完成处理 (I=19147, O=0, R=0, W=19147, U=0, E=0
2016/09/18 08:00:56 - load_dim_innchangelog.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Because of an error, this step can't continue: 
2016/09/18 08:00:56 - load_dim_innchangelog.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Error performing rollback on connection
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Invalid state, the Connection object is closed.
2016/09/18 08:00:56 - load_dim_innchangelog.0 - 
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.rollback(Database.java:867)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.rollback(Database.java:845)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:332)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:118)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at java.lang.Thread.run(Thread.java:722)
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Caused by: java.sql.SQLException: Invalid state, the Connection object is closed.
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java:1699)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:2100)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.rollback(Database.java:858)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     ... 5 more
2016/09/18 08:00:56 - load_dim_innchangelog.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Unexpected batch update error committing the database connection.
2016/09/18 08:00:56 - load_dim_innchangelog.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException: 
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Error updating batch
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Invalid state, the Connection object is closed.
2016/09/18 08:00:56 - load_dim_innchangelog.0 - 
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1386)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1378)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:575)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:96)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at java.lang.Thread.run(Thread.java:722)
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Caused by: java.sql.SQLException: Invalid state, the Connection object is closed.
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.TdsCore.checkOpen(TdsCore.java:452)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.TdsCore.clearResponseQueue(TdsCore.java:727)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.JtdsStatement.initialize(JtdsStatement.java:645)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:908)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1362)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     ... 3 more
2016/09/18 08:00:56 - tf_dim_innchangelog - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : 错误被检测到!
2016/09/18 08:00:56 - load_dim_innchangelog.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Unexpected error rolling back the database connection.
2016/09/18 08:00:56 - load_dim_innchangelog.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Error performing rollback on connection
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Invalid state, the Connection object is closed.
2016/09/18 08:00:56 - load_dim_innchangelog.0 - 
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.rollback(Database.java:867)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.rollback(Database.java:845)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:610)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:96)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at java.lang.Thread.run(Thread.java:722)
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Caused by: java.sql.SQLException: Invalid state, the Connection object is closed.
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java:1699)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:2100)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.rollback(Database.java:858)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     ... 4 more
2016/09/18 08:00:56 - load_dim_innchangelog.0 - 完成处理 (I=0, O=15000, R=18000, W=15000, U=0, E=1
2016/09/18 08:00:56 - Logging - New database connection defined
2016/09/18 08:00:56 - Logging - Attempting to find connection in Named Datasources
2016/09/18 08:00:56 - Logging - Unable to find datasource in Named Datasources. Finally will try to attempt connecting using class 
2016/09/18 08:00:56 - Logging - Connected to database.
2016/09/18 08:00:56 - Logging - Auto commit off

Answer 1

这是MSSQL错误，实际上不是水壶错误。在不了解您的环境的情况下很难确定，但是如果我不得不猜测，我认为您的MSSQL配置可能已经关闭。也许使用批处理花费的时间太长了，而且连接本身也出现了超时？检查一下“保持活着”的设置？我敢肯定你把什么东西推到了门槛附近，它就要死在你身上了。

祝好运!

Answer 2

这里的实际错误是：

"Unexpected batch update error committing the database connection."

此可能是由于其中一个值上的数据格式不正确(例如试图将太大的数字插入数字字段)、.但也可能发生，没有任何明显的原因，我可以找到。

如果您只想避免错误，请尝试禁用表输出步骤上的“使用批处理更新插入”复选框，因为如果没有该操作，该步骤可能会非常好地处理相同的数据。注:这将是一项业绩权衡，因此请谨慎行事:)