Inconsistency between the master and slave (master/slave) default engines causes replication failure solution _ MySQL

The default master-slave (masterslave) engine is inconsistent, causing replication failure to solve bitsCN.com

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

My colleague told me that the replication of an slave server was interrupted and the following error was reported:

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

Relay_Master_Log_File: mysql-bin.004368

Slave_IO_Running: Yes

Slave_ SQL _Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_errno.: 1071

Last_Error: Error 'specified key was too long; max key length is 1000 bytes 'onquery. 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 default 0 comment''

Skip_Counter: 0

Exec_Master_Log_Pos: 571890750

Relay_Log_Space: 1802530754

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_ SQL _Errno: 1071

Last_ SQL _Error: Error '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 _ uv' int null default 0 comment', 'pv' int null default 0 comment', 'uv' int null default 0 comment '', 'shopcart _ PV' int NULL ulult0

1 row in set (0.00 sec)

ERROR:

No query specified

An error occurred while creating 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 _ U' intNULL DEFAULT 0 COMMENT'', 'pv' int null default 0 COMMENT '', 'u' 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 stopped at log 'MySQL-bin.004368 'position571890750

The log shows that the table creation failed at 10:57 A.M.. Obviously, the index length exceeds 1000 bytes. then, I told my colleagues that the index creation length exceeds 1000 bytes, resulting in slave io_thread interruption. 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 the storage engine to use:

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

It is found that the table creation statement is written into the binlog binary log in this format.

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 (11) DEFAULT '0' comment '',

'Topic _ U' int (11) DEFAULT '0' comment '',

Primary key ('domain ', 'topic _ name', 'topic _ 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 server. 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.

BitsCN.com