Out of range value adjusted for column error

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. 🙂

Advertisements

76 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.

  2. 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 . . .

  3. @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())

  4. 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

  5. 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())

  6. 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.

  7. 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!

  8. 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.

  9. 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? 😦

  10. 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 😉

  11. @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

  12. SET GLOBAL SQL_MODE=”;

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

  13. 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

  14. 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.

  15. 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.

  16. 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’);

  17. 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

  18. 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

  19. 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.

  20. 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;

  21. 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.

  22. 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

  23. 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

  24. 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…

  25. 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?

  26. 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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s