MySQL Bugs: #79276: set session default_storage

Bug #79276	set session default_storage_engine doesn't replicate
Submitted:	13 Nov 2015 17:55	Modified:	5 Jan 2016 15:37
Reporter:	Yael Goldberg	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.6	OS:	CentOS
Assigned to:		CPU Architecture:	Any
Tags:	default_storage_engine replication

Description:
If you set the default storage engine (i.e. to MyISAM) in the session,
and create a table, the table on the master inherits the session storage engine, while the table on the slave inherits the default global storage engine.

How to repeat:
On master and slaves, global default storage engine is set to innodb:

my.cnf:
default-storage-engine=innodb

master-mysql>  SET SESSION default_storage_engine=MyISAM;
master-mysql> CREATE TABLE db1.table1 (col1 INT);
master-mysql> SHOW CREATE TABLE db1.table1;

CREATE TABLE `table1` (
  `col1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

slave-mysql> SHOW CREATE TABLE db1.table1;

CREATE TABLE `table1` (
  `col1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Suggested fix:
replicate session default_storage_engine

Thank you for the bug report.

https://dev.mysql.com/doc/refman/5.6/en/replication-solutions-diffengines.html

17.3.2 Using Replication with Different Master and Slave Storage Engines

It does not matter for the replication process whether the source table on the master and the replicated table on the slave use different engine types. In fact, the default_storage_engine and storage_engine system variables are not replicated.

Thank you for pointing that out.  However, I as quite surprised that MySQL does not replicate the session default_storage_engine.  I question how often that "feature" is used.  

I think that it's a real issue that MySQL does not replicate the default_storage_engine, and it will be frustrating to MySQL users that expect the same behavior on master and slave.

Specifically, in our case, we have found that Innodb has critical performance issues when creating and joining with tables without primary keys.

This is very impractical, as existing backend scripts (that were originally designed using the default storage engine of MyISAM) that calculate data don't necessarily have primary keys on those calculation tables.  This makes those scripts very very slow.  It would be so convenient to add to the top of those scripts to change the storage engine to myisam and replicate that to the slaves.  Otherwise, the performance issue is a problem on the slaves as well, and replication just lags behind.  

I think you should reconsider that "feature" and allow replicating the default storage engine.

Hi,

Can I please request this as a feature request?  I think that many people would find it useful to have the default_storage_engine replicated.

To quote another bug logged by Mark Callaghan:

"Things that make the outcome on a slave different than on a master must be done on the slave, not on the events written to the binlog."

https://bugs.mysql.com/bug.php?id=41101