On Jun 19, 2004, at 10:37 AM, Michael Stassen wrote:
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.
To me as well, that's why I posted.
What kind of table is it (MyISAM, InnoDB,...)?
MyISAM.
Have you tried
SHOW TABLE STATUS LIKE 'yourtable'
Nope. I'm sure we'll be able to reproduce the problem shortly though, and I'll try it then.
when this happens? What is the next auto_increment value (in the Auto_increment column) according to the output?
Well, since I didn't try it I don't know for sure :-) When I check LAST_INSERT_ID, it's correct. I.e., the ID that fails would be the next ID after LAST_INSERT_ID.
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?
The max value is one less than the value that failed, the number of rows is correct, and the values in the column are consecutive.
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?
The table has been accumulating data for about a month, starting with an empty table. Each insert is on the order of 10-20,000, inserting using INSERT INTO doing multiple rows at a time.
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.
I did an ALTER TABLE to set AUTO_INCREMENT to one greater than the value that failed. We were able to insert more data after that, and the data inserted had the expected values for the ID column...
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.
We hadn't done any deletes on the table. The first ID value is one.
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.
Right. 87 million rows in the table.
Kevin
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
