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