Thanks Martijn for you prompt answer!
> Do you have indices on INVOICE_ID?
Yes (KEY `IDX_step1` (`INVOICE_ID`))
> If so, try dropping it and recreating it?
OK, I will do it (I will have to wait until this week-end to do it (=> the
table is a bit big (47 mio records) and I cannot stop the service during the
week) and tell you the results.
Shall also perform a REPAIR TABLE?
Regards,
Patrick
> -----Original Message-----
> From: Martijn Tonies [mailto:m.tonies@stripped]
> Sent: Tuesday, 04 April 2006 10:34
> To: Patrick Herber; mysql@stripped
> Subject: Re: MySQL 5.0.18-standard - Wrong record (sorry, I
> cannot find a better subject)
>
> 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
> > mysql> (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/mysql?unsub=1
>
