How do I add a new column to MySQL via phpMyAdmin?

Question

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.

How did you create the rest of the table, and are there any triggers in place? — Strawberry, Mar 21 '20 at 05:52

score 1 · Answer 1 · answered Mar 22 '20 at 21:33

1

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

answered Mar 22 '20 at 21:33

CopBlaster

21
6

score -1 · Answer 2 · answered Mar 21 '20 at 05:48

-1

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.

answered Mar 21 '20 at 05:48

mail2bapi

1,547
1
12
18

Please review https://stackoverflow.com/questions/5634104/what-is-the-size-of-column-of-int11-in-mysql-in-bytes – P.Salmon Mar 21 '20 at 07:21

How do I add a new column to MySQL via phpMyAdmin?

2 Answers2