MySQL Bugs: #20269: Replication fails if query is issued command-line with mysql client (-e opt)

Bug #20269	Replication fails if query is issued command-line with mysql client (-e opt)
Submitted:	5 Jun 2006 14:14	Modified:	15 Jun 2006 10:57
Reporter:	Corrado Pandiani	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.20	OS:	Linux (Linux Debian)
Assigned to:		CPU Architecture:	Any

Description:
Replication fails, but don't stop, when a query is issued command-line with mysql client with -e option in the case you don't specify the target database but you use a qualified notation. See the example below.

How to repeat:
I have two servers: master host A and slave host B. The replication is correctly up&running. I use the 'test' database in the example.

on master A
corra@localhost[test]> create table sample(a int);
Query OK, 0 rows affected (0.03 sec)

corra@localhost[test]> insert into sample values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

corra@localhost[test]> select * from sample;
+------+
| a    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

on slave B
mysql> select * from sample;
+------+
| a    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

OK. Replication is working.

Now let's try to issue this command on master A at OS prompt

mysql test -e "insert into sample values(3)"

and then check the table on slave B
mysql> select * from sample;
+------+
| a    |
+------+
| 1    |
| 2    |
| 3    |
+------+
3 rows in set (0.00 sec)

this works correctly!

Now try to issue the command below on master A without spiecifying target database and using qualified notation
mysql -e "insert into test.sample values(4)"

on master A the query succeds
corra@localhost[test]> select * from sample;
+------+
| a    |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
+------+
4 rows in set (0.00 sec)

but on slave B does not
mysql> select * from sample;
+------+
| a    |
+------+
| 1    |
| 2    |
| 3    |
+------+
3 rows in set (0.00 sec)

but replication is up&running again
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
.....
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
.....
1 row in set (0.00 sec)

Take a look now to the binlog content on master A. Let's find out issued queries.

mysqlbinlog log-bin.000338 | grep "insert into sample"
insert into sample values(1),(2);
insert into sample values(3);

these queries are present in the binlog

but...
mysqlbinlog log-bin.000338 | grep "insert into test.sample"
has no result. The query issued using qualified notation was not written in the binlog.

Thank you for a problem report. Please, send my.cnf files content from master and slave.

This is master my.cnf ([mysqld] section only)

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306

server-id       = 1

binlog-ignore-db        = mysql

log-bin
expire_logs_days        = 5
log-error               = /var/log/mysql/mysql.err
log-slow-queries        = /var/log/mysql/slow-log

log-warnings

max_connections         = 200
thread_concurrency      = 8
table_cache             = 500
max_connect_errors      = 100
key_buffer_size         = 48M
thread_cache_size       = 20
long_query_time         = 3
tmp_table_size          = 8M
record_buffer           = 4M
query_cache_size        = 50M
query_cache_limit       = 524228
myisam_sort_buffer_size = 16M

ft_min_word_len         = 3
ft_stopword_file        = /etc/mysql/stopwordlist

max_allowed_packet      = 8M
thread_stack            = 128K

skip-innodb
skip-bdb

and this is slave my.cnf

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking

key_buffer              = 72M
sort-buffer_size        = 1M
max_allowed_packet      = 8M
thread_stack            = 128K

max_connections         = 100
thread_concurrency      = 8
table_cache             = 500
max_connect_errors      = 100
thread_cache_size       = 20
long_query_time         = 3
tmp_table_size          = 8M

ft_min_word_len         = 3
ft_stopword_file        = /etc/mysql/stopwordlist

query_cache_limit       = 512K
query_cache_size        = 100M
query_cache_type        = 1

server-id       = 2
master-host     = 2xx.1xx.1xx.1xx  (updated IP for security reason)
master-user     = myrepluser
master-password = mypwdrepluser
master-port     = 3306

max-relay-log-size      = 500M

log-slave-updates
log-bin                 = /var/log/mysql/zio-binlog
log-slow-queries        = /var/log/mysql/zio-slow.log

expire_logs-days        = 5
 
skip-dbd
skip-innodb

The same problem arises when you are using mysql client interactively.

If you issue such a query in the master:
mysql> insert into test.sample values(5);
it replicates correctly only if you have a default database defined (set for example with the USE command). The default database could be one of the defined databases, not only 'test'.
In the other case, if you are connected to the server but you didn't define a default database, the query works only on the master but not on the slaves. In the binlog file you can't find the entries related to that query.

I made some other tests on other machines and everything at the beginning worked well. 
The only difference I found was that on my master I have the 'binlog-ignore-db = mysql' option activated
I tried to disable the option and the master correctly began to write the qualified query without a default database.

So I tried to issue the same queries on the other machines with and without the binlog-ignore-db option. 
On every case the server behaviour was the same:
- with binlog-ignore-db: qualified query with no default database was not written to binlog
- without binlog-ignore-db: qualified query with no default database was correctly written to binlog  

My tests envirnment was:
Linux Fedora Core 4 , MySQL 5.0.18
Linux Debian, MySQL 4.1.11
Windows XP, MySQL 5.0.11

I hope this could help.

sorry .... no matter what database was specified in the binlog-ignore-db

> sorry .... no matter what database was specified in the binlog-ignore-db

So, you really mean that if any binlog-ignore-db value is present, even if it is not test, INSERT INTO test.anytable statements are not written to binlog? Please, check it with a newer version, 5.0.22, and inform about the results.

Yes, it works the same on 5.0.22. 
In fact this is a feature, not a bug.

I found the answer reading the official manual at "5.12.3. The Binary Log"

Here the explanation taken from the manual:

--binlog-ignore-db=dbname 
Tell the server to suppress binary logging of updates for which the default database is db_name (that is, the database selected by USE). If you use this option, you should ensure that you do updates only in the default database.
...
 The server evaluates the options for logging or ignoring updates to the binary log according to the following rules:

1 - Are there --binlog-do-db or --binlog-ignore-db rules?
    *No: Write the statement to the binary log and exit.
    *Yes: Go to the next step.
2 - There are some rules (--binlog-do-db, --binlog-ignore-db, or both). Is there a default database (has any database been selected by USE?)?
    *No: Do not write the statement, and exit.
    *Yes: Go to the next step.

....

So, it's not a bug, it's just a feature. 
But my opinion is that this is not a great and intuitive feature :-( 

Thanks

So, it is not a bug.