| Bug #64325 | MySQL Replication not applying changes | ||
|---|---|---|---|
| Submitted: | 14 Feb 2012 18:07 | Modified: | 15 Feb 2012 12:05 |
| Reporter: | Renan RdS | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.1.49 | OS: | Linux (CentOS 5.5) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | freeze, mixed, relay, replication, Stuck | ||
[14 Feb 2012 18:15]
Valeriy Kravchuk
Looks like table you are trying to replicate does not have primary key defined (see bug #53375) Please, check. Also check if disabling query cache entirely helps in any way.
[14 Feb 2012 18:16]
Renan RdS
Changed from MySQL Replication stucked to MySQL Replication not applying changes
[14 Feb 2012 18:46]
Renan RdS
Hi Valeriy,
The table does not have a PK because it has rows with the same value:
mysql> select * from emailmkt.email_settings_cron_schedule limit 10;
+-----------------+------------+
| jobtype | lastrun |
+-----------------+------------+
| autoresponder | 0 |
| bounce | 0 |
| send | 1329132962 |
| triggeremails_s | 0 |
| triggeremails_p | 0 |
| maintenance | 1329071946 |
| splittest | 0 |
| send | 1329132962 |
| send | 1329132962 |
| send | 1329132962 |
+-----------------+------------+
10 rows in set (0.00 sec)
I've created indexes on both columns but the problem persists:
mysql> show create table emailmkt.email_settings_cron_schedule\G
*************************** 1. row ***************************
Table: email_settings_cron_schedule
Create Table: CREATE TABLE `email_settings_cron_schedule` (
`jobtype` varchar(20) DEFAULT NULL,
`lastrun` int(11) DEFAULT '0',
KEY `idx_teste1` (`jobtype`),
KEY `idx_teste2` (`lastrun`),
KEY `idx_teste3` (`jobtype`,`lastrun`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
About the query cache, I tried before setting query_cache_size=0, but the problem persisted.
[14 Feb 2012 20:28]
Renan RdS
By the way, binlog_format=MIXED. I'm not sure if this bug applies to it. Wouldn't MySQL use 'statement' instead of 'row' based replication for tables without PK?
[15 Feb 2012 5:09]
Valeriy Kravchuk
So, this is a duplicate of bug #53375. There is no real solution in frames of 5.1 or 5.5 (work in progress for future versions), other than add primary key or force statement based replication. Read http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html about the details of when MIXED means ROW actually. It depends on many things, like transaction isolation level for InnoDB, but it does not depend on presence of PK (unfortunately). Your even from the binary log is ROW...
[15 Feb 2012 12:05]
Renan RdS
Thanks a lot! I've changed binlog_format to STATEMENT. Is there a way to manually apply binlogs generated with ROW_BASED converting to STATEMENT_BASED? Because I want to apply the old binlogs instead of recreating the standby server. I tried "mysqlbinlog --verbose", but it only generates commented information that is not SQL standard. ### UPDATE emailmkt.email_settings_cron_schedule ### WHERE ### @1='send' ### @2=1329132962 ### SET ### @1='send' ### @2=1329133022
Description: "Show slave status" showing "seconds_behind_master=0", but the server is not replicating (Relay_Log_Pos not increasing and I also tested manually creating a table that did not replicate). The only way to restart the server is killing mysqld process. - Slave IO_Thread is working fine, byt SQL_Thread is not. It shows: Reading event from the relay log - Show engine innodb status shows some activity in the database: 0.00 inserts/s, 95.13 updates/s, 0.00 deletes/s, 1700978.09 reads/s ---TRANSACTION 0 327373568, ACTIVE 103 sec, process no 17462, OS thread id 1174251840 fetching rows mysql tables in use 1, locked 1 45 lock struct(s), heap size 6752, 18979 row lock(s), undo log entries 18966 MySQL thread id 4, query id 10 Reading event from the relay log - When I tried to apply the relay log manually, the connection gets stuck with the following information: invalidating query cache entries (table) BINLOG '\n3vU4TxMBAAAASgAAAMidPQAAAKABAAAAAAEACGVtYWlsbWt0ABxlbWFpbF9zZXR0aW5nc19jcm9u\nX3NjaGVkdWxlAA - The slave was created using mysqldump and took 24 hours to be built. - Initially, Seconds_behind_master was increasing but after a restart, it shows 0 (zero). - Its a multimaster replication but it is used as a master-standby configuration (read/write in a server and the other one is spare). The problem happens only in the master -> standby way. The other way is working fine (tested manually). master: isp-db-mysql-1 standby: isp-db-mysql-2 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: isp-db-mysql-1.be.infra Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: isp-db-mysql-1.052619 Read_Master_Log_Pos: 3138691 Relay_Log_File: isp-db-mysql-1-relay-bin.000002 Relay_Log_Pos: 32861 Relay_Master_Log_File: isp-db-mysql-1.052556 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 8109731 Relay_Log_Space: 6642282995 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.01 sec) mysql> show engine innodb status\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 120214 16:04:25 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 40 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1200, signal count 1198 Mutex spin waits 0, rounds 24291, OS waits 1067 RW-shared spins 176, OS waits 88; RW-excl spins 40, OS waits 40 ------------ TRANSACTIONS ------------ Trx id counter 0 327373569 Purge done for trx's n:o < 0 327373057 undo n:o < 0 0 History list length 5 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 17462, OS thread id 1174518080 MySQL thread id 318, query id 938 localhost root show engine innodb status ---TRANSACTION 0 327373568, ACTIVE 875 sec, process no 17462, OS thread id 1174251840 fetching rows mysql tables in use 1, locked 1 126 lock struct(s), heap size 30704, 54531 row lock(s), undo log entries 54507 MySQL thread id 4, query id 10 Reading event from the relay log -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 3912 OS file reads, 1877 OS file writes, 1553 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 2.02 writes/s, 1.72 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 49176, seg size 49178, 0 inserts, 0 merged recs, 0 merges Hash table size 21249871, node heap has 4 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 286 601068183 Log flushed up to 286 601067143 Last checkpoint at 286 601047367 0 pending log writes, 0 pending chkp writes 1206 log i/o's done, 1.35 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 11809719821; in additional pool allocated 1042688 Dictionary memory allocated 2076648 Buffer pool size 655360 Free buffers 651455 Database pages 3900 Modified db pages 3 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 3774, created 126, written 797 0.00 reads/s, 0.07 creates/s, 0.70 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 17462, id 1172920640, state: sleeping Number of rows inserted 0, updated 54507, deleted 0, read 1486372475 0.00 inserts/s, 30.00 updates/s, 0.00 deletes/s, 1618479.39 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ How to repeat: Just start the slave. Nothing is applied during 5 hours running the replication.