I have a couple of servers running in MIXED mode replication and I do maintenance regularly on them deleting a decent sized set of data. There is one table that had no primary key and is just used for logging. When we delete old data out of this table it runs fast on either the slave or the master but when it's part of a script that runs over night it blocks and runs very slowly on the slave. This was working fine when we were using statement based replication everywhere on an older version of MySQL.
The table has a structure something like this:
CREATE TABLE `user_events` ( `event_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `user_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `event_id` mediumint(8) unsigned NOT NULL DEFAULT '0', `type` tinyint(3) unsigned DEFAULT '0', `ipnum` int(10) unsigned NOT NULL, INDEX(`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
The delete statement is a simple.
DELETE FROM `user_events` WHERE `event_time` < CURDATE()
Doing research I discovered that it is possible that this is because the system is switching over to row-based replication for this particular statement. In particular it looks a lot similar to this bug: http://bugs.mysql.com/bug.php?id=53375
Only this delete does not use RAND() or LIMIT so it should not be nondeterministic. So I'm not sure why it is using row based. It might be because the script it is being run in is using temporary tables. I could confirm this by running the delete inside and outside a script that uses temporary tables easily enough if I could see whether the statement was logged using row or statement based replication.
If I can confirm when a particular statement is run whether it was written row or statement based I can quickly solve the problem. I would also like to know this in general so I can determine which if any statements we are using are being pushed via row based replication.
I know the ultimate solution is to change to partitioning or adding a primary key and breaking the deletes into chunks and I'm also aware I can force the replication mode to statement for a session or even tell the sql statement not to write to the bin log and run an equivalent delete on the slave, but I'm right now just looking for a better way to understand and diagnose the problem. Thanks.