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';