Re: Accessing last_insert_id problem.

[Paul DuBois]

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

---------------------------------------------------------------------
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