Tables which have created_at and updated_at columns are not correct on some native Magento.
created_at field has got CURRENT_TIMESTAMP as default value, and this is correct. But it also has an option ON UPDATE CURRENT_TIMESTAMP which is totally wrong and modify on every update the created_at value. For the updated_at field, default value is 0000-00-00 00:00:00 and there is no option.

We will do these following changes :
– For created_at fields, default value CURRENT_TIMESTAMP and no option
– Fro updated_at fields, default value 0000-00-00 00:00:00 and option ON UPDATE CURRENT_TIMESTAMP

You need to have at least the 5.6.5 version of MySQL to have multiples columns with CURRENT_TIMESTAMP action, else you will have this error :
#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Some tables haven’t got the creation fields, and sometimes fieldnames are differents. In order to help you, I give you the SQL requests to do the changes.

If you find other tables or other columns to update, write a comment and I will edit this post. For your information, I launch these requests on Magento Enterprise Edition 1.14

ALTER TABLE  `customer_entity` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `customer_entity` CHANGE  `updated_at`  `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';

ALTER TABLE  `cron_schedule` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';

ALTER TABLE  `api_user` CHANGE  `created`  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `api_user` CHANGE  `modified`  `modified` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';

ALTER TABLE  `admin_user` CHANGE  `created`  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `admin_user` CHANGE  `modified`  `modified` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';

ALTER TABLE  `customer_address_entity` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `customer_address_entity` CHANGE  `updated_at`  `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';

ALTER TABLE  `downloadable_link_purchased` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `downloadable_link_purchased` CHANGE  `updated_at`  `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';

ALTER TABLE  `downloadable_link_purchased_item` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `downloadable_link_purchased_item` CHANGE  `updated_at`  `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';

ALTER TABLE  `index_event` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';

ALTER TABLE  `eav_entity` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `eav_entity` CHANGE  `updated_at`  `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';

ALTER TABLE  `sales_flat_quote_address` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `sales_flat_quote_address` CHANGE  `updated_at`  `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';

ALTER TABLE  `sales_flat_quote` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `sales_flat_quote` CHANGE  `updated_at`  `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';

ALTER TABLE  `sales_flat_quote_address_item` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `sales_flat_quote_address_item` CHANGE  `updated_at`  `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';

ALTER TABLE  `sales_flat_quote_payment` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `sales_flat_quote_payment` CHANGE  `updated_at`  `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';

ALTER TABLE  `sales_flat_quote_shipping_rate` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `sales_flat_quote_shipping_rate` CHANGE  `updated_at`  `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';

ALTER TABLE  `sales_recurring_profile` CHANGE  `created_at`  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  'Created At';
ALTER TABLE  `sales_recurring_profile` CHANGE  `updated_at`  `updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00' COMMENT  'Updated At';
Correct created_at and updated_at columns issue
Share on FacebookTweet about this on TwitterShare on Google+Email this to someone
Tagged on:         

Leave a Reply

Your email address will not be published. Required fields are marked *

We use cookies to ensure that we give you the best experience on our website.
Ok