maria-developers team mailing list archive

[Oliver Schonrock <oliver@xxxxxxxxxxxxxx>]

Further info below:

On 03/02/18 13:42, Oliver Schonrock wrote:
> DELETE FROM member WHERE id = 1;
> 
> SELECT * FROM member;
> [ empty ]
> 
> SELECT * FROM address;
> [ empty ]
> 
> SELECT * FROM payment_method;
> +----+-----------+-----------------------+
> | id | member_id | cardholder_address_id |
> +----+-----------+-----------------------+
> |  3 |         1 |                  NULL |
> +----+-----------+-----------------------+
> 
> ------------
> 
> DB is now in an inconsistent state: payment_method.member_id=1
> references a non-existent member record.

It gets slightly worse. Once the DB is in this inconsistent state,
queries that should highlight the problem, do not, eg:

SELECT
  payment_method.id, member_id
FROM
  payment_method
LEFT JOIN
  member ON member.id=payment_method.member_id
WHERE
  payment_method.member_id IS NOT NULL AND member.id IS NULL;

returns an empty result set.

If you mysqldump the inconsistent DB, and re-insert it, then the above
query returns:

+----+-----------+
| id | member_id |
+----+-----------+
|  3 |         1 |
+----+-----------+

as it should. Suspect inconsistent INDEX, or similar ?



-- 
Oliver Schönrock

Attachment: signature.asc
Description: OpenPGP digital signature