I'm trying to get a primary key field to start the autoincrement at zero,
but seem stuck at 1. Is this possible?

After googling for webpages on this topic, I've seen many references to
using a 'delete from my_table' command, followed by a
'alter table my_table auto_increment=0'. However, in my implementation, the
next insert still produces a 1 for the auto-incremented field (primary
field)

While in mysql, I ran the following statements,

*********
mysql> create table test_table(
-> id int unsigned not null auto_increment,
-> value varchar(255),
-> right_now timestamp not null,
-> primary key(id));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test_table values (0,'steve', NOW());
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_table;
+----+-------+---------------------+
| id | value | right_now |
+----+-------+---------------------+
| 1 | steve | 2008-01-22 22:07:05 |
+----+-------+---------------------+
1 row in set (0.00 sec)

mysql> delete from test_table;
Query OK, 1 row affected (0.00 sec)

mysql> alter table test_table auto_increment=0;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into test_table(value,right_now) values ('steve', NOW());
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+----+-------+---------------------+
| id | value | right_now |
+----+-------+---------------------+
| 1 | steve | 2008-01-22 22:12:14 |
+----+-------+---------------------+
1 row in set (0.00 sec)
***********

As you can see, the auto-increment still doesn't seem to work:

I tried again by adding 2 records, then deleting them, and altering, and
re-inserting. Same problem


*********
mysql> insert into test_table (value,right_now) values ('joe',NOW());
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+----+-------+---------------------+
| id | value | right_now |
+----+-------+---------------------+
| 1 | steve | 2008-01-22 22:12:14 |
| 2 | joe | 2008-01-22 22:24:46 |
+----+-------+---------------------+
2 rows in set (0.01 sec)

mysql> delete from test_table;
Query OK, 2 rows affected (0.00 sec)

mysql> alter table test_table auto_increment=0;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into test_table(value,right_now) values ('jack', NOW());
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+----+-------+---------------------+
| id | value | right_now |
+----+-------+---------------------+
| 1 | jack | 2008-01-22 22:25:40 |
+----+-------+---------------------+
1 row in set (0.00 sec)
*********

When looking at the variables, it shows that auto_increment_increment, and
auto_increment_offset are both 1, even though I set the offset to zero in
the my.cnf file.

*******
# The MySQL server
[mysqld]
basedir="C:/xampp/mysql"
tmpdir="C:/xampp/tmp"
datadir="C:/xampp/mysql/data"

# Added here (01/21/08) to allow auto-increment to start at zero
auto_increment_offset = 0
***********



From the reference manual,
http://dev.mysql.com/doc/refman/5.0/...variables.html ,
it states that setting the auto_increment_increment or auto_increment_offset
to 0 effectively sets them to 1 instead. This suggests to me that the
auto-incremented value of zero is effectively off limits as an
auto-increment value. Is that correct?


How do I get the auto-increment to start at 0?

Thanks in advance to any help given.

piper


Here is my configuration

O.S. Windows XP
MySQL version 5.0.45-community-nt, packaged with XAMPP version 1.6.4