One of the problems that you encounter when you deploy your program, the MySQL definition is as follows:
Copy Code code as follows:
CREATE TABLE ' Example ' (
' ID ' INTEGER UNSIGNED not NULL auto_increment,
' Created ' TIMESTAMP not NULL DEFAULT Current_timestamp,
' LastUpdated ' TIMESTAMP not NULL on UPDATE Current_timestamp,
PRIMARY KEY (' id ')
) Engine=innodb;
This is the SQL I extracted from the project, on the test machine everything is normal, but deployed to the production machine MySQL error:
Copy Code code as follows:
ERROR 1293 (HY000): incorrect table definition; There can be only one TIMESTAMP column with Current_timestamp in DEFAULT or on UPDATE clause.
It means there can only be one timestamp column with Current_timestamp, but why is there no problem with the local test? Locally tested machine installed MySQL version 5.6.13, and the production machine is installed on the 5.5 version, after searching the network to know the difference between the two versions of timestamp processing is:
In the MySQL 5.5 document there is this passage:
Copy Code code as follows:
One TIMESTAMP column in a table can have the current TIMESTAMP as the default value for initializing the column, as the AU To-update value, or both. It isn't possible to have the current timestamp being the default for one column and auto-update value for another Column.
The following changes were made in the MySQL 5.6.5:
Copy Code code as follows:
Previously, at most one TIMESTAMP column per table could is automatically initialized or updated to the current date and T Ime. This restriction has been lifted. Any TIMESTAMP column definition can have no combination of DEFAULT current_timestamp and on UPDATE current_timestamp Ses. In addition, the clauses now can is used with DATETIME column definitions. For the more information, the Automatic initialization and updating for TIMESTAMP and DATETIME.
Depending on the solution on the web, triggers can be used instead:
Copy Code code as follows:
CREATE TABLE ' Example ' (
' ID ' INTEGER UNSIGNED not NULL auto_increment,
' Created ' TIMESTAMP not NULL DEFAULT Current_timestamp,
' LastUpdated ' DATETIME not NULL,
PRIMARY KEY (' id ')
) Engine=innodb;
DROP TRIGGER IF EXISTS ' Update_example_trigger ';
DELIMITER//
CREATE TRIGGER ' Update_example_trigger ' before update on ' example '
For each ROW SET NEW, ' lastupdated ' = Now ()
//
DELIMITER;