MySQL Bugs: #46559: Restore failure message doesn't indicate that SE support is not available

Bug #46559	Restore failure message doesn't indicate that SE support is not available
Submitted:	5 Aug 2009 4:00	Modified:	8 Sep 2009 22:40
Reporter:	Hema Sridharan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	mysql-6.0-backup	OS:	Any
Assigned to:	Chuck Bell	CPU Architecture:	Any

Description:
Create database db1.
Create innodb table db1.t1
Perform backup of database db1 to db1.bak
Now start the server without innodb storage engine support
Perform restore, will fail with an error " Could not restore table".

This above error is misleading and does not indicate the actual cause for the failure. Instead the error message should indicate the absence of storage engine (innodb) support.

How to repeat:
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> create table db1.t1(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> drop table db1.t1;
Query OK, 0 rows affected (0.04 sec)

mysql> create table db1.t1(id int)engine=innodb;
Query OK, 0 rows affected (0.04 sec)

mysql> backup database db1 to '/export/home/tmp/backup_dmp/db1_in.bak';
+-----------+
| backup_id |
+-----------+
| 276       |
+-----------+
1 row in set (0.33 sec)

Now restart the server again without innodb support and perform restore from db1_in.bak

mysql> restore from '/export/home/tmp/backup_dmp/db1_in.bak';
ERROR 1698 (HY000): Could not restore table `db1`.`t1`

hi,
Which server version have you tested?. Thanks in advance.

Tested in MySQL Version 5.4.4

Thank you for the report.

Verified as described:

$./bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.4.4-alpha Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table t1(f1 int) engine=innodb;
Query OK, 0 rows affected (0.21 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> backup database test to 'test_46559.bkp';
+-----------+
| backup_id |
+-----------+
| 556       | 
+-----------+
1 row in set (0.35 sec)

mysql> \q
Bye

$./bin/mysqladmin shutdown -uroot -S /tmp/mysql_ssmirnova.sock 

$  ./libexec/mysqld --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --port=33051 --socket=/tmp/mysql_ssmirnova.sock -O thread_stack=1280000 --log-error --new --skip-innodb &
[1] 19596

$./bin/mysql -uroot -S /tmp/mysql_ssmirnova.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.4.4-alpha Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       | 
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         | 
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         | 
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         | 
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         | 
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       | 
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         | 
| InnoDB     | NO      | Supports transactions, row-level locking, and foreign keys     | NULL         | NULL | NULL       | 
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         | 
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> restore from  'test_46559.bkp';
ERROR 1767 (HY000): Database 'test' already exists. Use OVERWRITE flag to overwrite.
mysql> restore from  'test_46559.bkp' overwrite; 
ERROR 1698 (HY000): Could not restore table `test`.`t1`

The error mentioned above is indeed detected but it is not placed on the 'top' of the error stack. You can see the error here:

mysql> restore from '1.bak' OVERWRITE;
ERROR 1698 (HY000): Could not restore table `db1`.`t1`
mysql> SHOW ERRORS;                   
+-------+------+------------------------------------+
| Level | Code | Message                            |
+-------+------+------------------------------------+
| Error | 1286 | Unknown storage engine 'InnoDB'    | 
| Error | 1698 | Could not restore table `db1`.`t1` | 
+-------+------+------------------------------------+
2 rows in set (0.00 sec)

The reason is the code currently doesn't return any errors when an object is recreated using execute_direct(). The patch shall have to be changed to detect the error and raise it.

The error mentioned above is indeed detected but it is not placed on the 'top' of the error stack. You can see the error here:

mysql> restore from '1.bak' OVERWRITE;
ERROR 1698 (HY000): Could not restore table `db1`.`t1`
mysql> SHOW ERRORS;                   
+-------+------+------------------------------------+
| Level | Code | Message                            |
+-------+------+------------------------------------+
| Error | 1286 | Unknown storage engine 'InnoDB'    | 
| Error | 1698 | Could not restore table `db1`.`t1` | 
+-------+------+------------------------------------+
2 rows in set (0.00 sec)

The reason is the code currently doesn't return any errors when an object is recreated using execute_sql_statement(). The patch shall have to be changed to detect the error and raise it.

From 8 September Backup Decision meeting:
   DECISION:                                                                        - Add to manual that user should do SHOW ERROR if he gets error 
  1698 (restore failed).
- Close this bug as not a bug.
- Open a new bug with feature request to print number of errors generated

The new bug is BUG#47199 : Backup does not provide indication when there are multiple errors.