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

Thanks a lot!

Regards,
Patrick

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