MySQL Error 1293 - Incorrect table definition (TIMESTAMP)

MySQL Error 1293 FAQ: Help, I'm getting a MySQL incorrect table definition error message related to a TIMESTAMP column. How do I get past it?

If you ever get this MySQL Error 1293 (like I just did):

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

fear not, it may not be too bad. Let's take a look.

Only one TIMESTAMP field can default to "now"

I should say first of all, if you are trying to define more than one MySQL TIMESTAMP fields using CURRENT_TIMESTAMP or "default now", unfortunately that is bad, you can't do it in MySQL. (I don't know why, but as you can see from the error message, and the MySQL documentation, this isn't allowed in MySQL 5.x.)

Fixing the MySQL current_timestamp problem

However, if you already knew about that limitation, but you're still getting this "Error 1293" message, I think I can help you there. I just got this MySQL TIMESTAMP error when trying to create a table like this:

create table users (
  id int unsigned auto_increment not null primary key,
  username varchar(50) not null unique,
  password varchar(40) not null,
  email_address varchar(128) not null unique,
  email_sent timestamp not null,
  last_login timestamp not null default now()
) ENGINE = InnoDB;

As you can see, I'm declaring two MySQL TIMESTAMP fields in this database table, but since I'm only declaring one using the "default now" syntax, it should work, right? Unfortunately that turns out to be wrong.

When I first solved this problem I thought MySQL required the "CURRENT_TIMESTAMP (default now)" field to be declared before any other TIMESTAMP fields, so I solved my problem like this:

create table users (
  id int unsigned auto_increment not null primary key,
  username varchar(50) not null unique,
  password varchar(40) not null,
  email_address varchar(128) not null unique,
  last_login timestamp not null default now(),
  email_sent timestamp not null
) ENGINE = InnoDB;

A better solution

However, thanks to the first comment shown below, I learned that an easier/better solution to this problem is that you just need to declare a default value for your other MySQL timestamp field, like this:

create table users (
  id int unsigned auto_increment not null primary key,
  username varchar(50) not null unique,
  password varchar(40) not null,
  email_address varchar(128) not null unique,
  email_sent timestamp not null default 0,
  last_login timestamp not null default now()
) ENGINE = InnoDB;

This solution works exactly the same as my earlier solution, but it's a much better approach, because you don't have to mess up the order of your table fields to keep MySQL happy.

So, in summary, if you're trying to get past the dreaded MySQL Error 1293 "There can be only one TIMESTAMP column" error message, I hope these tips and examples are helpful.