Hi all: I have various mysql tables (innodb type) where I am inserting values in auto increment columns. The value inserted into the auto increment columns is null which automatically means that the table handler inserts the next higher value in that column.
Problem is, many of these updates are transactional and sometimes these transactions are rolled back. This leaves gaps in the auto increment column (the auto increment counter does not rollback even if the transaction is rolled back). This looks unsightly and it also means I have to unnecessarily create auto increment columns with data sizes much larger than they have to be. So for example, if I get 10,000 inserts+rollbacks, my auto increment column will be 10,001 the next time a row is inserted (even if there was only 1 row to begin with). What is the best way to prevent such gaps from happening, especially in transactional tables like innodb ? Any practical suggestions off the top of anyone's head ? (Is everyone here declaring their auto increment columns as "int" or "bigint" to get around the fact that auto increment is *always* monotonically increasing, regardless of transaction rollback ?) Best regards, [EMAIL PROTECTED] __________________________________________________ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.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