ALTER TABLE yourtable AUTO_INCREMENT=87123458
After that, the auto_increment id column resumes working as expected, until the next time. Is that right?
I can't imagine why that would happen. I have some suggestions: (You may have done some of these already.)
First, run a CHECK TABLE on your table. Assuming that indicated no problems, the next time this happens, start by running CHECK TABLE again. Then, do a
SHOW TABLE STATUS LIKE 'yourtable'
to verify that the next auto_increment value (87,123,457 in my example) is the one that produced the error, then run a
SELECT * FROM yourtable WHERE id=87123457
(use the value that produced the error) to verify that there is no row with that id. Then try manually inserting a row to verify you get the same error. Assuming you do, try manually inserting a row with the id explicitly set to 87123457 to see if the problem is with the value as opposed to the auto_increment.
Have you tried resetting the auto_increment id by manually inserting a larger number?
INSERT INTO yourtable (id) values (87123458);
Under normal circumstances, this would cause the auto_increment counter to change to the next value. It would also be a lot quicker than an ALTER TABLE statement.
One more thing. When you do a multiple row INSERT statement, LAST_INSERT_ID() returns the auto_increment id of the first row inserted. (See <http://dev.mysql.com/doc/mysql/en/Information_functions.html>) So, if you've just done a multiple row insert, LAST_INSERT_ID()+1 should already exist as an ID in the table.
Michael
Kevin Brock wrote:
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]
