bug Percona 5.5.20 - replication binary log

mysqldbahelpmysqldbahelp EntrantInactive User Role Participant
Hi guys

I think I have come across a bug - I havent confirmed whether this is a MySQL bug or a Percona bug.


Summary of bug
****************
When binary log ignore DB is set, without first using a database schema and running a fully quantified SQL statement specifying the schema database and table in the SQL (eg insert into foo.bar values('test');) the change statement isnt written to the binary log file.



How to replicate
****************
[[email protected] yum.repos.d]# rpm -qa | grep Perco
Percona-Server-shared-compat-5.5.20-rel24.1.217.rhel6.x86_64
Percona-Server-client-55-5.5.20-rel24.1.217.rhel6.x86_64
Percona-Server-shared-55-5.5.20-rel24.1.217.rhel6.x86_64
Percona-Server-server-55-5.5.20-rel24.1.217.rhel6.x86_64

Have a my.cnf of:

[mysqld]
log_bin
binlog_ignore_db = monitor
binlog_format = STATEMENT


Now do this:


mysql>
mysql> create database testbinlog;
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+
+
+
+

+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+
+
+
+

+
| localhost-bin.000003 | 107 | | monitor |
+
+
+
+

+
1 row in set (0.00 sec)

mysql> use testbinlog;
Database changed
mysql> create table testtable ( a int unsigned primary key auto_increment) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table testtable\G
*************************** 1. row ***************************
Table: testtable
Create Table: CREATE TABLE `testtable` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show master status;
+
+
+
+

+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+
+
+
+

+
| localhost-bin.000003 | 257 | | monitor |
+
+
+
+

+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> Bye
[[email protected] yum.repos.d]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.20-55-log Percona Server (GPL), Release rel24.1, Revision 217

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> insert into testbinlog.testtable vaules (NULL);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'vaules (NULL)' at line 1
mysql> insert into testbinlog.testtable values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+
+
+
+

+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+
+
+
+

+
| localhost-bin.000004 | 107 | | monitor |
+
+
+
+

+
1 row in set (0.00 sec)

mysql> insert into testbinlog.testtable values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+
+
+
+

+
[mysqld]
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+
+
+
+

+
| localhost-bin.000004 | 107 | | monitor |
+
+
+
+

+
1 row in set (0.00 sec)

mysql> insert into testbinlog.testtable values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+
+
+
+

+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+
+
+
+

+
| localhost-bin.000004 | 107 | | monitor |
+
+
+
+

+
1 row in set (0.00 sec)

mysql> use testbinlog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into testbinlog.testtable values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+
+
+
+

+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+
+
+
+

+
| localhost-bin.000004 | 351 | | monitor |
+
+
+
+

+
1 row in set (0.00 sec)

mysql> insert into testbinlog.testtable values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+
+
+
+

+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+
+
+
+

+
| localhost-bin.000004 | 595 | | monitor |
+
+
+
+

+
1 row in set (0.00 sec)

mysql> Ctrl-C -- exit!
Aborted







I assume this isnt expected behaviour? Just wanted to confirm here first.

Kind regards,

Trent Hornibrook
@mysqldbahelp

Comments

  • xaprbxaprb Mentor Inactive User Role Leader
    Have you checked the (complex) rules for this in the MySQL manual to see if this is expected? Off the top of my head I can't say it's a bug.

    If you file it as a bug, it will likely be marked a documentation bug :)
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.