last_insert_id gives 0 when 0 is explicitly specified for an auto_increment primary key in an insertion

[ko41]

As is described in this example,

https://github.com/dolthub/go-mysql-server/blob/729f39b6b665932cb0da516d5013f0d938c860d0/enginetest/queries/insert_queries.go#L559-L567

When 0 is specified explicitly,

• a new row with id 4 is created,
• but we have 0 as InsertID.

This is not consistent with MySQL's behaviour, which can be replicated as follows:

> docker run -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=test -p 3306:3306 -d mysql:8
> mysql -h 127.0.0.1 -P 3306 -u root -p'root' test

MySQL [test]> CREATE TABLE `auto_increment_tbl` (   `pk` bigint NOT NULL AUTO_INCREMENT,   `c0` bigint,   PRIMARY KEY (`pk`) );
Query OK, 0 rows affected (0.016 sec)

MySQL [test]> INSERT INTO auto_increment_tbl values (0, 44);
Query OK, 1 row affected (0.031 sec)

MySQL [test]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.001 sec)

MySQL [test]> SELECT * FROM auto_increment_tbl ORDER BY pk;
+----+------+
| pk | c0   |
+----+------+
|  1 |   44 |
+----+------+
1 row in set (0.001 sec)

[fulghum]

Hi @ko41, thanks for taking the time to report this discrepancy with MySQL's behavior. I've moved this issue over to the dolt repository so that it'll get more attention from our team (even though the fix will likely involve changes in go-mysql-server).

I am able to reproduce the behavior you're reporting with last_insert_id() – if 0 is provided for the auto_increment column value, then MySQL will replace that with the next value in the auto_increment sequence. Dolt and GMS are doing that part correctly, but they are still reporting 0 when last_insert_id() is called, which is the incorrect behavior here.

Let me know if I misunderstood anything in there. We'll see if we can get someone to dig into this fix.

[fulghum]

Hi @ko41, thanks again for reporting this issue. 🙏 I've fixed the bug in the go-mysql-server package and I've merged the new dependency version into dolt, too. This will go out in the next Dolt release, next week. Just let us know if you need it released in Dolt faster and we'll be happy to kick off a release for you.

Let us know if you find any other issues!

[ko41]

Thank you for looking into this issue @fulghum

After digging around a bit more, I noticed that in MySQL,

• the last_insert_id value in the OK Packet in response to an insertion
• and the result of select last_insert_id() after an insertion

are not identical when a non-zero primary key is specified (which is not emulated by Dolt's latest go-mysql-server).

> docker run -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=test -p 3306:3306 -d mysql:8

package main

import (
	"database/sql"
	"fmt"

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

func main() {
	config := mysql.NewConfig()
	config.Net = "tcp"
	config.Addr = ":3306"
	config.DBName = "test"
	config.User = "root"
	config.Passwd = "root"

	db, err := sql.Open("mysql", config.FormatDSN())
	if err != nil {
		panic(err)
	}

	if _, err := db.Exec(`DROP TABLE IF EXISTS auto_increment_tbl`); err != nil {
		panic(err)
	}

	if _, err := db.Exec(`CREATE TABLE auto_increment_tbl (pk bigint NOT NULL AUTO_INCREMENT, c0 bigint, PRIMARY KEY (pk))`); err != nil {
		panic(err)
	}

	result, err := db.Exec(`INSERT INTO auto_increment_tbl VALUES (10, 44)`)
	if err != nil {
		panic(err)
	}

	lastInsertIdFromOkPacket, err := result.LastInsertId()
	if err != nil {
		panic(err)
	}

	// the ok packet says that last_insert_id is 10
	fmt.Printf("last_insert_id from ok packet: %d\n", lastInsertIdFromOkPacket)

	var lastInsertIdFromQuery int
	if err := db.QueryRow(`SELECT last_insert_id()`).Scan(&lastInsertIdFromQuery); err != nil {
		panic(err)
	}

	// while the query says that last_insert_id is 0
	fmt.Printf("last_insert_id from query: %d\n", lastInsertIdFromQuery)
}

go-mysql-server used to behave the same as MySQL (confirmed with v0.18.1) and my guess is that dolthub/go-mysql-server#2616 has changed that behaviour.

Could you kindly look into this? Thank you 🙏