MyISAM to InnoDB Replication - Replication Errors

When migrating from a system that uses MyISAM tables to a system that does not support MyISAM tables, the way to go is often to do a logical dump and restore and set up replication between the two systems.

If one then continues creating tables on the MyISAM system, one might be left with broken replication with the error -

Error executing row event: 'Can't find file: ‘sbtest6’ (errno: 2 - No such file or directory)

A helpful variable to keep in mind in such cases is – default_storage_engine

Default_storage_engine is a dynamic variable that can be set on both global and session level.
Documentation on the variable can be found here.

This is the wrong way to create a table on such a system and this will cause replication errors, if the slave does not support MyISAM tables.

mysql>  CREATE TABLE `sbtest6` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL DEFAULT '0',

  `c` char(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k_6` (`k`)

) Engine = MyISAM;

The right way to create a table is –

On the slave

mysql> set global default_storage_engine=InnoDB;

On the master

mysql> set default_storage_engine=MyISAM;

mysql>  CREATE TABLE `sbtest6` (  
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
`k` int(10) unsigned NOT NULL DEFAULT '0', 
 `c` char(120) NOT NULL DEFAULT '',  
`pad` char(60) NOT NULL DEFAULT '',  
PRIMARY KEY (`id`),  
KEY `k_6` (`k`)
);

On the master –

mysql> show create table sbtest6 \G
*************************** 1. row ***************************      
 Table: sbtest6
Create Table: CREATE TABLE `sbtest6` (  
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
`k` int(10) unsigned NOT NULL DEFAULT '0', 
 `c` char(120) NOT NULL DEFAULT '',  
`pad` char(60) NOT NULL DEFAULT '',  
PRIMARY KEY (`id`),  
KEY `k_6` (`k`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

On the slave –

mysql> show create table sbtest6 \G
*************************** 1. row ***************************      
 Table: sbtest6
Create Table: CREATE TABLE `sbtest6` (  
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
`k` int(10) unsigned NOT NULL DEFAULT '0', 
 `c` char(120) NOT NULL DEFAULT '',  
`pad` char(60) NOT NULL DEFAULT '',  
PRIMARY KEY (`id`),  
KEY `k_6` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)


This way it is still possible to have MyISAM tables on the master, have replication going and have InnoDB tables on the slave.