Bug #37483 MySQL-Query-Browser and replication bug
Submitted: 18 Jun 2008 14:56 Modified: 21 Jan 2009 6:22
Reporter: Tibor Kesztyues Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Query Browser Severity:S1 (Critical)
Version:1.2.12 OS:Mac OS X (and Linux (Debian 4.0))
Assigned to:
Tags: mysql-query-browser, replication

[18 Jun 2008 14:56] Tibor Kesztyues
Description:
I have a strange behaviour with the query-browser and our replicated database servers. We have two MySQL 5.0.32 Servers No. 1 is master, no. 2 is slave. We are wirking only with InnoDB storage engine for the replicated databases.
Replication works fine with our applications, every commited update or insert is replicated correctly.

If I change records on the master server with the MySQL-Query-Browser, the changes are NOT replicated. Same thing if I insert new records in the tables. 
I can reproduce the error on Linux and on MacOS. Have  not tested with the MS-Win Version of the MySQL-Query-Browser at the moment.

If I use another tool like PHPMyadmin everything is fine again. All changes on the master server are replicated to the slave.

How to repeat:
Just open two instances of the MySQL-Query-Browser on a computer. Then connect with one to the master server and with the other to the slave server.
If you perform changes on the master in a record you'll see that the changes are not replicated to the slave.

With PHPMyadmin it works.
[18 Jun 2008 17:06] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Please provide your actions step-by-step and both slave and master configuration files.
[19 Jun 2008 6:54] Tibor Kesztyues
master configuration file

Attachment: my.cnf.master (application/octet-stream, text), 3.78 KiB.

[19 Jun 2008 6:54] Tibor Kesztyues
slave configuration file

Attachment: my.cnf.slave (application/octet-stream, text), 3.87 KiB.

[19 Jun 2008 7:02] Tibor Kesztyues
As I already wrote:
Just open two instances of the MySQL-Query-Browser on a computer. Then connect with one to the master server and with the other to the slave server.
Pick the right schema - the one which is in the replication - in our case by the configuration files: "testdatabase".
On this schema you open a table and perform changes with the query-browser. If you commit the changes you'll see that these changes are NOT replicated.
Same result if you insert a new record to the table.
With PHPMyadmin everything works perfectly, also with Emma or our applications. If I work with the MySQL-Admin tool for adding or changing tables, I have no problem. Also here the changes are replicated.
So, it's clearly a problem of the MySQL-Query-Browser.

In the attachments you'll find our configuration files. The replication user has of course the necessary rights on the master server.

The replication is not the problem! Everything works just fine! It's only the MySQL-Query-Browser.
[19 Jun 2008 10:42] Tibor Kesztyues
An update: 
I tested it now on MS-Win XP with MySQL-Query-Browser Ver. 1.2.10 and this works correctly.
So, obviously there's only a problem with the X-implementations of the MySQL-Query-Browser.
[19 Jun 2008 18:01] Sveta Smirnova
Thank you for the feedback.

I still can not repeat described behavior on Mac OSX 10.4.

What I did:

1. Copied query cache settings from your configuration files into mine.
2. Restarted master and slave
3. Created table:
CREATE TABLE `test` (
  `ID` int(11) NOT NULL auto_increment,
  `TEXT` text NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
4. On master inserted values (1, 'foo') into it
5. On slave performed SELECT * FROM test
6. On master performed SELECT * FROM test
7. On master clicked "Edit" button, then changed "foo" to "bar"
8. On master performed  SELECT * FROM test to be sure changes were made
9. On slave performed SELECT * FROM test and found data has replicated
10. Repeated steps 6-9 several times with different values. All times changes were replicated.

Please examine "how-to" above and correct it in such a way what we can repeat the problem.
[19 Jul 2008 23:00] Bugs System
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".
[30 Sep 2008 14:27] Eric Pretet
I have exactly the same problem. 2 servers with mysql 5.0.32 from debian etch (4.0). When I update from Mysql Query Browser on one (slave or master), the other don't change.

Thank's
[2 Oct 2008 8:53] Sveta Smirnova
Eric,

thank you for the feedback. Please try with current server version 5.0.67. Also please use our binaries available from http://dev.mysql.com/downloads to be sure this is not problem of Debian binaries.
[3 Nov 2008 0:00] Bugs System
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".
[21 Jan 2009 1:51] Geoff Reidy
Hi,

I also see this, when using the "start editing" option.

Server version: 5.0.20a-standard-log.

I can't really update the version but I do have some thoughts as to the possible cause.

What I have noticed when this occurs is that the binary log is not updated.

According to this page:
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

Evaluation of update selection options.   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.

At step 1 we do use --binlog-do-db and --binlog-ignore-db so we go to step 2.

The problem happens when I haven't issued a "use" command or double clicked on a database but have used "SELECT FROM database.table WHERE ... and then added a row using the gui.

If I issue a "use database" command first the binary log is updated when I add a row and then replication can work.

Also if I double click on _any_ database first the log is updated.

I suspect the query-browser needs to issue a use command before inserting records.

Regards,
Geoff
[21 Jan 2009 5:01] Geoff Reidy
Just to confirm I can do this from the command line client.

If I do not specify a default database and do not use a "use database" command and then run:
INSERT INTO database.table VALUES ('x', 'y', 'z');

the binary log is not updated as per the rules stated in the documentation (see the link in my last comment).

I don't know what the answer is other than "don't do that then".

Am I missing something obvious??

Regards,
Geoff
[21 Jan 2009 6:22] Sveta Smirnova
Geoff,

thank you for the investigation.

> If I issue a "use database" command first the binary log is updated when I add a row and
then replication can work.
>
> Also if I double click on _any_ database first the log is updated.

This means Query Browser works as it should. It should possibility to issue statements without using USE to have same functionality like mysql command line client: there can be cases when one who has --binlog-do/ignore-* rules don't want database is updated.
[21 Jan 2009 7:24] Geoff Reidy
Sveta,

Thanks for getting back to me quickly.

So this is how it's meant to work?

It's a bit of a trap though isn't it?

Our "replicated" databases ended up being quite different here because no one knew about that.

Oh well, I have removed the binlog-do-db and binlog-ignore-db statements from my.cnf and can confirm that the binary log is now updated even without a default db or a use statement being issued.

So my problems are solved.

Thanks again,
Geoff
[1 Mar 2010 13:55] Remco Jansen
I confirm the last 4 comments on this bug report. When using the ignore-db option for excluding a database schema from replication, a "USE [db]" has to be issued before executing queries in order to get these queries logged in the binary logs. 

This is definitely a bug in MySQL, since it creates a possibility for data inconsistency between master and slave that nobody is aware of!

I suggest changing the status of this bug report. I am experiencing the bug on RedHat Linux, so please extend the list of affected OS'es as well. 

Thanks, Remco
[13 May 2011 13:39] marc drolet
I'm facing the same problem.  My master server log all bin-log, no binlog-do-db ... and I have a couple of database into it.

On my slave, I'm replicating only one of the master database "test" and I set replication-do-db=test into my slave my.cnf

queries executed by the mysql query browser are not replicated to the slave.