Cannot update to 2.1.0 error caused by behaviours plugin column tickets_id Column

[jcervantes-sipecom]

Describe the bug

I'm getting this error when trying to update to 2.1.0:

Cannot alter glpi_plugin_mailanalyzer_message_id table! Out of range value for column 'tickets_id'

It's because using plugin behaviours for changing the ticket_id format to ymd0001 it was necesary to change the column type to INT(11) UNSIGNED.

But in hook.php at line 10 and 33 there are these queries:

$query = "ALTER TABLE `glpi_plugin_mailanalyzer_message_id`
                  CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT FIRST,
                  CHANGE COLUMN `ticket_id` `tickets_id` INT(11) NOT NULL DEFAULT '0' AFTER `message_id`,
                  DROP INDEX `ticket_id`,
                  ADD INDEX `ticket_id` (`tickets_id`);";

$query = "CREATE TABLE `glpi_plugin_mailanalyzer_message_id` (
            `id` INT(11) NOT NULL AUTO_INCREMENT,
            `message_id` VARCHAR(255) NOT NULL DEFAULT '0',
            `tickets_id` INT(11) NOT NULL DEFAULT '0',
            PRIMARY KEY (`id`),
            UNIQUE INDEX `message_id` (`message_id`),
            INDEX `tickets_id` (`tickets_id`)
         )
         COLLATE='utf8_general_ci'
         ENGINE=innoDB;
         ";

So it's necessary to change these queries to respectively:

$query = "CREATE TABLE `glpi_plugin_mailanalyzer_message_id` (
            `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
            `message_id` VARCHAR(255) NOT NULL DEFAULT '0',
            `tickets_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
            PRIMARY KEY (`id`),
            UNIQUE INDEX `message_id` (`message_id`),
            INDEX `tickets_id` (`tickets_id`)
         )
         COLLATE='utf8_general_ci'
         ENGINE=innoDB;
         ";

$query = "ALTER TABLE `glpi_plugin_mailanalyzer_message_id`
                  CHANGE COLUMN `id` `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
                  CHANGE COLUMN `ticket_id` `tickets_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' AFTER `message_id`,
                  DROP INDEX `ticket_id`,
                  ADD INDEX `ticket_id` (`tickets_id`);";

After that the plugin can be installed.

To reproduce

Steps to reproduce the behavior:

1. Install plugin behaviour and change the ticket format to ymd0001
2. Create a ticket with ID with year 22 or with ticket ID 2109300015 for example
3. Install plugin mailanalyzer or try to update to version 2.1.0
4. The error: Cannot alter glpi_plugin_mailanalyzer_message_id table! Out of range value for column 'tickets_id' raised up

[tomolimo]

Hello @jcervantes-sipecom Thank you for the report, there is an issue with the INT(11) UNSIGNED which should be INT UNSIGNED and not INT(11) The correct requests must be:

CREATE TABLE `glpi_plugin_mailanalyzer_message_id` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `message_id` VARCHAR(255) NOT NULL DEFAULT '0' COLLATE 'utf8_unicode_ci',
    `tickets_id` INT UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `message_id` (`message_id`) ,
    INDEX `ticket_id` (`tickets_id`) 
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

ALTER TABLE `glpi_plugin_mailanalyzer_message_id`
    CHANGE COLUMN `id` `id` INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    CHANGE COLUMN `tickets_id` `tickets_id` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `message_id`;

Thank you, Regards, Tomolimo