Something about your description doesn't quite fit, however. You say that you are "nowhere near the limit", but you say that resetting the "auto_increment starting point" fixes the problem. Those seem contradictory to me. Perhaps if you explained further, someone might be able to figure out what is happening. To that end, here are some questions:
What kind of table is it (MyISAM, InnoDB,...)?
Have you tried
SHOW TABLE STATUS LIKE 'yourtable'
when this happens? What is the next auto_increment value (in the Auto_increment column) according to the output?
Have you looked at the data in the table? Are the values in the auto_increment column consecutive, as expected? What's the max value in that column?
Are you inserting 87 million rows in an empty or existing table? If the latter, how many rows are already there? In either case, is this a large bulk insert that fails before it's done, or are you saying that in normal operations it fails every 87 million or so inserts?
How are you resetting the AUTO_INCREMENT starting point, exactly? How do you choose the starting value? What is the value you choose? Do you verify it worked with SHOW TABLE STATUS? Note that if you try to set the next AUTO_INCREMENT to a value less than the largest value currently in the column, the ALTER silently fails.
Normally, auto_increment columns do not reuse values, so if you insert then delete a row, that number is still gone. Are you doing just inserts, or are there deletions mixed in? If, for example, you insert 80 million rows a day and delete rows that were inserted yesterday, you'll run out of INT keys in under a month, even though there are only 80 million rows in the table.
Finally, just to cover all the bases, that really is 87 million inserts, not 8.7 million, right? I only ask because a MEDIUMINT column runs out a little past 8.3 million.
Michael
Kevin Brock wrote:
On Jun 19, 2004, at 6:03 AM, Terry Riley wrote:
Just a suggestion, Kevin, but how about changing from INT to BIGINT?
I thought of trying that, but since we're nowhere near the limit even for an INT I think changing to BIGINT is premature. I want to find out a bit more about what's happening first. The fact that it stops at such a low number makes me think it's not related to the size of the field.
I saw a reference in a post (to a different mailing list) to a tables auto_increment limit, as though that was something different from the max value of an INT, but I haven't run across it in any official documentation.
Kevin
----------Original Message---------
We have a table with a primary index which is INT NOT NULL AUTO_INCREMENT.
After inserting ~87,000,000 entries, we started seeing error 1062, ER_DUP_ENTRY.
We can get going again after doing an ALTER TABLE to reset the auto_increment starting point, but this takes about an hour...
I've seen a couple of places where how to get around this problem was discussed, but nobody seems to discuss *why* this occurs in the first place.
Does anyone know why MySQL would start failing to increment an auto_increment index properly when it's nowhere near the upper limit? Does anyone know a way to get things functioning again without a couple of hours downtime?
Hoping there's an answer out there somewhere...
Kevin Brock [EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
