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

看了下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