SEO

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?

Marty

--
Web Installed Formmail - http://face2interface.com/formINSTal/
Webmaster's BBS - http://bbs.face2interface.com/

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/...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@ucdavis.edu |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+

The table was created and then loaded and not modified in any way I'm aware
of afterwards. It's on a local, only accessible by me server.

Really weird thing about it is that I wrote/ran a program specifically to
find any gaps in the id sequence - because of the size of the table it took
days to run but the result was

1-100537311

IOW it confirms the max id that Mysql gave, but also indicates that there
are no gaps in the row id's all the way through. This doesn't make sense to
me in light of Mysql reporting the count as posted previously i.e.

>mysql> select count(*) from fidcid;
>+-----------+
>| count(*) |
>+-----------+
>| 100480507 |
>+-----------+
>1 row in set (0.09 sec)

I did do some testing before letting the program run, but here's the main
code:


my($cur,$prv,$rating,$line) = (1);
$line = "$cur-";

for $cur(1..100537311)
{
$rating = getDat $DB
({table=>'fidcid',cols=>'id,cId,fId,ring',cond=>"i d=$cur"});
if($prv != $cur-1) { $line .= "$prv\n"; print $line; $line = "$cur-"
}
$prv = $cur;
print "---->> $cur at ",`time /T\n` if not $cur % 100000
}
print $line,"100537311\n"



On 2/20/07, Steve Edberg <sbedberg@ucdavis.edu> wrote:
>
> 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/...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@ucdavis.edu |
> | Bioinformatics programming/database/sysadmin (530)754-9127 |
> +---------------- said t e lawrence, picking up his fork ----------------+
>



--
Web Installed Formmail - http://face2interface.com/formINSTal/
Webmaster's BBS - http://bbs.face2interface.com/

Marty Landman wrote:
> The table was created and then loaded and not modified in any way I'm aware
> of afterwards. It's on a local, only accessible by me server.
>
> Really weird thing about it is that I wrote/ran a program specifically to
> find any gaps in the id sequence - because of the size of the table it took
> days to run but the result was
>
> 1-100537311
>
> IOW it confirms the max id that Mysql gave, but also indicates that there
> are no gaps in the row id's all the way through. This doesn't make sense to
> me in light of Mysql reporting the count as posted previously i.e.
>
>> mysql> select count(*) from fidcid;
>> +-----------+
>> | count(*) |
>> +-----------+
>> | 100480507 |
>> +-----------+
>> 1 row in set (0.09 sec)
>

If this table is InnoDB, then count(*) is
just an approximation.


--
Gerald L. Clark
Supplier Systems Corporation

At 10:34 AM -0600 2/21/07, Gerald L. Clark wrote:
>Marty Landman wrote:
>>The table was created and then loaded and not modified in any way I'm aware
>>of afterwards. It's on a local, only accessible by me server.
>>
>>Really weird thing about it is that I wrote/ran a program specifically to
>>find any gaps in the id sequence - because of the size of the table it took
>>days to run but the result was
>>
>>1-100537311
>>
>>IOW it confirms the max id that Mysql gave, but also indicates that there
>>are no gaps in the row id's all the way through. This doesn't make sense to
>>me in light of Mysql reporting the count as posted previously i.e.
>>
>>>mysql> select count(*) from fidcid;
>>>+-----------+
>>>| count(*) |
>>>+-----------+
>>>| 100480507 |
>>>+-----------+
>>>1 row in set (0.09 sec)
>>
>
>If this table is InnoDB, then count(*) is
>just an approximation.
>
>
>--
>Gerald L. Clark
>Supplier Systems Corporation


select count(*), as well as other functions like max(), min() etc
should be accurate regardless of table type; it's the 'show table
status' report that may be inaccurate for Innodb:

http://dev.mysql.com/doc/refman/5.0/...le-status.html

Going back to the original problem:

What is the table type & MySQL version? Also, if you drop the
auto_increment column and recreate it (on a copy of the original
table, if necessary), are these results repeatable?

Also, if the server has been shutdown improperly, there may be table
corruption:

MyISAM tables:
http://dev.mysql.com/doc/refman/5.0/...-problems.html

InnoDB problems:
http://dev.mysql.com/doc/refman/5.0/...eshooting.html

steve

PS. This may be an obvious question, but: are you sure data loading
was finished before running the select count(*) and select max(id)
queries?


--
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center sbedberg@ucdavis.edu |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+