#1264 Out of range value fix?

Question

When I try to insert the below into my MySQL

INSERT INTO `rooms` (`id`, `ip`) VALUES ('131213', '-259857341');

I fails with the follow error:

Warning: #1264 Out of range value for column 'ip' at row 1

I am looking around but haven't found how to fix or work it out...

My field is unsigned int which should work just fine for that entry.

What is the problem and how do I solve ?

I am using unsigned int because I wanted to store ips using inet_ntoa/aton.

EDIT:

I am using unsigned INT as recommend in MySQL website:

To store values generated by INET_ATON(), use an INT UNSIGNED column rather than INT, which is signed. If you use a signed column, values corresponding to IP addresses for which the first octet is greater than 127 cannot be stored correctly. See Section 10.6, “Out-of-Range and Overflow Handling”.

http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html

Answer 1

Firstly, by definition, you cannot insert a negative number into a unsigned int field. Change the field to int instead (or if possible use non-negative numbers).

Secondly i think that you should remove the single-quotes around the inserted number to that the the value is treated as an int and not a string.

Answer 2

A negative number is out of range for an UNSIGNED INT. Read the documentation for a full list of allowed values for a given data type.

Answer 3

Change your INT field into BIGINT and it will work for me smoothly.

Answer 4

Unsigned integer means non-negative value at least.

Not sure if this is what you need but you can try to convert signed integer to 4 bytes unsigned integer as your ipconverter does (http://www.silisoftware.com/tools/ipconverter.php):

INSERT INTO `rooms` (`id`, `ip`) VALUES ('131213', '-259857341' & 0xffffffff);