Corriger le bug des champs created_at et updated_at

Les tables qui contiennent les champs created_at et updated_at n’ont pas la bonne structure dans certains Magento natifs. En effet, le champ created_at est en valeur par défaut CURRENT_TIMESTAMP, ce qui est correct. Mais il a aussi l’option ON UPDATE CURRENT_TIMESTAMP qui n’a aucune raison d’être présent pour ce champ. Pour le champ updated_at, la valeur par défaut est 0000-00-00 00:00:00 et il n’y a aucune option.

Voici ce que l’on va faire :
– Pour les champs created_at, valeur par defaut CURRENT_TIMESTAMP et pas d’option
– Pour les champs updated_at, valeur par defaut 0000-00-00 00:00:00 et l’option ON UPDATE CURRENT_TIMESTAMP

Vous devez avoir au minimum la version 5.6.5 de MySQL pour pouvoir avoir cette structure, sinon vous aurez le message d’erreur :
#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Pour certaines tables, il n’y a que le champ de création, et des fois ce ne sont pas les mêmes noms de colonne.
Pour vous aider voici les requêtes SQL pour les tables concernées.

Si vous remarquez d’autres tables, d’autres colonnes, n’hésitez pas à mettre un commentaire pour que je mette à jour l’article ! Pour info, j’ai lancé ces requêtes sur un Magento Enterprise 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';