3

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

Guapo
  • 3,446
  • 9
  • 36
  • 63
  • 2
    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. – Guapo Jul 08 '11 at 07:56
  • 1
    how many bytes to int field? refs: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html – JellyBelly Jul 08 '11 at 07:59
  • How did you come up with a negative IP in the first place? Are you programming for some parallel universe? – Jonathan Hall Jul 08 '11 at 08:03
  • @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. – Guapo Jul 08 '11 at 08:07
  • @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 http://www.silisoftware.com/tools/ipconverter.php – Guapo Jul 08 '11 at 08:12
  • 1
    Well, this "ip", regardless of where it came from, isn't really an IP. IPs cannot be negative. – Jonathan Hall Jul 08 '11 at 08:12
  • @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? – Avada Kedavra Jul 08 '11 at 08:13
  • possible duplicate of [How do I fix the 'Out of range value adjusted for column' error?](http://stackoverflow.com/questions/1786509/how-do-i-fix-the-out-of-range-value-adjusted-for-column-error) – Brad Werth Sep 16 '14 at 16:26

4 Answers4

8

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.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
  • 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”. – Guapo Jul 08 '11 at 08:06
  • 3
    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. – Jonathan Hall Jul 08 '11 at 08:09
3

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);
Karolis
  • 9,396
  • 29
  • 38
2

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.

Avada Kedavra
  • 8,523
  • 5
  • 32
  • 48
1

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

Mabood Ahmad
  • 31
  • 5