crash-resistant replication doesn't work when InnoDB operates with binary log disabled
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
| Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
Medium
|
Sergei Glushchenko | |
| 5.1 |
Fix Released
|
High
|
Sergei Glushchenko | |
| 5.5 |
Fix Released
|
High
|
Sergei Glushchenko | |
| 5.6 |
Fix Released
|
Medium
|
Sergei Glushchenko |
Bug Description
Steps to reproduce:
1) setup a slave (S1) ( (5.5.17-
2) make a copy via xtrabackup
3) create a new mysql instance (S2) (5.5.28-rel29.2) from backup taken at 2). Execute change master to make S2 a slave for S1 (new master)
4) check if replication is working correctly from S1 to S2.
5) execute kill -9 on mysqld process on S2
6) During recovery, old master log file and master log position are shown after
InnoDB: In a MySQL replication slave the last master binlog file
Related branches
- Laurynas Biveinis (community): Approve on 2013-05-14
-
Diff: 136 lines (+112/-0)4 files modifiedPercona-Server/mysql-test/suite/rpl/r/rpl_percona_bug1092593.result (+20/-0)
Percona-Server/mysql-test/suite/rpl/t/rpl_percona_bug1092593-slave.opt (+1/-0)
Percona-Server/mysql-test/suite/rpl/t/rpl_percona_bug1092593.test (+81/-0)
Percona-Server/storage/innodb_plugin/trx/trx0trx.c (+10/-0)
- Laurynas Biveinis (community): Approve on 2013-05-14
-
Diff: 138 lines (+114/-0)4 files modifiedPercona-Server/mysql-test/suite/rpl/r/rpl_percona_bug1092593.result (+20/-0)
Percona-Server/mysql-test/suite/rpl/t/rpl_percona_bug1092593-slave.opt (+1/-0)
Percona-Server/mysql-test/suite/rpl/t/rpl_percona_bug1092593.test (+81/-0)
Percona-Server/storage/innobase/trx/trx0trx.c (+12/-0)
- Laurynas Biveinis (community): Approve on 2013-05-14
-
Diff: 121 lines (+107/-0)3 files modifiedPercona-Server/mysql-test/suite/rpl/r/rpl_percona_bug1092593.result (+23/-0)
Percona-Server/mysql-test/suite/rpl/t/rpl_percona_bug1092593-slave.opt (+1/-0)
Percona-Server/mysql-test/suite/rpl/t/rpl_percona_bug1092593.test (+83/-0)
Nickolay Ihalainen (ihanick) wrote : | #2 |
I have 3 Virtual machines:
c1:
[mysqld]
server_id=1
innodb_
log-bin=
c2: slave of c1, donor for c3, future master of c3
[mysqld]
server_id=2
innodb_
log-bin=
relay-log=
innodb_
innodb_
log_slave_updates
c3: new server,
[mysqld]
server_id=3
innodb_
log-bin=
relay-log=
innodb_
innodb_
Steps to reproduce:
1) setup c1 with single innodb table
2) setup replication from c1(master) to c2(slave)
3) on c1 insert rows:
start while true ; do mysql test -e 'insert x values(1000);' ; done
4) c2, send kill -9 to mysqld_safe & mysqld
5) copy /var/lib/mysql from c2 to c3
6) close connections via tcp 3306 from c3 to c1
7) start mysql on c2
8) start mysql on c3
9) on c3: stop slave; change master (not working) ; reset slave;change master; start slave;
9) check on c3 if replication is up&running
10) on c3 kill -9 on mysqld pid
11) replication is not working now on c3, because it wont to use relay log names from c2.
SHOW SLAVE STATUS\G shows:
InnoDB: Warning: innodb_
InnoDB: relay-log.info is detected.
InnoDB: relay log: position 842094, file name ./mysql2_
InnoDB: master log: position 2341964, file name mysql1_
InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 2339279, file name mysql1_
InnoDB: and relay log file
InnoDB: position 839409, file name ./mysql2_
InnoDB: Last MySQL binlog file position 0 797205, file name ./mysql2_
Can't run change master:
change master to master_
130412 21:40:26 [ERROR] Failed to open the relay log './mysql2_
reset slave;
now change master is working
kill -9 to mysqld on new server
130412 21:42:05 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Warning: innodb_
InnoDB: relay-log.info is detected.
InnoDB: relay log: position 211907, file name ./mysql3_
InnoDB: master log: position 211757, file name mysql2_
InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 2339279, file name mysql1_
InnoDB: and relay log f...
Nickolay -
What VM software and client OSes do you use?
I am able to reproduce this following instructions provided by Nickolay.
The workaround is to turn on log-slave-updates on c3 as well.
summary: |
- crash resistant replication doesn't work correctly after change master - or reset slave + crash-resistant replication doesn't work when InnoDB operates with + binary log disabled |
Will port the testcase to 5.6.
David Smid (datasmid) wrote : | #7 |
Regression on Fedora 19 with 5.5.32-31.0
[12:23:00][Step 2/4] rpl.rpl_
[12:23:00][Step 2/4] rpl.rpl_
[12:23:15][Step 2/4] rpl.rpl_
[12:23:15][Step 2/4] Test ended at 2013-07-05 12:23:15
[12:23:15][Step 2/4] line
[12:23:15][Step 2/4] 130705 13:23:06 InnoDB: Error: table `mysqld.
[12:23:15][Step 2/4] 130705 13:23:06 InnoDB: Error: table `mysqld.
[12:23:15][Step 2/4] ^ Found warnings in /home/white_
[12:23:15][Step 2/4] ok
[12:23:15][Step 2/4]
[12:23:15][Step 2/4] - saving '/home/
[12:23:15][Step 2/4] mysql-test-run: *** ERROR: Not all tests completed
[12:23:15][Step 2/4]
[12:23:15][Step 2/4] Only 341 of 2518 completed.
David -
Please open a new bug for this. Thanks!
Shahriyar Rzayev (rzayev-sehriyar) wrote : | #9 |
Percona now uses JIRA for bug reports so this bug report is migrated to: https:/
Not seeing the expected result. But logs need to be further verified.
The hosts were prepared as described in the bug report.
Full slave error log is here http:// sprunge. us/RERT (ignore the 1062 error for now)
Few show slave status outputs: http:// sprunge. us/MFNJ
Interesting to note:
a)
show slave status\G ******* ******* ****** 1. row ******* ******* ******* ******
Slave_ IO_State: Queueing master event to the relay log
Master_ Host: Percona
Master_ User: root
Master_ Port: 3306
Connect_ Retry: 60
Master_ Log_File: mysql-bin.000004
Read_ Master_ Log_Pos: 75733104
Relay_ Log_File: archie- relay-bin. 000004
Relay_ Log_Pos: 14733102
Relay_ Master_ Log_File: mysql-bin.000004
Slave_ IO_Running: Yes
Slave_ SQL_Running: Yes
Replicate_ Do_DB:
Replicate_ Ignore_ DB:
Replicate_ Do_Table:
Replicate_ Ignore_ Table: _Wild_Do_ Table: Wild_Ignore_ Table:
Last_ Errno: 0
Last_ Error:
Skip_ Counter: 0
Exec_ Master_ Log_Pos: 72653284
Relay_Log_ Space: 39013793
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: 1 SSL_Verify_ Server_ Cert: No
Last_ IO_Errno: 0
Last_ IO_Error:
Last_SQL_ Errno: 0
Last_SQL_ Error: Ignore_ Server_ Ids:
Master_ Server_ Id: 392
*******
Replicate
Replicate_
Master_
Replicate_
1 row in set (0.00 sec)
mysql> mysql/mysql. sock' (111)
mysql> show slave status\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/
ERROR:
Can't connect to the server
b)
InnoDB: In a MySQL replication slave the last master binlog file relay-bin. 000004 mysql/mysql- bin.000002
InnoDB: position 79107507, file name mysql-bin.000004
InnoDB: and relay log file
InnoDB: position 21187325, file name ./archie-
InnoDB: Last MySQL binlog file position 0 22958867, file name /var/lib/
As can be seen, the position looks good.
@Nickolay, can you verify the above results? I crashed it twice so as to get more results.