Err: unsupport type 0 in binlog
- Dominant language
- Go
- Stars
- 13.6k
- Forks
- 1.4k
- Avg merge
- 2h 31m
- Merged PRs (30d)
- 4
Description
I've run into an issue testing a replication on slave.
The slave I'm testing on normally replicates off a Maxscale 2.2 Binlog Server but for this test I changed it to point to the real master which is in a different physical location.
I've been using the following to test
```
gh-ost \
--user="ghost" \
--password="pasword1" \
--host=db-replica.com \
--test-on-replica \
--database="db1" \
--table="table1" \
--verbose \
--alter="MODIFY status VARCHAR(20) NOT NULL DEFAULT 'OK'" \
--initially-drop-ghost-table \
--initially-drop-old-table \
--max-load=Threads_running=30 \
--assume-rbr \
--chunk-size=500 \
--cut-over=default \
--exact-rowcount \
--concurrent-rowcount \
--serve-socket-file=/tmp/gh-ost.test.sock \
--panic-flag-file=/tmp/gh-ost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--skip-foreign-key-checks \
--execute
```
I know there are no foreign keys referencing that table so in order to speed up my testing I've set it to skip the check,
I very quickly start seeing the following errors showing up
```
[2019/06/19 14:01:40] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql cw-db266-98.cw.hostelworld.com 3306 ghost false false false UTC true 0 0s 0s 0 false}
[2019/06/19 14:01:40] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql-bin.007205, 4)
[2019/06/19 14:01:40] [info] binlogsyncer.go:203 register slave for master server cw-db266-98.cw.hostelworld.com:3306
2019-06-19 14:01:40 INFO rotate to next log from mysql-bin.007205:0 to mysql-bin.007205
2019-06-19 14:01:40 INFO StreamEvents encountered unexpected error: Header &replication.EventHeader{Timestamp:0x5d0a3b53, EventType:0x17, ServerID:0xf811c, EventSize:0x1b7, LogPos:0x4cf3, Flags:0x0}, Data "\x85Y\x10\x03\x00\x00\x01\x00\a\u007f\x80\xf8\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI47134\x00\x80\xf9\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI47134\x00\x80\xf9\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI93266\x00\x80\xfa\xc6\x0f\vDbl Private\x02\x00\x02\x00 55.00\bAPI47134\x00\x80\xfa\xc6\x0f\vDbl Private\x02\x00\x02\x00 55.00\bAPI93266\x00\x80\xfb\xc6\x0f\vDbl Private\x02\x00\x02\x00 65.00\bAPI47134\x00\x80\xfc\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI47134\x00\x80\xfc\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI93266\x00\x80\xfd\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI47134\x00\x80\xfd\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI93266\x00", Err: unsupport type 0 in binlog and don't know how to handle
/home/shlomi-noach/go/src/github.com/github/gh-ost/vendor/github.com/siddontang/go-mysql/replication/binlogsyncer.go:712:
[2019/06/19 14:01:40] [info] binlogsyncer.go:723 rotate to (mysql-bin.007205, 4)
[2019/06/19 14:01:40] [error] binlogstreamer.go:77 close sync with err: Header &replication.EventHeader{Timestamp:0x5d0a3b53, EventType:0x17, ServerID:0xf811c, EventSize:0x1b7, LogPos:0x4cf3, Flags:0x0}, Data "\x85Y\x10\x03\x00\x00\x01\x00\a\u007f\x80\xf8\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI47134\x00\x80\xf9\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI47134\x00\x80\xf9\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI93266\x00\x80\xfa\xc6\x0f\vDbl Private\x02\x00\x02\x00 55.00\bAPI47134\x00\x80\xfa\xc6\x0f\vDbl Private\x02\x00\x02\x00 55.00\bAPI93266\x00\x80\xfb\xc6\x0f\vDbl Private\x02\x00\x02\x00 65.00\bAPI47134\x00\x80\xfc\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI47134\x00\x80\xfc\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI93266\x00\x80\xfd\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI47134\x00\x80\xfd\xc6\x0f\vDbl Private\x02\x00\x02\x00 42.50\bAPI93266\x00", Err: unsupport type 0 in binlog and don't know how to handle
2019-06-19 14:01:45 INFO Reconnecting... Will resume at mysql-bin.007205:488297168
```
The process continues to run and I can see it migrating data but eventually after throwing the same error it throws the following
```
2019-06-19 14:30:50 FATAL 61 successive failures in streamer reconnect at coordinates mysql-bin.007205:4
```
Here are the versions of the master and slave
Replica Version:
```
mysql Ver 15.1 Distrib 10.1.37-MariaDB, for Linux (x86_64) using readline 5.1
```
Master Version:
```
mysql Ver 15.1 Distrib 10.1.32-MariaDB, for Linux (x86_64) using readline 5.1
```
This is the structure of the table being tested against
```
CREATE TABLE `table1` (
`table1_id` int(11) NOT NULL AUTO_INCREMENT,
`dtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`sid` varchar(50) NOT NULL DEFAULT '',
`bookdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`type` varchar(20) DEFAULT NULL,
`room` varchar(20) DEFAULT NULL,
`beds` int(5) DEFAULT NULL,
`price` double(16,4) DEFAULT NULL,
`status` enum('OK','CANX') NOT NULL DEFAULT 'OK',
`idRateTypeModel` int(11) NOT NULL DEFAULT '1',
`LastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`table1_id`),
KEY `dtime` (`dtime`),
KEY `sid` (`sid`),
KEY `bookdate` (`bookdate`),
KEY `room` (`room`),
KEY `sid_bkdate` (`sid`,`bookdate`),
KEY `idx_LastModified` (`LastModified`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
```
This is the binlog config on the replica
```
SLAVE:root@localhost:[(none)]> show global variables like '%bin%';
+-----------------------------------------+----------------------------------------------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------------------------------------------------------------------+
| binlog_annotate_row_events | OFF |
| binlog_cache_size | 32768 |
| binlog_checksum | NONE |
| binlog_commit_wait_count | 0 |
| binlog_commit_wait_usec | 100000 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | ROW |
| binlog_optimize_thread_scheduling | ON |
| binlog_row_image | FULL |
| binlog_stmt_cache_size | 32768 |
| encrypt_binlog | OFF |
| gtid_binlog_pos | 0-1016092-14515500241 |
| gtid_binlog_state | 0-10144913-14515313912,0-1016094-2668429493,0-1016091-6844356199,0-1016092-14515500241 |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | ON |
| log_bin_basename | /var/data/spool/mysql/mysql-bin |
| log_bin_index | /var/data/spool/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sql_log_bin | ON |
| sync_binlog | 0 |
| wsrep_forced_binlog_format | NONE |
+-----------------------------------------+----------------------------------------------------------------------------------------+
25 rows in set (0.00 sec)
```
I had the same error previously when using the MaxScale Binlog servers which is why I've setup this slave to connect to the Master and not to a Binlog server.
When I set this slave to use ROW replication I flushed all existing logs first.
Contributor guide
Assessment
This issue has not been assessed yet.