replication failed on slave due to super_read_only

Bug #1500638 reported by monty solomon on 2015-09-28
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
New
Undecided
Unassigned

Bug Description

The master executed this statement (excerpt from the master general log)

150928 22:10:19 2430019 Query SELECT 1
                2430891 Query UPDATE channels c, accounts a SET c.active = false WHERE c.active = true and a.deleted = true and c.accountGuid = a.accountGuid

and the slave stopped replicating (excerpt from the slave error log)

2015-09-28 22:10:19 5680 [ERROR] Slave SQL: Error 'The MySQL server is running with the --read-only (super) option so it cannot execute this statement' on query. Default database: 'BroadcastApi'. Query: 'UPDATE channels c, accounts a SET c.active = false WHERE c.active = true and a.deleted = true and c.accountGuid = a.accountGuid', Error_code: 1290
2015-09-28 22:10:19 5680 [Warning] Slave: The MySQL server is running with the --read-only (super) option so it cannot execute this statement Error_code: 1290
2015-09-28 22:10:19 5680 [Warning] Slave: Unknown error Error_code: 1105
2015-09-28 22:10:19 5680 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'bin.006687' position 214170

Excerpt from the slave general log

150928 22:10:19 255 Query BEGIN
                  255 Query UPDATE `heartbeat`.`heartbeat` SET ts='2015-09-28T22:10:19.000880', file='bin.006687', position='213731', relay_master_log_file=NULL, exec_master_log_pos=NULL WHERE server_id='1625821009'
                  255 Query COMMIT /* implicit, from Xid_log_event */
                  255 Query BEGIN
                  255 Query UPDATE channels c, accounts a SET c.active = false WHERE c.active = true and a.deleted = true and c.accountGuid = a.accountGuid

monty solomon (monty+launchpad) wrote :

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: calm-voice
                  Master_User: SUSR_Repl
                  Master_Port: 3306
                Connect_Retry: 15
              Master_Log_File: bin.006688
          Read_Master_Log_Pos: 226901
               Relay_Log_File: relay.005480
                Relay_Log_Pos: 214368
        Relay_Master_Log_File: bin.006687
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1290
                   Last_Error: Error 'The MySQL server is running with the --read-only (super) option so it cannot execute this statement' on query. Default database: 'BroadcastApi'. Query: 'UPDATE channels c, accounts a SET c.active = false WHERE c.active = true and a.deleted = true and c.accountGuid = a.accountGuid'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 214170
              Relay_Log_Space: 1370601
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /opt/mysql/dbdata1/public_key_certificates/ca-cert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /opt/mysql/dbdata1/public_key_certificates/client-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /opt/mysql/dbdata1/private_keys/client-key.pem
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1290
               Last_SQL_Error: Error 'The MySQL server is running with the --read-only (super) option so it cannot execute this statement' on query. Default database: 'BroadcastApi'. Query: 'UPDATE channels c, accounts a SET c.active = false WHERE c.active = true and a.deleted = true and c.accountGuid = a.accountGuid'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1625821009
                  Master_UUID: c0d78983-f8cf-11e4-9ea6-120e63b6c4e7
             Master_Info_File: /opt/mysql/dbdata1/broadcastapi-q0/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 150928 22:10:19
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: c0d78983-f8cf-11e4-9ea6-120e63b6c4e7:1154394-3332219
            Executed_Gtid_Set: 2be7a979-1a2c-11e5-b830-0e8fff638305:1-15,
c0d78983-f8cf-11e4-9ea6-120e63b6c4e7:1-3330031
                Auto_Position: 1
1 row in set (0.00 sec)

monty solomon (monty+launchpad) wrote :

mysql> show master status\G
*************************** 1. row ***************************
             File: bin.006688
         Position: 360796
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: c0d78983-f8cf-11e4-9ea6-120e63b6c4e7:1-3332524
1 row in set (0.00 sec)

monty solomon (monty+launchpad) wrote :

master

mysql Ver 14.14 Distrib 5.6.25-73.1, for Linux (x86_64) using 6.0
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f
Linux calm-voice 3.18.11-58.el6.x86_64 #1 SMP Mon Apr 20 12:36:31 EDT 2015 x86_64 x86_64 x86_64 GNU/Linux
CentOS release 6.6 (Final)

slave

mysql Ver 14.14 Distrib 5.6.25-73.1, for Linux (x86_64) using 6.0
Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f
Linux lingering-shape 3.18.11-58.el6.x86_64 #1 SMP Mon Apr 20 12:36:31 EDT 2015 x86_64 x86_64 x86_64 GNU/Linux
CentOS release 6.6 (Final)

YamakasY (yamakasi-014) wrote :

I have the same issue at the moment, is there a workaround for this ?

Start in normal mode, than switch to slave in the config and restart ?

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers