Friday, August 28, 2009

Out of range value adjusted for column error

This is a known bug of MySql5 which I got while customizing one of my PHP projects. In the older version of MySql offered lots of freedom to developers. Previously anything can insert/modify into its tables but it does not seem to be practical anymore.

No need to worry about the error. You can fix it easily. Below are the reason and solutions for this error...

Reason:
Let's see an example query:

Insert into users(id, name, country) values(‘’, ‘Morshed Alam’,’Bangladesh’);

In the above query “id” is an integer field and trying to insert empty string into it. That’s why MySql encountered the error.


Solutions:
  • Rewrite the query correctly. Below is the example to write above query correctly.

    Insert into users(id, name, country) values(‘1’, ‘Morshed Alam’,’Bangladesh’);
    or
    Insert into users(id, name, country) values(1, ‘Morshed Alam’,’Bangladesh’);

  • Login to mysql shell and off sql_mode by following command:
    SET GLOBAL SQL_MODE=”;

  • Revert your MySql into older version(ex. MySql 4)

Related link:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

No comments:

Post a Comment