Re: MysqlConnectionPoolDataSource - Can not insert to mysql db using jdbc?
Hi Filipe,
ps.executeUpdate() returns 1 for both cases...
Below is the general log from server. As far as I understand Id : 1884 is the case without paranoid flag and id : 1886 is the case when flag is set.. Please note that I tried to obfuscate sensitive info like schemaname, tablename and etc. in the log
Time Id Command Argument
2018-06-12T05:54:54.295318Z 1884 Connect mydbuser@myip on myschema using TCP/IP
2018-06-12T05:54:54.347660Z 1884 Query /* mysql-connector-java-8.0.11 (Revision: 6d4eaa273bc181b4cf1c8ad0821a2227f116fedf) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2018-06-12T05:54:54.416338Z 1884 Query SET NAMES latin5
2018-06-12T05:54:54.470331Z 1884 Query SET character_set_results = NULL
2018-06-12T05:54:54.494273Z 1884 Query SET autocommit=1
2018-06-12T05:54:54.607296Z 1884 Change user mydbuser@myip on myschema using TCP/IP
2018-06-12T05:54:54.636535Z 1884 Query SET NAMES latin5
2018-06-12T05:54:54.666301Z 1884 Query SET character_set_results = NULL
2018-06-12T05:54:57.996339Z 1884 Query INSERT INTO myschema.mytable (x,x,x) VALUES (y,y,y)
2018-06-12T05:55:33.495973Z 1886 Connect mydbuser@myip on myschema using TCP/IP
2018-06-12T05:55:33.515062Z 1886 Query SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2018-06-12T05:55:33.540498Z 1886 Query SET NAMES latin5
2018-06-12T05:55:33.608223Z 1886 Query SET character_set_results = NULL
2018-06-12T05:55:33.626139Z 1886 Query SET autocommit=1
2018-06-12T05:55:33.691626Z 1886 Query INSERT INTO myschema.mytable (x,x,x) VALUES (y,y,y)
The only difference I can see is, change user command is not issued when paranoid is set.. I think this is expected because resetServerState becomes no-op when flag is set. But I can not understand what difference it makes for commit...
com.mysql.cj.jdbc.MysqlPooledConnection
/**
* Invoked by the container. Return a logicalHandle object that wraps a
* physical connection.
*
* @see javax.sql.DataSource#getConnection()
*/
public synchronized Connection getConnection() throws SQLException {
return getConnection(true, false);
}
protected synchronized Connection getConnection(boolean resetServerState, boolean forXa) throws SQLException {
if (this.physicalConn == null) {
SQLException sqlException = SQLError.createSQLException(Messages.getString("MysqlPooledConnection.0"), this.exceptionInterceptor);
callConnectionEventListeners(CONNECTION_ERROR_EVENT, sqlException);
throw sqlException;
}
try {
if (this.logicalHandle != null) {
((ConnectionWrapper) this.logicalHandle).close(false);
}
if (resetServerState) {
this.physicalConn.resetServerState();
}
this.logicalHandle = ConnectionWrapper.getInstance(this, this.physicalConn, forXa);
} catch (SQLException sqlException) {
callConnectionEventListeners(CONNECTION_ERROR_EVENT, sqlException);
throw sqlException;
}
return this.logicalHandle;
}
com.mysql.cj.jdbc.ConnectionImpl
/**
* Resets the server-side state of this connection. Doesn't work if isParanoid() is set (it will become a
* no-op in this case). Usually only used from connection pooling code.
*
* @throws SQLException
* if the operation fails while resetting server state.
*/
public void resetServerState() throws SQLException {
if (!this.propertySet.getBooleanReadableProperty(PropertyDefinitions.PNAME_paranoid).getValue() && (this.session != null)) {
changeUser(this.user, this.password);
}
}