Using ActiveJDBC, I've been manually opening and closing connections, inserting and reading anything, all with no errors like this:
public class DatabaseLoader {
private static final Logger logger = LogManager.getLogger(DatabaseLoader.class);
public static void openConnection() {
Base.open("com.mysql.cj.jdbc.Driver", ConfigManager.getAddress(), ConfigManager.getUsername(), ConfigManager.getPassword());
}
public static void openConnectionIfClosed() {
if (!Base.hasConnection()) {
logger.info("Connecting to database!");
openConnection();
}
}
public static void closeConnectionIfOpen() {
if (Base.hasConnection()) {
logger.info("Disconnecting from database!");
Base.close();
}
}
}
However, I have recently attempted to add C3P0 and connection pool, following https://github.com/javalite/javalite/blob/master/activejdbc/src/test/java/org/javalite/activejdbc/C3P0PoolTest.java as closely as possible. The new code looks like this:
public class DatabaseLoader {
private static final Logger logger = LogManager.getLogger(DatabaseLoader.class);
static DataSource dataSourceUnpooled;
static DataSource dataSourcePooled;
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
dataSourceUnpooled = DataSources.unpooledDataSource(ConfigManager.getAddress(), ConfigManager.getUsername(), ConfigManager.getPassword());
dataSourcePooled = DataSources.pooledDataSource(dataSourceUnpooled); //init the connection pool
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
}
}
public static void openConnection() {
Base.open(dataSourcePooled);
}
public static void openConnectionIfClosed() {
if (!Base.hasConnection()) {
openConnection();
}
}
public static void closeConnectionIfOpen() {
if (Base.hasConnection()) {
logger.info("Disconnecting from database!");
Base.close();
}
}
}
Using this code, initial connection to the database appears successful and all is well, until I insert something. For example, like this:
// ServerMessage is an activejdbc model that has worked to insert things 31k times before adding C3P0
ServerMessage message = ServerMessage.findOrCreateIt("message_id_snowflake", messageIdSnowflake, "server_id", serverId, "user_id", userId);
Whenever I do insert something, I now get this error:
org.javalite.activejdbc.DBException: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '::BIGINT,19::INT,1772::INT)' at line 1, query: INSERT INTO server_messages (message_id_snowflake, server_id, user_id) VALUES (?::BIGINT,?::INT,?::INT), params: 928224895626793001, 19, 1772
at org.javalite.activejdbc.DB.execInsert(DB.java:734)
at org.javalite.activejdbc.Model.insert(Model.java:2741)
at org.javalite.activejdbc.Model.save(Model.java:2673)
at org.javalite.activejdbc.Model.saveIt(Model.java:2597)
at org.javalite.activejdbc.ModelDelegate.createIt(ModelDelegate.java:146)
at org.javalite.activejdbc.ModelDelegate.findOrCreateIt(ModelDelegate.java:438)
at org.javalite.activejdbc.ModelDelegate.findOrCreateIt(ModelDelegate.java:412)
at dev.laarryy.eris.models.guilds.ServerMessage.findOrCreateIt(ServerMessage.java:219)
at dev.laarryy.eris.listeners.MessageCreateListener.on(MessageCreateListener.java:49)
... 21 more
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '::BIGINT,19::INT,1772::INT)' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1348)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:1502)
at org.javalite.activejdbc.DB.execInsert(DB.java:713)
... 29 more
This seems strange to me, since the bigint, int, and int that are supposed to be inserted are indeed a bigint, int and int as required, and I don't know what else I might do to find the cause of the error. The C3P0 line at the second to last line of the stacktrace makes me think that the statement as prepared by ActiveJDBC is somehow not the statement making it to the database.
I have also already tried enabling the C3P0 statement cache, in case that had an effect, but it did not.
Any advice or guidance would be appreciated!
I have now also attempted to follow the quick-start procedure here https://www.mchange.com/projects/c3p0/#quickstart in the hopes that manually setting the driver class might resolve the issue, however it does not. I still get the same stacktrace. The code I tried:
public class DatabaseLoader {
private static final Logger logger = LogManager.getLogger(DatabaseLoader.class);
static ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
static {
try {
comboPooledDataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
comboPooledDataSource.setJdbcUrl(ConfigManager.getAddress());
comboPooledDataSource.setUser(ConfigManager.getUsername());
comboPooledDataSource.setPassword(ConfigManager.getPassword());
} catch (PropertyVetoException e) {
e.printStackTrace();
}
}
public static void openConnection() {
Base.open(comboPooledDataSource);
}
public static void openConnectionIfClosed() {
if (!Base.hasConnection()) {
openConnection();
}
}
public static void closeConnectionIfOpen() {
if (Base.hasConnection()) {
logger.info("Disconnecting from database!");
Base.close();
}
}
}
I have also attempted switching to Hikari version 5, and I get precisely the same error except the second-to-last line is from Hikari instead of c3p0. This leads me to believe something is wrong on the ActiveJDBC end rather than the connection pooling end.
Requested Details:
Unfortunately, there was a defective snapshot published for a few hours. That snapshot v 3.0-SNAPSHOT had a bug where PostgreSQL syntax was accidentally made default for MySQL. It seems that it caused a problem for you. The JDBC connection pool has nothing to do with this. We are careful with snapshots, but things like this happens (once in a few years). You got unlucky by getting right in the middle of the update.
How to fix this is to blow away all your Maven caches:
rm -rf ~/.m2/repository/org/javalite
On the next build, you will get a new (vastly improved :)) snapshot that is free from this defect.
Sorry about the trouble, but snapshots are "work in progress".
Related bug: https://github.com/javalite/javalite/issues/1187