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

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 
        --connect jdbc:mysql:// 
        --username sqoop ${user} 
        --password ${passwd} 
        --table ${tab_name} 
        --export-dir ${dir} 
  • You can also increase the limited number of statements in a single TiDB transaction, but this will consume more memory.