Forum Stats

  • 3,781,292 Users
  • 2,254,499 Discussions
  • 7,879,633 Comments

Discussions

The slave database become hang when shutting down

2784987
2784987 Member Posts: 54
edited Aug 24, 2016 8:37AM in MySQL Community Space

I have a master-slave configuration,when I use following JAVA code to insert rows into the master database,database hang will happen in the slave database when doing shutdown:

private boolean handleOne(XAConnection xaCon1,Connection connection, Xid xid1,int i,int threadNumber){
boolean result = false;


PreparedStatement pstmt = null;
XAResource xaRes1 = null;

try {
pstmt = connection.prepareStatement(insertSql);
xaRes1 = xaCon1.getXAResource();

xaRes1.start(xid1, XAResource.TMNOFLAGS);
pstmt.setString(1, ""+i);
pstmt.setString(2, "wuyiyuntest");
pstmt.setString(3, String.valueOf(i+1));
pstmt.executeUpdate();

xaRes1.end(xid1, XAResource.TMSUCCESS);
//xaRes1.prepare(xid1);
xaRes1.commit(xid1, true);
result = true;
} catch (SQLException e) {
try {
xaRes1.rollback(xid1);
} catch (XAException e1) {
inner_logger.error("TX ROLLBACK FAILED,XAException:",e);
}
inner_logger.error("INSERTING SINGLE ROW DATA FAILED,SQLException:",e);
} catch (XAException e) {
try {
xaRes1.rollback(xid1);
} catch (XAException e1) {
inner_logger.error("TX ROLLBACK FAILED,XAException:",e);
}
inner_logger.error("INSERTING SINGLE ROW DATA FAILED,XAException:",e);
} catch (Exception e) {
try {
xaRes1.rollback(xid1);
} catch (XAException e1) {
//inner_logger.error("TX ROLLBACK FAILED,XAException:",e);
}
//inner_logger.error("INSERTING SINGLE ROW DATA FAILED,Exception:",e);
} finally{
DataOperateTest.close(pstmt);
}
return result;
}

the error log of the slave database will be something like following:

....

2016-08-22T08:54:58.757916Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'

2016-08-22T08:54:58.757926Z 0 [Note] Shutting down plugin 'INNODB_TRX'

2016-08-22T08:54:58.757932Z 0 [Note] Shutting down plugin 'InnoDB'

2016-08-22T08:54:58.758017Z 0 [Note] InnoDB: FTS optimize thread exiting.

2016-08-22T08:54:58.758348Z 0 [Note] InnoDB: Starting shutdown...

2016-08-22T08:54:58.858604Z 0 [Note] InnoDB: Dumping buffer pool(s) to /data/mysql/mysql5.7.13/MYSQL3306/data/ib_buffer_pool

2016-08-22T08:54:58.859093Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 160822 16:54:58

2016-08-22T08:55:58.939602Z 0 [Note] InnoDB: Waiting for 853171 active transactions to finish

2016-08-22T08:56:59.121355Z 0 [Note] InnoDB: Waiting for 853171 active transactions to finish

2016-08-22T08:57:59.304303Z 0 [Note] InnoDB: Waiting for 853171 active transactions to finish

2016-08-22T08:58:59.487239Z 0 [Note] InnoDB: Waiting for 853171 active transactions to finish

2016-08-22T08:59:59.671308Z 0 [Note] InnoDB: Waiting for 853171 active transactions to finish

2016-08-22T09:00:59.854342Z 0 [Note] InnoDB: Waiting for 853171 active transactions to finish

2016-08-22T09:02:00.035826Z 0 [Note] InnoDB: Waiting for 853171 active transactions to finish

2016-08-22T09:03:00.219016Z 0 [Note] InnoDB: Waiting for 853171 active transactions to finish

......

Any ideas?

thanks

Answers