CODEWITHSUNDEEP
mysqlintoutofrangeexceptionmysql-error-1264
Guapo
Guapo

Reputation: 3482

#1264 Out of range value fix?

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

Upvotes: 3

Views: 41272

Answers (4)

Mabood Ahmad
Mabood Ahmad

Reputation: 31

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

Upvotes: 1

Karolis
Karolis

Reputation: 9582

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

Upvotes: 3

Avada Kedavra
Avada Kedavra

Reputation: 8691

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.

Upvotes: 2

Jonathan Hall
Jonathan Hall

Reputation: 79794

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.

Upvotes: 9

Related Questions