Bug #98211 Auto increment value didn't reset correctly.
Submitted: 13 Jan 2020 18:47 Modified: 18 Jun 2020 17:06
Reporter: Zhao Jianwei Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[13 Jan 2020 18:47] Zhao Jianwei
Description:
Hi, guys

When I did ALTER TABLE statement, it report error:

mysqltest: At line 18: Query 'alter table sb drop primary key, add column c1 int not null auto_increment, add key(c1)' failed.
ERROR 1264 (22003): Out of range value for column 'columns' at row 167

It happened occasionally, not stable. 

Look through the codes:

When ALTER TABLE command executed to copy data from old table into new tmp table, 
the function copy_data_between_tables() is called, it allocated a group of IDs 
by to->file->ha_start_bulk_insert(from->file->stats.records); after copy data completed,
the IDs were not consumed totally´╝îthen the DD modification, INSERT on mysql.columns table will inherit the IDs, So it report error.

since of the stats.records,  so the case is not stable.

How to repeat:
see the test case file.

Suggested fix:
after ha_end_bulk_insert(),  reset the IDs which were not consumed when ha_release_auto_increment(), the the DD modification will regenerate new IDs.
[13 Jan 2020 18:48] Zhao Jianwei
test case

Attachment: t.test (application/octet-stream, text), 577 bytes.

[13 Jan 2020 18:49] Zhao Jianwei
patch file

Attachment: x.diff (application/octet-stream, text), 1.23 KiB.

[13 Jan 2020 19:57] MySQL Verification Team
Thank you for the bug report. Please when reporting bugs test first the latest version released, please check version 8.0.19 and, if the issue continues print here the result and change the field version.
[14 Jan 2020 7:25] Zhao Jianwei
Update the latest version MySQL 8.0.19,   still report error.
[14 Jan 2020 13:22] MySQL Verification Team
Hello Mr. Jianwei,

Thank you for your bug report.

However, this is not a bug.  Your test was simply wrong.

Namely, if you name your new auto_increment column as a PRIMARY index, the tests works like a charm.

That is how MySQL is designed. 

Not a bug.
[14 Jan 2020 15:10] Zhao Jianwei
Hi, Sinisa Milivojevic

As you said,  it will work fine if I named the new column as PRIMARY key.

But,  firstly from the design of MySQL,  that didn't require the auto increment column must be as part of PRIMARY key. it can be normal index. so if it's not a bug, it should report syntax error.

Secondly, the DDL statement report error just when dictionary_client()->store(dd::Column),
it is not reasonable way to report error until modified data dictionary.

Lastly, from the codes:
    -- get_auto_increment(..., &nr, ...) ;  "nr" was returned 0;
then 
    -- nr = compute_next_insert_id(nr - 1, variables);  "nr - 1" is overflowed,

I don't think the overflow is under the design.
[14 Jan 2020 15:19] MySQL Verification Team
Hi Mr. Jianwei,

You do not seem to grasp the basic design of MySQL.

First of all, without primary key, I did not get any error about column named `columns`. 

Second, yes, it is a basic request and design premise for MySQL RDBMS that auto_increment column is a PRIMARY index or part of it. Again, I refer you to our Reference Manual. For your information, I was the one who designed and coded MySQL that way, 23 years ago.

Regarding your query of when should our data dictionary return the error is totally irrelevant.

This bug is closed as "Not a bug".
[14 Jan 2020 18:03] Valeriy Kravchuk
Dear Sinisa,

While you definitely know better the original design intentions of MySQL creators 9as one of them), the fine MySQL manual does NOT require auto_increment column to be a part of the primary key, it must be indexes. let me quote https://dev.mysql.com/doc/refman/8.0/en/create-table.html

"There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value."

So, either your argument from the last comment is invalid or your fine MySQL manual is wrong. Pick up the interpretation you prefer, but just one of them!
[14 Jan 2020 19:17] Federico Razzoli
Och, I've found a terrible bug in 8.0!

mysql> CREATE TABLE t (a INT PRIMARY KEY, b INT AUTO_INCREMENT, INDEX (b));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t (a) VALUES (1), (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
+---+---+
2 rows in set (0.00 sec)
[15 Jan 2020 13:49] MySQL Verification Team
Hi All,

I am verifying this bug based on the last test case.

Verified as reported, with a changed category and severity.
[18 Jun 2020 17:06] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.21 release, and here's the proposed changelog entry from the documentation team:

An ALTER TABLE operation that copied data from one table to another
returned an Out of range value for column error. The counter that tracks
the number of AUTO_INCREMENT rows required for a multi-row insert
operation was not always set back to zero after a bulk insert operation.
[19 Jun 2020 12:06] MySQL Verification Team
Thank you, Daniel.