# strange issue in replication mysql 5.5.28
**URL:** https://forums.percona.com/t/strange-issue-in-replication-mysql-5-5-28/2863
**Category:** Other MySQL® Questions
**Created:** [August 7, 2013, 1:57am UTC](https://forums.percona.com/t/strange-issue-in-replication-mysql-5-5-28/2863 "2013-08-07T01:57:24Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@yogesh777](https://forums.percona.com/u/yogesh777)
#### Post date: [August 7, 2013, 1:57am UTC](https://forums.percona.com/t/strange-issue-in-replication-mysql-5-5-28/2863/1 "2013-08-07T01:57:24Z")
i am facing very strange issue in mysql replication, I am using mysql 5.5.28 and statement based replication. all was going well when one of the developer report data inconsistencies on master and slave DB. After investigation i found update from application on database not getting reflected in binary log however updates on mysql console change the binary position.
On Master
mysql\> select count(_) from ad\_details;
±---------+
| count(_) |
±---------+
| 3520246 |
±---------+
mysql\> show master status\G
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
File: mysql-bin.000009
Position: 154166060
Binlog\_Do\_DB: cuser
Binlog\_Ignore\_DB: mysql,information\_schema
Now table ad\_details gets the updates and count increased however binary position not changed and data not reflected on slave
mysql\> select count(_) from ad\_details;
±---------+
| count(_) |
±---------+
| 3520248 |
±---------+
mysql\> show master status\G
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
File: mysql-bin.000009
Position: 154166060
Binlog\_Do\_DB: cuser
Binlog\_Ignore\_DB: mysql,information\_schema
On Slave
mysql\> show slave status\G
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
Slave\_IO\_State: Waiting for master to send event
Master\_Host: \*\*\*\*\*\*\*\*
Master\_User: repl
Master\_Port: 3306
Connect\_Retry: 60
Master\_Log\_File: mysql-bin.000009
Read\_Master\_Log\_Pos: 154166060
Relay\_Log\_File: mysqld-relay-bin.000030
Relay\_Log\_Pos: 253
Relay\_Master\_Log\_File: mysql-bin.000009
Slave\_IO\_Running: Yes
Slave\_SQL\_Running: Yes
Replicate\_Do\_DB: cuser
Replicate\_Ignore\_DB: mysql,information\_schema
Replicate\_Do\_Table:
Replicate\_Ignore\_Table:
Replicate\_Wild\_Do\_Table:
Replicate\_Wild\_Ignore\_Table:
Last\_Errno: 0
Last\_Error:
Skip\_Counter: 0
Exec\_Master\_Log\_Pos: 154166060
Relay\_Log\_Space: 410
Until\_Condition: None
also physical size of binary file is same as binary position and increasing proportionally to the binary position
[root@DB mysql]# ls -l mysql-bin.000009
-rw-rw---- 1 mysql mysql 154166060 Aug 7 12:06 mysql-bin.000009
Now I am creating a table manually on mysql console
mysql\> create table test1(id int);
Query OK, 0 rows affected (0.03 sec)
mysql\> show master status\G
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
File: mysql-bin.000009
Position: 154166151
Binlog\_Do\_DB: cuser
Binlog\_Ignore\_DB: mysql,information\_schema
On Slave
mysql\> show slave status\G
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
Slave\_IO\_State: Waiting for master to send event
Master\_Host: \*\*\*\*\*\*\*\*
Master\_User: repl
Master\_Port: 3306
Connect\_Retry: 60
Master\_Log\_File: mysql-bin.000009
Read\_Master\_Log\_Pos: 154166151
Relay\_Log\_File: mysqld-relay-bin.000030
Relay\_Log\_Pos: 344
Relay\_Master\_Log\_File: mysql-bin.000009
Slave\_IO\_Running: Yes
Slave\_SQL\_Running: Yes
Replicate\_Do\_DB: cuser
Replicate\_Ignore\_DB: mysql,information\_schema
Replicate\_Do\_Table:
Replicate\_Ignore\_Table:
Replicate\_Wild\_Do\_Table:
Replicate\_Wild\_Ignore\_Table:
Last\_Errno: 0
Last\_Error:
Skip\_Counter: 0
Exec\_Master\_Log\_Pos: 154166151
Relay\_Log\_Space: 501
Until\_Condition: None
also size of binary file also increased to the binary position
[root@DB mysql]# ls -l mysql-bin.000009
-rw-rw---- 1 mysql mysql 154166151 Aug 7 12:58 mysql-bin.000009
Any idea what is this happening?
---
### Author:  [@niljoshi](https://forums.percona.com/u/niljoshi)
#### Post date: [August 7, 2013, 3:27am UTC](https://forums.percona.com/t/strange-issue-in-replication-mysql-5-5-28/2863/2 "2013-08-07T03:27:34Z")
Hi,
binlog-do-db option is quiet dangerous. Because its not doing what it supposed to do. Check below post.
[URL][http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/[/URL]](http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/%5B/URL%5D)
So I would suggest you to filter database/tables on slave with replicate-wild-\* options rather then from master and check. Thanks.
---
### Author:  [@yogesh777](https://forums.percona.com/u/yogesh777)
#### Post date: [August 7, 2013, 3:55am UTC](https://forums.percona.com/t/strange-issue-in-replication-mysql-5-5-28/2863/3 "2013-08-07T03:55:04Z")
I got your point. How binary position relate to size of binary file size here in this particular case?
---
### Author:  [@mirfan](https://forums.percona.com/u/mirfan)
#### Post date: [August 7, 2013, 2:43pm UTC](https://forums.percona.com/t/strange-issue-in-replication-mysql-5-5-28/2863/4 "2013-08-07T14:43:01Z")
Hi,
If you are using replication filters then there is caveat which can cause this issue.
Let me show with one quick example.
```auto
master [localhost] {msandbox} ((none)) > use test
master [localhost] {msandbox} (test) > insert into t1 values (1),(2);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
master [localhost] {msandbox} (test) > select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > select * from test.t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
master [localhost] {msandbox} ((none)) > insert into test.t1 values (1),(2);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
master [localhost] {msandbox} ((none)) > select * from test.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 1 |
| 2 |
+------+
4 rows in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > select * from test.t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
```
You can see that when using database alias it fails to replicate so make sure you select database first to update via “USE db;” command in your app. This behavior is documented in MySQL Manual.
---
### Author:  [@yogesh777](https://forums.percona.com/u/yogesh777)
#### Post date: [August 7, 2013, 11:12pm UTC](https://forums.percona.com/t/strange-issue-in-replication-mysql-5-5-28/2863/5 "2013-08-07T23:12:49Z")
Thanks mirfan and niljoshi for clarification. I checked and verified it on my database. I am really surprised to see this is happening. I think to use these options developers should know how and when to use a database and query while writing code.
If these replication and binary logging options have such problems then why they are available to use and if so then for whats specific requirements we should use these options?
And any clue about binary position and size of binary file, Today again i check and see binary position and size of binary file is same.
mysql\> show master status\G
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
File: mysql-bin.000009
Position: 216476307
Binlog\_Do\_DB: cuser
Binlog\_Ignore\_DB: mysql,information\_schema
[root@DB mysql]# ls -l mysql-bin.000009
-rw-rw---- 1 mysql mysql 216476307 Aug 8 10:10 mysql-bin.000009