Kettle5.4 problem record

One 、Kettle Of “Use Result Streaming Cursor” Options

ETL Tools Kettle in , Use mysql When connecting , There is one “Use Result Streaming Cursor” The option to , amount to ((com.mysql.jdbc.Statement)stat).enableStreamingResults(), In this case , Take only one piece of data at a time , Efficiency is very low .

however , Kettle In the configuration mysql When connecting , Advanced options will useCursorFetch=true and fetchsize Set up , According to the relationship of priority , Will be used preferentially RowDataCursor.

new edition mysql Solutions for

mysql jdbc Drive settings fetchsize There is useCursorFetch=true Talent

jdbc:mysql://127.0.0.1/：3306/test?useCursorFetch=true mysql edition , That is, the driver versions are 5 above , You can use setFetchSize The method . Tested ,debug After that . If there is no such parameter , only setFetchSize(Integer.MIN_VALUE);  It works , But every time rs.next() Take one .oracle General default settings 10 strip .

this.statement = connection.prepareStatement(parsedQuery,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

this.statement.setFetchSize(10);

in addition , At the same time useCursorFetch=true and ((com.mysql.jdbc.Statement)stat).enableStreamingResults() When setting , useCursorFetch Have a higher priority , This is the time , mysql Of jdbc The driver will use RowDataCursor.

Two 、 Table output Prepared statement contains too many placeholders

MySQL Of prepared statement Can only support at most 65535 Placeholder variables , But the reason is unknown . When inserting data in batch ,MySQL Of JDBC The driver will merge a single insert statement into a multi value insert statement ; And the scale of the data we import , A record has many columns , When importing data, it is 1000 When recording ,SQL The number of placeholders in is just less than 65535; When importing 2000 When recording ,SQL The number of midpoint markers has exceeded 65535, An error must be reported when the statement is executed .

performance parameter ：

useServerPrepStmts=false

rewriteBatchedStatements=true

useCompression=true

code ：

characterSetResults=utf8

characterEncoding=utf8

defaultFetchSize=500

useCursorFetch=true

Date format processing

zeroDateTimeBehavior=convertToNull

3、 ... and 、kettle （ Table input ） Inquire about MySQL data Encounters an error ：

Unable to get timestamp from resultset at index 125

In the discovery data timestamp Type value contain 0000-00-00 or 0000-00-00 00:00:00 It's a mistake to make when you're in the game

jdbc driver Deal with problems caused by date types , Solve it in the following way ：

1、 Use date_format Function formatting

2、 stay jdbc URL Link add parameters （zeroDateTimeBehavior=convertToNull）：jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull

3、 If it is hibernate The following parameters can be configured ：

<property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>

pan call ktr The way

An error is reported after running for a period of time ：

2022/03/01 07:02:31 - Table input .0 - Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)

2022/03/01 07:02:31 - Table input .0 - IO error : Connection reset

resolvent , stay sh In the document java Add parameters to the command line ：-Djava.security.egd=file:/dev/../dev/urandom