#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

Crumpler

it is on the question above ;) My field is unsigned int which should work just fine for that entry. Since -259857341 does not exceed the minimum nor maximum. – Crumpler

how many bytes to int field? refs: dev.mysql.com/doc/refman/5.0/en/numeric-types.html – Bronchiectasis

How did you come up with a negative IP in the first place? Are you programming for some parallel universe? – Lebar

@Flimzy it is not an ip I came up with I am reading it from a set of files that is given to me and I have to parse it into the database. – Crumpler

@Flimzy it is an ip if you convert it you will see the ip just fine... You can use this tool with the negative number above and you will get the ip silisoftware.com/tools/ipconverter.php – Crumpler

Well, this "ip", regardless of where it came from, isn't really an IP. IPs cannot be negative. – Lebar

@Guapo: in your example above you are inserting the value -259857341 into your db. The column may be named IP but that does make the value anything like an ip-address? – Homerus

possible duplicate of How do I fix the 'Out of range value adjusted for column' error? – Amersham

Answer 1

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);

Algol

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.

Lebar

From MySQL 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”. – Crumpler

I'm not disputing that INT UNSIGNED makes sense for storing IPs. I'm only saying that it doesn't work for storing negative numbers. And IPs are never negative. – Lebar

Answer 3

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.

Homerus

Answer 4

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

Brendabrendan