MySQL Forums
Forum List  »  Newbie

Re: autoincrement id missing when i do load data local into file
Posted by: Rick James
Date: January 03, 2011 09:52AM

AUTO_INCREMENT has certain properties. MAX(id) == COUNT(*) is not one of them.

If you need the ids to be 1,2,3,..., then you should set them yourself, not depend on AUTO_INCREMENT.

Here are some of the properties / quirks (some quite subtle)...

* Each id in the table will be unique. This is the only thing you should depend on!

* For non-overlapping INSERTs, new numbers will be MAX(id)+1.

* Unlike some competing products that grab ids in batches, MySQL grabs only one at a time. (Other products 'burn' batches of ids after a crash.)

* After a crash, the next id to be used may be MAX(id)+1, or it may be the next id after where it left off. This depends on the Engine. This can be a surprise when a user grabs an id, DELETEs the record, restarts the server, then expects that grabbed id to never be assigned again.

* For overlapping INSERTs using InnoDB, it is possible to COMMIT id=5 before COMMITting id=4. (This can be a surprise in replication.)

* DELETE of the highest id -- That id may or may not be reused. It depends on the Engine you are using and whether the server was restarted in the mean time.

* DELETE of lower ids permanently waste that id. The gap will not be refilled.

* You can explicitly INSERT a row with an unused id, thereby allowing you to fill in a gap. (Not good practice.)

* REPLACE is a DELETE + INSERT.

* TRUNCATE may or may not reset the AUTO_INCREMENT value to use next. This depends on Engine, version of MySQL, SBR/RBR, etc.

* CREATE TEMPORARY TABLE (... AUTO_INCREMENT...) SELECT ... -- This will generate ids starting at 1 and having no gaps. You can use this trick to work with groupwise-max, etc.

* The setting of AUTO_INCREMENT_INCREMENT (sic) and its friend can be useful for multi-master (not recommended). Setting that VARIABLE to 2 lets you have 1,3,5,,, on one Master and 2,4,6... on the other. This leads to the likely quirk of the inserts being in, say, this order: 1,3,2,5,7,4,9,11,6,13,15,8,... (assuming the odd machine is INSERTing twice as fast as the even one).

(Caveat: This covers most quirks, perhaps not all, of MyISAM and InnoDB, crashes, replication, etc. But it does not necessarily cover NDB Cluster.)

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.