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
[root@localhost 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
[root@localhost 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
