Incorrect table definition there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
Category : TECH Author : Mannu Malhotra Date : Fri Jul 29 2016 Views : 2263

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

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
Disclaimer: The above content reflect author’s personal views and do not reflect the views of OYEWIKI. Neither OYEWIKI nor any person/organization acting on its behalf is liable to accept any legal liability/responsibility for any error/mislead in this information or any information available on the website. This website in no way accepts the responsibility for any loss, injury, damage, discomfort or inconvenience caused as a result of reliance on any information provided on this website.
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.