Not the answer you need?
Register and ask your own question!

InnoDB: Assertion failure while trying to prepare.

2Luke22Luke2 EntrantInactive User Role Participant
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: [/code]
[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.



Comments

  • 2Luke22Luke2 Entrant Inactive User Role Participant
    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
    
  • wagnerbianchiwagnerbianchi Remote DBA Current User Role Advisor
    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: http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/restoring_a_backup_ibk.html

    WB
  • 2Luke22Luke2 Entrant Inactive User Role Participant
    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.

    http://www.percona.com/doc/percona-x...ackup_ibk.html

    http://www.percona.com/doc/percona-x...ackup_ibk.html

    http://www.percona.com/doc/percona-x...ackup_ibk.html

    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.
  • wagnerbianchiwagnerbianchi Remote DBA Current User Role Advisor
    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
  • 2Luke22Luke2 Entrant Inactive User Role Participant
    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?
  • 2Luke22Luke2 Entrant Inactive User Role Participant
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.