Description:
If server uses SQL_MODE="TRADITIONAL" mysqlbackup fails to create backup_history table with error: "mysqlbackup: Warning: Backup Logging: Create mysql.backup_history table query failed with error 1067: Invalid default value for 'start_time'. This backup operation cannot be logged."
Create table for bakup_history clearly shows the problem:
CREATE TABLE IF NOT EXISTS mysql.backup_history(
...
`start_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`end_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
...
How to repeat:
mysql> SET GLOBAL SQL_MODE='TRADITIONAL';
shell> mysqlbackup OPTIONS
...
mysqlbackup: Warning: Backup Logging: Create mysql.backup_history table query failed with error 1067: Invalid default value for 'start_time'. This backup operation cannot be logged.
mysqlbackup: Warning: Backup Logging: MySQL server cannot create prepared statement for backup history table. Failed with error Table 'mysql.backup_history' doesn't exist. This backup operation cannot be logged.
mysqlbackup: start_lsn: 5294236160
mysqlbackup: incremental_base_lsn: 5294236249
mysqlbackup: end_lsn: 5294239324
mysqlbackup: Warning: Backup Logging: Execution of prepared statement for inserting a row into backup history table failed with error Unknown prepared statement handler (0) given to mysqld_stmt_execute. This backup operation cannot be logged.
110128 18:49:20 mysqlbackup: mysqlbackup completed OK!
Suggested fix:
Either fix CREATE TABLE statement or temporary modify SQL_MODE when create backup_history table.
