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

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