Some corrections fail with DB Error: syntax error

[magnolia61]

Hi there, Thanks for this extension. It worked for some but not for other corrections. The ones that failed with a syntax error are below. I'm not really able to debug and understand what goes wrong, but I thought you'd might want to know so that's why I report it here. I am more than happy to supply more info when needed.

As an example , for the civicrm_cache.created_date the dblog says: $Fatal Error Details = Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => exceptionHandler ) [code] => -2 [message] => DB Error: syntax error [mode] => 16 [debug_info] => ALTER TABLE civicrm_cache CHANGE created_date created_date TIMESTAMPNULL DEFAULT CURRENT_TIMESTAMP [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMPNULL DEFAULT CURRENT_TIMESTAMP' at line 1] [type] => DB_Error [user_info] => ALTER TABLE civicrm_cache CHANGE created_date created_date TIMESTAMPNULL DEFAULT CURRENT_TIMESTAMP [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMPNULL DEFAULT CURRENT_TIMESTAMP' at line 1] [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="ALTER TABLE civicrm_cache CHANGE created_date created_date TIMESTAMPNULL DEFAULT CURRENT_TIMESTAMP [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIMESTAMPNULL DEFAULT CURRENT_TIMESTAMP' at line 1]"] )

The current structure output for that table is:

CREATE TABLE `civicrm_cache` (
  `id` int(10) UNSIGNED NOT NULL,
  `group_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'group name for cache element, useful in cleaning cache elements',
  `path` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Unique path name for cache element',
  `data` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci COMMENT 'data associated with this path',
  `component_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'Component that this menu item belongs to',
  `created_date` datetime DEFAULT NULL COMMENT 'When was the cache item created',
  `expired_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Other tabled that failes are:

"civicrm_cache.created_date" - Change data type from DATETIME to TIMESTAMP (CRM-9683) "civicrm_mailing_event_bounce.time_stamp" - Change data type from DATETIME to TIMESTAMP (CRM-9683) "civicrm_mailing_event_confirm.time_stamp" - Change data type from DATETIME to TIMESTAMP (CRM-9683) "civicrm_mailing_event_delivered.time_stamp" - Change data type from DATETIME to TIMESTAMP (CRM-9683) "civicrm_mailing_event_forward.time_stamp" - Change data type from DATETIME to TIMESTAMP (CRM-9683) "civicrm_mailing_event_opened.time_stamp" - Change data type from DATETIME to TIMESTAMP (CRM-9683) "civicrm_mailing_event_reply.time_stamp" - Change data type from DATETIME to TIMESTAMP (CRM-9683) "civicrm_mailing_event_subscribe.time_stamp" - Change data type from DATETIME to TIMESTAMP (CRM-9683) "civicrm_mailing_event_trackable_url_open.time_stamp" - Change data type from DATETIME to TIMESTAMP (CRM-9683) "civicrm_mailing_event_unsubscribe.time_stamp" - Change data type from DATETIME to TIMESTAMP (CRM-9683) "civicrm_mailing_abtest.created_date" - Change data type from DATETIME to TIMESTAMP (CRM-9683) "civicrm_subscription_history.date" - Change data type from DATETIME to TIMESTAMP (CRM-21157) "civicrm_activity.created_date" - Fill in missing values using "civicrm_log" (CRM-20958) "civicrm_activity.modified_date" - Fill in missing values using "civicrm_log" (CRM-20958) "civicrm_case.created_date" - Fill in missing values using the activity log (CRM-20958) "civicrm_case.modified_date" - Fill in missing values using the activity log (CRM-20958)

[MegaphoneJon]

This issue is fixed by either #2 or #3.

[totten]

Merged the fixes from above.