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:[EMAIL PROTECTED] > Sent: Tuesday, 04 April 2006 10:34 > To: Patrick Herber; mysql@lists.mysql.com > 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/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
