mysqlclient returning wrong lastrowid when calling insert with auto_increment with negative value

[matejsp]

Describe the bug

lastrowid returns 18446744073709551615 instead of -1 or 0 (in case auto increment is not used).
https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-lastrowid.html

https://dev.mysql.com/doc/c-api/5.7/en/mysql-insert-id.html

The return value of [mysql_insert_id()](https://dev.mysql.com/doc/c-api/5.7/en/mysql-insert-id.html) is always zero unless explicitly updated under one of the following conditions:

[INSERT](https://dev.mysql.com/doc/refman/5.7/en/insert.html) statements that store a value into an AUTO_INCREMENT column. This is true whether the value is automatically generated by storing the special values NULL or 0 into the column, or is an explicit nonspecial value.

To Reproduce

Schema

CREATE TABLE `auth_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(150) NOT NULL,
  `first_name` varchar(150) NOT NULL,
  `last_name` varchar(150) NOT NULL,
  `email` varchar(254) NOT NULL,
  `password` varchar(128) NOT NULL,
  `is_staff` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `is_superuser` tinyint(1) NOT NULL,
  `last_login` datetime DEFAULT NULL,
  `date_joined` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `email` (`email`),
  KEY `last_name_and_frist_name` (`last_name`,`first_name`),
  FULLTEXT KEY `auth_user_ft_first_last_email_user` (`first_name`,`last_name`,`email`,`username`)
) ENGINE=InnoDB AUTO_INCREMENT=31039988 DEFAULT CHARSET=utf8;

Code

con = MySQLdb.Connection(...)
cur = con.cursor()
cur.execute("INSERT INTO `auth_user` (id, password, `last_login`, `is_superuser`, `username`, `first_name`, "
             "`last_name`, `email`, `is_staff`, `is_active`, `date_joined`) "
             "VALUES (-1, '', NULL, 0, '1111113', 'Some', 'User', '', 0, 1, '2022-11-02 08:29:59.021095')")
print(cur.lastrowid)

Environment

MySQL Server

• Server OS (e.g. Windows 10, Ubuntu 20.04): Docker mysql:5.7.39
• Server Version (e.g. MariaDB 10.3.16): 5.7.39

MySQL Client

• OS (e.g. Windows 10, Ubuntu 20.04): MacOSX 12.6

• Python (e.g. Homebrew Python 3.7.5): pyenv python 3.8.13

• Connector/C (e.g. Homebrew mysql-client 8.0.18): brew install mysql@5.7

Additional context

Add any other context about the problem here.

[methane]

At protocol level, there is no flag to distinguish signed / unsigned.
https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_ok_packet.html

At API level, it is defined as uint64_t. So it is unsigned.
https://dev.mysql.com/doc/c-api/8.0/en/mysql-insert-id.html

I can do nothing for you. You should avoid using negative id, or convert unsigned int to signed int manually.

[matejsp]

I thing that mysql should return value 0 for lastrowid in case auto inc is not used.

The return value of [mysql_insert_id()](https://dev.mysql.com/doc/c-api/5.7/en/mysql-insert-id.html) is always zero unless explicitly updated under one of the following conditions:

[INSERT](https://dev.mysql.com/doc/refman/8.0/en/insert.html) statements that store a value into an AUTO_INCREMENT column. This is true whether the value is automatically generated by storing the special values NULL or 0 into the column, or is an explicit nonspecial value.

I see ... so yes it works according to specs I guess. It is hard to fix this since this change propagates across Django.

[methane]

I thing that mysql should return value 0 for lastrowid in case auto inc is not used.

I don't know. This project is not developing MySQL. Please use bugs.mysql.com.