Shulou(Shulou.com)06/01 Report--

0 phenomenon:

Error 1050 was reported when recovering data through binlog increments.

Version 0:

MySQL 5.5.x and below, lower version of MySQL 5.6.x (tested in the higher version of 5.6and cannot be reproduced, suspected to be repaired)

0 reason:

Binlog records the DDL statement that went wrong. Here, the DDL is CREATE VIEW $view_name AS...

It's a little strange, is it possible that the wrong DDL will also be recorded in binlog?

0 recurrence:

[17:52:45] root@localhost [a] > FLUSH LOGS

Query OK, 0 rows affected (0.01 sec)

[17:52:47] root@localhost [a] > SHOW BINLOG EVENTS IN 'mysql55-bin.000006'

+-- +

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-- +

| | mysql55-bin.000006 | 4 | Format_desc | 55 | 107 | Server ver: 5.5.57-log, Binlog ver: 4 |

+-- +

1 row in set (0.00 sec)

[17:53:13] root@localhost [a] > CREATE TABLE t (id int)

Query OK, 0 rows affected (0.01 sec)

[17:53:18] root@localhost [a] > CREATE VIEW vantage AS SELECT * from t

Query OK, 0 rows affected (0.00 sec)

[17:53:21] root@localhost [a] > CREATE VIEW vantage AS SELECT * from t

ERROR 1050 (42S01): Table 'vemot' already exists

At this point, check the binlog again:

[17:53:24] root@localhost [a] > SHOW BINLOG EVENTS IN 'mysql55-bin.000006'\ G

* * 1. Row *

Log_name: mysql55-bin.000006

Pos: 4

Event_type: Format_desc

Server_id: 55

End_log_pos: 107

Info: Server ver: 5.5.57-log, Binlog ver: 4

* 2. Row * *

Log_name: mysql55-bin.000006

Pos: 107

Event_type: Query

Server_id: 55

End_log_pos: 189

Info: use `a`; CREATE TABLE t (id int)

* 3. Row * *

Log_name: mysql55-bin.000006

Pos: 189

Event_type: Query

Server_id: 55

End_log_pos: 369

Info: use `a`; CREATE ALGORITHM=UNDEFINED DEFINER= `root` @ `localhost` SQL SECURITY DEFINER VIEW `vt` AS SELECT * from t

* * 4. Row *

Log_name: mysql55-bin.000006

Pos: 369

Event_type: Query

Server_id: 55

End_log_pos: 549

Info: use `a`; CREATE ALGORITHM=UNDEFINED DEFINER= `root` @ `localhost` SQL SECURITY DEFINER VIEW `vt` AS SELECT * from t

4 rows in set (0.00 sec)

It can be found that the second execution of CREATE VIEW, even if the mysql client execution Times error, is also recorded in binlog.

Naturally, an error will also be reported when you replay binlog through mysqlbinlog.

However, in the replication structure, the events is not replayed from the slave instance, so the slave library does not report an error.

Re-parse the log through mysqlbinlog to get:

# at 107

# 180206 17:53:18 server id 55 end_log_pos 189 Query thread_id=13 exec_time=0 error_code=0

Use `a` / *! * /

SET timestamp 1517910798

SET @ @ session.pseudoclinic thread readership idling 13user / session /

SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.

SET @ @ session.sqlcards, modewords, 0planks / sessions.

SET @ @ session.auto_increment_increment=1, @ @ session.

/ *!\ C utf8 * /! * /

SET @ @ session.session. Session setting setting clientmakers 33 minutes session. Collationalization connections33 camera sessions session. Collationalization serverals 8 packs.

SET @ @ session. Session. LCC timetables namespace.

SET @ @ session.collationalization databases

CREATE TABLE t (id int)

/ *! * /

# at 189

# 180206 17:53:21 server id 55 end_log_pos 369 Query thread_id=13 exec_time=0 error_code=0

SET timestamp 1517910801

CREATE ALGORITHM=UNDEFINED DEFINER= `root` @ `localhost` SQL SECURITY DEFINER VIEW `vt` AS SELECT * from t

/ *! * /

# at 369

# 180206 17:53:22 server id 55 end_log_pos 549 Query thread_id=13 exec_time=0 error_code=1050SET timestamp 1517910802

CREATE ALGORITHM=UNDEFINED DEFINER= `root` @ `localhost` SQL SECURITY DEFINER VIEW `vt` AS SELECT * from t

/ *! * /; the statement that reported the second error can be found. Although it is recorded in binlog, the error_code is identified as 1050.

As for why errors are not reported from the library, it is probably because sql thread will specifically handle this kind of error_code and will not execute it.

Mysqlbinlog mysql-binlog.00000x | mysql- u-p-S will report an error if you replay the binlog. -- probably because mysql, the client tool, does not do special handling for error_code parsed by mysqlbinlog.

It is not known if it is bug, but the higher version of MySQL5.6.x has fixed the problem, as well as 5.7.

As a result, special handling may be required when recovering data through binlog increments in versions that may be problematic.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.