wrong parsed query in mysql slave

Question

I faced a strange issue.

database: MySQL5.7

OS: centOS6

setup the replication for Mysql, the primary server named host1, the secondary is host2.

the secondary is in read-only mode,  and disabled all application to connect this server.

I checked the DPA, found some DML queries in the slave, and these queries came from the server named 'NO_HOST_NAME', discussed with the dev team, they said the query runs on the primary server.

so I want to know why the issue happen,  and does the DPA tools parse the replication query? if the DPA parsed all queries included replication, is it missed to parse the filter?

thanks

mandevil · Answer

Taking offline for now - I'll direct message you.

xulean · Answer

Thanks, Mandevil, so is your meaning maybe the DPA get the wrong query alert on sometimes?

in fact, the DML query should not be on the screen. it only runs on master server.

mandevil · Answer

We get this information from the information_schema.processlist(host column) table and the performance_schema.threads(processlist_host column) table. It gets a bit more involved, but basically, we pull that data for anything that meets these criteria: WHERE t.PROCESSLIST_ID is not null AND t.INSTRUMENTED = 'YES' AND t.PROCESSLIST_ID <> connection_id() AND (t.PROCESSLIST_INFO is not null or s.SQL_TEXT is not null) AND (t.PROCESSLIST_COMMAND <> 'Sleep' or t.PROCESSLIST_TIME <= 2) Basically, seems like the replication query would not be excluded in this query, so likely that is why we capture it. If you query against host2 specifically and look for the host value (as described above), I'll bet you see the same results we show in DPA. We're only as good as what MySQL can tell us. Hope that helps though!