List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 19 2002 5:29pm
Subject:Re: Accessing last_insert_id problem.
View as plain text  
At 15:13 +0000 12/19/02, Jeff Snoxell wrote:
>At 09:46 19/12/02 -0500, you wrote:
>>Jeff Snoxell wrote:
>>
>>>Nope. That doesn't do it either!
>>>
>>>I go:
>>>
>>>TRUNCATE TABLE my_table
>>
>>
>>Are you using InnoDB tables?  You'll have to do something akin to 
>>ALTER TABLE my_table AUTO_INCREMENT=1 ... at least according to 
>>Paul ... :)
>
>No, I'm using MyISAM I believe.

Can you check for sure.  With MyISAM, TRUNCATE TABLE should always reset
the counter, I believe.  Here's an example:

mysql> CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE t;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table 
|
+-------+----------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
   `i` int(11) NOT NULL auto_increment,
   PRIMARY KEY  (`i`)
) TYPE=MyISAM |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql> TRUNCATE TABLE t;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)


With InnoDB, what I observe is that you have to issue an ALTER TABLE
after truncating the table to force the counter back to 1:

mysql> CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY) 
TYPE = INNODB;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE t;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table 
|
+-------+----------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
   `i` int(11) NOT NULL auto_increment,
   PRIMARY KEY  (`i`)
) TYPE=InnoDB |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql> TRUNCATE TABLE t;
Query OK, 2 rows affected (0.00 sec)

mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+---+
| i |
+---+
| 3 |
+---+
1 row in set (0.00 sec)

mysql> TRUNCATE TABLE t;
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE t AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t SET i = NULL;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)


>
>Jeff
>------------------------
>MySQL, query, SQL
>

Thread
Executing the "source c:/Test.sql" commandAmit Lonkar18 Dec
  • Re: Executing the "source c:/Test.sql" commandPaul DuBois18 Dec
    • Accessing last_insert_id problem.Jeff Snoxell18 Dec
      • re: Accessing last_insert_id problem.Victoria Reznichenko18 Dec
        • re: Accessing last_insert_id problem.Jeff Snoxell18 Dec
          • re: Accessing last_insert_id problem.Wico de Leeuw18 Dec
          • Re: Accessing last_insert_id problem.Michael T. Babcock18 Dec
          • re: Accessing last_insert_id problem.Paul DuBois18 Dec
            • re: Accessing last_insert_id problem.Jeff Snoxell19 Dec
              • re: Accessing last_insert_id problem.Wico de Leeuw19 Dec
            • thread_cache . what is it ? (no newbie question) .cristian ditoiu20 Dec
              • Re: thread_cache . what is it ? (no newbie question) .Benjamin Pflugmann20 Dec
      • Re: Accessing last_insert_id problem.Paul DuBois18 Dec
      • Re: Accessing last_insert_id problem.Matthew Smith2 Jan
        • Using Perl DBI quote() method with NULL values?Jeff Snoxell7 Jan
          • Re: Using Perl DBI quote() method with NULL values?Paul DuBois7 Jan
          • Re: Using Perl DBI quote() method with NULL values?Keith C. Ivey8 Jan
      • Re: Using Perl DBI quote() method with NULL values?walt7 Jan
      • Re: Using Perl DBI quote() method with NULL values?Rodney Broom8 Jan
      • Re: Using Perl DBI quote() method with NULL values?Salam Baker Shanawa8 Jan
  • Re: Accessing last_insert_id problem.Harald Fuchs18 Dec
Re: Accessing last_insert_id problem.Jeff Snoxell19 Dec
  • Re: Accessing last_insert_id problem.Paul DuBois19 Dec
  • RE: Accessing last_insert_id problem.Will Merrell20 Dec
    • Badly placed ()'s and also access deniedGary Hostetler20 Dec
      • RE: Badly placed ()'s and also access deniedJennifer Goodie20 Dec
        • Re: Badly placed ()'s and also access deniedGary Hostetler20 Dec