How do I disable DEFAULT and ON UPDATE when creating a table

Question

After hours of debugging my PHP code, because the dates were being incorrectly stored, I realized that XAMPP was automatically adding a DEFAULT attribute and a ON UPDATE attribute to my date_added column.

I have this table:

CREATE TABLE test_table(
    date_added          timestamp
                        NOT NULL,

    last_updated        timestamp
                        NOT NULL
);

As you can see, I am clearly not setting a DEFAULT value or a ON UPDATE tag on my table creation code. However, if I go into phpmyadmin and look at the structure of this table, it looks like this:

I tried to click on "change" and set DEFAULT to None and ON UPDATE to blank but it doesn't change it. Any suggestions on how I can remove these things?

score 1 · Answer 1 · answered Mar 22 '19 at 08:24

This is because of the setting of the system variable explicit_defaults_for_timestamp, which in MySQL < 8.0.2, defaults to OFF. When it is OFF,

If explicit_defaults_for_timestamp is disabled, the server enables the nonstandard behaviors and handles TIMESTAMP columns as follows:

TIMESTAMP columns not explicitly declared with the NULL attribute are automatically declared with the NOT NULL attribute. Assigning such a column a value of NULL is permitted and sets the column to the current timestamp.

The first TIMESTAMP column in a table, if not explicitly declared with the NULL attribute or an explicit DEFAULT or ON UPDATE attribute, is automatically declared with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

TIMESTAMP columns following the first one, if not explicitly declared with the NULL attribute or an explicit DEFAULT attribute, are automatically declared as DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

Depending on whether strict SQL mode or the NO_ZERO_DATE SQL mode is enabled, a default value of '0000-00-00 00:00:00' may be invalid. Be aware that the TRADITIONAL SQL mode includes strict mode and NO_ZERO_DATE. See Section 5.1.11, “Server SQL Modes”.

See the manual.

This question discusses how to change the setting.

is there a way to set this using php (when I make a mysqli connection) — Paul Shan, Mar 22 '19 at 08:30
You could try sending a query which sets the variable when you make the connection. — Nick, Mar 22 '19 at 08:59
`SET GLOBAL explicit_defaults_for_timestamp = 1`. If you can't do that, you would need to edit the MySQL configuration file as described in the question I linked. — Nick, Mar 23 '19 at 00:51

How do I disable DEFAULT and ON UPDATE when creating a table

1 Answers1