Out of range value adjusted for column error

~ Celia Rexselin

This is one bug that had me smitten ever since Mysql 5 was released. If you had encountered this error and are searching in vain throughout google and msn for a fix, please do not be discouraged. This is a very simple problem and can be corrected easily.

1) Use SQL_MODE functionality:

Login to the Mysql shell and give the following command.

SET GLOBAL SQL_MODE=”;

Voila. That fixes your problem.

2) Revert to Mysql 4.

If you are not sure on how to do 1), this is the only other option avaliable.


Just for programmers:

In the olden days, PHP programmers were amazed by the freedom offered by PHP and Mysql. Unlike their C counterparts, they were not needed to declare any variable. While Oracle and Mssql programmers were worrying about the sql constraints, Mysql was being such a sweet ‘art and was letting them insert/modify anything into its tables. But it doesn’t seem to be practical anymore.

So, if you are a programmer and have found this bug in your code, I am very sorry. I think you might need to rewrite most of your queries. But don’t panic. It is not all that complex. I would tell you what to do.

Lets take an example query:

1264 – Out of range value adjusted for column ‘products_id’ at row 1

insert into products_notifications (products_id, customers_id, date_added) values (”, ‘2’, now())

In the above query, the error was thrown for ‘products_id’ because products_id is an integer field and the programmer was trying to insert an empty string into it. Got my point? So, just because Mysql was silent all these days, we can’t exploit a field like this. Can we? This is how the query should be.

insert into products_notifications (products_id, customers_id, date_added) values (‘1’, ‘2’, now())

Hope my suggestion helped you. 🙂

Published by Celia Rexselin

Application Security Consultant, PHP Web Developer, thinks open source, loves people, writing, languages and calvin & hobbes.

79 thoughts on “Out of range value adjusted for column error”

  1. Thank you. Wasted nearly a whole day trying to work around this problem. Set the SQL_MODE variable, and now everything is fine.

    Thanks again.

    Reply

  3. thanks, this was helpful. it kind of defeats the purpose of having an auto increment field when you can’t get the thing to automatically increment . . .

    Reply

  4. @MrQue – Welcome 🙂

    @Diego – wow, that is another finding. I didn’t know it. Thanks.

    @andrew – Well, for an auto increment field, you needn’t mention it in the query in the first place. Your query can just be

    insert into products_notifications (customers_id, date_added) values (‘2′, now())

    Reply

  5. oops again. i’d gotten so in the habit of just writing

    insert into products_notifications values (‘’, ‘2′, now())

    that i forgot about the longer (proper?) way. thanks again, andrew

    Reply

  7. Thanks for your tutorial

    If I try your sql query than error appears
    insert into products_notifications (products_id, customers_id, date_added) values (’1′, ‘2′, now())

    products_id in my table is primery key

    and I make the following

    insert into products_notifications (products_id, customers_id, date_added) values (null, ‘2′, now())

    Reply

  8. Andrew, you would have to calculate the primary key value prior to inserting it into the database.

    The Logic would be:

    1) Get last inserted id from products_notifications table.

    2) Add 1 to it.

    3) Insert into products_notifications table.

    Reply

  13. It didn’t work. The PHP part. Do I need to change the SQL mode first? Because I don’t like to do that in case my next webhost insist on having it on. Any alternative? What is that error anyway?

    Reply

  14. I tried doing the recommended solution and nothing happened.

    I also tried to comment out the line by using # and still not working.

    Please help! quite a noobie at this!

    Reply

  15. Thank you very much.
    I was starting to chew on my nails since I couldn’t get PHP and MySQL to work on IIS7. Eventually when I managed to get the MySQL extension to load into PHP, I got this error. How frustrating.

    Reply

  16. @sean – Did it work for you finally? Please check whether your changes get reflected on the server by querying for the SQL_MODE again.

    Reply

  18. I receive the same error message(Out of range value adjusted for column…). But the weird thing is I only receive the error message if I am using the firefox browser. No error message for IE7.

    I already tried the suggestions above, ie. changing the query and the my.ini. But same error.

    Anyone please help? 😦

    Reply

  21. hello,

    problem 1: not everybody has root access or admin rights to change the sql mode.

    problem 2: may be, you need the server running in strict mode for other reasons

    problem 3: sometimes you have to process large formulars with many optional (and empty) fields, in this case you may have a query like this:

    insert into (autofield, field1, field2, field3) values (´´,´value1´, ´´, ´value3´)

    normally you have to fill the empty values with NULL.

    but there is still another possibility, you can tell the server to ignore the error with an query like

    insert IGNORE into (autofield, field1, field2, field3) values (´´, value1, ´´, ´value3´)

    see also: http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

    greets from switzerland 😉

    Reply

  23. @celia – my pleasure

    hint for all, which have to conform large projects with many “old styled” querys

    @linux you can use quanta webeditor for doing a helpfull job. if you load all scripts with affected querys into the editor, you can do a “search and replace” over all opened files. so you don’t have to edit every single file

    other ide’s may have an equal function

    Reply

  25. SET GLOBAL SQL_MODE=”;

    as simple as that, thank you very very much, saved from more hours of head banging 😉

    Reply

  36. Hi guys,

    I have the same problem but one thing is not clear. Is it 2 single quotes or a double quote after SET GLOBAL SQL_MODE= ?

    It seems to be one double quote but mysql does not let me enter this (a second double quote is needed to close the string?).

    Thanks for your help

    Reply

  42. A word of warning to those using this method at command line (or satement via phpMyAdmin or whatever):

    SET GLOBAL SQL_MODE=”;

    Note that this method will only work until you restart your MySQL server.

    I don’t reccommend this for a production server. Make sure you remove STRICT_TRANS_TABLES from the .ini file as mentioned by nicolaspar and then restart your MySQL server.

    Reply

  43. I would correct the the query you quoted from:

    insert into products_notifications (products_id, customers_id, date_added) values (’1′, ‘2′, now())

    to

    insert into products_notifications (customers_id, date_added) values (‘2′, now())

    This in case the products_id has auto_increment.

    Reply

  44. Hmmm, what’s wrong with using MySQL provided mechanisms?
    -Either leave out your autofield, or use the ‘DEFAULT’ keyword thusly:
    INSERT INTO `mytable` (`autofield`, `field1`, `field2`, `field3`) values (DEFAULT,’value1′, ”, ‘value3’);

    Reply

  46. The reason that I received this error is because I was trying to insert too large of a number into an int field. What helped me was modifying the table and column and turning it into a bigint.

    alter table table_name modify column_name bigint;

    worked for me

    Reply

  49. Excellent solution. I would have wasted many hours solving this but here everything is explained so well that it was solved in matters of minutes. Thanks to all . Comments at no. 21 are also quite useful

    Reply

  53. I used type casting as follows.

    create table temp_table (user_id int(10));

    insert into temp_table (user_id)
    values (”);
    Error: Out of range value adjusted for column ‘user_id’ at row 1

    SET @vUser_ID =”;
    insert into temp_table (user_id)
    values (if(@vUser_ID = ”, NULL, CAST(@vUser_ID AS SIGNED)));

    Statement: Executed Successfully
    Statement: 1 rows affected

    I feel changing SQL_MODE is not a good idea at all. It could impacts some where else.

    Reply

  54. another solution is you can change your data type,example you can change tinyint be int type,becouse every data type is have their own capacity;

    Reply

  55. You can set this on a session-by-session basis too, if you want to use different SQL_MODE values for different applications.

    SET SESSION SQL_MODE=’MYSQL40′;

    I run this query at the top of a common.php file which is included in all my scripts for a particular application. It seems to suppress the ‘Out of range value’ errors without breaking my other applications which are using the DB server’s default SQL_MODE values. (I assume it’s basically setting the MySQL session to emulate MySQL 4.0 behaviour.

    Reply

  58. I had the same problem with a column when I keep celphone numbers.
    The field that I defined had the properties (INT 10); I only change it to BIGINT and the problem was solved.

    Sometimes, maybe… the problem is the type of Field

    Reply

  66. I haven’t read through all the comments, but if you are writing a sql insert statement that has an auto-incremented id, simply exclude it from the insert statement. This id should be set up in the table’s structure to be primary key, no null, autoincrement

    Reply

  71. I have to agree, seen this and though, oops, but then realised all I had to do was change the type of field and bingo…

    Reply

  72. This doesn’t really have to do with the topic right now, but I must ask if you might know where I might find a decent captcha plugin that I can implement on my blog?? I’m utilising the same
    blog platform as yours and I’m having difficulty locating one?

    Reply

  73. It’s appropriate time to make some plans for the future and it is time to be happy. I’ve read this post and if I could I want to suggest you some interesting things or suggestions.
    Perhaps you could write next articles referring to this
    article. I want to read more things about it!

    Reply
  77. Pingback: mysql - Comment puis-je corriger les "en Dehors de la plage de la valeur ajustée pour la colonne' erreur?
  78. Pingback: How do I fix the 'Out of range value adjusted for column' error?

Leave a reply to العاب بنات Cancel reply