-1

When I was using Sqoop to write data into TiDB in batches, I ran into the following error:

java.sql.BatchUpdateExecption:statement count 5001 exceeds the transaction limitation

I configured the --batch option already, but this error still occurred. How to resolve this error?

Lilian Lee
  • 190
  • 1
  • 12

1 Answers1

0

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.

Caitin Chen
  • 151
  • 1
  • 10