Bug #69454 there are better ways to sync server clocks than MySQL query
Submitted: 12 Jun 2013 18:46 Modified: 12 Jun 2013 20:53
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.x OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D4 (Minor)

[12 Jun 2013 18:46] Domas Mituzas
Description:
MySQL has this amazing code to deal with a problem, that doesn't exist, yet introduces new problems :(

  if (!mysql_real_query(mysql, STRING_WITH_LEN("SELECT UNIX_TIMESTAMP()")) &&
      (master_res= mysql_store_result(mysql)) &&
      (master_row= mysql_fetch_row(master_res)))
  {
    mi->clock_diff_with_master=
      (long) (time((time_t*) 0) - strtoul(master_row[0], 0, 10));
  }

As there can be various sort of network delays that MySQL does not compensate (e.g. how long this query took and why, and should it discard results because of extended time it took to get the result, blah blah), this value is crap. In our production environment we end up seeing this value distorted by multiple seconds, thus not allowing us to understand replication health from 'SHOW SLAVE STATUS' 34th column. 

How to repeat:
SHOW SLAVE STATUS

Suggested fix:
http://en.wikipedia.org/wiki/Network_Time_Protocol
[12 Jun 2013 20:53] Miguel Solorzano
Thank you for the bug report.