Re: Resetting auto_increment field in an INNODB table
Hassan Shaikh [EMAIL PROTECTED] wrote: Hi, How do I reset an AUTO_INCREMENT column? My table type is InnoDB and the method mentioned in the manual is not applicable. I am using MySQL 4.0.17. If you want to start auto_increment sequence with value bigger than current counter value, you can just add dummy row and specify explicitly column value equal to needed_value-1. Then delete this row. Otherwise you should recreate the table. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Resetting auto_increment field in an INNODB table
How do I reset an AUTO_INCREMENT column? My table type is InnoDB and the method mentioned in the manual is not applicable. I am using MySQL 4.0.17. Otherwise you should recreate the table. Or, if you no longer need any of the data, simply use TRUNCATE. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Resetting auto_increment field in an INNODB table
Chris Boget [EMAIL PROTECTED] wrote: How do I reset an AUTO_INCREMENT column? My table type is InnoDB and the method mentioned in the manual is not applicable. I am using MySQL 4.0.17. Otherwise you should recreate the table. Or, if you no longer need any of the data, simply use TRUNCATE. TRUNCATE TABLE doesn't reset auto_increment value for InnoDB tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Resetting Auto_Increment
On Mon, 12 Jan 2004, Hassan Shaikh wrote: Hi, The following does not work for InnoDB tables. The manual says The next AUTO_INCREMENT value you want to set for your table (MyISAM). ALTER TABLE table_name AUTO_INCREMENT = new_value; Any suggestions for InnoDB? Insert a row with a custom value, then delete it, the next value insrted will be value+1 of the value you juse inserted. Example below: [EMAIL PROTECTED]:tmp CREATE TABLE ai (num INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARy KEY) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) [EMAIL PROTECTED]:tmp INSERT INTO ai VALUES (NULL), (NULL), (NULL); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 [EMAIL PROTECTED]:tmp SELECT * FROM ai; +-+ | num | +-+ | 1 | | 2 | | 3 | +-+ 3 rows in set (0.00 sec) [EMAIL PROTECTED]:tmp INSERT INTO ai VALUES(10); Query OK, 1 row affected (0.00 sec) [EMAIL PROTECTED]:tmp DELETE FROM ai WHERE num=10; Query OK, 1 row affected (0.01 sec) [EMAIL PROTECTED]:tmp INSERT INTO ai VALUES(NULL); Query OK, 1 row affected (0.00 sec) [EMAIL PROTECTED]:tmp SELECT * FROM ai; +-+ | num | +-+ | 1 | | 2 | | 3 | | 11 | +-+ 4 rows in set (0.00 sec) [EMAIL PROTECTED]:tmp \t -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Resetting Auto_Increment
You got to be kidding! I am sure there's a better solution. Hassan - Original Message - From: Tobias Asplund [EMAIL PROTECTED] To: Hassan Shaikh [EMAIL PROTECTED] Cc: Sent: Monday, January 12, 2004 9:43 PM Subject: Re: Resetting Auto_Increment On Mon, 12 Jan 2004, Hassan Shaikh wrote: Hi, The following does not work for InnoDB tables. The manual says The next AUTO_INCREMENT value you want to set for your table (MyISAM). ALTER TABLE table_name AUTO_INCREMENT = new_value; Any suggestions for InnoDB? Insert a row with a custom value, then delete it, the next value insrted will be value+1 of the value you juse inserted. Example below: [EMAIL PROTECTED]:tmp CREATE TABLE ai (num INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARy KEY) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) [EMAIL PROTECTED]:tmp INSERT INTO ai VALUES (NULL), (NULL), (NULL); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 [EMAIL PROTECTED]:tmp SELECT * FROM ai; +-+ | num | +-+ | 1 | | 2 | | 3 | +-+ 3 rows in set (0.00 sec) [EMAIL PROTECTED]:tmp INSERT INTO ai VALUES(10); Query OK, 1 row affected (0.00 sec) [EMAIL PROTECTED]:tmp DELETE FROM ai WHERE num=10; Query OK, 1 row affected (0.01 sec) [EMAIL PROTECTED]:tmp INSERT INTO ai VALUES(NULL); Query OK, 1 row affected (0.00 sec) [EMAIL PROTECTED]:tmp SELECT * FROM ai; +-+ | num | +-+ | 1 | | 2 | | 3 | | 11 | +-+ 4 rows in set (0.00 sec) [EMAIL PROTECTED]:tmp \t --- avast! Antivirus: Inbound message clean. Virus Database (VPS): 1/8/2004 Tested on: 1/12/2004 11:26:18 PM avast! is copyright (c) 2000-2003 ALWIL Software. http://www.avast.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RESETTING AUTO_INCREMENT
Or drop and recreate the table (that's actually what truncate is doing) /rudy -Original Message- From: Miguel Perez [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 20:08 To: [EMAIL PROTECTED] Subject: RESETTING AUTO_INCREMENT Hi everyone: Does anyone know how to reset the auto_increment value of certain table. Any ideas or sugestions Greetings in advance _ Únete al mayor servicio mundial de correo electrónico: http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RESETTING AUTO_INCREMENT
Miguel: Wednesday, July 9, 2003, 3:07:33 PM, você escreveu: ---[inicio]-- MP Hi everyone: MP Does anyone know how to reset the auto_increment value of certain table. MP Any ideas or sugestions MP Greetings in advance MP _ MP Únete al mayor servicio mundial de correo electrónico: MP http://www.hotmail.com ---[cortar]-- alter table TABLE_NAME auto_increment = 1; mysql,innodb,query - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 296-2311 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RESETTING AUTO_INCREMENT
Why are you looking to reset it? If you mean resetting when there is no data in a table, a truncate table should start the auto_increment over again. If you are referring to recovering some auto_increment values that were previously used by no rows now use them, it is better to avoid this. That way you can prevent some potential conflicts. You can reset the auto_increment with ALTER TABLE tablename AUTO_INCREMENT = 1; but know what you are doing when you do. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Miguel Perez [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 12:08 PM To: [EMAIL PROTECTED] Subject: RESETTING AUTO_INCREMENT Hi everyone: Does anyone know how to reset the auto_increment value of certain table. Any ideas or sugestions Greetings in advance _ Únete al mayor servicio mundial de correo electrónico: http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RESETTING AUTO_INCREMENT
hi mike you suggested the following If you are referring to recovering some auto_increment values that were previously used by no rows now use them, it is better to avoid this. how can you prevent this? best regards benny
