Master-slave (master/slave) default engine inconsistency causes replication failure Solution

The master/slave (master/slave) default engine is inconsistent, causing the replication to fail. The colleague told me that the replication of one slave server is interrupted. Check the slave server with the following Error: 'specified key was too long; max keylength is 1000 bytes 'on query mysql> show slave status \ G; * *************************** 1. row ************************* Slave_IO_State: Waiting formaster to send event Master_Host: 192. xxx. xxx.146 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.004369 Read _ Master_Log_Pos: 717957570 Relay_Log_File: relay-bin.013649 Relay_Log_Pos: 571890895 runtime: mysql-bin.004368 runtime: Yes Slave_ SQL _Running: No runtime: Replicate_Do_Table: runtime: Last_Errno: 1071 Last_Error: error 'specified key was too long; max key length is 1000 bytes 'o Nquery. default database: 'analyze _ XXX '. query: 'create table' meta _ topic_scan '('domain' varchar (200) not null comment '', 'topic _ name' varchar (200) not null comment '', 'topic _ url' varchar (200) not null comment', 'topic _ pv' int null default 0 comment '', 'topic _ uv' int null default 0 comment', 'pv' int null default 0 comment', 'uv' int null default 0 comment '', 'shopcart _ pv' int NULL D EFAULT 0 COMMENT ''Skip_Counter: 0 condition: 571890750 Relay_Log_Space: 1802530754 Until_Condition: None Until_Log_File: condition: 0 condition: No condition: Master_SSL_Cert: condition: Master_SSL_Key: condition: NULL condition: no Last_IO_Errno: 0 Last_IO_Error: Last_ SQL _Errno: 1071 Last_ SQL _Error: Erro R 'specified keywas too long; max key length is 1000 bytes 'on query. default database: 'analyze _ product '. query: 'create table' meta _ topic_scan '('domain' varchar (200) not null comment '', 'topic _ name' varchar (200) not null comment '', 'topic _ url' varchar (200) not null comment', 'topic _ pv' int null default 0 comment '', 'topic _ U' int null default 0 comment', 'pv' int null default 0 COMMENT '', 'Uv' int null default 0 COMMENT'', 'shopcart _ pv' int NULL DEFAULT0 1 row in set (0.00 sec) ERROR: No query specified failed to create the table, the index length exceeds the maximum value of 1000. Check the error log [root @ db ~]. # Tail-n 500/my/data/mysql/dbxxxxxx. err 130606 10:57:20 [ERROR] Slave SQL: Error 'specified key was too long; max key length is 1000 bytes 'on query. defaultdatabase: 'analyze _ XXX '. query: 'create table' meta _ topic_scan '('domain' varchar (200) not null comment '', 'topic _ name' varchar (200) not null comment '', 'topic _ url' varchar (200) not null comment', 'topic _ pv' int null default 0 comment'', 'Topic _ uv' intNULL DEFAULT 0 comment', 'pv' int null default 0 comment', 'uv' int NULLDEFAULT 0 comment '', 'shopcart _ pv' int null default 0 130606 10:57:20 [Warning] Slave: Specifiedkey was too long; max key length is 1000 bytes Error_code: 1071 130606 10:57:20 [ERROR] Error runningquery, slave SQL thread aborted. fix the problem, and restart the slave SQLthread with "slave start ". we s The topped at log 'mysql-bin.004368 'position571890750 log shows that table creation failed at 10:57 A.M.. It is an obvious error that the index length exceeds 1000 bytes. Then, inform your colleagues, when the index length of the created table exceeds 1000 bytes, the slave io_thread is interrupted. Then, my colleague said strangely that it was okay to create a table on the master node in the morning. Why did I report an error when I copied the table on the slave? Later, I found that the default storage engines of the master and slave are inconsistent through the creation of a table on slave. Will the inconsistency cause interruption? Yes, whether the TABLE creation statement explicitly specifies whether to use the following storage engine statement create table 'meta _ topic_scan '('domain' varchar (200) not null, 'topic _ name' varchar (200) not null, 'topic _ url' varchar (200) not null, 'topic _ pv' int (11) DEFAULT '0 ', 'topic _ U' int (11) DEFAULT '0', primary key ('domain ', 'topic _ name', 'topic _ url ')); if no storage engine is specified during table creation, the default engine on the master is innodb, And the created table is innodb, what format is the statement written into the binlog? We use mysqlbinlog to find this statement and analyze it in the following format: [root @ aeolus1 ~] # Mysqlbinlog-uroot -- start-position = 1914/usr/local/mysql/data/mysql-bin.000014 found that the TABLE creation statement writes the create table 'meta _ topic_scan 'in the binlog binary log in this format' ('domain 'varchar (200) not null comment '', 'topic _ name' varchar (200) not null comment'', 'topic _ url' varchar (200) not null comment '', 'topic _ pv' int (11) DEFAULT '0' comment', 'topic _ uv' int (11) DEFAULT '0' comment '', primary key ('domain ', 'topic _ name', 'to Pic_url '); in this way, io_thread transfers the binlog from the master server to the slave, and SQL _thread executes this statement on the slave. This shows the Default Server Load balancer engine and creates a table with the default engine. Because slave is mysiam by default, the index length on innodb and mysiam engines is different, leading to master-slave replication interruption. Modify my. cnf, change the default engine innodb, and restart the Service (business is not very important, restart allowed). OK. If restart is not allowed, create a table on slave, skip the error event, and restart the table in the maintenance window to take effect.