Monday, November 27, 2017

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_engineDefault_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 slavemysql> set global default_storage_engine=InnoDB;