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
