MySQL row based replication but still Statements in binlogDifference between row-based and statement-based...
Differential and Linear trail propagation in Noekeon
How to get the n-th line after a grepped one?
Describing a chess game in a novel
What favor did Moody owe Dumbledore?
Print a physical multiplication table
Using Past-Perfect interchangeably with the Past Continuous
Print last inputted byte
Deletion of copy-ctor & copy-assignment - public, private or protected?
Replace four times with sed
Writing in a Christian voice
How can an organ that provides biological immortality be unable to regenerate?
Is honey really a supersaturated solution? Does heating to un-crystalize redissolve it or melt it?
What is the significance behind "40 days" that often appears in the Bible?
Does multi-classing into Fighter give you heavy armor proficiency?
Suggestions on how to spend Shaabath (constructively) alone
Is there a term for accumulated dirt on the outside of your hands and feet?
gerund and noun applications
Asserting that Atheism and Theism are both faith based positions
How is the partial sum of a geometric sequence calculated?
What does "Four-F." mean?
HP P840 HDD RAID 5 many strange drive failures
What if (if any) the reason to buy in small local stores?
Calculate the frequency of characters in a string
I got the following comment from a reputed math journal. What does it mean?
MySQL row based replication but still Statements in binlog
Difference between row-based and statement-based replication in MySQL?Limit WHERE to MAX() & COUNT()mysql statement based replication - unsafe statementsMySql replication from 5.1 master to 5.6 slave keep crashingAre CREATE INDEX statements replicated in row-based Mysql Replication?Show sql statements from mysql binlog (GTID)MySQL Commited data not seen to select querycommit statement missing for a dml statement in binary logMySQL row-based replication - Delete FailsSlave failing to replication in maria db 10
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
mysql replication
asked Oct 5 '17 at 22:14
MschrMschr
1
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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
mysql replication
asked Oct 5 '17 at 22:14
MschrMschr
1
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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
add a comment |
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
mysql replication
asked Oct 5 '17 at 22:14
MschrMschr
1
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
mysql replication
mysql replication
asked Oct 5 '17 at 22:14
MschrMschr
1
asked Oct 5 '17 at 22:14
MschrMschr
1
asked Oct 5 '17 at 22:14
MschrMschr
1
asked Oct 5 '17 at 22:14
MschrMschr
1
asked Oct 5 '17 at 22:14
MschrMschr
1
1
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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
add a comment |
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
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
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
add a comment |
1 Answer
1
active
oldest
votes
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
answered Oct 5 '17 at 23:56
MschrMschr
1
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f187809%2fmysql-row-based-replication-but-still-statements-in-binlog%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Oct 5 '17 at 23:56
MschrMschr
1
add a comment |
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
answered Oct 5 '17 at 23:56
MschrMschr
1
add a comment |
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
answered Oct 5 '17 at 23:56
MschrMschr
1
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
answered Oct 5 '17 at 23:56
MschrMschr
1
answered Oct 5 '17 at 23:56
MschrMschr
1
answered Oct 5 '17 at 23:56
MschrMschr
1
answered Oct 5 '17 at 23:56
MschrMschr
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f187809%2fmysql-row-based-replication-but-still-statements-in-binlog%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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