Invalidating query cache entries table mysql
If I do "stop slave" and then "start slave" again it immediately has an error like this, seemingly for random tables that have been written to (not one in particular): Could not execute Write_rows event on table XXX; Duplicate entry 'YYY' for key 'ZZZ', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binlog.000003, end_log_pos 704849 I have scoured the bug reports and found nothing to explain either of these. I'm running the Linux (AMD64 / Intel EM64T) build of 5.1.25 on a xeon box...nothing that unusual about my environment or database. If nobody has ideas I suppose I will file bug reports, but these are such general failures I don't know what to say in them other than "it don't work".
Only thing I could think of is that my app does some large insert delayed ...
Inno DB uses this primary key value to search for the row in the clustered index.
consider this on the master with --binlog-format=row : delete from t1 order by rand(); when t1 has N rows, and no primary/unique key, the slave must read N² rows to process the delete.
You may also see "invalidating query cache entries (table)" as a symptom in the processlist.
If you see that, check to see whether this is the possible root cause instead of giving full blame to only the query cache.
#run on master: drop table if exists t1; create table t1(a int)engine=innodb; insert t1 values (1),(2),(3),(4),(5); set @a=5; insert into t1 select (@a:[email protected] 1) from t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6,t1 t7; delete from t1 order by rand(); #watch as slave hits 100% of 1 cpu core and reads billions or rows.
Suggested fix: if nothing can be fixed or worked around on the server level, then clearly document the effects of having unindexed tables and row based replication: bug needs some serious attention, twice now I have run into this issue where the fix was to force kill the server, run the statement manually and then skip the binary log event.