MySQL Bugs: #11599: I get a Duplicate entry error message even when the key doesn't exist yet

Bug #11599	I get a Duplicate entry error message even when the key doesn't exist yet
Submitted:	28 Jun 2005 0:07	Modified:	28 Jun 2005 11:09
Reporter:	Gavriel Fleischer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.1.11a	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I'm trying to insert a row into my table but I get a duplicate key error eventhough the key doesn't exist yet in the table. Even the ON DUPLICATE KEY UPDATE doesn't help.

Here are the examples:

mysql> describe ip2host;
+-----------+--------------+------+-----+-------------------+-------+
| Field     | Type         | Null | Key | Default           | Extra |
+-----------+--------------+------+-----+-------------------+-------+
| ip        | int(11)      |      | PRI | 0                 |       |
| host      | varchar(128) |      |     |                   |       |
| timestamp | timestamp    | YES  |     | CURRENT_TIMESTAMP |       |
+-----------+--------------+------+-----+-------------------+-------+

mysql> select * from ip2host where ip=3252699955;
Empty set (0.01 sec)

mysql> INSERT INTO ip2host (ip,host) VALUES (3252699955, '');
ERROR 1062 (23000): Duplicate entry '2147483647' for key 1

mysql> INSERT INTO ip2host (ip,host) VALUES (3252699955, '') ON DUPLICATE KEY UPDATE host='', timestamp=NULL;
Query OK, 2 rows affected, 1 warning (0.04 sec)

mysql> select * from ip2host where ip=3252699955;
Empty set (0.00 sec)

How to repeat:
I'm sorry, I don't know how to reproduce it in an empty databese.

actualy you have a primary key (according to 'describe')

You inserts a value that bigger than 'int' field can fit.
and according to documentation (http://dev.mysql.com/doc/mysql/en/numeric-types.html ) the value is clipped to  upper endpoint of the range and stores 2147483647  instead.

So, if you have to store (say) 3252699955, you must alter table and change type of field to bigint

Well I meant "key doesn't exist" is that this key is not in the table yet.

And the 2nd comment was helpful! Thanks! Yes, I made the change from INT to INT UNSIGNED, and that was enough!

Globaly, can we say that when we are storing numerical values, there is no point to store it like a 'int' if we are not going to make mathematical operation on this values ?
ex  : the ip address won't be use to add/divide or other kind of math calculus, will it ?
So, one solution is to store the ip like a text field.