MySQL Bugs: #98211: Auto increment value didn't reset correctly.

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

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.

test case

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

patch file

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

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.

Update the latest version MySQL 8.0.19,   still report error.

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.

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.

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".

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!

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)

Hi All,

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

Verified as reported, with a changed category and severity.

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.

Thank you, Daniel.