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