List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:February 20 2007 3:00pm
Subject:Re: row count inconsistency
View as plain text  
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 ----------------+
Thread
row count inconsistencyMarty Landman20 Feb
  • Re: row count inconsistencySteve Edberg20 Feb
Re: row count inconsistencyMarty Landman21 Feb
  • Re: row count inconsistencyGerald L. Clark21 Feb
    • Re: row count inconsistencySteve Edberg21 Feb