Leonardo, >Hello there. > >I just found something that I couldn't exactly call a bug. Certainly it >is a problem. > >If I have a InnoDB table with an auto_increment field and try to insert >a row in this table, it will increment the field in question normally. >The problem happens when the query used has a problem, such as an ID >that is already in the table, for instance. Then MySQL will raise the >internal auto_incremented field by 1, even if the insert didn't happen.
this is the intended behavior, because InnoDB assigns new values to the auto-inc column past the transaction mechanism. A rollback does not reset the value to what it was originally, and cannot do so, because some other transaction may have incremented the counter meanwhile. >Also, a truncate table or a delete from table won't reset the internal >pointer to 0 again. Only a drop/create sequence will do it. Not that >this should be a trouble at all, but I thought it would be nice if at >least a truncate did so. TRUNCATE in MySQL-3.23 is implemented as a DELETE FROM ... In 4.0 it is a DROP + CREATE and will reset the auto-inc counter. >The version we're using is mysql-3.23-44-max. Never had time to test the >newer versions and see if this was fixed. If it was, please answer me >and I'll do an upgrade as soon as possible. If you need to reste the counter, the best bet is to use DROP + CREATE for the table. >Thanks, > >-- >Leonardo Dias >Catho Online >WebDeveloper >http://www.catho.com.br/ Regards, Heikki http://www.innodb.com -- Order commercial MySQL/InnoDB support at https://order.mysql.com/ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
