GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

InnoDB: Assertion failure while trying to prepare.

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • InnoDB: Assertion failure while trying to prepare.

    Hello, I'm trying to restore a database from one server to another. I have done the following thus far.

    Code:
    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.
    The question is what am I missing? I've never used this tool before and read through the backup/restore procedures in the documentation, but it doesn't seem to give me any clue as to what I'm doing wrong. I would appreciate any help/direction you can give me. Thanks!
    Last edited by 2Luke2; 11-22-2013, 06:20 PM.

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

    Code:
    [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

    Comment


    • #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: http://www.percona.com/doc/percona-x...ackup_ibk.html

      WB
      WB with #MySQL, www.wagnerbianchi.com

      Comment


      • #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.

        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.

        Code:
        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.

        Code:
        [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:

        Code:
        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.
        Last edited by 2Luke2; 11-23-2013, 11:02 PM.

        Comment


        • #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
          WB with #MySQL, www.wagnerbianchi.com

          Comment


          • #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:

            Code:
            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:

            Code:
            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?

            Comment


            • #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.

              Comment

              Working...
              X