Search code examples
phpmysqldefault

mysql default value errors on new server

I have a strange issue. I have copied PHP source code and MySQL database (phpMyAdmin standard export-import) from one server to another. On the old server everything worked fine but on the new server I have database errors.

Field 'xxx' doesn't have a default value

I know how to set the default value or need to pass value if null is off. But why did it work on the old server? Did I miss something during export-import? There are no triggers in the old database. Maybe the configuration is different?

Solution

  • MySQL is most likely in STRICT mode. Try running

    SET GLOBAL sql_mode=''

    or edit your my.cnf to make sure you aren't setting STRICT_ALL_TABLES or the like.

    OR, change table definition and add default value for the field.

    For example:

    ALTER TABLE `tableA` CHANGE `field` `field` TEXT NULL

    or, give it a default value as empty string:

    ALTER TABLE `tableA` CHANGE `field` `field` TEXT NOT NULL DEFAULT ''