MySQL Bugs: #49140: Insert / update / delete commands not replicated to slave in some circumstances

Bug #49140	Insert / update / delete commands not replicated to slave in some circumstances
Submitted:	26 Nov 2009 15:27	Modified:	14 Jan 2010 16:10
Reporter:	Maxime MARAIS	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	5.0.32-Debian-7etch1 -log, 5.0.84-0.dotdeb.0-log	OS:	Linux
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	binary log, delete, insert, replication, UPDATE

Description:
When executing a data altering query on a database when not explicitly connected to this database, insert, update, and delete queries are not saved to binary logs and, as a result, not replicated to slaves. This may also make a slave server to fail down when trying to insert data violating a unique key contraint (insert done, delete not taken into account as not present in binary log, reinsert fails due to contraint violation).

How to repeat:
/** PHP code to repeat dysfunction. **/

// Instanciation a PDO object.
// NOTE : no database is explicitly selected
$objPDO = new PDO('mysql:host=127.0.0.1', 'username', 'password');

// Prepare a simple insert query
$objPDOStatement = $objPDO -> prepare("
    INSERT     INTO MYBASE.mytable (
        myfield
    ) VALUES (
        :myvalue
    );
"; 

// Value binding...
$objPDOStatement -> bindValue(':myfield', 'myvalue');

// On execution, data is well inserted on the master database, but is not replicated on slave servers (statement not found in binary logs).
$objPDOStatement -> execute(); 

/** PHP code to skirt dysfunction. **/

// Instanciation a PDO object.
// NOTE : now, a database is explicitly selected with "dbname=MYBASE"
$objPDO = new PDO('mysql:host=127.0.0.1;dbname=MYBASE', 'username', 'password');

// Prepare a simple insert query
$objPDOStatement = $objPDO -> prepare("
    INSERT     INTO MYBASE.mytable (
        myfield
    ) VALUES (
        :myvalue
    );
"; 

// Value binding...
$objPDOStatement -> bindValue(':myfield', 'myvalue');

// On execution, data is well inserted on the master database, and also well  replicated on slave servers.
$objPDOStatement -> execute(); 

Suggested fix:
Write statements in binary logs even if a statement is executed on a different database than the database currently connected to.

Please, send your my.cnf file content.

From both master and slave I mean...

both my.cfn for master and first slave sent (visible to developpers only).

If you are 100% sure you did not have

binlog_do_db MYBASE

in my.cnf on master when it started, but still see that statements are written to the binary log only if this database is a default one, please, check with a newer version, 5.0.88, on master and inform about the results.

Valeriy, will plan to update our serveurs on december 25th, day when there will not be any activity in our company. I keep you in touch. Regards.

Maxime,

thank you for the feedback. Will set status of the report as "Need feedback" and we will wait results of your testing.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

As planned, our servers are now updated to version 5.0.84-0.dotdeb.0-log.

The issue we have on 5.0.32-Debian-7etch1-log remains on 5.0.84-0.dotdeb.0-log.

Thank you for the feedback.

But you used Debian package which can has own issues. Could you please try our 5.0.89 binaries available at http://dev.mysql.com/downloads/mysql/5.0.html? Please use Generic Linux binaries.

Sveta, we will not be able to install the version you are talking about, the server is used for a 24/7 application we can not stop, except one day by year at christmas or new year's day (dates where our compagny is closed).

This issue is quiet simple to reproduce. What about trying to install the same configuration in your lab? It's only two servers in standard master to slave replication.

Thank you for the feedback.

Verified as described.

Test case:

$cat src/tests/t/rpl_bug49140.test 
--source include/master-slave.inc
SELECT DATABASE();
create table test.t1(f1 int);

connect (addconroot, localhost, root,,*NO-ONE*);
connection addconroot;

SELECT DATABASE();
insert into test.t1 values(1);

prepare p1 from 'insert into test.t1 values(?)';
set @v1=2;
execute p1 using @v1;

sleep 2;
connection master;
SELECT DATABASE();
select * from t1;

connection slave;
SELECT DATABASE();
select * from t1;

Option file:

$cat src/tests/t/rpl_bug49140-master.opt 
--binlog-ignore-db=db1

This behavior described in bug #6749 and earlier was described in user manual, but after moving binlog-* rules to replicate-* rules manual is wrong (from http://dev.mysql.com/doc/refman/5.1/en/binary-log.html to http://dev.mysql.com/doc/refman/5.1/en/replication-rules-db-options.html).

Suggested fix: fix the manual, so it contains " (has any database been selected by USE?)" for binlog-ignore-db again.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated http://dev.mysql.com/doc/refman/5.1/en/replication-rules-db-options.html.