A web developer’s diary

October 1, 2006

Out of range value adjusted for column error

Filed under: MYSQL,PHP — Bindu @ 7:23 am

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. :)

About these ads

75 Comments »

  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.

    Comment by MrQue — October 5, 2006 @ 4:14 pm | Reply

  2. Hi,
    I try this and got sucess
    It comments the line in my.ini
    #sql-mode …

    Good luck ;D
    Diego

    Comment by Diego — October 9, 2006 @ 6:45 pm | 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 . . .

    Comment by andrew — October 11, 2006 @ 3:07 pm | 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())

    Comment by Bindu — October 12, 2006 @ 5:09 am | 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

    Comment by andrew — October 12, 2006 @ 3:09 pm | Reply

  6. thanks a lot!!!!!

    Comment by angelica — October 29, 2006 @ 7:47 pm | 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())

    Comment by Andrew — November 2, 2006 @ 9:30 am | 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.

    Comment by Bindu — November 2, 2006 @ 10:05 am | Reply

  9. Remove only value “STRICT_TRANS_TABLES” in directive “sql-mode” (file my.ini)

    Thk.

    Comment by nicolaspar — November 15, 2006 @ 3:08 pm | Reply

  10. Thanks a bunch, setting the SQL_MODE worked like a charm!

    Comment by Fredrik — January 3, 2007 @ 12:58 pm | Reply

  11. Thanks a lot.
    It worked for me by setting the sql mode.

    Cheers.

    Comment by Aran — January 10, 2007 @ 12:20 pm | Reply

  12. Thanks a lot! I got this error in Streber Project Manager.

    I was trying to add a Automação Comercial new Project.

    Comment by Lucio Antoniolo — January 10, 2007 @ 1:47 pm | 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?

    Comment by Computer Programmer — January 25, 2007 @ 7:00 pm | 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!

    Comment by sean — February 20, 2007 @ 1:03 pm | 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.

    Comment by Antonie Potgieter — February 23, 2007 @ 1:25 pm | 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.

    Comment by Celia — March 13, 2007 @ 5:16 am | Reply

  17. Thank you! Thank you!!! Thank you!!!!!

    Comment by Pablo — March 26, 2007 @ 12:28 am | 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? :(

    Comment by hello — March 28, 2007 @ 6:59 am | Reply

  19. Probably a cache problem??

    Comment by Bindu — March 28, 2007 @ 7:37 am | Reply

  20. Thanks for that. :)

    Comment by Brendan — March 29, 2007 @ 1:04 am | 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 ;-)

    Comment by uwe127 — April 13, 2007 @ 3:22 am | Reply

  22. @Brendan – Welcome.

    @uwe127 – Thats amazing :) Thanks for sharing it.

    Comment by Celia — April 13, 2007 @ 4:48 am | 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

    Comment by uwe127 — April 14, 2007 @ 9:22 am | Reply

  24. Muchas gracias….. i dont speak english….

    thanks…. llevaba mas de un día con ese problema.

    Comment by Javier Lopez — April 16, 2007 @ 12:49 pm | Reply

  25. SET GLOBAL SQL_MODE=”;

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

    Comment by Neil — July 14, 2007 @ 3:00 pm | Reply

  26. Many thanks!

    Comment by Hemant — August 28, 2007 @ 7:19 pm | Reply

  27. Thanks .. removing the STRICT_TRANS_TABLES option in .ini worked.

    Comment by Vikram — November 3, 2007 @ 4:50 am | Reply

  28. Very good! I didn’t struggle much to find this on google and worked first time!

    Comment by Andre — December 17, 2007 @ 6:18 am | Reply

  29. thanks !!!

    Comment by whirp — December 28, 2007 @ 11:54 am | Reply

  30. Thanks so much. Did’nt have to struggle to find the solution.

    Comment by breeze — February 19, 2008 @ 12:43 pm | Reply

  31. []its the best post from you, thanks a lot[]

    Comment by SamaraRegion — March 1, 2008 @ 7:41 pm | Reply

  32. MAN you Saved me !!!!
    Thank you very much

    Comment by Rajavanya — March 25, 2008 @ 5:39 am | Reply

  33. I thought it was something wrong with deadlock

    Comment by Rajavanya — March 25, 2008 @ 5:39 am | Reply

  34. Thank you very much, you have saved my lots of time.

    Thanks again.

    Comment by Ginani Ahmad — April 17, 2008 @ 5:41 am | Reply

  35. Thanks man,

    I have altered the table that’s way i got that error. but i have not wasted so much time to find the solution because of you,

    thank you very much.

    Comment by somappa batikeri — May 10, 2008 @ 7:28 am | 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

    Comment by mick — May 16, 2008 @ 5:49 am | Reply

  37. Thanks buddy, it’s working

    Comment by Bhavin — May 26, 2008 @ 12:08 pm | Reply

  38. @ uwe127 (Comment No.21)

    You saved my life!
    As you mentioned, I didn’t have rights to change the server variables.
    Your suggestion worked out too well!
    Thanks a lot!

    Comment by Nirmal Natarajan — June 16, 2008 @ 11:41 am | Reply

  39. Great. It works! I’ve been looking for this solution for awhile. Thanks!

    Comment by Fred — June 25, 2008 @ 10:16 am | Reply

  40. Hello,

    I’m with problems when try to insert negative values (like -1 or -2 ) on a integer field.

    Could you help-me?

    Comment by Aang — June 26, 2008 @ 5:31 pm | Reply

  41. @Diego & @Bindu Thank you very much. Saved a lot of time!!! 100% working solution

    Comment by Vibhor — June 27, 2008 @ 10:05 am | 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.

    Comment by NetWebLogic — August 20, 2008 @ 3:59 pm | 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.

    Comment by Benyi Robert — October 27, 2008 @ 1:50 pm | 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′);

    Comment by Codename: Steeve Knight — January 29, 2009 @ 8:11 pm | Reply

  45. Hi,

    I was not finding the reason. and this helped me a lot in solving the problem.

    Comment by Swatirani Senapati — March 7, 2009 @ 11:09 am | 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

    Comment by Matt — March 11, 2009 @ 3:27 pm | Reply

  47. I have wasted 2 days, i got this sql_mode solution now everything is okay

    Comment by Sumit — March 21, 2009 @ 11:05 am | Reply

  48. Thank U!!!!

    even I wasted a day for this

    HaPpY CoDiNg

    Comment by Chandralekha — April 23, 2009 @ 6:20 am | 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

    Comment by websmith — July 4, 2009 @ 10:29 am | Reply

  50. Thanks to #46 !!!!!!!!!

    Comment by Hiya ! — September 22, 2009 @ 3:09 pm | Reply

  51. coomooo se donaaa???

    Comment by lloololo — February 17, 2010 @ 4:01 pm | Reply

  52. Use CAST() for proper type casting. It works fine without touching any parameter.

    Comment by Rajesh Patil — March 9, 2010 @ 10:26 pm | 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.

    Comment by Rajesh Patil — March 9, 2010 @ 10:50 pm | 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;

    Comment by charisma — May 18, 2010 @ 4:51 am | 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.

    Comment by pode — May 18, 2010 @ 3:45 pm | Reply

  56. I just changed the coloumn type from smallint to bigint!!! worked like a charm for those who dont have SUPER privileges

    Comment by Will — November 29, 2010 @ 11:57 pm | Reply

  57. Anywhere I can find step by step instructions on how to do this? I’m new sql and can’t get it to work. Thanks!

    Comment by anon — December 15, 2010 @ 5:17 pm | 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

    Comment by Klama-town — January 26, 2011 @ 4:57 pm | Reply

  59. c4LCsR http://gdjI3b7VaWpU1m0dGpvjRrcu9Fk.com

    Comment by frenky — May 7, 2011 @ 11:05 pm | Reply

  60. thank you soooo much!!! I really appreciate this help! I spent days trying to fix this issue.
    you are awsome!

    Comment by gio — May 10, 2011 @ 7:30 pm | Reply

  61. Where did you go to university? preteen ass
    jfoh

    Comment by Sfvxtwbb — September 7, 2011 @ 4:25 am | Reply

  62. Have you got a telephone directory? free lolita preteen pics vrl

    Comment by Glolyfqd — September 9, 2011 @ 12:49 am | Reply

  63. I quite like cooking Hooters Teen Models
    =-]]]

    Comment by Carafytr — September 24, 2011 @ 7:43 pm | Reply

  64. The manager 3d Bikini Models
    9095

    Comment by Hnpwglcx — September 24, 2011 @ 9:13 pm | Reply

  65. Books in PDF format, free to download
    http://goo.gl/oQ9pI

    Technology books here

    Comment by jhonnybook — January 1, 2012 @ 5:07 am | 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

    Comment by Chris — March 12, 2012 @ 4:39 pm | Reply

  67. Could I ask who’s calling? http://nedubinumige.blog.free.fr/ preteen porn 16 omg i want that pussy and more and more my peenis is bigger and im not old ass hell but i get you mamma hot asss babe

    Comment by Xgfaqqsq — May 2, 2012 @ 11:31 am | Reply

  68. Remove card http://ehioujyti.de.tl models teen pedo one of the best ever… fuck everything is just soo sexual in that vids.. FAv if I hadn’t done it yet.

    Comment by Wmuhoanp — May 3, 2012 @ 1:47 pm | Reply

  69. I’ve got a very weak signal http://siraoube.de.tl sexomodelo this little gal can suck a dick, wow! any idea if this gal takes it in her crap hole?

    Comment by Nuecjiri — May 3, 2012 @ 4:36 pm | Reply

  70. A insightful post there mate ! Cheers for posting .

    Comment by site — May 27, 2012 @ 4:54 pm | 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…

    Comment by Reconix — January 7, 2013 @ 3:48 pm | 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?

    Comment by hachoir — May 18, 2013 @ 9:50 am | 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!

    Comment by kitchen cabinetry — August 1, 2013 @ 2:34 am | Reply

  74. Thank you so much, huge help!!

    Comment by Ricardo — November 8, 2013 @ 10:13 am | Reply

  75. Provocative depict shots
    http://selfshots.erolove.in/?summon forth_jewel
    erotic erotic couples massage emma watson erotic bondage

    Comment by bridgettpo16 — November 28, 2013 @ 4:41 am | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: