I've got a database with tables like
CREATE TABLE `category_user` ( `user_id` int(10) UNSIGNED NOT NULL, `category_id` int(10) UNSIGNED NOT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Now, I'm transferig the database from local to a remote remote server.
The server is running MySQL 5.5.41 and gives me the error
#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
This would be accepted by MySQL >= 5.6, but it is not available on the server.
What I want to obtain is:
when a record is created, both created_at and updated_at will contain the current timestamp, when a record is updated, updated_at will contain the timestamp at the time of the update, while created_at doens't change.
How can I obtain the same behaviour which is compatible with MySQL 5.5?