MySQL Bugs: #39379: Restore of online backup ignoring NO_ENGINE_SUBSTITUTION sql

Bug #39379	Restore of online backup ignoring NO_ENGINE_SUBSTITUTION sql_mode
Submitted:	10 Sep 2008 19:08	Modified:	11 Aug 2009 17:37
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.8	OS:	Any
Assigned to:	Øystein Grøvlen	CPU Architecture:	Any

Description:
If a backup includes tables in engines not currently available in the running server when a restore is performed, warnings are returned indicating the engine is not available and that the tables are created with the default storage engine (expected).

However the same is observed if the server is running with the 'NO_ENGINE_SUBSTITUTION' sql_mode. In this case an error should be returned and the restore should abort.

How to repeat:
(Example including innodb but applies to any engine)
 - Start the server with innodb enaled.

 - Create a database with an innodb table and populate it.
   = create database db1;
   = use db1;
   = create table tb1 (i int) engine=innodb;
   = insert into tb1 values (1), (2), (3), (4), (5);
   = backup database omer to '/tmp/omer_inotbl.bck';

 - Restart the server with innodb diabled.
 - Set sql mode to 'NO_ENGINE_SUBSTITUTION';
 - Verify that innodb does is not listed in SHOW ENGINES and that 
   NO_ENGINE_SUBSTITUTION is listed in SHOW VARIABLES LIKE '%mode%';
 - Restore the tb1 database

Observe:

Server version: 6.0.8-alpha-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+----+------------+
| CSV        | YES     | CSV storage engine                                             | NO           | NO | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO | NO         |
| MARIA      | YES     | Crash-safe tables with MyISAM heritage                         | YES          | NO | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO | NO         |
+------------+---------+----------------------------------------------------------------+--------------+----+------------+
7 rows in set (0.01 sec)

mysql> show variables like '%mode%';
+-----------------+------------------------+
| Variable_name   | Value                  |
+-----------------+------------------------+
| slave_exec_mode | STRICT                 |
| sql_mode        | NO_ENGINE_SUBSTITUTION |
+-----------------+------------------------+
2 rows in set (0.01 sec)

mysql> restore from '/tmp/omer_inotbl.bck';
+-----------+
| backup_id |
+-----------+
| 1         |
+-----------+
1 row in set, 2 warnings (0.04 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1286 | Unknown table engine 'InnoDB'               |
| Warning | 1266 | Using storage engine MyISAM for table 'tb1' |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)

Suggested fix:
An error should be returned and the restore should abort.

I checked the difference between create table and restore in debugger.

When creating table through restore, sql_mode in THD is 0.  Hence, a missing storage engine is not flagged as an error during restore.  When creating tables directly, sql_mode in THD is set as expected, both before and after restore.  The strange thing is that it seems to be the same THD object in all cases (at least same address).

Since restore create tables through the service object interface, I will postpone further investigations until the ongoing re-implementation of the this interface is completed.

This problem is still present with the new object service API:

set session sql_mode = 'NO_ENGINE_SUBSTITUTION';
create database db1;
use db1;
create table tb1 (i int) engine=innodb;
ERROR 42000: Unknown storage engine 'innodb'
restore from '/tmp/inotbl.bck' overwrite;
backup_id
270
Warnings:
Warning 1286    Unknown storage engine 'InnoDB'
Warning 1266    Using storage engine MyISAM for table 'tb1'
create table tb2 (i int) engine=innodb;
ERROR 42000: Unknown storage engine 'innodb'

Discussed in Backup Camp:

Decision for this bug report:
1. Make it strict (no substitution of engines) for 6.0.
2. Make it clear that this limitation is documented:
   "You must have the same engines enabled on the restore server 
   as was backed up on the backup server".

Override is a separate discussion and will be handled outside of 
this bug report.  (Chuck thinks this is needed now, rest think 
it can wait).

Long time ago I sent this email Alik, Kostja and the dev-backup list:

Hi Alik & Kostja,

I am looking into Bug#39379 (Restore of online backup ignoring
NO_ENGINE_SUBSTITUTION sql_mode).  After a discussion in the backup
team we have decided that automatic engine substitution during restore
should be prevented since backup drivers may create backup images that
are not compatible with other engines.

The current behavior is a result of that SI objects resets the
sql_mode as part of run_service_interface_sql().  In order to get the
desired behavior, we need to be able to execute the query with
sql_mode = NO_ENGINE_SUBSTITUTION.  I see the following alternatives:

1. Change Si_session_context::reset_si_ctx() to set sql_mode to
   NO_ENGINE_SUBSTITUTION.  The disadvantage is that this introduces a
   backup specific aspect into the interface.  On the other hand, the
   documentation for run_service_interface_sql() says "Execute one DML
   statement in a backup-specific context." Does that mean that
   si_objects is intended to be backup specific code?

2. Change Abstract_obj::create() to set sql_mode to
   NO_ENGINE_SUBSTITUTION after reset_si_ctx() has been called.  This
   will also be a backup specific change to si_objects.

3. Add an sql_mode parameter to Abstract_obj::create() so that restore
   can request a specific behavior.

What do you think? Do you have other suggestions?  I guess the
underlying question is about the role of si_objects.  Is it supposed
to be a general interface, or is it OK to tailor it to specific backup
needs?

Rafal replied:

Even though not asked, I want to express my opinion  :)  I think that it is ok to set the NO_ENGINE_SUBSTITUTION option inside si_objects as in option 2.

That will influence the semantics of Abstract_obj::create() method to the effect that a table which was serialized will always use the same storage engine when re-created from a serialization string (or si_objects will report error if this is not possible). Although this detail of the semantics was not explicitly specified, I think everyone will agree that this is the intended behaviour.

Kostja replied:

* Øystein Grøvlen <Oystein.Grovlen@Sun.COM> [09/02/04 16:34]:
> > I am looking into Bug#39379 (Restore of online backup ignoring
> > NO_ENGINE_SUBSTITUTION sql_mode).  After a discussion in the backup
> > team we have decided that automatic engine substitution during restore
> > should be prevented since backup drivers may create backup images that
> > are not compatible with other engines.
> > 
> > The current behavior is a result of that SI objects resets the
> > sql_mode as part of run_service_interface_sql().  In order to get the
> > desired behavior, we need to be able to execute the query with
> > sql_mode = NO_ENGINE_SUBSTITUTION.  I see the following alternatives:
> > 
> > 1. Change Si_session_context::reset_si_ctx() to set sql_mode to
> >     NO_ENGINE_SUBSTITUTION.  The disadvantage is that this introduces a
> >     backup specific aspect into the interface.  On the other hand, the
> >     documentation for run_service_interface_sql() says "Execute one DML
> >     statement in a backup-specific context." Does that mean that
> >     si_objects is intended to be backup specific code?

The service interface consists of two parts: the embedded
connection, implemented in sql_prepare.cc (level 0 interface), and
si_objects.{h,cc} (the backup interface).

Thus reset_si_ctx() will continue to be a backup-specific
functionality. I think it's okay for now to tweak it to make sure
no_engine_substitution is part of the server environment while
backup SQL is running.

> > 2. Change Abstract_obj::create() to set sql_mode to
> >     NO_ENGINE_SUBSTITUTION after reset_si_ctx() has been called.  This
> >     will also be a backup specific change to si_objects.
> > 
> > 3. Add an sql_mode parameter to Abstract_obj::create() so that restore
> >     can request a specific behavior.
> > 
> > What do you think? Do you have other suggestions?  I guess the
> > underlying question is about the role of si_objects.  Is it supposed
> > to be a general interface, or is it OK to tailor it to specific backup
> > needs?

See above, si_objects.cc is practically all yours. Moreover, I
would like to encourage the backup team to move all interaction
with the server to si_objects.cc, so that mysql_priv.h is not
included in sql/backup files. The only pieces that, I would
say, still belong to runtime are those related to name locking.

At the same time, you should feel free to come up with
additional requests for the level 0 interface in sql_prepare.{h,cc}.

E.g. there is a function in si_objects.cc, copy_warnings(). This
function is redundant. We can extend the embedded connection
interface to make sure that warnings are stored in THD directly.
I don't know how to proceed with that from the process point of view
-- it would be best if someone submitted a patch.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/78179

2843 oystein.grovlen@sun.com	2009-07-08
      Bug#39379 Restore of online backup ignoring NO_ENGINE_SUBSTITUTION sql_mode
      
      Do not allow engine substitution during RESTORE.
     @ mysql-test/suite/backup/r/backup_nodata_driver.result
        Updated result file with output of new test case.
     @ mysql-test/suite/backup/t/backup_nodata_driver.test
        Test that restore fails for a backcup image that contains a table in a storage engine that is not available.
     @ sql/si_objects.cc
        Add MODE_NO_ENGINE_SUBSTITUTION to default sql_mode so that engine
        subsititution is not performed during restore.

Good to push.

Approved pending changes. See commit email for details.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/78256

2844 oystein.grovlen@sun.com	2009-07-09
      Bug#39379 Restore of online backup ignoring NO_ENGINE_SUBSTITUTION sql_mode
      
      Do not allow engine substitution during RESTORE.
     @ mysql-test/suite/backup/r/backup_nodata_driver.result
        Updated result file with output of new test case.
     @ mysql-test/suite/backup/t/backup_nodata_driver.test
        Test that restore fails for a backup image that contains a table in a storage
        engine that is not available.
     @ sql/si_objects.cc
        Add MODE_NO_ENGINE_SUBSTITUTION to default sql_mode so that engine
        substitution is not performed during restore.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090806082225-qssc912qdv1mm6xv) (version source revid:ingo.struewing@sun.com-20090715090055-ad2xvrgz16y4a3mn) (merge vers: 5.4.4-alpha) (pib:11)

No changelog entry needed. Not in any released version.