MySQL | Table Creation Error Related to DEFAULT timestamp

In MySQL 5.5 and earlier, creating a table with a single TIMESTAMP field that uses a DEFAULT value, as shown below, is not a problem.

CREATE TABLE table_a (
  time_a timestamp DEFAULT CURRENT_TIMESTAMP
)

However, an error occurs when two TIMESTAMP columns are used.

CREATE TABLE table_a (
  time_a timestamp DEFAULT CURRENT_TIMESTAMP,
  time_b timestamp DEFAULT CURRENT_TIMESTAMP
)

The error message is as follows.

Error Code : 1293
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

This means that only one TIMESTAMP column in a table can specify CURRENT_TIMESTAMP as its DEFAULT value.

In this case, specify DEFAULT CURRENT_TIMESTAMP for only one field and initialize the other column with CURRENT_TIMESTAMP when inserting data.

CREATE TABLE table_a (
  time_a timestamp DEFAULT CURRENT_TIMESTAMP,
  time_b timestamp
)

This restriction was removed in MySQL 5.6, so upgrading from mysql-server-5.5 to mysql-server-5.6 resolves the issue.

If you are using Ubuntu Linux, you can upgrade with the following commands.

$ sudo apt-get update
$ sudo apt-get install mysql-server-5.6

Even if you already have databases managed by MySQL 5.5, installing 5.6 as shown above automatically migrates the databases, so you do not need to dump and move them separately. However, make a backup before installation in case something unexpected happens.