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 [EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
