InnoDB: Assertion failure while trying to prepare.

MySQL & MariaDB Percona XtraBackup
1

innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

I move the backup file to the new server.

I then try to prepare the backup with the data directory empty and I get this error:

[mysql@db1 2013-11-17_22-30-02]$ innobackupex --apply-log /home/mysql/backups/full_backup

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints “completed OK!”.

131122 13:53:57 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file=“/home/mysql/backups/full_backup/backup-my.cnf” --defaults-group=“mysqld” --prepare --target-dir=/home/mysql/backups/full_backup --tmpdir=/tmp

xtrabackup_55 version 2.1.4 for Percona Server 5.5.31 Linux (x86_64) (revision id: 656)
xtrabackup: cd to /home/mysql/backups/full_backup
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2572288, start_lsn=(1280062692691)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2572288
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2572288
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
131122 15:18:55 InnoDB: The InnoDB memory heap is disabled
131122 15:18:55 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131122 15:18:55 InnoDB: Compressed tables use zlib 1.2.3
131122 15:18:55 InnoDB: Initializing buffer pool, size = 100.0M
131122 15:18:55 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
131122 15:18:55 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait…
131122 15:18:55 InnoDB: Error: all log files must be created at the same time.
131122 15:18:55 InnoDB: All log files must be created also in database creation.
131122 15:18:55 InnoDB: If you want bigger or smaller log files, shut down the
131122 15:18:55 InnoDB: database and make sure there were no errors in shutdown.
131122 15:18:55 InnoDB: Then delete the existing log files. Edit the .cnf file
131122 15:18:55 InnoDB: and start the database again.
xtrabackup: innodb_init(): Error occured.
innobackupex: Error:
innobackupex: ibbackup failed at /home/mysql/percona-xtrabackup-2.1.4/bin/innobackupex line 416.

2

Not sure if this is needed, but this is my.cnf file. The

[mysqld]
sql_mode=STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERR OR_FOR_DIVISION_BY_ZERO
max_allowed_packet = 134217728
default-storage-engine=INNODB
group_concat_max_len=4294967295
expire_logs_days=7
max_connections=1024
open_files_limit=4096
log-bin=binarylog
slow_query_log
log-error=log.err
port=3306
datadir=/opt/mysql/data
pid-file=/opt/mysql/pid/mysql.pid
basedir=/opt/mysql
binlog-format=MIXED
innodb_lock_wait_timeout = 100

[mysqld_safe]
sql_mode=STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERR OR_FOR_DIVISION_BY_ZERO
max_allowed_packet = 134217728
default-storage-engine=INNODB
group_concat_max_len=4294967295
expire_logs_days=7
max_connections=1024
open_files_limit=4096
log-bin=binarylog
slow_query_log
log-error=log.err
port=3306
datadir=/opt/mysql/data
pid-file=/opt/mysql/pid/mysql.pid
basedir=/opt/mysql
ledir=/opt/mysql/bin
binlog-format=MIXED

[mysqladmin]
socket=/opt/mysql/data/mysql.sock

[client]
socket=/opt/mysql/data/mysql.sock
3

Have you done any changes on transaction log files? This is what the output logs of the innobackupex is pointing out. First you need to check if you have both ib_logfile0 and ib_logfile1 in the datadir. Another point is, it seems that you’re trying to restore using a wrong command, have a look on this page: [url]Percona XtraBackup

WB

4

Thank you WB, I appreciate the help. I have tried the copy command, but for some reason it gives a different error so I thought I had to prepare the backup. The below is what I’m following. So that’s why I’m doing the repair. The backup is taken while the database is running and then I move the backup to the new server. I made sure the data folder was empty and then I try to prepare the folder.

[URL=“Creating a Backup with innobackupex”]http://www.percona.com/doc/percona-x...ackup_ibk.html[/URL]

[URL=“Preparing a Full Backup with innobackupex”]http://www.percona.com/doc/percona-x...ackup_ibk.html[/URL]

[URL=“Restoring a Full Backup with innobackupex”]http://www.percona.com/doc/percona-x...ackup_ibk.html[/URL]

When i try to just do a copy before the prepare this is the error I get when trying to start the database.

131123 19:38:01 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data
131123 19:38:02 [Note] Plugin 'FEDERATED' is disabled.
131123 19:38:02 InnoDB: The InnoDB memory heap is disabled
131123 19:38:02 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
131123 19:38:02 InnoDB: Compressed tables use zlib 1.2.3
131123 19:38:02 InnoDB: Using Linux native AIO
131123 19:38:02 InnoDB: Initializing buffer pool, size = 128.0M
131123 19:38:02 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 2883584 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
131123 19:38:02 [ERROR] Plugin 'InnoDB' init function returned error.
131123 19:38:02 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
131123 19:38:02 [ERROR] Unknown/unsupported storage engine: INNODB
131123 19:38:02 [ERROR] Aborting

If I delete ibdata1, ib_logfile0, and ib_logfile1 and restart I can get mysqld to start, but then I get the following errors.

[ERROR] Cannot find or open table opentaps_ogsys_utf8/ships from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

If I try to prepare after a successful startup of mysqld with the can’t open table error I get these errors:

xtrabackup_55 version 2.1.4 for Percona Server 5.5.31 Linux (x86_64) (revision id: 656)
xtrabackup: cd to /home/mysql/backups/2013-11-17_22-30-02
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2572288, start_lsn=(1280062692691)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2572288
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2572288
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
131123 20:00:01 InnoDB: The InnoDB memory heap is disabled
131123 20:00:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131123 20:00:01 InnoDB: Compressed tables use zlib 1.2.3
131123 20:00:01 InnoDB: Initializing buffer pool, size = 100.0M
131123 20:00:01 InnoDB: Completed initialization of buffer pool
131123 20:00:01 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1280062692691
131123 20:00:01 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Doing recovery: scanned up to log sequence number 1280064983722 (100 %)
131123 20:00:01 InnoDB: Assertion failure in thread 47556714634320 in file fsp0fsp.c line 2923
InnoDB: Failing assertion: space != 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
innobackupex: Error:
innobackupex: ibbackup failed at /home/mysql/percona-xtrabackup-2.1.4/bin/innobackupex line 416.

For the above error I made sure to stop and start mysqld without errors other than the open table error, but it doesn’t seem to want to work anyway I try it.

5

So, there is a interesting point here! The file you must move/remove is just the transaction log, that is, just the ib_logfileX. The ibdata1 is the shared tablespace and perhaps the container of all of your database’s data - at least it contains the database’s metadata - and it CANNOT be removed. Try move/remove just the transaction logs and restart mysqld. This way mysqld will create new files and the life shall goes on better. Try that and give a feedback, WB

6

So I think I found out why this is happening…

When the innobackupex runs on the server I’m trying to backup. It has this line:

xtrabackup_55 version 2.1.4 for Percona Server 5.5.31 Linux (x86_64) (revision id: 656)

On the two new servers I built to test all this on it has this line:

xtrabackup_56 version 2.1.5 for MySQL server 5.6.11 Linux (x86_64) (revsion id: 680)

So I guess my question is… did the wrong software get installed for the original servers or is there an option that wasn’t set correctly?

7

We still haven’t been able to get this to work, so we have decided to move our databases to Oracle’s Enterprise database and use their backup and recovery.