Sql_Log_Bin And Replication

[crslsc]

We have two servers in a master/slave setup. We do all database & table creation and alters from within SQLYog. Is there anything within SQLYog that could cause SQL_LOG_BIN to be turned off for some statements, or does SQLYog just use whatever value of SQL_LOG_BIN is currently set at the server?

After creating a database on the master, creating some tables, and then using SQLYog's “Copy table to different host/database” function, we managed to break replication to the slave with an error about a missing table (one of the newly created tables). I'm trying to figure out what broke replication and wondering if there's any way that a creation/alter executed from within SQLYog wouldn't be replicated.

Thanks.

[peterlaursen]

“Is there anything within SQLYog that could cause SQL_LOG_BIN to be turned off for some statements?”  NO.   

SQLyog never executes SET sql_log_bin ON|OFF. You probably encountered a server bug.  You did not tell the server version, but there are dozens/hundreds of reports at bugs.mysql.com about replication breakage with various versions.

According to http://dev.mysql.com/doc/refman/5.0/en/set-sql-log-bin.html also only a user with SUPER privilege can do it.  Try connect with SQLyog as a user without that privilege.  That will make it clear the it is not a client issue. It is server internals.

To debug this I think you will need to enable the server general log, reproduce it and isolate the series of SQL statements causing this. the same series of SQL statements would probably then cause replication breakage no mater from what client they are executed.

[crslsc]

Great, thank you for the information.

[Author]

Posts