TiSpark execution error on insert into select

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiSpark执行insert into select报错

| username: Zealot

Executing SQL: insert into db2.tb2 select * from db1.tb1
Error:
org.tikv.common.exception.TiBatchWriteException: currently user provided auto increment value is only supported in update mode!
please set parameter replace to true!

It looks like it is asking me to set some parameter, but I searched the documentation and couldn’t find it.

| username: Jellybean | Original post link

The error here means that there is already data with the same primary key in the db2.tb2 table, and executing insert will result in an error.

You need to assess whether the business requires using db1.tb1 to overwrite the data in the db2.tb2 table. If so, set the Spark parameter to enable the automatic conversion of insert to replace by setting “please set parameter replace to true”.

Actually, you can try using the replace into select statement.

| username: dba远航 | Original post link

Auto-increment key value conflict caused

| username: ShawnYan | Original post link

Or specify the fields and exclude the auto-increment field.

| username: Zealot | Original post link

Uh, TiSpark does not support replace into.

| username: Jellybean | Original post link

Well, then honestly adjust the configuration for running tasks in Spark, and enable the feature to automatically convert insert to replace in Spark.
Alternatively, adjust the table structure to handle the auto-increment field.

| username: Zealot | Original post link

I looked at the source code, and it seems to be the parameter spark.tispark.replace. I’ll have to give it a try.

| username: 随缘天空 | Original post link

Try changing the SQL to the following: insert into db2.tb2 select * from db1.tb1 replace true

| username: Zealot | Original post link

No need, it is indeed the spark.tispark.replace parameter. Just add spark.tispark.replace true and it will work.

| username: 随缘天空 | Original post link

Yes, solving the problem is enough. It’s best to pin your solution so that others can quickly find it if they encounter similar issues in the future.

| username: zxgaa | Original post link

You can use replace.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.