List:General Discussion« Previous MessageNext Message »
From:Patrick Herber Date:April 4 2006 8:56am
Subject:RE: MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject)
View as plain text  
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
> 

Thread
MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject)Patrick Herber4 Apr
  • Re: MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject)Martijn Tonies4 Apr
    • RE: MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject)Patrick Herber4 Apr
    • RE: MySQL 5.0.18-standard - Wrong record (sorry, I cannot find a better subject)Patrick Herber8 Apr