MysqlConnectionPoolDataSource - Can not insert to mysql db using jdbc?
Posted by: Ceyhan Kasap
Date: June 04, 2018 04:22AM

I have to use a custom jdbc connection pooling library to connect to mysql db.

The problem is my that inserts are not persisted in db... To narrow down the issue, I extracted the portion of the library code inserting into db and have some strange findings:

MysqlConnectionPoolDataSource ds1 = new MysqlConnectionPoolDataSource();
ds1.setUser("usr");
ds1.setPassword("pwd");
ds1.setServerName("server");
ds1.setPort(port);
ds1.setDatabaseName("dbname");
ds1.setUseSSL(false);
ds1.setAllowPublicKeyRetrieval(true);


Connection conn = ds1.getPooledConnection("usr", "pwd").getConnection();

logger.info("connection " + conn.toString());

PreparedStatement ps = null;
ResultSet rs = null;

try {

String query = "INSERT INTO ...";
ps = conn.prepareStatement(query);

int timeout = 10;
ps.setQueryTimeout(timeout);

logger.info("timeout: " + timeout);
logger.info("Starting query execution for query: " + query);
long qeStart = System.currentTimeMillis();

ps.setString(1, "...");

ps.executeUpdate();

long qeEnd = System.currentTimeMillis();
logger.info("Query execution completed in " + (qeEnd - qeStart) + "msec.");
} catch (Exception e) {
logger.error("ERROR OCCURED", e);
System.err.println("ERROR OCCURED");
e.printStackTrace();

} finally {
closeResultSet(rs);
closeStatement(ps);
closeConnection(conn);
}

The above code does NOT work when I connect to a remote mysql db with version: 8.0.11-commercial . By not working I mean there is no error, the insert is simply lost...

When I execute the same code against my local mysql db with version : 8.0.11 hosted on windows machine, it is working...

If I change connection getting code from

Connection conn = ds1.getPooledConnection("usr", "pwd").getConnection();

to:

Connection conn = ds1.getConnection();

it also starts to work against remote mysql db with version: 8.0.11-commercial...

The autocommit mode of the underlying connection is already true...

I tried to implement a custom log4j logger with the hope of seeing some trace but that did not help either:

ds1.setLogger("com.ibtech.mysqlproblem.Log4jLogger");
My custom connection pooling library uses pooled connection so I need to get the above code working. In the client I am using mysql-connector-java-8.0.11.jar.

Any help is greatly appreciated...

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.