Investigation of One-Second Timestamp Discrepancy in MySQL Master‑Slave Replication with Server‑Side Prepared Statements

Recently a case was reported where a datetime field showed a one‑second discrepancy between MySQL master and slave, while other data appeared normal. Initial suspicion of manual modification or hacking was ruled out, and the issue was traced to a systematic difference where the slave’s timestamp was consistently one second behind the master.

Further analysis revealed that the problematic rows were inserted via the JDBC PreparedStatement with server‑side prepared statements enabled ( useServerPrepStmts=true ). The master’s binlog already contained timestamps that were one second smaller than the values stored in InnoDB, indicating the problem originated on the master side.

To reproduce the bug, a simple Java program was written that inserts the current timestamp into a table using a prepared statement. When executed with useServerPrepStmts=true , the binlog entries show timestamps that are one second less whenever the sub‑second part exceeds 0.5 seconds.

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);
            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();
        }
    }
}

The core reason lies in how MySQL handles fractional seconds for temporal types when server‑side prepared statements are used. During the Item_param::set_time call, the decimals field is forced to zero, causing the fractional part to be discarded when the SQL string is reconstructed for the binlog.

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;            // save time
  value.time.time_type= time_type;
  // ... range checks omitted ...
  state= TIME_VALUE;
  maybe_null= 0;
  max_length= max_length_arg;
  decimals= 0;               // precision forced to 0
  DBUG_VOID_RETURN;
}

When the query string is built ( Item_param::query_val_str ), the function my_TIME_to_str receives a precision of zero, so it truncates any sub‑second part, producing a string like '2018-11-02 10:45:17' for the binlog.

int my_datetime_to_str(const MYSQL_TIME *l_time, char *to, uint dec)
{
  int len= TIME_to_datetime_str(to, l_time); // converts up to seconds
  if (dec)
    len+= my_useconds_to_str(to + len, l_time->second_part, dec);
  else
    to[len]= '\0'; // discard sub‑second part
  return len;
}

In contrast, the InnoDB engine rounds the fractional part (using my_datetime_round ), adding one second when the sub‑second value exceeds 0.5 seconds. This mismatch between the SQL layer (truncation) and the storage engine (rounding) leads to the observed one‑second offset.

The issue was fixed in MySQL 5.7.18 and 5.6.36 by preserving the fractional second precision in server‑side prepared statements. Until upgrading, three work‑arounds are possible:

Zero out sub‑second precision on the client side before sending the timestamp.

Use row‑based binlog format instead of statement‑based.

Disable server‑side prepared statements ( useServerPrepStmts=false ).

Official commit details (SHA‑1: 6b3d07f3343a1fe7039cfc5fb8b6da092ccde793) describe the bug and its resolution: the fractional second part is ignored while preparing the query string, causing off‑by‑second differences on slaves; the fix sets the correct microsecond precision for temporal parameters.

In summary, the discrepancy originates from inconsistent handling of fractional seconds between the SQL layer and InnoDB when server‑side prepared statements are used; aligning the precision handling or upgrading to a version where the bug is patched eliminates the problem.