Ticket auto-increment can be lowered, leading to ID reuse after purge

[EORIS-SUPPORT]

Description

When the tickets_id_format option is enabled (e.g. Ym0001), the beforeAdd
hook in inc/ticket.class.php can lower the glpi_tickets.AUTO_INCREMENT
after tickets of the current month have been physically deleted (purged).
This results in new tickets reusing IDs already assigned to purged tickets.

The same bug exists in inc/change.class.php with changes_id_format.

Steps to reproduce

1. Configure Behaviors with tickets_id_format = Ym0001 (or any month-based format).
2. In the current month (e.g. June 2026), create 2 tickets. They get IDs 2026060001 and 2026060002. The table's AUTO_INCREMENT is now 2026060003.
3. Permanently delete (purge) both tickets — through the trash UI, the autopurge_delay entity setting, or any cron task that hard-deletes tickets. After purge:
   • MAX(glpi_tickets.id) falls back to the last May ticket (< 2026060001).
   • AUTO_INCREMENT remains 2026060003 (DELETE doesn't reset it — correct MySQL/MariaDB behavior).
4. Create a new ticket.

Expected behavior

New ticket should receive ID 2026060003 (or higher). Previously-used IDs must never be reused.

Actual behavior

New ticket receives ID 2026060001. A second new ticket gets 2026060002, silently colliding with the IDs of the purged tickets. From a user's perspective, old tickets appear to have been "replaced" by new unrelated ones with the same number — a serious data integrity issue and audit problem.

Root cause

inc/ticket.class.php, beforeAdd():

$max = 0;
$sql = [
    'SELECT' => ['MAX' => 'id AS max'],
    'FROM' => 'glpi_tickets',
];
foreach ($DB->request($sql) as $data) {
    $max = $data['max'];
}
$want = date($config->getField('tickets_id_format'));
if ($max < $want) {
    $DB->query("ALTER TABLE `glpi_tickets` AUTO_INCREMENT=$want");
}

The hook compares MAX(id) to the date-based target $want. After a purge that removes all tickets of the current month, MAX(id) drops to the last ticket of the previous month, so $max < $want becomes true again, and the ALTER TABLE is executed.

The key issue: MySQL/MariaDB accepts ALTER TABLE AUTO_INCREMENT = N whenever N > MAX(id), even if N is below the current AUTO_INCREMENT value. So the AI is lowered from 2026060003 down to 2026060001, freeing up IDs that were previously assigned.

MAX(id) is not a safe proxy for the table's auto-increment when DELETE operations are possible.

Proposed fix

Read the real AUTO_INCREMENT from information_schema.TABLES instead of MAX(id). This value does not decrease on DELETE.

if ($config->getField('tickets_id_format')) {
    $max = 0;
    foreach ($DB->request(
        "SELECT AUTO_INCREMENT AS max
         FROM information_schema.TABLES
         WHERE TABLE_SCHEMA = DATABASE()
           AND TABLE_NAME = 'glpi_tickets'"
    ) as $data) {
        $max = (int)$data['max'];
    }
    $want = (int)date($config->getField('tickets_id_format'));
    if ($max < $want) {
        $DB->query("ALTER TABLE `glpi_tickets` AUTO_INCREMENT=$want");
    }
}

Same fix applies to inc/change.class.php with glpi_changes.

Environment

• GLPI version: 10.0.25
• Behaviors plugin version: 2.7.8

Reproducibility

Consistent. Reproduced manually multiple times. Production impact reported by an end user (two unrelated tickets reusing IDs of previously purged tickets, originally created via FormCreator and later overwritten by manually-created tickets in the same month).