Announcement

Announcement Module
Collapse
No announcement yet.

bug Percona 5.5.20 - replication binary log

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • bug Percona 5.5.20 - replication binary log

    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

  • #2
    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

    Comment

    Working...
    X