CODEWITHSUNDEEP
mysqlreplicationunix-timestamp
ukits
ukits

Reputation: 67

MySQL replication uses unix_timestamp for timestamp type

MySQL Version: 5.6.21-log
OS: CentOS release 6.6 (Final)

I have two MySQL servers with replicaiton.

Slave server get error 1032

Could not execute Delete_rows event on table tosa.liv_low; Can't find record in 'liv_low', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000005, end_log_pos 381675314

The sql query statement on mysqlbinlog is 

DELETE FROM `tosa`.`liv_low`
WHERE
@1='BE41008-44500244'
@2=NULL
...
@30=NULL
@31=1418180543
@32=NULL

Original query is 

DELETE FROM `tosa`.`liv_low` WHERE BOSA_ID = 'BE41008-44500244'

The 31st column on schema is 

mysql> desc tosa.liv_low;

+--------------+-------------+------+-----+-------------------+-----------------------------+
| Field        | Type        | Null | Key | Default           | Extra                       |
+--------------+-------------+------+-----+-------------------+-----------------------------+
| BOSA_ID      | varchar(30) | YES  | MUL | NULL              |                             |
| LOT_NUM      | varchar(30) | YES  |     | NULL              |                             |
...
| TEC_current2 | float(5,2)  | YES  |     | NULL              |                             |
| IN_Date      | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Human        | varchar(20) | YES  |     | NULL              |                             |
+--------------+-------------+------+-----+-------------------+-----------------------------+

When I fetch a row, it has 'datetime' format value. And when I use timestamp value on WHERE clause, there is no result.

mysql> SELECT * FROM `tosa`.`liv_low` WHERE BOSA_ID='BE41008-44500244'\G
*************************** 1. row ***************************
     BOSA_ID: BE41008-44500244
     LOT_NUM: NULL
  Kink_level: NULL
...
TEC_current2: NULL
     IN_Date: 2014-12-10 12:02:23
       Human: NULL
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2014-12-10 12:02:23')\G
*************************** 1. row ***************************
UNIX_TIMESTAMP('2014-12-10 12:02:23'): 1418180543
1 row in set (0.00 sec)

mysql> SELECT * FROM `tosa`.`liv_low` WHERE IN_Date = 1418180543\G
Empty set, 1 warning (0.18 sec)

Why does MySQL replication use 'unix_timestamp' format on timestamp type field? It brokes replication.

Here is a replication part of my.cnf:

server-id                 = 5202

replicate-ignore-db       = cacti

log-bin                   = /home/mysql/log/mysql-bin.log
binlog-format             = ROW
binlog_cache_size         = 2M
max_binlog_size           = 1G
expire_logs_days          = 40

sync_binlog               = 1

log-slave-updates         = true
gtid-mode                 = on
;disable-gtid-unsafe-statements = true
enforce-gtid-consistency  = true
master-info-repository    = TABLE
relay-log-info-repository = TABLE
sync-master-info          = 1

Upvotes: 0

Views: 1130

Answers (1)

Dean Winchester
Dean Winchester

Reputation: 659

Are you sure this binlog comes from this SQL DELETE FROM tosa.liv_low WHERE BOSA_ID = 'BE41008-44500244'?

I did a simple test:

  1. create table create table a (id int(11), t timestamp)(no primary key, at first I thought this might have something to do with a table without an explicit primary key)

  2. insert data insert into a (id) values (1)

  3. delete it delete from a where id = 1.

What's in binary log is still delete from a where id = 1

Tested on 5.6.21-70.1-log, binlog format is MIXED(should not matter anyway)

Upvotes: 1

Related Questions