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=>"id=$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 <[EMAIL PROTECTED]> 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/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 ----------------+
--
Web Installed Formmail - http://face2interface.com/formINSTal/
Webmaster's BBS - http://bbs.face2interface.com/
