Connection is read-only. Queries leading to data modification are not allowed

2018/02/06 09:55
阅读数 33
看了下mysql-connector-5.1.40版本中,如果设置failoverReadOnly=true (即默认值,参考 链接),当mysql连接failover时,会根据jdbc连接串将当前连接的readOnly值设置为true (第8行代码)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
private  synchronized  void  switchCurrentConnectionTo( int  hostIndex, MySQLConnection connection)  throws  SQLException {
     invalidateCurrentConnection();
 
     boolean  readOnly;
     if  (isPrimaryHostIndex(hostIndex)) {
         readOnly =  this .explicitlyReadOnly ==  null  false  this .explicitlyReadOnly;
     else  if  ( this .failoverReadOnly) {
         readOnly =  true ;
     else  if  ( this .explicitlyReadOnly !=  null ) {
         readOnly =  this .explicitlyReadOnly;
     else  if  ( this .currentConnection !=  null ) {
         readOnly =  this .currentConnection.isReadOnly();
     else  {
         readOnly =  false ;
     }
     syncSessionState( this .currentConnection, connection, readOnly);
     this .currentConnection = connection;
     this .currentHostIndex = hostIndex;
}
  当执行update操作时,如果检测到readonly,就会跑出createSQLException (第8-9行代码)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
protected  int  executeUpdate( byte [][] batchedParameterStrings, InputStream[] batchedParameterStreams,  boolean [] batchedIsStream,  int [] batchedStreamLengths,
         boolean [] batchedIsNull,  boolean  isReallyBatch)  throws  SQLException {
 
     synchronized  (checkClosed().getConnectionMutex()) {
 
         MySQLConnection locallyScopedConn =  this .connection;
 
         if  (locallyScopedConn.isReadOnly()) {
             throw  SQLError.createSQLException(Messages.getString( "PreparedStatement.34" ) + Messages.getString( "PreparedStatement.35" ),
                     SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor());
         }
 
         ....
     }
}

  上述报错信息是PreparedStatement.34和PreparedStatement.35,查mysql connector的LocalizedErrorMessages.properties

1
2
PreparedStatement. 34 =Connection is read-only.
PreparedStatement. 35 =Queries leading to data modification are not allowed

  报错信息一致。

 

因此, 如果是使用的主备mysql,需要手动切换master和slave,如果使用的是多主的mysql(例如,phxsql),需要设置failoverReadOnly=false

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
OSCHINA
登录后可查看更多优质内容
返回顶部
顶部