Re: Resetting the auto_increment to start from 1
Paul, >>It's a rather old book, which deals with MySQL 3.23, and not with MySQL >>4.x. >Actually, he's talking about MySQL Cookbook (p549). Which is a new book, >which is why I said *may* reset the counter rather than *will* >reset the counter as was true in older versions of MySQL. Oops. Sorry for calling the brand new MySQL Cookbook an "old book"! :/ >> TRUNCATE TABLE tbl >>This will in fact do a DROP/CREATE, thus resetting the AUTO_INCREMENT >>counter etc. >Not always! >Try this script: >CREATE TABLE t (i INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) >TYPE = INNODB; >INSERT INTO t SET i = NULL; >INSERT INTO t SET i = NULL; >INSERT INTO t SET i = NULL; >SELECT * FROM t; >TRUNCATE TABLE t; >INSERT INTO t SET i = NULL; >SELECT * FROM t; You're right, and my 4.0.7 behaves the same way. Is TRUNCATE supposed to behave this way? (The manual doesn't say anything about TRUNCATE and AUTO_INCREMENT.) >>Actually, the counter is reset to 0, not 1. The first inserted value >>then is auto-incremented, and thus becomes 1. >Sure about that? Create a new table and try SHOW TABLE STATUS LIKE 't' >and you'll get: [snip] > Auto_increment: 1 Oh, well ... There's a slight contradiction in the manual, but you're right again, anyway: "When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1." So, if initially value=1, then the first auto_increment value would be 1+1. Anyway, we know what the manual wants to tell us, so sorry for being precocious. Regards, -- Stefan Hinz <[EMAIL PROTECTED]> Geschäftsführer / CEO iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]>; "Octavian Rasnita" <[EMAIL PROTECTED]>; "MySQL" <[EMAIL PROTECTED]> Sent: Thursday, January 09, 2003 1:49 AM Subject: Re: Resetting the auto_increment to start from 1 At 20:39 +0100 1/8/03, Stefan Hinz, iConnect (Berlin) wrote: >Octavian, > >> I've read the following in a MySQL book: > >It's a rather old book, which deals with MySQL 3.23, and not with MySQL >4.x. Actually, he's talking about MySQL Cookbook (p549). Which is a new book, which is why I said *may* reset the counter rather than *will* reset the counter as was true in older versions of MySQL. > >> DELETE FROM tbl_name WHERE 1 > 0; > >In MySQL 3.23, this was a workaround to force the server to delete a >table row by row. By default, 3.23 would on DELETE FROM tbl just do a >DROP TABLE + CREATE TABLE, because this was faster in most cases than >deleting the rows. This behaviour wasn't ANSI SQL compliant, though. > >MySQL 4.x does a DELETE FROM tbl with or without WHERE clause ANSI >compliant. This means, it will always delete the rows, not DROP/CREATE >the table. To do the latter, use > > TRUNCATE TABLE tbl > >This will in fact do a DROP/CREATE, thus resetting the AUTO_INCREMENT >counter etc. Not always! Try this script: DROP TABLE IF EXISTS t; CREATE TABLE t (i INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) TYPE = INNODB; INSERT INTO t SET i = NULL; INSERT INTO t SET i = NULL; INSERT INTO t SET i = NULL; SELECT * FROM t; TRUNCATE TABLE t; INSERT INTO t SET i = NULL; SELECT * FROM t; See if you get the output I do (MySQL 4.0.8): +---+ | i | +---+ | 1 | | 2 | | 3 | +---+ +---+ | i | +---+ | 4 | +---+ What's the solution? Do this: ALTER TABLE t AUTO_INCREMENT = 1; > >> Well, I've tried that sql statement, but the auto_increment point of >start >> was not reset to 1. > >Actually, the counter is reset to 0, not 1. The first inserted value >then is auto-incremented, and thus becomes 1. Sure about that? Create a new table and try SHOW TABLE STATUS LIKE 't' and you'll get: mysql> show table status like 't'\G *** 1. row *** Name: t Type: InnoDB Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: NULL Index_length: 0 Data_free: 0 Auto_increment: 1 Create_time: NULL Update_time: NULL Check_time: NULL Create_options: Comment: InnoDB free: 14336 kB > >Regards, >-- > Stefan Hinz <[EMAIL PROTECTED]> > Geschäftsführer / CEO iConnect GmbH <http://iConnect.de> > Heesestr. 6, 12169 Berlin (Germany) > Tel: +49 30 7970948-0 Fax: +49 30
Re: Resetting the auto_increment to start from 1
Ha ha, thank you for "page 558" but I am blind and I am reading the electronic version of the book. However, I think I've reached to page 558 because I've seen how to do it. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Original Message - From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Octavian Rasnita" <[EMAIL PROTECTED]>; "MySQL" <[EMAIL PROTECTED]> Sent: Wednesday, January 08, 2003 9:26 PM Subject: Re: Resetting the auto_increment to start from 1 At 9:33 +0200 1/8/03, Octavian Rasnita wrote: >Hi all, > >I've read the following in a MySQL book: > > A special case of record deletion occurs when you clear out a table >entirely using a DELETE with no WHERE clause: > DELETE FROM tbl_name; > In this case, the sequence counter may be reset to 1, even for table types >for which values normally are not reused (MyISAM and InnoDB). For those >types, if you wish to delete all the records while maintaining the current >sequence value, tell MySQL to perform a record-at-a-time delete by including >a WHERE clause that specifies some trivially true condition: > DELETE FROM tbl_name WHERE 1 > 0; > >--- > >Well, I've tried that sql statement, but the auto_increment point of start >was not reset to 1. Note that the text in question says *may* be reset to 1. To reset it for sure, use the statement at the top of page 558. :-) >I use MySQL 4.05 under Windows 2000. > >Thanks. > >Teddy, >Teddy's Center: http://teddy.fcc.ro/ >Email: [EMAIL PROTECTED] - 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
Re: Resetting the auto_increment to start from 1
At 20:39 +0100 1/8/03, Stefan Hinz, iConnect (Berlin) wrote: Octavian, I've read the following in a MySQL book: It's a rather old book, which deals with MySQL 3.23, and not with MySQL 4.x. Actually, he's talking about MySQL Cookbook (p549). Which is a new book, which is why I said *may* reset the counter rather than *will* reset the counter as was true in older versions of MySQL. DELETE FROM tbl_name WHERE 1 > 0; In MySQL 3.23, this was a workaround to force the server to delete a table row by row. By default, 3.23 would on DELETE FROM tbl just do a DROP TABLE + CREATE TABLE, because this was faster in most cases than deleting the rows. This behaviour wasn't ANSI SQL compliant, though. MySQL 4.x does a DELETE FROM tbl with or without WHERE clause ANSI compliant. This means, it will always delete the rows, not DROP/CREATE the table. To do the latter, use TRUNCATE TABLE tbl This will in fact do a DROP/CREATE, thus resetting the AUTO_INCREMENT counter etc. Not always! Try this script: DROP TABLE IF EXISTS t; CREATE TABLE t (i INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) TYPE = INNODB; INSERT INTO t SET i = NULL; INSERT INTO t SET i = NULL; INSERT INTO t SET i = NULL; SELECT * FROM t; TRUNCATE TABLE t; INSERT INTO t SET i = NULL; SELECT * FROM t; See if you get the output I do (MySQL 4.0.8): +---+ | i | +---+ | 1 | | 2 | | 3 | +---+ +---+ | i | +---+ | 4 | +---+ What's the solution? Do this: ALTER TABLE t AUTO_INCREMENT = 1; Well, I've tried that sql statement, but the auto_increment point of start was not reset to 1. Actually, the counter is reset to 0, not 1. The first inserted value then is auto-incremented, and thus becomes 1. Sure about that? Create a new table and try SHOW TABLE STATUS LIKE 't' and you'll get: mysql> show table status like 't'\G *** 1. row *** Name: t Type: InnoDB Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: NULL Index_length: 0 Data_free: 0 Auto_increment: 1 Create_time: NULL Update_time: NULL Check_time: NULL Create_options: Comment: InnoDB free: 14336 kB Regards, -- Stefan Hinz <[EMAIL PROTECTED]> Geschäftsführer / CEO iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: "Octavian Rasnita" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Wednesday, January 08, 2003 8:33 AM Subject: Resetting the auto_increment to start from 1 Hi all, I've read the following in a MySQL book: A special case of record deletion occurs when you clear out a table entirely using a DELETE with no WHERE clause: DELETE FROM tbl_name; In this case, the sequence counter may be reset to 1, even for table types for which values normally are not reused (MyISAM and InnoDB). For those types, if you wish to delete all the records while maintaining the current sequence value, tell MySQL to perform a record-at-a-time delete by including a WHERE clause that specifies some trivially true condition: DELETE FROM tbl_name WHERE 1 > 0; --- Well, I've tried that sql statement, but the auto_increment point of start was not reset to 1. I use MySQL 4.05 under Windows 2000. Thanks. Teddy, Teddy's Center: http://teddy.fcc.ro/ > Email: [EMAIL PROTECTED] - 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
Re: Resetting the auto_increment to start from 1
Octavian, > I've read the following in a MySQL book: It's a rather old book, which deals with MySQL 3.23, and not with MySQL 4.x. > DELETE FROM tbl_name WHERE 1 > 0; In MySQL 3.23, this was a workaround to force the server to delete a table row by row. By default, 3.23 would on DELETE FROM tbl just do a DROP TABLE + CREATE TABLE, because this was faster in most cases than deleting the rows. This behaviour wasn't ANSI SQL compliant, though. MySQL 4.x does a DELETE FROM tbl with or without WHERE clause ANSI compliant. This means, it will always delete the rows, not DROP/CREATE the table. To do the latter, use TRUNCATE TABLE tbl This will in fact do a DROP/CREATE, thus resetting the AUTO_INCREMENT counter etc. > Well, I've tried that sql statement, but the auto_increment point of start > was not reset to 1. Actually, the counter is reset to 0, not 1. The first inserted value then is auto-incremented, and thus becomes 1. Regards, -- Stefan Hinz <[EMAIL PROTECTED]> Geschäftsführer / CEO iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: "Octavian Rasnita" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Wednesday, January 08, 2003 8:33 AM Subject: Resetting the auto_increment to start from 1 > Hi all, > > I've read the following in a MySQL book: > > A special case of record deletion occurs when you clear out a table > entirely using a DELETE with no WHERE clause: > DELETE FROM tbl_name; > In this case, the sequence counter may be reset to 1, even for table types > for which values normally are not reused (MyISAM and InnoDB). For those > types, if you wish to delete all the records while maintaining the current > sequence value, tell MySQL to perform a record-at-a-time delete by including > a WHERE clause that specifies some trivially true condition: > DELETE FROM tbl_name WHERE 1 > 0; > > --- > > Well, I've tried that sql statement, but the auto_increment point of start > was not reset to 1. > I use MySQL 4.05 under Windows 2000. > > Thanks. > > Teddy, > Teddy's Center: http://teddy.fcc.ro/ > Email: [EMAIL PROTECTED] > > > > - > 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 > - 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
Re: Resetting the auto_increment to start from 1
At 9:33 +0200 1/8/03, Octavian Rasnita wrote: Hi all, I've read the following in a MySQL book: A special case of record deletion occurs when you clear out a table entirely using a DELETE with no WHERE clause: DELETE FROM tbl_name; In this case, the sequence counter may be reset to 1, even for table types for which values normally are not reused (MyISAM and InnoDB). For those types, if you wish to delete all the records while maintaining the current sequence value, tell MySQL to perform a record-at-a-time delete by including a WHERE clause that specifies some trivially true condition: DELETE FROM tbl_name WHERE 1 > 0; --- Well, I've tried that sql statement, but the auto_increment point of start was not reset to 1. Note that the text in question says *may* be reset to 1. To reset it for sure, use the statement at the top of page 558. :-) I use MySQL 4.05 under Windows 2000. Thanks. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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
Resetting the auto_increment to start from 1
Hi all, I've read the following in a MySQL book: A special case of record deletion occurs when you clear out a table entirely using a DELETE with no WHERE clause: DELETE FROM tbl_name; In this case, the sequence counter may be reset to 1, even for table types for which values normally are not reused (MyISAM and InnoDB). For those types, if you wish to delete all the records while maintaining the current sequence value, tell MySQL to perform a record-at-a-time delete by including a WHERE clause that specifies some trivially true condition: DELETE FROM tbl_name WHERE 1 > 0; --- Well, I've tried that sql statement, but the auto_increment point of start was not reset to 1. I use MySQL 4.05 under Windows 2000. Thanks. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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
