Identifying Missing Primary Keys

solution.sql

SET @mpy_id = (SELECT MAX(mpy_id) + 1 from members_payments);

DROP TABLE IF EXISTS members_payments_all_ids;

CREATE TEMPORARY TABLE members_payments_all_ids AS (
	SELECT @mpy_id := @mpy_id - 1 AS mpy_id
	FROM members_payments mp1, members_payments mp2
	WHERE @mpy_id > 1
);

SELECT members_payments_all_ids.mpy_id
FROM members_payments_all_ids
LEFT JOIN members_payments ON members_payments_all_ids.mpy_id = members_payments.mpy_id
WHERE members_payments.mpy_id IS NULL
ORDER BY members_payments_all_ids.mpy_id ASC

+--------+
| mpy_id |
+--------+
|      2 |
|      3 |
|      5 |
|      7 |
|      8 |
|    ... |
+--------+