At 6:23 PM -0500 2/19/07, Marty Landman wrote:
>Hi,
>
>I've got a very large table set up and have defined the id as
>auto_increment. No rows have been added, deleted, or replaced since the
>initial load so I'd expect the row count to equal the max(id) since
>
>mysql> describe fidcid;
>+--------+-----------------------+------+-----+---------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+--------+-----------------------+------+-----+---------+----------------+
>| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
>| fId | smallint(5) unsigned | NO | MUL | | |
>| cId | mediumint(8) unsigned | NO | MUL | | |
>| ring | tinyint(3) unsigned | NO | | | |
>+--------+-----------------------+------+-----+---------+----------------+
>4 rows in set (0.38 sec)
>
>But this is not the case, as seen below:
>
>mysql> select count(*) from fidcid;
>+-----------+
>| count(*) |
>+-----------+
>| 100480507 |
>+-----------+
>1 row in set (0.09 sec)
>
>mysql> select max(id) from fidcid;
>+-----------+
>| max(id) |
>+-----------+
>| 100537311 |
>+-----------+
>1 row in set (0.22 sec)
>
>mysql>
>
>Any ideas on what might've happened to explain this?
>
Had the table been used before? The auto_increment counter is
normally not reset, for example:
mysql> create table test (id int unsigned auto_increment not null primary key);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test values (null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
mysql> delete from test;
Query OK, 3 rows affected (0.00 sec)
mysql> insert into test values (null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+
| id |
+----+
| 4 |
| 5 |
| 6 |
+----+
3 rows in set (0.00 sec)
You can either drop/recreate the auto_increment field or explicitly
reset it using an
alter table <tablename> auto_increment=1
statement. See
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
for more info.
steve
--
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center sbedberg@stripped |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
