Mysql database connection error


#1

Hi there, i tried to connect to mysql database but i am getting some errors. I did the following.

  1. ant download-mySQL-JDBC and changed the mysql data source to match the username and password in my mysql configurations. but i am getting many errors but the top one is:

2019-07-14 18:54:08,525 |Scipio-JobPoller |DBCPConnectionFactory |E| null
java.lang.ClassNotFoundException: org.apache.derby.jdbc.EmbeddedDriver
at java.net.URLClassLoader.findClass(URLClassLoader.java:382) ~[?:1.8.0_191]
at java.lang.ClassLoader.loadClass(ClassLoader.java:424) ~[?:1.8.0_191]
at java.lang.ClassLoader.loadClass(ClassLoader.java:357) ~[?:1.8.0_191]
at java.lang.Class.forName0(Native Method) ~[?:1.8.0_191]
at java.lang.Class.forName(Class.java:348) ~[?:1.8.0_191]
at org.ofbiz.entity.connection.DBCPConnectionFactory.getConnection(DBCPConnectionFactory.java:90) [ofbiz-entity.jar:?]
at org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:83) [ofbiz-geronimo.jar:?]
at org.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:260) [ofbiz-entity.jar:?]
at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:366) [ofbiz-entity.jar:?]
at org.ofbiz.entity.datasource.GenericDAO.selectListIteratorByCondition(GenericDAO.java:763) [ofbiz-entity.jar:?]
at org.ofbiz.entity.datasource.GenericHelperDAO.findListIteratorByCondition(GenericHelperDAO.java:140) [ofbiz-entity.jar:?]
at org.ofbiz.entity.GenericDelegator.find(GenericDelegator.java:1547) [ofbiz-entity.jar:?]
at org.ofbiz.entity.GenericDelegator.findList(GenericDelegator.java:1583) [ofbiz-entity.jar:?]
at org.ofbiz.entity.util.EntityQuery.query(EntityQuery.java:526) [ofbiz-entity.jar:?]
at org.ofbiz.entity.util.EntityQuery.queryList(EntityQuery.java:451) [ofbiz-entity.jar:?]
at org.ofbiz.service.job.JobManager.reloadCrashedJobs(JobManager.java:395) [ofbiz-service.jar:?]
at org.ofbiz.service.job.JobPoller$JobManagerPoller.run(JobPoller.java:223) [ofbiz-service.jar:?]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_191]

getting hrd times in figuring it out. please help


#2

It looks like the jar hasn’t downloaded correctly. Please check the \framework\entity\lib\jdbc if the jar has been added there.

If not, you can also use the mysql connector provided by our software by copying it from \framework\entity\lib\jdbc\unused to \framework\entity\lib\jdbc


#3

First i used the jar already provided but i got errors and then i downloade it still i got error


#4

well, there’s many different reasons i guess. Could be your settings, could be the drivers not matching the mysql version you use, could be…

You can do it manually, though. Check your mysql db version first, then download the correct version from the mysql website that corresponds to your mysql version. Put it under \framework\entity\lib\jdbc. Reinstall the system (you switched database, so you must initialize again), start…


#5

Have you changed delegator’s datasource entries to mysql in entityengine.xml?


#6

it’s localmysql actually the name of the datasource you must use


#7

yes i did, though after trying for sometime i decided to restore defaults


#8

it’s very strange because the error you are getting is not about mysql lib being missing, is about Derby lib missing actually. Unless you are getting a different error, of course. In that case, can you post the stacktrace again in here?
On the other hand, I’d try to checkout a clean copy again and start over by just adjusting delagator’s datasource entries and the datasource itself, download mysql driver and then run ./ant clean-all load-extseed


#9

@minifreak is correct. You are getting an error because the system still wants to use the derby database and you must have removed the driver from the jdbc folder.

So your configuration is probably incorrect. We got some tutorial for that: https://www.scipioerp.com/community/developer/installation-configuration/configuration/#database-configuration

You probably didn’t do step 3. of that guide


#10

@minfreak and @madppiper i have combined what you have told me. I restarted the app and after that i am getting this error on web browser

HTTP Status 500 – Internal Server Error

Type Exception Report

Message Could not get next sequenced ID for sequence name: Visit

Description The server encountered an unexpected condition that prevented it from fulfilling the request.

Exception

java.lang.IllegalArgumentException: Could not get next sequenced ID for sequence name: Visit org.ofbiz.entity.GenericDelegator.getNextSeqId(GenericDelegator.java:2250) org.ofbiz.entity.GenericDelegator.getNextSeqId(GenericDelegator.java:2238) org.ofbiz.entity.GenericEntity.setNextSeqId(GenericEntity.java:626) org.ofbiz.entity.GenericDelegator.createSetNextSeqId(GenericDelegator.java:769) org.ofbiz.webapp.stats.VisitHandler.getVisit(VisitHandler.java:187) org.ofbiz.webapp.stats.VisitHandler.getVisitId(VisitHandler.java:100) org.ofbiz.webapp.control.ControlServlet.doGet(ControlServlet.java:213) javax.servlet.http.HttpServlet.service(HttpServlet.java:635) javax.servlet.http.HttpServlet.service(HttpServlet.java:742) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) org.ofbiz.webapp.control.ContextFilter.doFilter(ContextFilter.java:393) org.ofbiz.webapp.control.SimpleRedirectFilter.doFilter(SimpleRedirectFilter.java:95)

Note The full stack trace of the root cause is available in the server logs.

Apache Tomcat/8.5.31


#11

You get that error because you are using scipio with a database that has been used before. The error comes from a person trying to visit a page and the system creating a new tracking entry for it, but can’t because the auto generated id is already used. In this case you can increase the number the system uses to generate the ids in order to fix the error. I think we covered that in a different discussion on this forum before…


#12

now it seems like it can not connect to the db using the given credentials. but the credentials are correct.
it gives first this error NFO: No global web.xml found
2019-07-15 11:33:28,446 |0.0.0.0-startStop-1 |ServiceGroupReader |I| Loaded [1] Group definitions from file:/C:/Users/Msuta/scipio-erp/framework/common/servicedef/groups_test.xml
2019-07-15 11:33:28,454 |0.0.0.0-startStop-1 |ServiceGroupReader |I| Loaded [4] Group definitions from file:/C:/Users/Msuta/scipio-erp/framework/entityext/servicedef/groups.xml
2019-07-15 11:33:28,462 |0.0.0.0-startStop-1 |ServiceGroupReader |I| Loaded [2] Group definitions from file:/C:/Users/Msuta/scipio-erp/applications/workeffort/servicedef/service_groups.xml
2019-07-15 11:33:28,472 |0.0.0.0-startStop-1 |ServiceGroupReader |I| Loaded [4] Group definitions from file:/C:/Users/Msuta/scipio-erp/applications/accounting/servicedef/groups.xml
2019-07-15 11:33:29,757 |Scipio-batch-1 |GenericDelegator |E| Failure in removeByCondition operation for entity [ServiceSemaphore]: org.ofbiz.entity.GenericDataSourceException: Generic Entity Exception occurred in deleteByCondition (Unable to esablish a connection with the database. (Unable to acquire a new connection from the pool)). Rolling back transaction.
org.ofbiz.entity.GenericDataSourceException: Generic Entity Exception occurred in deleteByCondition (Unable to esablish a connection with the database. (Unable to acquire a new connection from the pool))
at org.ofbiz.entity.datasource.GenericDAO.deleteByCondition(GenericDAO.java:1191) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.datasource.GenericHelperDAO.removeByCondition(GenericHelperDAO.java:161) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.GenericDelegator.removeByCondition(GenericDelegator.java:1110) [ofbiz-entity.jar:?]
at org.ofbiz.entity.GenericDelegator.removeByAnd(GenericDelegator.java:1088) [ofbiz-entity.jar:?]
at org.ofbiz.entity.GenericDelegator.removeByAnd(GenericDelegator.java:1079) [ofbiz-entity.jar:?]
at org.ofbiz.service.ServiceDispatcher.(ServiceDispatcher.java:106) [ofbiz-service.jar:?]
at org.ofbiz.service.ServiceDispatcher.(ServiceDispatcher.java:133) [ofbiz-service.jar:?]
at org.ofbiz.service.ServiceDispatcher.getInstance(ServiceDispatcher.java:165) [ofbiz-service.jar:?]
at org.ofbiz.service.ServiceDispatcher.getLocalDispatcher(ServiceDispatcher.java:144) [ofbiz-service.jar:?]
at org.ofbiz.service.GenericDispatcherFactory.createLocalDispatcher(GenericDispatcherFactory.java:42) [ofbiz-service.jar:?]
at org.ofbiz.service.ServiceContainer.getLocalDispatcher(ServiceContainer.java:90) [ofbiz-service.jar:?]
at org.ofbiz.entityext.EntityServiceFactory.getLocalDispatcher(EntityServiceFactory.java:34) [ofbiz-entityext.jar:?]
at org.ofbiz.entityext.EntityServiceFactory.getDispatchContext(EntityServiceFactory.java:39) [ofbiz-entityext.jar:?]
at org.ofbiz.entityext.eca.DelegatorEcaHandler$1.call(DelegatorEcaHandler.java:62) [ofbiz-entityext.jar:?]
at org.ofbiz.entityext.eca.DelegatorEcaHandler$1.call(DelegatorEcaHandler.java:60) [ofbiz-entityext.jar:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_191]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_191]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_191]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_191]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_191]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_191]
Caused by: org.ofbiz.entity.GenericDataSourceException: Unable to esablish a connection with the database. (Unable to acquire a new connection from the pool)
at org.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:263) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:366) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:337) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.datasource.GenericDAO.deleteByCondition(GenericDAO.java:1212) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.datasource.GenericDAO.deleteByCondition(GenericDAO.java:1188) ~[ofbiz-entity.jar:?]
… 20 more
Caused by: java.sql.SQLException: Unable to acquire a new connection from the pool
at org.apache.commons.dbcp2.managed.ManagedConnection.updateTransactionStatus(ManagedConnection.java:130) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.dbcp2.managed.ManagedConnection.(ManagedConnection.java:59) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.dbcp2.managed.ManagedDataSource.getConnection(ManagedDataSource.java:81) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.ofbiz.entity.connection.DebugManagedDataSource.getConnection(DebugManagedDataSource.java:52) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.connection.DBCPConnectionFactory.getConnection(DBCPConnectionFactory.java:65) ~[ofbiz-entity.jar:?]
at org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:83) ~[ofbiz-geronimo.jar:?]
at org.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:260) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:366) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:337) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.datasource.GenericDAO.deleteByCondition(GenericDAO.java:1212) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.datasource.GenericDAO.deleteByCondition(GenericDAO.java:1188) ~[ofbiz-entity.jar:?]
… 20 more

THEN I GET THIS ERROR BUT MY CREDENTIALS ARE CORRECT PLEASE HELPI FEEL LIKE I AM ABOUT TO GET THERE

Caused by: java.sql.SQLException: Access denied for user ‘msuta’@‘localhost’ (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1714) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1224) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2190) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2037) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2021) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:776) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_191]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_191]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_191]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_191]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330) ~[mysql-connector-java-5.1.46.jar:5.1.46]
at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.dbcp2.managed.LocalXAConnectionFactory.createConnection(LocalXAConnectionFactory.java:68) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.dbcp2.managed.PoolableManagedConnectionFactory.makeObject(PoolableManagedConnectionFactory.java:65) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.pool2.impl.GenericObjectPool.create(GenericObjectPool.java:889) ~[commons-pool2-2.5.0.jar:2.5.0]
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:433) ~[commons-pool2-2.5.0.jar:2.5.0]
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:362) ~[commons-pool2-2.5.0.jar:2.5.0]
at org.apache.commons.dbcp2.managed.ManagedConnection.updateTransactionStatus(ManagedConnection.java:127) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.dbcp2.managed.ManagedConnection.(ManagedConnection.java:59) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.dbcp2.managed.ManagedDataSource.getConnection(ManagedDataSource.java:81) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.ofbiz.entity.connection.DebugManagedDataSource.getConnection(DebugManagedDataSource.java:52) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.connection.DBCPConnectionFactory.getConnection(DBCPConnectionFactory.java:65) ~[ofbiz-entity.jar:?]
at org.ofbiz.geronimo.GeronimoTransactionFactory.getConnection(GeronimoTransactionFactory.java:83) ~[ofbiz-geronimo.jar:?]
at org.ofbiz.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:260) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:366) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:337) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.datasource.GenericDAO.deleteByCondition(GenericDAO.java:1212) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.datasource.GenericDAO.deleteByCondition(GenericDAO.java:1188) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.datasource.GenericHelperDAO.removeByCondition(GenericHelperDAO.java:161) ~[ofbiz-entity.jar:?]
at org.ofbiz.entity.GenericDelegator.removeByCondition(GenericDelegator.java:1110) ~[ofbiz-entity.jar:?]
… 23 more


#13

I don’t think we can help you with the credentials. The error is quite clear that you don’t have access to the db.

Access denied for user ‘msuta’@‘localhost’ (using password: YES)

So please check your localmysql settings again, make sure it is correct.


#14

now i get this error


#15

2019-07-15 11:44:23,283 |pool-3-thread-1 |DatabaseUtil |E| Could not create table [AGREEMENT_GEOGRAPHICAL_APPLIC]: SQL Exception while executing the following:
CREATE TABLE AGREEMENT_GEOGRAPHICAL_APPLIC (AGREEMENT_ID VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_col NOT NULL, AGREEMENT_ITEM_SEQ_ID VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_col NOT NULL, GEO_ID VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_col NOT NULL, LAST_UPDATED_STAMP DATETIME, LAST_UPDATED_TX_STAMP DATETIME, CREATED_STAMP DATETIME, CREATED_TX_STAMP DATETIME, CONSTRAINT PK_AGREEMENT_GEOGRAPHICAL_APPL PRIMARY KEY (AGREEMENT_ID, AGREEMENT_ITEM_SEQ_ID, GEO_ID)) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_col
Error was: java.sql.SQLException: Unknown collation: ‘utf8mb4_col’
2019-07-15 11:44:23,288 |pool-3-thread-1 |DatabaseUtil |E| Could not create table [AGREEMENT_ITEM]: SQL Exception while executing the following:
CREATE TABLE AGREEMENT_ITEM (AGREEMENT_ID VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_col NOT NULL, AGREEMENT_ITEM_SEQ_ID VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_col NOT NULL, AGREEMENT_ITEM_TYPE_ID VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_col, CURRENCY_UOM_ID VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_col, AGREEMENT_TEXT LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_col, AGREEMENT_IMAGE LONGBLOB, LAST_UPDATED_STAMP DATETIME, LAST_UPDATED_TX_STAMP DATETIME, CREATED_STAMP DATETIME, CREATED_TX_STAMP DATETIME, CONSTRAINT PK_AGREEMENT_ITEM PRIMARY KEY (AGREEMENT_ID, AGREEMENT_ITEM_SEQ_ID)) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_col
Error was: java.sql.SQLException: Unknown collation: ‘utf8mb4_col’


#16

That’s the vital bit. utf8mb4 is the default collation type for mysql, but you use a different configuration. You will have to change it in your mysql config


#17

so what collation type should i use? should i change it in my mysql or in scipio erp? i have been trying to change in mysql but still getting the error


#18

Well, check the one you set for your already existing database and update the scipio erp configuration. That seems to be the simplist way forward…


#19

Which MySQL version are you using btw?

Check this SO post, it may be of help to solve this issue.


#20

Thank you @madppiper and @minifreak now i have a database being set up., A lot of tables though hahaha