B4J Question [RESOLVED] Potential issue with C3P0 and MySQL 8?

[Haris Hafeez]

Hi,

Just installed mysql8 on a VM and started a new project. The B4J app crashes upon initialisation where I initialise the DB and run a simple select query.


Not that I had to use the following in the connection URL to disable forced SSL connections in mysql 8.

mysql_db_url/mydb??characterEncoding=utf8&useSSL=false

Without using the above flag, pool ends up in an infinite loop trying to get a connection but being told off by the server to use the above flag.



The code is nothing special:

Sub Class_Globals
    Private pool As ConnectionPool
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize
    Main.Config = LoadConfigMap
    pool.Initialize(Main.Config.Get("DriverClass"), Main.config.Get("JdbcUrl"), Main.config.Get("User"), _
        Main.config.Get("Password"))
      
    LoadZoneIds
End Sub

Private Sub LoadConfigMap As Map
    Return File.ReadMap(File.DirAssets, "config.properties")
End Sub

Private Sub LoadZoneIds
    Dim con As SQL = GetConnection
    Dim Cursor As ResultSet
    Cursor = con.ExecQuery("SELECT * FROM zone")
    Dim m As Map
    m.Initialize
    Do While Cursor.NextRow
        Dim uid As Int = Cursor.GetInt("id")
        Dim zone As String = Cursor.GetString("name")
        m.Put(zone, uid)
    Loop
    con.Close
    Main.ZONE_IDS = m
End Sub

Public Sub GetConnection As SQL
    Return pool.GetConnection
End Sub

The error log is:

Spoiler: Logs

Waiting for debugger to connect...
Program started.
2018-11-28 19:56:11.344:INFO::main: Logging initialized @592ms to org.eclipse.jetty.util.log.StdErrLog
Nov 28, 2018 7:56:11 PM com.mchange.v2.log.MLog
INFO: MLog clients using java 1.4+ standard logging.
Nov 28, 2018 7:56:11 PM com.mchange.v2.c3p0.C3P0Registry
INFO: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
Nov 28, 2018 7:56:11 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1b60i8q9zphw8sv1wmhteu|7dc36524, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1b60i8q9zphw8sv1wmhteu|7dc36524, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://35.189.86.129/clarity_zones?characterEncoding=utf8&useSSL=false, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdle...
Error occurred on line: 35 (DBConfig)
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPool.java:45)
at b4j.example.dbconfig._getconnection(dbconfig.java:80)
at b4j.example.dbconfig._loadzoneids(dbconfig.java:113)
at b4j.example.dbconfig._initialize(dbconfig.java:54)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:625)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:168)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:94)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
at b4j.example.main.main(main.java:29)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@239963d8 -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
... 20 more

 

[Haris Hafeez]

My bad. It seems the issue was with using mysql5 driver. Updating the driver resolved it.