Description:
The replicate-rewrite-db option allows one to have tables in a different database on a slave.
I'd like to see something similar for storage engines to allow one to have tables in a different storage engine on a slave.
This works:
M = ndbcluster
S = InnoDB
But that's because S probably has ndbcluster disabled.
This relies on NO_ENGINE_SUBSTITUTION being disabled.
This doesn't really work:
M = InnoDB
S = ndbcluster
This works if skip-innodb (deprecated) is used.
This is even more of a problem for 'blackhole':
If you setup this:
M -> IM -> S
With M and S using InnoDB but IM using blackhole then you need to alter the table on the IM.
Note that having to alter new and/or changed tables with InnoDB-to-NDB is even more of a problem as the table won't be visible in the cluster when it is using InnoDB.
The default-storage-engine setting helps a bit, but doesn't really fix this.
It only applies to DDL not explicitly setting a storage engine.
Because of MyISAM being the default for a long time people often use ENGINE=InnoDB in their create table statements.
Also third-party tools and restoring from mysqldump/mysqlpump might explicitly set an engine in the default configuration.
ALTER TABLE xxx ENGINE=InnoDB is often used to force a table rebuild.
Note that this also affects third-party storage engines like TokuDB, MyROCKS, etc.
How to repeat:
Replicate from InnoDB to NDB or vice versa.
Replicate from MyISAM to blackhole or vice versa.
Now create a new tables on the master:
CREATE TABLE t1 (id SERIAL PRIMARY KEY);
CREATE TABLE t2 (id SERIAL PRIMARY KEY) ENGINE=<engine>;
Now check on the slave if the table has the desired engine.
Suggested fix:
Add replicate-rewrite-engine option.
OR
Add storage-engine-rewrite option, which also works outside of a replication context. This migth allow some third party software which has ENGINE=MyISAM to work on InnoDB and some software with hardcoded InnoDB to work with NDB.
This could help migration to a new storage engine.
