MySQL / phpMyAdmin throws duplicity error on UNIQUE column when no duplicity occurs-CodePudding

I have the following table:

CREATE TABLE pret_user (
    pret_user_index INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    pret_user_fname VARCHAR(250) NOT NULL,
    pret_user_lname VARCHAR(250) NOT NULL,
    pret_user_email VARCHAR(250) NOT NULL,
    pret_user_telegram_subscribe_token_hash VARCHAR(64) UNIQUE,
    pret_user_telegram_subscribe_token_hash_valid_until INT(15),
    pret_user_telegram_chat_id INT(15) UNIQUE,
    pret_user_telegram_subscribed TINYINT(1) NOT NULL DEFAULT 0
);

when I try to manually update a value of pret_user_telegram_chat_id, the following error is shown warning of duplicit value in a UNIQUE column even though there is no value with the same value (the table only has 4 rows so I checked manually). I've also noticed that while I'm trying to set the value to 5163196961, the warning is about value 2147483647. Any ideas as to what might be causing this would be much appreciated!

SQL:

UPDATE `d28748_admin`.`pret_user`
SET    `pret_user_telegram_chat_id` = '5163196931` 
WHERE  `pret_user`.`pret_user_index` = 2

Error:

#1062 - Duplicate entry '2147483647' for key 'pret_user_telegram_chat_id'

To get around the problem, I switched the column to VARCHAR for the time being and after that the problem disappeared, but I'd really like to get to the bottom of why this happened in case I have a similar problem in the future.

CodePudding user response：

Your pret_user_telegram_chat_id is too small. The max value for INT is 2147483647, even though you've specified 15 spots. You'll want BIGINT instead