Error

I was getting this error in adding a table in MYSQL

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

My new MYSQL table looks something like this.

create table table_name
(col1 int(5) auto_increment primary key, 
col2 varchar(300), 
col3 varchar(500), 
col4 int(3), 
col5 tinyint(2), 
col6 timestamp default current_timestamp, 
col7 timestamp default current_timestamp on update current_timestamp, 
col8 tinyint(1) default 0, 
col9 tinyint(1) default 1);

After some time of reading about changes in different MYSQL versions and some of the googling. I found out that there was some changes that were made in MYSQL version 5.6 over version 5.5. 


MYSQL 5.5

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.


MYSQL 5.6

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.


Resolution

If you are also getting the same error than check your mysql version and if it is 5.5 than follow below steps to upgrade your mysql.


## Check current Mysql version
mysql --version

sudo apt-get update ## Fetch the list of avaliable updated
sudo apt-get upgrade ## Upgrade the current packages
sudo apt-get dist-upgrade ## Install new updates.

## Take Mysql databases backup in case you need to restore databases after upgrade,
## Although Mysql automatically restores all the data.

mysqldump --lock-all-tables -u root -p --all-databases > mysql_backup.sql

## Now upgrade the mysql to 5.6
sudo apt-get install mysql-server-5.6

## Restore database if required
mysql -u root -p < mysql_backup.sql

## Check current Mysql version
mysql --version



If you want to add more comments to the article or you see any thing incorrect please write a comment below and we will surely get back to you.



Trending Articles


Incorrect table definition there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Google Betting Big on AI Future

How to solve IE/Edge CORS error ?

Infix To Postfix Conversion Using Stack in Javascript

Web Server vs Application Server Difference Explained

How to Use Facebook Profile Guard to Protect Your Profile Photos

SQL Overview

Binary Search Tree Insert node Implementation in Javascript