时间精度引起MySQL主从不一致问题剖析

import java.sql.*;
import java.text.SimpleDateFormat;
public class insertData {
    public static void main(String[] args)
    {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://127.0.0.1:3306/mydb?useServerPrepStmts=true";
        String user = "u1";
        String password = "123456";
        try 
        {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, user, password);
            if(!conn.isClosed())
                System.out.println("Succeeded connecting to the Database!");
            long time = System.currentTimeMillis();
            Date date = new Date(time);
                java.sql.Timestamp tmp= new java.sql.Timestamp(date.getTime());
              System.out.println(tmp);      

            //create table test(a datetime);
            PreparedStatement pstmt = conn.prepareStatement("insert into test values(?)");
            pstmt.setTimestamp(1, tmp);
            pstmt.execute();
            conn.close();
        }
        catch(ClassNotFoundException e) 
        {
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        }
        catch(SQLException e) 
        {
            e.printStackTrace();
        } catch(Exception e) 
        {
            e.printStackTrace();
        }
    }
}

937        if (query->replace(param->pos_in_query+length, 1, *res))
(gdb) bt
#0  insert_params_with_log (stmt=0x7f77e8c56280, null_array=0x7f77f7f6600a "", read_pos=0x7f77f7f6601a "", data_end=0x7f77f7f6601a "", query=0x7f7879e29f00) at /data/new56/event/CDB/sql/sql_prepare.cc:937
#1  0x000000000081eb0c in Prepared_statement::set_parameters (this=0x7f77e8c56280, expanded_query=0x7f7879e29f00, packet=0x7f77f7f6600e "\v\342\a\v\002\n\005\065\370\334\f", packet_end=0x7f77f7f6601a "")
    at /data/new56/event/CDB/sql/sql_prepare.cc:3594
#2  0x000000000081ebeb in Prepared_statement::execute_loop (this=0x7f77e8c56280, expanded_query=0x7f7879e29f00, open_cursor=false, packet=0x7f77f7f6600a "", packet_end=0x7f77f7f6601a "")
    at /data/new56/event/CDB/sql/sql_prepare.cc:3655
#3  0x000000000081c845 in mysqld_stmt_execute (thd=0x7f77f7f62000, packet_arg=0x7f77f7f66001 "\001", packet_length=25) at /data/new56/event/CDB/sql/sql_prepare.cc:2701
#4  0x00000000007f972a in dispatch_command (command=COM_STMT_EXECUTE, thd=0x7f77f7f62000, packet=0x7f77f7f66001 "\001", packet_length=25) at /data/new56/event/CDB/sql/sql_parse.cc:1351
#5  0x00000000007f8931 in do_command (thd=0x7f77f7f62000) at /data/new56/event/CDB/sql/sql_parse.cc:1060
#6  0x00000000007be7d1 in do_handle_one_connection (thd_arg=0x7f77f7f62000) at /data/new56/event/CDB/sql/sql_connect.cc:998
#7  0x00000000007be2ab in handle_one_connection (arg=0x7f77f7f62000) at /data/new56/event/CDB/sql/sql_connect.cc:905
#8  0x0000000000b2f665 in pfs_spawn_thread (arg=0x7f7877ffd920) at /data/new56/event/CDB/storage/perfschema/pfs.cc:1860
#9  0x00007f7879b6d9d1 in start_thread () from /lib64/libpthread.so.0
#10 0x00007f78788d68fd in clone () from /lib64/libc.so.6
(gdb) p *query
$1 = {Ptr = 0x7f77e8c15040 "insert into test values(?)", str_length = 26, Alloced_length = 32, alloced = true, str_charset = 0x182f260}
(gdb) n
940        length+= res->length()-1;
(gdb) p *query
$2 = {Ptr = 0x7f77e8c1c070 "insert into test values('2018-11-02 10:45:17')", str_length = 46, Alloced_length = 48, alloced = true, str_charset = 0x182f260}

static void set_param_datetime(Item_param *param, uchar **pos, ulong len)
{
  MYSQL_TIME tm;
  ulong length= get_param_length(pos, len);//前端发过来的参数长度

  if (length >= 4)
  {//下面把前端发过来的参数转成MYSQL_TIME时间
    uchar *to= *pos;

    tm.neg=    0;
    /*
     前端发过来时间是数字内存转的字符串:20181101125510124566，代表的时间为2018年11月01日12点55分10秒124566微秒
     分别用2字节存年，月日时分秒均用1个字节存，微秒4字节，一共2+1+1+1+1+1+4=11字节，前端发过来的时间
     格式就是按这个方式把数据堆在一起，下面的逻辑就是把时间还原出来   
    */
    tm.year=   (uint) sint2korr(to);
    /*
     sint2korr(A) (int16) (((int16) ((uchar) (A)[0])) + ((int16) ((int16) (A)[1]) << 8))
     把两个字节所表示的整数还原出来，下面sint4korr把四字节表示的整数还原出来
    */
    tm.month=  (uint) to[2]; //0~255之间值一个字节可以存放
    tm.day=    (uint) to[3];
    if (length > 4)
    {
      tm.hour=   (uint) to[4];
      tm.minute= (uint) to[5];
      tm.second= (uint) to[6];
    }
    else
      tm.hour= tm.minute= tm.second= 0;

    tm.second_part= (length > 7) ? (ulong) sint4korr(to+7) : 0;
  }
  else
    set_zero_time(&tm, MYSQL_TIMESTAMP_DATETIME);

  /*
   在此时打印转换后的时间是带精度的，精度在second_part 中，如下：
   (gdb) p tm
   $9 = {year = 2018, month = 11, day = 2, hour = 10, minute = 45, second = 17, second_part = 908000, neg = 0 '\000', time_type = 32632}
  */
  param->set_time(&tm, MYSQL_TIMESTAMP_DATETIME,
                  MAX_DATETIME_FULL_WIDTH * MY_CHARSET_BIN_MB_MAXLEN);
  *pos+= length;
}

void Item_param::set_time(MYSQL_TIME *tm, timestamp_type time_type,
                          uint32 max_length_arg)
{ 
  DBUG_ENTER("Item_param::set_time");

  value.time= *tm;           //保存时间
  value.time.time_type= time_type;

  if (check_datetime_range(&value.time))
  {
    make_truncated_value_warning(ErrConvString(&value.time,
                                               MY_MIN(decimals,
                                                      DATETIME_MAX_DECIMALS)),
                                 time_type);
    set_zero_time(&value.time, MYSQL_TIMESTAMP_ERROR);
  }

  state= TIME_VALUE;
  maybe_null= 0;
  max_length= max_length_arg;
  decimals= 0;            //精度设为0,不管原来值是多少，一律清0，【【【【关键点在这一行】】】】
  DBUG_VOID_RETURN;
}

const String *Item_param::query_val_str(THD *thd, String* str) const
{
  switch (state) {  //state值为TIME_VALUE
  case INT_VALUE:
    str->set_int(value.integer, unsigned_flag, &my_charset_bin);
    break;
  case REAL_VALUE:
    str->set_real(value.real, NOT_FIXED_DEC, &my_charset_bin);
    break;
  case DECIMAL_VALUE:
    if (my_decimal2string(E_DEC_FATAL_ERROR, &decimal_value,
                          0, 0, 0, str) > 1)
      return &my_null_string;
    break;
  case TIME_VALUE://==========>进入
    {
      char *buf, *ptr;
      str->length(0);
      /*
        TODO: in case of error we need to notify replication
        that binary log contains wrong statement 
      */
      if (str->reserve(MAX_DATE_STRING_REP_LENGTH+3))
        break; 

      /* Create date string inplace */
      buf= str->c_ptr_quick();
      ptr= buf;
      *ptr++= '\'';
      /*
        这里decimals在前面set_time中被设置成了0，MY_MIN(decimals, DATETIME_MAX_DECIMALS)为
        取小值，因此 my_TIME_to_str(&value.time, ptr,MY_MIN(decimals, DATETIME_MAX_DECIMALS))
        等价于my_TIME_to_str(&value.time, ptr,0) ，传入的精度位数为0，my_TIME_to_str函数是把时间
        转成字符串，如果精度为0直接忽略秒以下值
      */
      ptr+= (uint) my_TIME_to_str(&value.time, ptr,
                                  MY_MIN(decimals, DATETIME_MAX_DECIMALS));
      *ptr++= '\'';
      str->length((uint32) (ptr - buf));
      break;
    }
  case STRING_VALUE:
  case LONG_DATA_VALUE:
    {
      str->length(0);
      append_query_string(thd, value.cs_info.character_set_client, &str_value,
                          str);
      break;
    }
  case NULL_VALUE:
    return &my_null_string;
  default:
    DBUG_ASSERT(0);
  }
  return str;
}

//l_time 待转的时间
//to 存转换后的时间字符串
//dec 精度
int my_datetime_to_str(const MYSQL_TIME *l_time, char *to, uint dec)  
{
  int len= TIME_to_datetime_str(to, l_time); //TIME_to_datetime_str仅转换年月日时分秒
  if (dec) //精度不为0则进入把精度转换处理
    len+= my_useconds_to_str(to + len, l_time->second_part, dec);
  else  //精度为0则直接舍弃秒以下精度。前面已经将精度置为0，这种场景下，每次都走这个else逻辑分支
    to[len]= '\0';  //【【【【取完秒后直接结束字符串，秒以下精度一律舍弃】】】】
  return len;
}

SHA-1: 6b3d07f3343a1fe7039cfc5fb8b6da092ccde793

* BUG#25187670: BACKPORT BUG#19894382 TO 5.6 AND 5.7.

Backport from mysql-trunk to mysql-5.6 and mysql-5.7.

Bug#19894382 SERVER SIDE PREPARED STATEMENTS LEADS TO POTENTIAL
             OFF-BY-SECOND TIMESTAMP ON SLAVE

For temporal type input parameter (as time, timestamp and
datetime) of server side prepared statement, the fractional
second part is ignored while preparing the query string.
Executing such query string at slave is resulting in a time
value difference between master and server.

For server prepared statement, the parameters passed for
execution are parsed and Item_param objects are created for
it. While preparing Item_param for temporal types time,
timestamp and datetime, precision for fractional second part's
(Item_param::decimals) is set to "0" always. Because of which
while preparing query string with param values, fractional second
value is ignored. So execution of such prepared statements uses
correct values but query string formed is incorrect. Hence
difference in temporal type value is observed on usage of query
string.
Fix:
--------
If temporal types time, timestamp and datetime has fractional
second part then setting microseconds (6 digit) precision for
the Item_param.