skip to Main Content

Coming from years of experience with MS SQL Server I though it would be easy to add a simple nullable integer column to a MySQL database table using phpMyAdmin. I simply found the part of the UI that most resembled the part of SSMS where a new column is added to a table in SQL Server, clicked add column, entered a name, selected int, and null for default value.

enter image description here
The table itself is a posts table created by WordPress. When I click save I get an error saying

ALTER TABLE ‘wp_posts’ ADD ‘acserp’ INT NULL DEFAULT NULL AFTER ‘comment_count’;

MySQL said: Documentation

#1067 – Invalid default value for ‘post_date’

I really don’t see what adding an integer column has to do the the post_date column unless some row in the posts table has an invalid value for post_date and mySQL does some sort of checks to make sure that noting is wrong with the rest of the table before adding anything.

2

Answers


  1. Chosen as BEST ANSWER

    It looks like the answer is that you have to use raw SQL queries and tweak the SQL_Mode setting.

    SET SQL_MODE='ALLOW_INVALID_DATES'; ALTER TABLE wp_posts ADD new_table INT AFTER comment_count

    Invalid default value for 'create_date' timestamp field


  2. You forgot to define Length/Value of the integer field. It is the third column from the left. Give value to it and your issue will be resolved. Read this for more information.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search