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
