/cleanup and Integrity constraint violation

[Elglobo]

• Operating system: Linux debian Jessie
• PHP version: 7.2
• PHP Telegram Bot version: 0.551
• Using MySQL database: yes
• MySQL version: 10.0.37-MariaDB-0+deb8u1
• Update Method: Webhook
• Self-signed certificate: yes

Expected behaviour

Clean old record database

Actual behaviour

Error on execution:
Warning: PDO::query(): SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (database.message, CONSTRAINT message_ibfk_5 FOREIGN KEY (reply_to_chat, reply_to_message) REFERENCES message (chat_id, id))

Steps to reproduce

Initialize database and after a while error on /cleanup command.

[Elglobo]

Any reply or help ?

[noplanman]

@Elglobo Sorry for the late reply. I'm travelling at the moment but will check this out when I'm back 👍

[jacklul]

My guess we need to remove the records in specific order (replies first), it looks like it tries to remove a message that has replies to

[jacklul]

This will be hard to fix as for example you can have very old message that is supposed to be deleted and a reply message that is not supposed to be removed and in this case the you can't remove the old message because of the reply.

We could modify the query for cleaning 'message' so that it cleans newer first (replies for example) but it won't fix the example I mentioned above:

                DELETE FROM `%1$s`
                WHERE id IN
                (
                    SELECT id
                    FROM
                    (
                        SELECT ID
                        FROM  `%1$s`
                        WHERE `date` < \'%2$s\'
                        AND `id` NOT IN (
                            SELECT `message_id`
                            FROM `%3$s`
                            WHERE `message_id` = `%1$s`.`id`
                        )
                        AND `id` NOT IN (
                            SELECT `message_id`
                            FROM `%4$s`
                            WHERE `message_id` = `%1$s`.`id`
                        )
                        AND `id` NOT IN (
                            SELECT `id`
                            FROM `%1$s`
                            WHERE `reply_to_message` = `%1$s`.`id` AND `reply_to_chat` = `%1$s`.`chat_id`
                        )
                        ORDER BY id DESC
                    ) a
                )

Note that this query WAS NOT TESTED on bigger database than just few rows. I'm not using databases anymore and can't really test it properly.

[noplanman]

This has been fixed in #930 and part of the next release 🎉