Restore Troubleshooting - MySQL Agent

MSQL0002: Issue restoring MySQL databases when tables are defined using InnoDB engine

Restore operation fails with the following error message in the MySQLRestore.log under Log_Files folder.

err = ERROR 1005 (HY000) at line 33: Can't create table './DBNAME/TABLE.frm' (errno: 121)

Cause

If a database is deleted or lost due to the directory deletion, and that database contained tables that used the InnoDB engine, a restore from the CommCell Console fails.

Resolution

Delete the database directory that was created due to the failed restore. Run the commands to create and drop an empty database before running the restore. This causes the MySQL engine to remove the table entries from the InnoDB data dictionary before the restore takes place, thus preventing the errors.

To resolve this issue:

1. Delete the database directory that was created as a result of the failed restore.

2. Create an empty database by running the following command.

   mysql> create database dbname;

3. Drop the empty database by running the following command.

   mysql> drop database dbname;

   where <dbname> is the name of the database being restored.

4. Run the restore.

MSQL0006: MySQL database restore operation appears hung in the Job Controller window

Cause

One of the following could be a reason for the restore operation to hang.

• Memory is full in the datadir on the destination server resulting in no space for the restore operation.

• The query running as part of a dump is too long and might take long time to finish. It could also hang in the process.

• There are network issues resulting in restore operation to hang.

• The data being restored is huge so it might take a long time for the restore to finish. The restore operation might be running but it appears as hanging.

Resolution

To resolve this issue.

• From the application level, check if the MySQL server log memory is full.

• Run SHOW PROCESSLIST query on the database server to view the running activities.

• Log files are saved only if bin_log is enabled, so first make sure that it is enabled. Then monitor the transaction log file status to see if it is growing.

• If bin_log is not enabled, then monitor the datadir on the destination server to see if the file size is increasing. The file size should grow if the restore operation is running.

MSQL0009: Issue restoring MySQL system databases

When you restore a backup copy of the system databases, you are not able to see changes in the restored files after the restore operation is complete.

You can find the following error message in MySQLRestore.log file under Log_Files folder.

GetMySqlInfoExt::DropDB() - DropDB failed : [ mysql ]

Cause

If general logging and slow query logging are enabled in the MySQL Server, the MySQL system databases are not restored.

Resolution

To resolve this issue:

1. In the MySQL configuration file, disable general logging and slow query logging by setting their value as 0, as shown in the following example:

   # General and Slow logging Section
    log-output=FILE
    general-log=0
    general_log_file="xxx.log"
    slow-query-log=0
    slow_query_log_file="xxx-slow.log"
    long_query_time=10

2. Restart the MySQL Server.

3. Run the restore.

MSQL0012: MySQL log restore operation fails

MySQL log restore operation fails with the following error message.

Error Code: [87:83]
 Description: One or more errors occurred while applying the transaction logs to the database(s). Please check event messages OR logs for more details.
 Source: SDE-SAPVM2, Process: MySqlRestore

You can also see the following error in the MySqlRestore.log file:

MySqlChildProcess::CV_CreateProcess() - Creating Child Process : (G:\MySQL56\bin\mysqlbinlog.exe --start-position=120 -d "qwaszx_1" "G:\41964\41970\52978\mysql-bin.000031" --result-file="G:\41964\41970\52978\ArchiveEvents.sql")
 8060 1d24 11/02 15:38:42 41970 MySqlChildProcess::CV_CreateProcess() - Logon of User [ idcprodcert/admin ] Successful...
 8060 1d24 11/02 15:38:42 41970 MySqlChildProcess::Wait() - Child process: dwExitCode=2

Cause

The restore operation fails due to the presence of no-beep in the client section of the configuration file my.ini for Windows or my.cnf for Linux.

To verify this issue, select the Do not Recover option and apply the logs from the MySqlRestore.log file manually to the MySQL Server.

For example:

G:\MySQL56\bin>mysqlbinlog.exe --start-position=120 -d "qwaszx_1" "G:\41964\41970\52978\mysql-bin.000031" --result-file="G:\41964\41970\52978\ArchiveEvents.sql"

Your query should return the following value:

mysqlbinlog.exe: unknown option '--no-beep'

Resolution

Follow the given steps to resolve this issue:

1. Open the configuration file my.ini for Windows or my.cnf for Linux.

2. In the client section of your configuration file, comment out no-beep as shown below:

   #no-beep

3. Run the log restore operation again.

Completed with one or more errors

Restore jobs from MySQL iDataAgent will be displayed as "Completed w/ one or more errors" in the Job History in the following cases:

• During Logs Restore, if applying log into a database fails, it marks such databases as non-recoverable and continues with other databases.

Error Code [87:19] Restore failed with MySQL error

A restore operation for MySQL from the same host or to a different host fails.

Article ID: MSQL0001

Error Code: [87:83] Log restore operation failed

MySQL log restore operation fails

Article ID: MSQL0006

A MySQL Restore Operation With the Do Not Recover Option Fails with an Error

A MySQL restore operation on the Linux operating system, with the Do Not Recover option selected, fails.

Article ID: MSQL0016

A MySQL Database Restore Operation Fails

MySQL auto-discovered instance does not have a value set for the log data directory.

Article ID: MSQL0019

A MySQL Log Restore Operation Fails

MySQL auto-discovered instance does not have a value set for the log data directory.

Article ID: MSQL0021

MySQL Restore Operation Fails If Restore Header is Empty

MySQL restore operation fails if restore header is empty.

Article ID: 56959