In one of our environment mysql replication is failing intermittently. Below are the related mysql server logs.
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Connect_Retry: 60
Master_Log_File: mysql-bin.000421
Read_Master_Log_Pos: 997653853
Relay_Log_File: mysql-relay-bin.000058
Relay_Log_Pos: 498026393
Relay_Master_Log_File: mysql-bin.000421
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.servers,mysql.proc,mysql.user,mysql.columns_priv,mysql.ndb_binlog_index,mysql.slow_log,mysql.event,mysql.tables_priv,mysql.procs_priv,mysql.func,mysql.general_log,mysql.db,mysql.plugin
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.help%,ndbinfo.%,mysql.time_zone%,information_schema.%
Last_Errno: 1296
Last_Error: Error 'Got error 240 'Unknown error code' from NDBCLUSTER' on query. Default database: ''. Query: 'COMMIT'
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1296
Last_SQL_Error: Error 'Got error 240 'Unknown error code' from NDBCLUSTER' on query. Default database: ''. Query: 'COMMIT'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 22
Master_UUID:
Master_Info_File: /opt/mysql/cluster/server/database/master.info
1 row in set (0.00 sec)
Oracle support advice this error code related to Forgein Key, so we could able to isolated some queryes which happend on specific failier time.
Below is the last sql sucessfuly insert into master node and failed to replicate to slave.
### INSERT INTO `app_db`.`child_batch`
### SET
### @1=2080
### @4='000062'
### INSERT INTO `app_db`.`main_batch`
### SET
### @1=648518346341351432
### @6='NPROCESS\x02\x00\x00\x00\x00A\x00\x00\x00\x00\x00\x00\x00some_status\x00_app_event\x00ent\x00\x01\x0f\x11\x0f\x11\x0f\x08\x03@\x00\x00\x00'
### @7=0
Intersting thing related to both child_batch and main_batch table is, both have ID as primary key and one is FK for another one (InheritanceType.JOINED).
In JPA it is desiged as InheritanceType.JOINED.
@Table(name = "main_batch")
@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class MainBatch {
protected Long id;
protected Integer version;
private String transactionId;
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public void setVersion(Integer version) {
this.version = version;
}
@Version
public Integer getVersion() {
return version;
}
@Column(name = "transaction_id")
public String getTransactionId() {
return transactionId;
}
public void setTransactionId(String transactionId) {
this.transactionId = transactionId;
}
}
@Table(name = "child_batch", uniqueConstraints = {
@UniqueConstraint(columnNames = {
"received_file_name"
})
})
@Entity
public class ChildBatch extends MainBatch {
private String receivedFileName;
@Column(name = "received_file_name")
public String getReceivedFileName() {
return receivedFileName;
}
public void setReceivedFileName(String receivedFileName) {
this.receivedFileName = receivedFileName;
}
}
- Mysql Version : NDB 7.3.3 cluster
- OpenJPA : 2.2.2
- Java : 1.7.0_79
Since Mysql support say this is related to Foreign key , i suspect insert order in main_batch and child_batch is matter here. Because child_batch.id is FK for main_batch.id. So main_batch record should be available first before insert child_batch record.
If anyone has any suggestion or ideas, it would be greatly appreciated. Thanks in advance.
