Patrick, > I'm using MySQL 5.0.18-standard Server on a Linux SuSE 10 Server and I've > got following problem: > > I have a table with the followign structure > > +-----------------+----------------------+------+-----+-----------+--------- > -------+ > | Field | Type | Null | Key | Default | Extra > | > +-----------------+----------------------+------+-----+-----------+--------- > -------+ > | STEP_ID | int(10) unsigned | NO | PRI | NULL | > auto_increment | > | INVOICE_ID | int(10) unsigned | NO | MUL | 0 | > | > | STEP_TYPE_ID | smallint(5) unsigned | NO | MUL | 0 | > | > (some other field) ... > +-----------------+----------------------+------+-----+-----------+--------- > -------+ > > When I execute following statement > > SELECT STEP_ID, INVOICE_ID, STEP_TYPE_ID FROM step WHERE > INVOICE_ID=17081598; > > I get this result > > +----------+------------+--------------+ > | STEP_ID | INVOICE_ID | STEP_TYPE_ID | > +----------+------------+--------------+ > | 47870211 | 17081598 | 1 | > | 47870212 | 17081598 | 4 | > | 47870214 | 17081599 | 1 | << !! > +----------+------------+--------------+ > > As you can see there is a record with INVOICE_ID=17081599. > > Please note that if I ask for > > > mysql> SELECT STEP_ID, INVOICE_ID, STEP_TYPE_ID FROM step WHERE > INVOICE_ID=17081599; > > I also receive that record: > > +----------+------------+--------------+ > | STEP_ID | INVOICE_ID | STEP_TYPE_ID | > +----------+------------+--------------+ > | 47870214 | 17081599 | 1 | << > | 47870215 | 17081599 | 4 | > | 47870216 | 17081599 | 3 | > +----------+------------+--------------+ > > Interesting is also that no record with STEP_ID=47870213 is visible. > I wrote "visible" and not "present", because if I try to insert a new Record > with this PK I get this error > > mysql> INSERT INTO step (STEP_ID, INVOICE_ID, STEP_TYPE_ID) VALUES > (47870213, 17081598, 3); > ERROR 1062 (23000): Duplicate entry '47870213' for key 1 > > However: > > mysql> SELECT * FROM step where STEP_ID=47870213; > Empty set (0.00 sec) > > The problem for me is that I also collect a statistic from this come out > wrong, because when I ask for > > mysql> SELECT COUNT(*) FROM step WHERE INVOICE_ID IN (17081598,17081599) AND > STEP_TYPE_ID=1; > > I get, instead of 2: > > +----------+ > | COUNT(*) | > +----------+ > | 3 | > +----------+ > > Can you please tell me what the problem could be and what can I do to solve > it?
Corrupt index? Do you have indices on INVOICE_ID? If so, try dropping it and recreating it? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
