MySQL Bugs: #7822: storage_engine is not replicated

Bug #7822	storage_engine is not replicated
Submitted:	12 Jan 2005 0:59	Modified:	7 Oct 2008 11:37
Reporter:	Sergey Petrunya	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0-bk	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
table_type system variable affects type of tables created on the master but doesn't have effect on type of tables on slave.

How to repeat:
Run this test:
---mysql-test/t/rpl_table_type.test---
source include/master-slave.inc;

save_master_pos;
connection slave;
sync_with_master;
reset master;
connection master;

let $default=`select @@storage_engine`;
set storage_engine=HEAP;
create table t1(n char(30));
show create table t1;
connection slave;
sync_with_master;
show create table t1;
connection master;
drop table t1;
eval set storage_engine=$default;
save_master_pos;
connection slave;
sync_with_master;
stop slave;
--eof--
and in .result file see:

set storage_engine=HEAP;
create table t1(n char(30));
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `n` char(30) default NULL
) ENGINE=HEAP DEFAULT CHARSET=latin1
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `n` char(30) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Suggested fix:
Make table_type replicated.

It's listed in the replication known problems.
It will be fixed (hopefully in 5.0) like this:
for each CREATE TABLE which uses the @@STORAGE_ENGINE variable (we already have a flag for this, HA_ENGINE_USED), add an info in the Query_log_event of the CREATE TABLE to say this uses @@STORAGE_ENGINE=value.
We don't need to reset it after executing the event.
Only problem is that users may rely on the current behaviour to have Engine1->Engine2 replication (InnoDB->MyISAM for example). So we would need to provide an option to either not store the info in binlog (master's option) or not use it on slave (slave's option). Slave's option looks more flexible.

Not so bad suggested solutions in this bug report. See also WL#755 and BUG#88.

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/19707

ChangeSet@1.2408, 2007-02-12 18:43:41+01:00, gmax@gmac. +13 -0
  Changed file for transactional locks test.
  - Added sections to isolate transaction chuncks;
  - removed storage_engine variable to avoid effects of bug#7822
  
  Added tests for MyISAM (single server) and for replication (InnoDB and MyISAM)

Lars, is this still true?
I believe the bug is fixed?

I can't repeat this with newer version of MySQL. Tested with actual MySQL 5.0 and 5.1 bzr tree.