handleGetUpdates loop

[aerdnar]

This bug is still here and must be solved! I cannot use my telegram bot because my current update_id is lower than last time, now it's in loop because DB::selectTelegramUpdate() assume update_id increasing and return higher update_id stored. In https://github.com/php-telegram-bot/core/blob/master/src/DB.php#L193 there is the bug. Telegram explain this https://core.telegram.org/bots/api#getting-updates :

• Incoming updates are stored on the server until the bot receives them either way, but they will not be kept longer than 24 hours.
• update_id: If there are no new updates for at least a week, then identifier of the next update will be chosen randomly instead of sequentially.

The query in DB::selectTelegramUpdate() should ignore older update_id more than 48 hours. This can be fixed in two ways:

• by query where date/created_at values are acceptable: this is backward compatible but slow and complex
• by add new column in telegram_update called update_date: no backward compatible but fast, easy, old database could be updated but this could broken something

Originally posted in https://github.com/php-telegram-bot/core/issues/659#issuecomment-681309431

The first solution is slow, very slow about 6-20x slower, second requires table update and _ORDER BY CONVERT(updatedate,DATE) DESC, id DESC to avoid issues timestamp based (date return y-m-d).

How to reproduce:

• Create a new bot
• Send a message
• Manipulate update_id to higher value (or wait a week or two)
• Enjoy the loop

[aerdnar]

$twodaysago = null;
if ($id !== null) {
    $sql .= ' WHERE `id` = :id';
} else {
    //"faster" fix about 6x slower than before
    if($limit == 1){
        $sql .='WHERE 
                    (chat_id, message_id) IN 
                        (SELECT chat_id, id FROM '.TB_MESSAGE.' WHERE date >= :twodaysago) OR 
                    (chat_id, edited_message_id) IN 
                        (SELECT chat_id, id FROM '.TB_EDITED_MESSAGE.' WHERE edit_date >= :twodaysago) OR
                    (chat_id, channel_post_id) IN 
                        (SELECT chat_id, id FROM '.TB_MESSAGE.' WHERE date >= :twodaysago) OR 
                    (chat_id, edited_channel_post_id) IN 
                        (SELECT chat_id, id FROM '.TB_EDITED_MESSAGE.' WHERE edit_date >= :twodaysago) OR
                    inline_query_id IN 
                        (SELECT id FROM '.TB_INLINE_QUERY.' WHERE created_at >= :twodaysago) OR
                    chosen_inline_result_id IN 
                        (SELECT id FROM '.TB_CHOSEN_INLINE_RESULT.' WHERE created_at >= :twodaysago) OR
                    callback_query_id IN 
                        (SELECT id FROM '.TB_CALLBACK_QUERY.' WHERE created_at >= :twodaysago) OR
                    shipping_query_id IN 
                        (SELECT id FROM '.TB_SHIPPING_QUERY.' WHERE created_at >= :twodaysago) OR
                    pre_checkout_query_id IN 
                        (SELECT id FROM '.TB_PRE_CHECKOUT_QUERY.' WHERE created_at >= :twodaysago) OR
                    poll_id IN 
                        (SELECT id FROM '.TB_POLL.' WHERE created_at >= :twodaysago)
                ORDER BY id DESC';
        $twodaysago = self::getTimestamp(strtotime('-2 day'));
    }//"slower" fix about 20x slower than before but return all updates
    else {
        $sql = 'SELECT '.TB_TELEGRAM_UPDATE.'.id, 
                    ( CASE
                        WHEN message.created_at IS NOT NULL 
                            THEN message.created_at
                        WHEN edited_message.created_at IS NOT NULL 
                            THEN edited_message.created_at
                        WHEN inline_query.created_at IS NOT NULL 
                            THEN inline_query.created_at
                        WHEN chosen_inline_result.created_at IS NOT NULL 
                            THEN chosen_inline_result.created_at
                        WHEN callback_query.created_at IS NOT NULL 
                            THEN callback_query.created_at
                        WHEN shipping_query.created_at IS NOT NULL 
                            THEN shipping_query.created_at
                        WHEN pre_checkout_query.created_at IS NOT NULL 
                            THEN pre_checkout_query.created_at
                        WHEN poll.created_at IS NOT NULL 
                            THEN poll.created_at
                        ELSE NULL
                    END ) as update_date
                FROM 
                ( 
                '.TB_TELEGRAM_UPDATE.' 
                LEFT JOIN (SELECT id, date as created_at, chat_id FROM '.TB_MESSAGE.') message 
                    ON ('.TB_TELEGRAM_UPDATE.'.message_id = message.id 
                        OR '.TB_TELEGRAM_UPDATE.'.channel_post_id = message.id) 
                        AND '.TB_TELEGRAM_UPDATE.'.chat_id = message.chat_id
                LEFT JOIN (SELECT id, edit_date as created_at, chat_id FROM '.TB_EDITED_MESSAGE.')
                    edited_message       
                    ON ('.TB_TELEGRAM_UPDATE.'.edited_message_id = edited_message.id 
                        OR '.TB_TELEGRAM_UPDATE.'.edited_channel_post_id = edited_message.id) 
                        AND '.TB_TELEGRAM_UPDATE.'.chat_id = edited_message.chat_id
                LEFT JOIN (SELECT id, created_at FROM '.TB_CALLBACK_QUERY.') 
                    callback_query 
                    ON '.TB_TELEGRAM_UPDATE.'.callback_query_id = callback_query.id    
                LEFT JOIN (SELECT id, created_at FROM '.TB_INLINE_QUERY.') 
                    inline_query 
                    ON '.TB_TELEGRAM_UPDATE.'.inline_query_id = inline_query.id 
                LEFT JOIN (SELECT id, created_at FROM '.TB_CHOSEN_INLINE_RESULT.')
                    chosen_inline_result     
                    ON '.TB_TELEGRAM_UPDATE.'.chosen_inline_result_id = chosen_inline_result.id
                LEFT JOIN (SELECT id, created_at FROM '.TB_SHIPPING_QUERY.') 
                    shipping_query 
                    ON '.TB_TELEGRAM_UPDATE.'.shipping_query_id = shipping_query.id 
                LEFT JOIN (SELECT id, created_at FROM '.TB_PRE_CHECKOUT_QUERY.') 
                    pre_checkout_query 
                    ON '.TB_TELEGRAM_UPDATE.'.pre_checkout_query_id = pre_checkout_query.id 
                LEFT JOIN (SELECT id, created_at FROM '.TB_POLL.') 
                    poll 
                    ON '.TB_TELEGRAM_UPDATE.'.poll_id = poll.id 
                )
                ORDER BY CONVERT(update_date,DATE) DESC, id DESC';
    }

    //id is not unique and after a week without updates telegram set random value
    //https://core.telegram.org/bots/api#update -> update_id description

    //$sql .= ' ORDER BY `id` DESC';

    //best solution: add update_date to TB_TELEGRAM_UPDATE and use 
    //ORDER BY CONVERT(update_date,DATE) DESC, id DESC';
}

if ($limit !== null) {
    $sql .= ' LIMIT :limit';
}

$sth = self::$pdo->prepare($sql);

if ($limit !== null) {
    $sth->bindValue(':limit', $limit, PDO::PARAM_INT);
}
if ($id !== null) {
    $sth->bindValue(':id', $id);
}

//still fix..
if ($twodaysago !== null){
    $sth->bindValue(':twodaysago', $twodaysago, PDO::PARAM_STR);
}
$sth->execute();

That's my slow bad and broken temporary fix because I cannot use my bot otherwise

[noplanman]

Ah right, I see what you mean, thanks!

Obviously the problem still exists of possibly having updates with the same ID, so the state of the DB would be inconsistent (having multiple updates with the same ID).

Not entirely sure how best to tackle that 🤔

@jacklul any ideas?

[jacklul]

@jacklul any ideas?

No idea how to approach this

[noplanman]

I think an idea would be to add a timestamp field to the telegram_update table and include it to the primary key. That was there "really" shouldn't be any duplicate entries and the cleanup command can be huuuugely improved.

What do you think?