mysql_insert_id() incompatible between tidb and mysql

[mzhang77]

1. Minimal reproduce step (Required)

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func executeSQL(db *sql.DB, sql string) sql.Result {

	result, err := db.Exec(sql)
	if err != nil {
		log.Fatal("Error executing query:", err)
	}
	return result
}

func insertRow(db *sql.DB, a, b int) int {

	insertQuery := "INSERT INTO tb (a, b) VALUES (?, ?) ON DUPLICATE KEY UPDATE b = VALUES(b)"
	result, err := db.Exec(insertQuery, a, b)
	if err != nil {
		log.Fatal("Error executing insert query:", err)
	}

	// Get the last inserted ID
	lastInsertId, err := result.LastInsertId()
	if err != nil {
		log.Fatalf("Error fetching LastInsertId: %v", err)
	}

	return int(lastInsertId)
}

func main() {
	// Define the MySQL connection string
	dsn := "root@tcp(127.0.0.1:4000)/test"
    //dsn := "root@tcp(127.0.0.1:3306)/test"

	// Open a connection to the database
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatalf("Error opening database connection: %v", err)
	}
	defer db.Close()

	// Test the connection
	err = db.Ping()
	if err != nil {
		log.Fatalf("Error connecting to the database: %v", err)
	}

	_ = executeSQL(db, "drop table if exists tb")

	_ = executeSQL(db, "create table tb(pk int primary key auto_increment, a int, b int, unique(a))")

	lastInsertId := insertRow(db, 1, 1)
	fmt.Printf("Last inserted ID: %d\n", lastInsertId)

	lastInsertId = insertRow(db, 2, 2)
	fmt.Printf("Last inserted ID: %d\n", lastInsertId)

	lastInsertId = insertRow(db, 1, 2)
	fmt.Printf("Last inserted ID: %d\n", lastInsertId)

}


// tiup playground v7.5.1:
// Last inserted ID: 1
// Last inserted ID: 2
// Last inserted ID: 0

// MySQL v8.0.23:
// Last inserted ID: 1
// Last inserted ID: 2
// Last inserted ID: 1

2. What did you expect to see? (Required)

The code should return same result from mysql and tidb

3. What did you see instead (Required)

// tiup playground v7.5.1:
// Last inserted ID: 1
// Last inserted ID: 2
// Last inserted ID: 0

// MySQL v8.0.23:
// Last inserted ID: 1
// Last inserted ID: 2
// Last inserted ID: 1

4. What is your TiDB version? (Required)

mysql> select @@version;
+--------------------+
| @@version          |
+--------------------+
| 8.0.11-TiDB-v7.5.1 |
+--------------------+
1 row in set (0.01 sec)

[mzhang77]

Per mysql doc 5.4.38 mysql_insert_id()

When called after an INSERT ... ON DUPLICATE KEY UPDATE statement:

• If there is an AUTO_INCREMENT column in the table and there were some explicit successfully inserted values or some updated values, return the last of the inserted or updated values.

The 3rd insert has a duplicate on column a so column b is updated. And LastInsertId() should return pk of the updated row (1)

[yibin87]

Issue from business customer

[yibin87]

/label customer

[ti-chi-bot]

@yibin87: The label(s) customer cannot be applied. These labels are supported: fuzz/sqlancer, challenge-program, compatibility-breaker, first-time-contributor, contribution, good first issue, correctness, duplicate, proposal, security, ok-to-test, needs-ok-to-test, needs-more-info, needs-cherry-pick-release-5.4, needs-cherry-pick-release-6.1, needs-cherry-pick-release-6.5, needs-cherry-pick-release-7.1, needs-cherry-pick-release-7.5, needs-cherry-pick-release-8.1, needs-cherry-pick-release-8.3, affects-5.4, affects-6.1, affects-6.5, affects-7.1, affects-7.5, affects-8.1, affects-8.3, may-affects-5.4, may-affects-6.1, may-affects-6.5, may-affects-7.1, may-affects-7.5, may-affects-8.1.

In response to this:

/label customer

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[yibin87]

/report customer

[sylar8023baby]

The result is different if use "select last_insert_id()"：
TiDB v6.5.6

MySQL 8.0.27

[mzhang77]

Yes, the issue is related with mysql_insert_id(), which is in mysql C API, also used by mysql drivers of other languages, like Golang, python, etc. But this is different than SQL function last_insert_id(). Details are here

[tiancaiamao]

Currently, the bug behaviour is caused by the fact that we treat mysql_insert_id() equivalent to LAST_INSERT_ID().

There are (at least) two difference between them:

• LAST_INSERT_ID() perserve the last value of a success operation in the server side, while mysql_insert_id() returns 0 if one statement do not get the insert value. (tidb did it correctly)
• If an AUTO_INCREMENT column is set explicitly, LAST_INSERT_ID() will not record that value, while mysql_insert_id() returns it.

For example,

create table tb(pk int primary key auto_increment, a int, b int, unique(a))
insert into tb values (6, 7, 8)   ;;  mysql_insert_id() = 6
select last_insert_id() ;;      LAST_INSERT_ID() = 0