Bug #27120 MySQL Table Editor accepts questionable value for 'Auto Increment' field
Submitted: 14 Mar 2007 8:42 Modified: 26 May 2009 13:03
Reporter: Heinz Schweitzer (Gold Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Administrator Severity:S3 (Non-critical)
Version:1.2.10 OS:Windows (XP, Mac OS X)
Assigned to: CPU Architecture:Any

[14 Mar 2007 8:42] Heinz Schweitzer
Description:
MySQL Table Editor accepts questionable value for 'auto increment' field

Obviously there is no out of bound check or something similar

MySQL Administrator 1.2.10
MySQL Server Information
--------------------------------------------------------------------------------
Connected to MySQL Server Instance
  Username:                root
  Hostname:                localhost
  Port:                    3306
--------------------------------------------------------------------------------
Server Information
  MySQL Version:           MySQL 5.0.27-community-nt via TCP/IP
  Network Name:            localhost
  IP:                      127.0.0.1
--------------------------------------------------------------------------------
Client Information
  Version:                 MySQL Client Version 5.1.11
  Network Name:            xxxxxxxxxxx
  IP:                      xxxxxxxxxxx
  Operating System:        Windows XP
  Hardware:                2x Intel(R) Core(TM)2 CPU         T7200  @ 2.00GHz, 2.0 GB RAM

How to repeat:
Prerequisite:

	have a table definition that uses an 'auto increment' field.

Open MySQL Administrator 1.2.10

From catalogs select a schema you can fool around with.

In the right pane activate the 'Schema Tables' tab 
select a table and say 'edit table'. 

In the table editor activate the 'Advanced Options' tab
In there set the 'Auto Increment' field to this value:

123456789012345678901234567890

and say 'Apply Changes'   see screens shot i1
and execute.

The 'Auto Increment' value will be mapped to 9223372036854775807
see screens shot i2

Close the Table Editor.
Select this table again and say 'Edit Table Data'

In the MySQL Query Browser try to insert a record.
you will get an error 1467 Failed to read auto-increment value from storage engine
see screens shot i3

????

Suggested fix:
Please check for reasonable value
[14 Mar 2007 8:42] Heinz Schweitzer
i1

Attachment: i1.jpg (image/jpeg, text), 182.70 KiB.

[14 Mar 2007 8:43] Heinz Schweitzer
i2

Attachment: i2.jpg (image/jpeg, text), 198.63 KiB.

[14 Mar 2007 8:43] Heinz Schweitzer
i3

Attachment: i3.jpg (image/jpeg, text), 120.96 KiB.

[14 Mar 2007 9:25] Sveta Smirnova
Thank you for the report.

But according to http://dev.mysql.com/doc/refman/5.0/en/create-table.html it is expected behavior:

For engines that support the AUTO_INCREMENT table option in CREATE TABLE statements, you can also use ALTER TABLE tbl_name  AUTO_INCREMENT = N to reset the AUTO_INCREMENT value. The value cannot be set lower than the maximum value currently in the column.

There is nothing about high value. You can insert value into auto_increment field manually.
[14 Mar 2007 9:42] Heinz Schweitzer
I know that  I can set the auto increment value manualy, however it is 
strange that my value '123456789012345678901234567890' is mapped to
'9223372036854775807' and finally ends up as '1' in the data base, and in between
I am not able to insert records into this table.
Some sort of notification in case of mapping values would be nice.
[14 Mar 2007 10:38] Sveta Smirnova
Thank you for the additional comment.

You are right although SHOW CREATE TABLE ouputs AUTO_INCREMENT=18446744073709551615 MySQL GUI Tools show other auto_increment value for same table.
[26 May 2009 13:03] Susanne Ebrecht
Many thanks for writing a bug report. We are on the way to implement full functionality of MySQL Administrator into MySQL Workbench. We won't add this feature request anymore.

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/