SEO

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

On 23 Jan, 06:44, "piper1970" <some...@someone.com> wrote:
> 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

You need to put it in there specifically with the correct SQL mode
set, but it is not recommended
http://dev.mysql.com/doc/refman/5.0/...-sql-mode.html

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

A colleague of mine once did this. It will haunt you till the end of
times. It will be a real nuisance if you want to import such a table
into another database (like a developer's sandbox database, for
instance). So if you regard your colleagues, please don't.

Regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

On 23 Jan, 10:09, Willem Bogaerts
<w.bogae...@kratz.maardanzonderditstuk.nl> wrote:
> >> How do I get the auto-increment to start at 0?
>
> A colleague of mine once did this. It will haunt you till the end of
> times. It will be a real nuisance if you want to import such a table
> into another database (like a developer's sandbox database, for
> instance). So if you regard your colleagues, please don't.
>
> Regards,
> --
> Willem Bogaerts
>
> Application smith
> Kratz B.V.http://www.kratz.nl/

As I said "it is not recommended". Since you were only quoting the the
previous post, it would have been beter to respond to that one.

> ... Since you were only quoting the the
> previous post, it would have been beter to respond to that one.

Yes, I saw that too late. Sorry!

Regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

On Tue, 22 Jan 2008 22:44:32 -0800, I waved a wand and this message
magically appears in front of piper1970:

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

Short answer: DON'T DO THAT!
--
http://www.munted.org.uk

Fearsome grindings.

thanks for the advice. will be disregarding attempt to use 0 as
auto-increment value.

piper


"piper1970" <someone@someone.com> wrote in message
news:B7SdncEIbtvMfwvanZ2dnUVZ_qKgnZ2d@comcast.com. ..
> 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
>

On Wed, 23 Jan 2008 20:46:15 -0800, piper1970 wrote:
> thanks for the advice. will be disregarding attempt to use 0 as
> auto-increment value.

Ideally, your PK shouldn't have any meaning outside identifying the row.
For me, the best way to manage that is to never know nor care what the
value is.

--
74. When I create a multimedia presentation of my plan designed so that my
five-year-old advisor can easily understand the details, I will not label
the disk "Project Overlord" and leave it lying on top of my desk.
--Peter Anspach's list of things to do as an Evil Overlord

On Thu, 24 Jan 2008 08:27:40 -0600, "Peter H. Coffin"
<hellsop@ninehells.com> wrote:

>On Wed, 23 Jan 2008 20:46:15 -0800, piper1970 wrote:
>> thanks for the advice. will be disregarding attempt to use 0 as
>> auto-increment value.
>
>Ideally, your PK shouldn't have any meaning outside identifying the row.
>For me, the best way to manage that is to never know nor care what the
>value is.

Alternatively, the primary key can be meaningful, that is
identify an object in real life, like a PartNumber,
SerialNumber or SocialSecurityNumber, in which case it
can't autoincrement, of course.
--
( Kees
)
c[_] In any bureaucracy, paperwork increases as you spend more
and more time reporting on the less and less you are doing.
Stability is achieved when you spend all of your
time reporting on the nothing you are doing. (#173)

On Thu, 24 Jan 2008 19:48:50 +0100, Kees Nuyt wrote:
> On Thu, 24 Jan 2008 08:27:40 -0600, "Peter H. Coffin"
><hellsop@ninehells.com> wrote:
>
>>On Wed, 23 Jan 2008 20:46:15 -0800, piper1970 wrote:
>>> thanks for the advice. will be disregarding attempt to use 0 as
>>> auto-increment value.
>>
>>Ideally, your PK shouldn't have any meaning outside identifying the row.
>>For me, the best way to manage that is to never know nor care what the
>>value is.
>
> Alternatively, the primary key can be meaningful, that is
> identify an object in real life, like a PartNumber,
> SerialNumber or SocialSecurityNumber, in which case it
> can't autoincrement, of course.

Not a fan of it. Meaningful values change. Some change very slowly, some
can change quickly. EG SocialSecurityNumber doesn't survive identity
theft cases. PartNumbers often change with suppliers. CustomerNumbers
muiltiply like cockroaches when you're dealing with less than perfectly
sophisticated customers. Now, when you're dealing with a database that
is all in one space and never imports data back in from someplace else,
you're fine. If data is out someplace and needs to come back (result of
dealing with a vendor for a project, for example) and your meaningful
value has changed in the meantime, THEN you've got a problem. PKs that
exist only to be PKs don't have this problem.

I've gone so far before as to have both a meaning-free PK, *and* a
meaning-free user key, with intersection tables that described how
multiples of each actually combined into one aggregate entity. It was
ugly amounts of hair, but it made the database do what it was supposed
to in spite of some very dirty records, and kept everything functioning
while the mess got cleaned up.

--
56. My Legions of Terror will be trained in basic marksmanship. Any who
cannot learn to hit a man-sized target at 10 meters will be used for
target practice.
--Peter Anspach's list of things to do as an Evil Overlord