0

I use ROW based replication, but sometimes there are still SQL Statements in the binlog. You can find later some examples.

It seems that only some tables/queries are effected. Most events are row based. I could identify this Queries. There is a Perl Script that do this. But also one off our Java-Services (difference is only rewriteBatchedStatements=true in the JDBC Connection).

Maybe someone can help me here.

That issue is critical for me, because I want to monitor tables changes.

The Version of my MySQL Server is: 5.6.35-81.0-log Percona Server

eg:

#171005  4:23:15 server id 18  end_log_pos 438143 CRC32 0x1fa5c9ac      Query   thread_id=51274110      exec_time=0     error_code=0
SET TIMESTAMP=1507170195/*!*/;
BEGIN
/*!*/;
# at 438143
# at 438175
#171005  4:23:15 server id 18  end_log_pos 438175 CRC32 0x11673bd9      Intvar
SET INSERT_ID=156776/*!*/;
#171005  4:23:15 server id 18  end_log_pos 438707 CRC32 0x61de3d30      Query   thread_id=51274110      exec_time=0     error_code=0
SET TIMESTAMP=1507170195/*!*/;
insert into test_addon (test_id,hkey,hvalue) values ('9110082','key','sample')
/*!*/;
# at 438707
#171005  4:23:15 server id 18  end_log_pos 438738 CRC32 0x50d3f842      Xid = 13560868136
COMMIT/*!*/;
# at 438738

or

#171005  4:41:00 server id 18  end_log_pos 21919419 CRC32 0xcc94e7dc    Query   thread_id=51274110      exec_time=0     error_code=0
SET TIMESTAMP=1507171260/*!*/;
BEGIN
/*!*/;
# at 21919419
#171005  4:41:00 server id 18  end_log_pos 21919559 CRC32 0xdc72a052    Query   thread_id=51274110      exec_time=0     error_code=0
SET TIMESTAMP=1507171260/*!*/;
DELETE
FROM   domain_base_spool
WHERE  id='576123'
/*!*/;
# at 21919559
#171005  4:41:00 server id 18  end_log_pos 21919590 CRC32 0x126a90a5    Xid = 13561861610
COMMIT/*!*/;
# at 21919590

Row Events Example

#171005  4:41:00 server id 18  end_log_pos 21919682 CRC32 0x9aa3a079    Query   thread_id=71209397      exec_time=0     error_code=0
SET TIMESTAMP=1507171260/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
BEGIN
/*!*/;
# at 21919682
#171005  4:41:00 server id 18  end_log_pos 21919781 CRC32 0x8d9369ba    Table_map: `test`.`test_drop` mapped to number 19481071
# at 21919781
#171005  4:41:00 server id 18  end_log_pos 21919918 CRC32 0x48dc0b5a    Write_rows: table id 19481071 flags: STMT_END_F

BINLOG '
vJvV.......................3Eg=
'/*!*/;
# at 21919918
#171005  4:41:00 server id 18  end_log_pos 21919949 CRC32 0x15e4c43a    Xid = 13561861617
COMMIT/*!*/;
# at 21919949
  • Some additional information: This is in our DEV Environment, I just check PRODUCTION with Mysql: 5.5.50-MariaDB I didn't recognized this. – Mschr Oct 5 '17 at 23:29
0

I found out what it was. This services run over days without restarting. So the MySQL Session maybe didn't recognized that ROW Based Replication was activated.

Problem Solved

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.