B4J Question java.sql.SQLNonTransientConnectionException

[peacemaker]

Trying to use MySQL for the first time, migrating from SQLite.
And sometimes i get errors like

java.lang.RuntimeException: java.sql.SQLNonTransientConnectionException: (conn=1805) execute() is called on closed connection

or

java.lang.RuntimeException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

DB is used locally - B4J server app is running at the same host with DB (Private DBLocation As String = "localhost").
Is it normal situation ?

DB connection is so:

'#AdditionalJar: mysql-connector-java-5.1.49
'#AdditionalJar: mysql-connector-java-8.0.23
#AdditionalJar: mariadb-java-client-2.7.1.jar

'    SQL.Initialize2("com.mysql.jdbc.Driver", $"jdbc:mysql://${DBLocation}/${DBname}?allowPublicKeyRetrieval=false&autoReconnect=true&connectTimeout=8000&socketTimeout=8000&maxIdleTime=600&requireSSL=false&useSSL=false&pooling=true"$, DBUsername, DBPassword)
'    SQL.Initialize2("com.mysql.cj.jdbc.Driver", $"jdbc:mysql://${DBLocation}/${DBname}?allowPublicKeyRetrieval=false&autoReconnect=true&connectTimeout=5000&socketTimeout=5000&maxIdleTime=300&requireSSL=false&useSSL=false&pooling=false"$, DBUsername, DBPassword)
    SQL.Initialize2("org.mariadb.jdbc.Driver", $"jdbc:mariadb://${DBLocation}/${DBname}?allowPublicKeyRetrieval=false&autoReconnect=true&connectTimeout=5000&socketTimeout=5000&maxIdleTime=300&requireSSL=false&useSSL=false&pooling=false"$, DBUsername, DBPassword)

Tried all 3 versions, each works, and all with rare errors of this kind...

Why these errors ? Is it disconnect from DB ?

p.s. file-based SQLite has big advantage for this moment - no any deny during work...

 

[EnriqueGonzalez]

it seems that you are calling a sql.close somewhere before doing some more with the sql object.

With this db's you should only close sql once OR use a pool and open / close a new connection everytime is needed

 

[peacemaker]

sql.close

is not used at all. Why ? Server app is to be 24\7 working...

 

[EnriqueGonzalez]

Server app is to be 24\7 working...

if it is a serverapp then you have to use a ConnectionPool,

[Server] Building web servers with B4J

Edit: Web Apps tutorial: http://www.b4x.com/android/forum/threads/webapp-web-apps-overview.39811/ The server implementation is based on a popular and powerful Java server library named Jetty (license) The database connection pooling implementation is based on c3p0 (license). B4J Server...

www.b4x.com

look for Datebase Connections Pooling

 

[peacemaker]

OK, thanks.
Now i inited the DB in a server app with ABMaterial Admin-interface so:

    DBM.InitializeMySQL($"jdbc:mysql://${DBLocation}/${DBname}?characterEncoding=utf8&allowPublicKeyRetrieval=false&autoReconnect=true&connectTimeout=5000&socketTimeout=5000&maxIdleTime=300&requireSSL=false&useSSL=false&pooling=true"$, DBUsername, DBPassword, 10)
    SQL = DBM.GetSQL

SQL object is created only once, and never closed.

The working loop log now is with "init mysql" line and "Initializing c3p0 pool" red lines:

Filelisting.Size = 3524
Dirlisting.Size = 13
/01
Processing: /01
/01/img_2023-02-08_13-56-09.167_000.jpg

http://10.8.0.2:8111/api/inference_config

inference_config = {"ret":"ok","msg":""}
init mysql
2023-10-12 11:00:59.075:INFO :cmvci.AbstractPoolBackedDataSourceool-1-thread-6: 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 -> 1hge15jaz9mgylyi2xof4|69f95c0a, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge15jaz9mgylyi2xof4|69f95c0a, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost/stancontrol?characterEncoding=utf8&allowPublicKeyRetrieval=false&au
toReconnect=true&connectTimeout=5000&socketTimeout=5000&maxIdleTime=300&requireSSL=false&useSSL=false&pooling=true, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
Rate = 0
Clear_Table_ByMins.Finished
/01/img_2023-02-08_13-56-11.148_000.jpg

http://10.8.0.2:8111/api/inference_config

inference_config = {"ret":"ok","msg":""}
init mysql
2023-10-12 11:00:59.314:INFO :cmvci.AbstractPoolBackedDataSourceool-1-thread-6: 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 -> 1hge15jaz9mgylyi2xof4|b7ce397, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge15jaz9mgylyi2xof4|b7ce397, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost/stancontrol?characterEncoding=utf8&allowPublicKeyRetrieval=false&auto
Reconnect=true&connectTimeout=5000&socketTimeout=5000&maxIdleTime=300&requireSSL=false&useSSL=false&pooling=true, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
Rate = 0
Clear_Table_ByMins.Finished
/01/img_2023-02-08_13-56-13.135_000.jpg

http://10.8.0.2:8111/api/inference_config

inference_config = {"ret":"ok","msg":""}
init mysql
2023-10-12 11:00:59.549:INFO :cmvci.AbstractPoolBackedDataSourceool-1-thread-6: 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 -> 1hge15jaz9mgylyi2xof4|61b6049e, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge15jaz9mgylyi2xof4|61b6049e, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost/stancontrol?characterEncoding=utf8&allowPublicKeyRetrieval=false&au
toReconnect=true&connectTimeout=5000&socketTimeout=5000&maxIdleTime=300&requireSSL=false&useSSL=false&pooling=true, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
Rate = 0.7744427919387817

And if to initialize with poolsize = 10 or poolsize = 100 - sooner or later the working is stopped with "init mysql" line, but without pool-red-line and error:

java.lang.RuntimeException: java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: You can't operate on a closed Connection!!!

How to use this pool correctly? When to close ? Why again error ?
App needs the permanent SQL connection like with SQlite...

 

[Erel]

Check the DB module in the server example: https://www.b4x.com/android/forum/threads/webapp-web-apps-overview.39811/#content

Whenever you need to access the db, you get the connection (pool.GetConnection), use it and then close it (sql.Close).

Dim sql1 As SQL = pool.GetConnection
Try
 'work with sql1
Catch
 ...
End Try
sql1.Close