Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

jooqdb: Use JDBC Datasource and Connection pools instead of DriverManager.getConnection() #43

Closed
kovax opened this issue May 26, 2017 · 16 comments
Closed

jooqdb: Use JDBC Datasource and Connection pools instead of DriverManager.getConnection() #43

kovax opened this issue May 26, 2017 · 16 comments
Assignees
@genetorres
Labels
enhancement New feature or request jooqdb issue for the jooqdb package
Milestone
4.4.0

Comments

@kovax
Copy link
Member

kovax commented May 26, 2017 •
edited

Currently Lookup and ClusterStoreage instance will have their own jdbc connection, as each of them calls the DriverManager.getConnection(). This will not scale in a servlet environment, because it will use the same connection between servlets. This setup also prevents the proper use of TransactionClusterStore (see bug #42)
@kovax kovax transferred this issue from cristal-ise/jooqdb Feb 15, 2019
@kovax kovax added enhancement New feature or request jooqdb issue for the jooqdb package labels Feb 15, 2019
@kovax kovax added this to the Future Work milestone Feb 15, 2019
@kovax kovax changed the title Use JDBC Datasource and Connection pools instead of DriverManager.getConnection() jooqdb: Use JDBC Datasource and Connection pools instead of DriverManager.getConnection() Feb 15, 2019
kovax added a commit that referenced this issue Mar 24, 2019
@kovax
#43: initial implementation
da89c15
@taurus227 taurus227 mentioned this issue Mar 24, 2019
Closed
@taurus227
Copy link
Contributor

See also #22

@taurus227 taurus227 mentioned this issue Mar 24, 2019
Closed
5 tasks
kovax added a commit that referenced this issue Mar 28, 2019
@kovax
#43 - cookcie was not added to the response
3cb4a98
@kovax
Copy link
Member Author

kovax commented Jul 15, 2019

related to #42

genetorres added a commit that referenced this issue Jul 16, 2019
@genetorres
#43: Merge back from develop and added dslcontext object in JooqClust…
19bdd48 
…erStorage methods
@genetorres
Copy link
Contributor

Getting this error:

`EXCEPTION:java.lang.ClassCastException: org.jooq.impl.DataSourceConnectionProvider cannot be cast to org.jooq.impl.DefaultConnectionProvider
at org.cristalise.storage.jooqdb.JooqClusterStorage.commit(JooqClusterStorage.java:172)
at org.cristalise.kernel.persistency.ClusterStorageManager.commit(ClusterStorageManager.java:506)
at org.cristalise.kernel.persistency.TransactionManager.commit(TransactionManager.java:330)
at org.cristalise.kernel.entity.ItemImplementation.initialise(ItemImplementation.java:212)
at org.cristalise.kernel.entity.TraceableEntity.initialise(TraceableEntity.java:110)
at org.cristalise.kernel.entity.imports.ImportItem.create(ImportItem.java:188)
at org.cristalise.kernel.process.module.Module.importAll(Module.java:147)
at org.cristalise.kernel.process.module.ModuleManager.registerModules(ModuleManager.java:250)
at org.cristalise.kernel.process.Bootstrap$1.run(Bootstrap.java:141)
at java.lang.Thread.run(Thread.java:748)

JooqHandler.open() - uri:'jdbc:postgresql://localhost:5432/titustech' user:'postgres' dialect:'POSTGRES'
JooqClusterStorage.abort()
EXCEPTION:java.lang.ClassCastException: org.jooq.impl.DataSourceConnectionProvider cannot be cast to org.jooq.impl.DefaultConnectionProvider
at org.cristalise.storage.jooqdb.JooqClusterStorage.abort(JooqClusterStorage.java:195)
at org.cristalise.kernel.persistency.ClusterStorageManager.abort(ClusterStorageManager.java:512)
at org.cristalise.kernel.persistency.TransactionManager.commit(TransactionManager.java:333)
at org.cristalise.kernel.entity.ItemImplementation.initialise(ItemImplementation.java:212)
at org.cristalise.kernel.entity.TraceableEntity.initialise(TraceableEntity.java:110)
at org.cristalise.kernel.entity.imports.ImportItem.create(ImportItem.java:188)
at org.cristalise.kernel.process.module.Module.importAll(Module.java:147)
at org.cristalise.kernel.process.module.ModuleManager.registerModules(ModuleManager.java:250)
at org.cristalise.kernel.process.Bootstrap$1.run(Bootstrap.java:141)
at java.lang.Thread.run(Thread.java:748)

FATAL : Transactional database failure
Transaction Manager: Closing storages
JooqClusterStorage.close()
JooqHandler.open() - uri:'jdbc:postgresql://localhost:5432/titustech' user:'postgres' dialect:'POSTGRES'
JooqHandler.open() - uri:'jdbc:postgresql://localhost:5432/titustech' user:'postgres' dialect:'POSTGRES'
ProxyManager.shutdown() - flagging shutdown of server connections
Proxy Client: flagging shutdown.
ERROR : ProxyClientConnection.run() - clientID:0 - Error reading from socket.
ProxyClientConnection.run() - clientID:0 closed.
ProxyManager: Closing Server.
ERROR : ProxyServerConnection - Disconnected from localhost:1555
SimpleTCPIPServer.stopListening() - Closing server for org.cristalise.kernel.entity.proxy.ProxyClientConnection on port 1555
SimpleTCPIPServer.stopListening() - Closing server for org.cristalise.kernel.scripting.ScriptConsole on port 9990
SimpleTCPIPServer: Server closed for org.cristalise.kernel.entity.proxy.ProxyClientConnection on port 1555
SimpleTCPIPServer - Servers still running: 1
SimpleTCPIPServer: Server closed for org.cristalise.kernel.scripting.ScriptConsole on port 9990
SimpleTCPIPServer - Servers still running: 0
JVM ERROR:java.lang.ThreadDeath
at org.cristalise.kernel.process.AbstractMain.shutdown(AbstractMain.java:191)
at org.cristalise.kernel.utils.Logger.die(Logger.java:212)
at org.cristalise.kernel.persistency.TransactionManager.commit(TransactionManager.java:334)
at org.cristalise.kernel.entity.ItemImplementation.initialise(ItemImplementation.java:212)
at org.cristalise.kernel.entity.TraceableEntity.initialise(TraceableEntity.java:110)
at org.cristalise.kernel.entity.imports.ImportItem.create(ImportItem.java:188)
at org.cristalise.kernel.process.module.Module.importAll(Module.java:147)
at org.cristalise.kernel.process.module.ModuleManager.registerModules(ModuleManager.java:250)
at org.cristalise.kernel.process.Bootstrap$1.run(Bootstrap.java:141)
at java.lang.Thread.run(Thread.java:748)

FATAL : Exception performing bootstrap. Check that everything is OK.`

genetorres added a commit that referenced this issue Jul 16, 2019
@genetorres
#43: Modify JooqClusterStorage commit() and set isAutocommit to false
e058f10
genetorres added a commit that referenced this issue Jul 17, 2019
@genetorres
#43: Organize imports
64040c0
genetorres added a commit that referenced this issue Jul 17, 2019
@genetorres
#43: Make context as global variable
efd34e9
genetorres added a commit that referenced this issue Jul 18, 2019
@genetorres
#43: Fixed unit Test failure upon build
3d34676
@genetorres
Copy link
Contributor

Unit Test Results:

image

genetorres added a commit that referenced this issue Jul 18, 2019
@genetorres
#43: Make context field as global variable
9882f25
genetorres added a commit that referenced this issue Jul 18, 2019
@genetorres
#43: Make use of context on close()
838b941
@genetorres
Copy link
Contributor

Tested to run in GRAIN, errors encountered

image

`EXCEPTION:org.jooq.exception.DataAccessException: Error getting connection from data source HikariDataSource (HikariPool-1)
at org.jooq_3.11.9.POSTGRES.debug(Unknown Source)
at org.jooq.impl.DataSourceConnectionProvider.acquire(DataSourceConnectionProvider.java:86)
at org.jooq.impl.DefaultExecuteContext.connection(DefaultExecuteContext.java:724)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:318)
at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:393)
at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:380)
at org.jooq.impl.AbstractResultQuery.fetchOne(AbstractResultQuery.java:545)
at org.jooq.impl.SelectImpl.fetchOne(SelectImpl.java:2879)
at org.jooq.impl.DefaultDSLContext.fetchExists(DefaultDSLContext.java:4345)
at org.cristalise.storage.jooqdb.lookup.JooqDomainPathHandler.exists(JooqDomainPathHandler.java:129)
at org.cristalise.storage.jooqdb.lookup.JooqLookupManager.exists(JooqLookupManager.java:159)
at org.cristalise.kernel.lookup.Path.exists(Path.java:122)
at org.cristalise.kernel.utils.DescriptionObjectCache.findItem(DescriptionObjectCache.java:127)
at org.cristalise.kernel.utils.DescriptionObjectCache.get(DescriptionObjectCache.java:156)
at org.cristalise.kernel.utils.LocalObjectLoader.getSchema(LocalObjectLoader.java:125)
at org.cristalise.kernel.entity.imports.ImportItem.create(ImportItem.java:212)
at org.cristalise.kernel.process.module.Module.importItems(Module.java:160)
at org.cristalise.kernel.process.module.Module.importAll(Module.java:144)
at org.cristalise.kernel.process.module.ModuleManager.registerModules(ModuleManager.java:250)
at org.cristalise.kernel.process.Bootstrap$1.run(Bootstrap.java:141)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:697)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:196)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:161)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
at org.jooq.impl.DataSourceConnectionProvider.acquire(DataSourceConnectionProvider.java:83)
... 19 more

EXCEPTION:org.cristalise.kernel.process.module.ModuleException: Error importing items for module CristaliseDev
at org.cristalise.kernel.process.module.ModuleManager.registerModules(ModuleManager.java:254)
at org.cristalise.kernel.process.Bootstrap$1.run(Bootstrap.java:141)
at java.lang.Thread.run(Thread.java:748)

FATAL : Exception performing bootstrap. Check that everything is OK.`

@genetorres
Copy link
Contributor

Additional Config

image

genetorres added a commit that referenced this issue Jul 23, 2019
@genetorres
#43: Added ds config and reverted JooqLookupManager
cf23e9d
@dlsdioquino
Copy link
Contributor

@kovax @taurus227 Please review pull request.

kovax added a commit that referenced this issue Jul 24, 2019
@kovax
#43: fix create table issue with auto commit off
34c79d2
@genetorres
Copy link
Contributor

genetorres commented Jul 25, 2019 •
edited

  • Add a way to modify Gateway property(Jooq.autoCommit) config upon runBootStrap to fix the following error

Exception in thread "main" org.cristalise.kernel.common.ObjectNotFoundException: IDL:org.cristalise.kernel/common/ObjectNotFoundException:1.0 Role:/role/Admin at org.cristalise.storage.jooqdb.lookup.JooqLookupManager.setPermissions(JooqLookupManager.java:744) at org.cristalise.storage.jooqdb.lookup.JooqLookupManager.setPermission(JooqLookupManager.java:739) at org.cristalise.kernel.process.Bootstrap.checkAdminAgents(Bootstrap.java:528) at org.cristalise.kernel.process.Bootstrap.init(Bootstrap.java:103) at org.cristalise.kernel.process.Gateway.runBoostrap(Gateway.java:541) at org.cristalise.kernel.process.StandardServer.standardInitialisation(StandardServer.java:114) at org.cristalise.kernel.process.StandardServer.standardInitialisation(StandardServer.java:127) at org.cristalise.kernel.process.StandardServer.main(StandardServer.java:138)

genetorres added a commit that referenced this issue Jul 25, 2019
@genetorres
#43: Configured a new datasource after postBootstrap()
0c66840
@dlsdioquino
Copy link
Contributor

@kovax @taurus227 Please review.

@genetorres
Copy link
Contributor

genetorres commented Jul 26, 2019 •
edited

  • Cache dsl context object to the locker object for having same transaction when processing item to the storage that will fix the following error.

EXCEPTION:org.jooq.exception.DataAccessException: SQL [insert into "DOMAIN_PATH" ("PATH", "TARGET") values (?, cast(? as uuid)), (?, cast(? as uuid))]; ERROR: insert or update on table "DOMAIN_PATH" violates foreign key constraint "FK_"DOMAIN_PATH"" Detail: Key (TARGET)=(e88e43e5-6072-47c7-bd8c-31d2f689fc6f) is not present in table "ITEM". at org.jooq_3.11.9.POSTGRES.debug(Unknown Source) at org.jooq.impl.Tools.translate(Tools.java:2384) at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:832) at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364) at org.cristalise.storage.jooqdb.lookup.JooqDomainPathHandler.insert(JooqDomainPathHandler.java:125) at org.cristalise.storage.jooqdb.lookup.JooqLookupManager.add(JooqLookupManager.java:174) at org.cristalise.kernel.lifecycle.instance.predefined.item.CreateItemFromDescription.initialiseItem(CreateItemFromDescription.java:199) at org.cristalise.kernel.lifecycle.instance.predefined.item.CreateItemFromDescription.runActivityLogic(CreateItemFromDescription.java:126) at org.cristalise.kernel.lifecycle.instance.Activity.request(Activity.java:211) at org.cristalise.kernel.lifecycle.instance.Workflow.requestAction(Workflow.java:136) at org.cristalise.kernel.entity.ItemImplementation.delegatedAction(ItemImplementation.java:252) at org.cristalise.kernel.entity.ItemImplementation.requestAction(ItemImplementation.java:226) at org.cristalise.kernel.entity.TraceableEntity.requestAction(TraceableEntity.java:130) at org.cristalise.kernel.entity.ItemPOA._invoke(ItemPOA.java:187) at com.sun.corba.se.impl.protocol.CorbaServerRequestDispatcherImpl.dispatchToServant(CorbaServerRequestDispatcherImpl.java:654) at com.sun.corba.se.impl.protocol.CorbaServerRequestDispatcherImpl.dispatch(CorbaServerRequestDispatcherImpl.java:205) at com.sun.corba.se.impl.protocol.CorbaMessageMediatorImpl.handleRequestRequest(CorbaMessageMediatorImpl.java:1700) at com.sun.corba.se.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:1558) at com.sun.corba.se.impl.protocol.CorbaMessageMediatorImpl.handleInput(CorbaMessageMediatorImpl.java:940) at com.sun.corba.se.impl.protocol.giopmsgheaders.RequestMessage_1_2.callback(RequestMessage_1_2.java:198) at com.sun.corba.se.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:712) at com.sun.corba.se.impl.transport.SocketOrChannelConnectionImpl.dispatch(SocketOrChannelConnectionImpl.java:474) at com.sun.corba.se.impl.transport.SocketOrChannelConnectionImpl.doWork(SocketOrChannelConnectionImpl.java:1237) at com.sun.corba.se.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.performWork(ThreadPoolImpl.java:490) at com.sun.corba.se.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:519) Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "DOMAIN_PATH" violates foreign key constraint "FK_"DOMAIN_PATH""

genetorres added a commit that referenced this issue Aug 1, 2019
@genetorres
#43: Added changes on JooqClusterStorage
0759480
genetorres added a commit that referenced this issue Aug 1, 2019
@genetorres
#43: Address Review Points
f92215b
genetorres added a commit that referenced this issue Aug 2, 2019
@genetorres
#43: Fixed review points for close connection
db084c0
genetorres added a commit that referenced this issue Aug 2, 2019
@genetorres
#43: Autocommit property state config
178472f
genetorres added a commit that referenced this issue Aug 5, 2019
@genetorres
#43: Fixed review comments on postConnect and postBootstrap methods
e1bd9b8
@dlsdioquino
Copy link
Contributor

dlsdioquino commented Aug 6, 2019 •
edited

@genetorres working on the documentations related to the issue. Please document things on the server start up

@genetorres
Copy link
Contributor

genetorres commented Aug 7, 2019 •
edited

Sever Start up activity and Activity Execution Diagram

ItemImplementationFlow.pdf

@genfernandez
Copy link

done with activity execution flow. currently working on server startup

@dlsdioquino
Copy link
Contributor

@taurus227 and @kovax please check the PDF for your review.

genetorres added a commit that referenced this issue Aug 8, 2019
@genetorres
#43: Address latest review points
5d2bcc4
kovax added a commit that referenced this issue Aug 8, 2019
@kovax
#43 fix travis to use oraclejdk on trusty
0a2afb4
kovax added a commit that referenced this issue Aug 16, 2019
@kovax
#43 remove from contextMap was missing; mino code refactor
91f23e8
kovax added a commit that referenced this issue Aug 16, 2019
@kovax
#43 adding missing 'throws PersistencyException' to begin()
928ec1a
kovax added a commit that referenced this issue Aug 16, 2019
@kovax
#43 code reformat
cd3a84a
kovax added a commit that referenced this issue Aug 16, 2019
@kovax
#43 fix test code
86e1e92
kovax added a commit that referenced this issue Aug 17, 2019
@kovax
#43 use JooqHandler.ds.isAutoCommit() instead of JooqHandler.autoCommit
3a71975
kovax added a commit that referenced this issue Aug 17, 2019
@kovax
#43 config jmx bean and add mysql connection count log
f18f05f
kovax added a commit that referenced this issue Aug 18, 2019
@kovax
#43 store Connection to be able to close it
019b7fb
kovax added a commit that referenced this issue Aug 19, 2019
@kovax
#43 remove commented line
b4780f5
martinbielmannicube added a commit that referenced this issue Sep 5, 2019
@martinbielmannicube
#43: Adapted JooqClusterStorage to manage both locker object presence…
3eb0116 
… and auto-commit parameter
martinbielmannicube added a commit that referenced this issue Sep 6, 2019
@martinbielmannicube
#43: Tuned license-maven-plugin configuration (excludes) to avoid bui…
c7d85e1 
…ld warnings
martinbielmannicube added a commit that referenced this issue Sep 6, 2019
@martinbielmannicube
#43: Lazy datasource creation + aligned open/closed/recreate operatio…
4c8dac6 
…ns to allow multiple gateway open and close
martinbielmannicube added a commit that referenced this issue Sep 11, 2019
@martinbielmannicube
#43: JooqHandler -> added configuration: readOnlyDataSource for clien…
a655ee1 
…t connections
martinbielmannicube added a commit that referenced this issue Sep 11, 2019
@martinbielmannicube
#43: TransactionManager adaptation to comply with autocommit=false co…
14fc541 
…nfiguration
martinbielmannicube added a commit that referenced this issue Sep 11, 2019
@martinbielmannicube
#43: Fixed LookupManager typo
930e5d6
martinbielmannicube added a commit that referenced this issue Sep 11, 2019
@martinbielmannicube
#43: ClusterStorageManager: adapted notifications to not auto-committ…
a4fa576 
…ed transactions (autocommit=false)
martinbielmannicube added a commit that referenced this issue Sep 11, 2019
@martinbielmannicube
#43: Simplified message set management
f28c205
@kovax kovax closed this as completed Sep 11, 2019
@kovax kovax modified the milestones: Future Work, 4.4.0 Sep 11, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@genetorres genetorres

Labels
enhancement New feature or request jooqdb issue for the jooqdb package
Projects
None yet
Milestone
4.4.0
Development

No branches or pull requests
5 participants
@kovax @taurus227 @dlsdioquino @genetorres @genfernandez