Error: `java.sql.BatchUpdateExecption:statement count 5001 exceeds the transaction limitation` while using Sqoop to write data into TiDB in `batches`

FAQ
1

In Sqoop, --batch means committing 100 statements in each batch, but by default each statement contains 100 SQL statements. So, 100 * 100 = 10000 SQL statements, which exceeds 5000, the maximum number of statements allowed in a single TiDB transaction.

Two solutions:

  • Add the -Dsqoop.export.records.per.statement=10 option as follows:

    sqoop export 
    -Dsqoop.export.records.per.statement=10 
    --connect jdbc:mysql://mysql.example.com/sqoop 
    --username sqoop ${user} 
    --password ${passwd} 
    --table ${tab_name} 
    --export-dir ${dir} 
    --batch

  • You can also increase the limited number of statements in a single TiDB transaction, but this will consume more memory.