MySQL Bugs: #6115: INSERT records to table with auto increment field fails regularly after 65600

Bug #6115	INSERT records to table with auto increment field fails regularly after 65600
Submitted:	15 Oct 2004 14:05	Modified:	21 Oct 2004 18:12
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	1.0.0 beta	OS:	Windows (Windows XP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
I am trying to load a table with an auto increment field with 100,000 rows of test data. When the auto increment field reaches around 65,000 to 66,000 the update hangs.

I create the MySqlConnection, MySqlCommand, and MySqlDataAdapter objects in code, and use the MySqlCommandBuilder to generate the INSERT command. My program is designed to iterate 100,000 times. On each iteration, a new row is created (DataTable.NewRow), the required fields populated, and the row is added to the DataTable. I then call MySqlDataAdapter.Update to send the row to the MySQL database, after which I clear the row from the DataTable and the iteration repeats.

If using a table with an auto increment field, the program will hang on the MySqlDataAdapter.Update call somewhere around 65,000 to 66,000. If I restart the update, it will proceed for about another 50 to 300 records before stopping again.

If I delete all the rows from the table, but do not adjust the auto increment value (so it is still counting from ~66,000), the error occurs. If I adjust it down to 1 (or any value less than ~66,000) then the update loop will run until it gets to ~66,000 at which point it will again stop.

I have tested this with two different tables using auto increment fields, and the both display the same behavior. I have also tested my test project using a table with no auto increment field, and the problem did not occur.

I also tested the same code switching out the Connecter/.Net data provider for the Connector/ODBC provider, and the problem did not occur using the ODBC provider.

Using:
.Net 1.1 (VS 2003)
XP SP2

Feel free to contact me for any additional information you may need:
ray.greenley@chlsystems.com

How to repeat:
- Create a table in MySQL with an auto increment field
- Create a program which adds records to the table through MySqlDataAdapter.Update
- Add records to get the auto increment value to count past ~66,000

RESULTS: Once the auto increment counter gets to ~66,000, the program will hang on the MySqlDataAdapter.Update call, and no more records will be added.

EXPECTED: Program execution should not hang on the MySqlDataAdapter.Update call when the auto increment field value is at ~66,000.

Suggested fix:
N/A

Could you please provide the desc table_name output command for
to see what type of int are you using ? Anyway please see:

http://dev.mysql.com/doc/mysql/en/Numeric_types.html

if you aren't having storage capacity issue.

I checked this before submitting the bug.  The table is using the type unsigned integer ( int(10) unsigned in the desc table_name).  If this was the problem, then I would not be able to continue adding small groups of records when I would re-run my program to add the records.

The range at which it stops certainly seems to suggest an overflow of an unsigned 2 byte integer, but if so it is internal to Connector/Net, and not my table.

For completeness, and since you asked:

mysql> desc files;
+--------------------+-----------------------+------+-----+---------------------+----------------+
| Field              | Type                  | Null | Key | Default             | Extra          |
+--------------------+-----------------------+------+-----+---------------------+----------------+
| FileID             | int(10) unsigned      |      | PRI | NULL                | auto_increment |
| FileName           | varchar(100)          |      |     |                     |                |
| FileExtension      | varchar(10)           | YES  |     | NULL                |                |
| FileStatus         | varchar(20)           |      |     |                     |                |
| FileDateCreated    | datetime              |      |     | 0000-00-00 00:00:00 |                |
| FileDateCheckedOut | datetime              | YES  |     | NULL                |                |
| FileDateImported   | datetime              |      |     | 0000-00-00 00:00:00 |                |
| FileCheckedOutByID | int(10) unsigned      | YES  |     | NULL                |                |
| FileLatestVersion  | smallint(5) unsigned  |      |     | 0                   |                |
| IconID             | smallint(5) unsigned  | YES  | MUL | NULL                |                |
| FileCreatorID      | mediumint(8) unsigned |      | MUL | 0                   |                |
| FileDescription    | text                  | YES  |     | NULL                |                |
| FileCustomer       | varchar(100)          |      |     |                     |                |
| FileJobNumber      | mediumint(8) unsigned | YES  |     | NULL                |                |
| FileDwgNumber      | varchar(30)           | YES  |     | NULL                |                |
| FileDrawnBy        | varchar(50)           | YES  |     | NULL                |                |
| FileEngineer       | varchar(50)           | YES  |     | NULL                |                |
| FileApprovedBy     | varchar(50)           | YES  |     | NULL                |                |
| FileSheetNumber    | varchar(20)           | YES  |     | NULL                |                |
| FileTitleBlockDate | date                  | YES  |     | NULL                |                |
| FileDwgRevNumber   | smallint(5) unsigned  | YES  |     | NULL                |                |
| FileContactPerson  | varchar(50)           | YES  |     | NULL                |                |
| FileNSN            | varchar(25)           | YES  |     | NULL                |                |
| FileLocation       | varchar(45)           | YES  |     | NULL                |                |
| FileMill           | varchar(45)           | YES  |     | NULL                |                |
| FileLine           | varchar(45)           | YES  |     | NULL                |                |
| FileEquiptment     | varchar(45)           | YES  |     | NULL                |                |
| FileType           | varchar(30)           |      |     |                     |                |
| FileJobTitle       | varchar(45)           | YES  |     | NULL                |                |
+--------------------+-----------------------+------+-----+---------------------+----------------+
29 rows in set (0.00 sec)

Ok..thank you..I just wanted to be sure that the issue wasn't related
to the storage capacity of the column type.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Can you reduce this down to the smallest table that shows the problem and send me a simple test program  (with source) that will show it.  Also, what version of MySQL are you running?

I have attached a .zip containing a sample program (a console app) and a .sql for the table.  It's a very simple table, one integer column that auto-increments.  When I run the project, it iterates through creating rows in the table in batches of 5000.  When it's trying to update the batch for rows 65000 to 70000, it hangs.  If you don't want to wait that long, you can set the autoincrement number to start from up higher (just realize that the counter in the console will still count from 1).

I'm using MySQL 4.1.5-gamma-nt

Sorry, I spoke too soon.  When I try to attach the file, I get a 'The page cannot be displayed' browser error.  I hope you don't mind, but I'm going to send it to your listed address reggie@mysql.com.

Did you get the sample application I sent?  Have you been able to reproduce the issue?  Please let me know what the status of this issue is, and if it has changed.

Thanks,
 Ray G.