Database error when enabling data logging.

When enabling the (beta) database logging, I received the following error because civicrm tried to create table "log_civicrm_job_log" with two "DEFAULT CURRENT_TIMESTAMP" columns:

Sorry. A non-recoverable error has occurred.
DB Error: unknown error
Error Details
Database Error Code: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause, 1293
Additional Details:

Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => handle
)

[code] => -1
[message] => DB Error: unknown error
[mode] => 16
[debug_info] => CREATE TABLE `civicrm`.log_civicrm_job_log (
`id` int(10) unsigned NOT NULL COMMENT 'Job log entry Id',
`domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this scheduled job for',
`run_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Log entry date',
`job_id` int(10) unsigned DEFAULT NULL COMMENT 'Pointer to job id - not a FK though, just for logging purposes',
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title of the job',
`command` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Full path to file containing job script',
`description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title line of log entry',
`data` text COLLATE utf8_unicode_ci COMMENT 'Potential extended data for specific job run (e.g. tracebacks).',

log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
log_conn_id INTEGER,
log_user_id INTEGER,
log_action ENUM('Initialization', 'Insert', 'Update', 'Delete')
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci [nativecode=1293 ** Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause]
[type] => DB_Error
[user_info] => CREATE TABLE `civicrm`.log_civicrm_job_log (
`id` int(10) unsigned NOT NULL COMMENT 'Job log entry Id',
`domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this scheduled job for',
`run_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Log entry date',
`job_id` int(10) unsigned DEFAULT NULL COMMENT 'Pointer to job id - not a FK though, just for logging purposes',
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title of the job',
`command` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Full path to file containing job script',
`description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title line of log entry',
`data` text COLLATE utf8_unicode_ci COMMENT 'Potential extended data for specific job run (e.g. tracebacks).',

log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
log_conn_id INTEGER,
log_user_id INTEGER,
log_action ENUM('Initialization', 'Insert', 'Update', 'Delete')
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci [nativecode=1293 ** Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause]
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TABLE `civicrm`.log_civicrm_job_log (
`id` int(10) unsigned NOT NULL COMMENT 'Job log entry Id',
`domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this scheduled job for',
`run_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Log entry date',
`job_id` int(10) unsigned DEFAULT NULL COMMENT 'Pointer to job id - not a FK though, just for logging purposes',
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title of the job',
`command` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Full path to file containing job script',
`description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Title line of log entry',
`data` text COLLATE utf8_unicode_ci COMMENT 'Potential extended data for specific job run (e.g. tracebacks).',

log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
log_conn_id INTEGER,
log_user_id INTEGER,
log_action ENUM('Initialization', 'Insert', 'Update', 'Delete')
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci [nativecode=1293 ** Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause]"]
)

Return to home page.