B4J Question B4J RDC Connection to Mysql

[jeffkwells]

Hi All,

I am new to B4A but I've managed to get a couple of simple apps working on my Android device. I'm now trying to develop a simple database application by connecting the device to MySql on my laptop. However, I'm afraid I have reached an impasse with the RDC set up that Erel provided some time ago. I've got JrDC2 working, I think(!) At least, I get a message that it's running but when I test the connection to MySql with

http://localhost:17178/test

there is a delay and then I get the message

RemoteServer is running (03/01/2018 13:35:02)
Error fetching connection.

Is it my MySql setup? I've used my username and password in the B4J config file and I have set full privileges for myself on the server. Anything else I should do?

Thanks. Help will be appreciated.

 

[Erel]

The error means that jRDC2 failed to connect to the database server.

Where is the database server hosted? On the same pc? Can you post the configuration file?

 

[jeffkwells]

The error means that jRDC2 failed to connect to the database server.

Where is the database server hosted? On the same pc? Can you post the configuration file?

Yes, on the same PC. Herewith config file. Thanks a lot.

Ps. I had to change the file name from config.properties to config.txt to upload it.

 

[Erel]

Looks correct. Make sure that MySQL is configured to allow TCP connections.

 

[jeffkwells]

I'm pretty sure it is. Here's a couple of screen shots. Thanks

 

[OliverA]

I don't see a test database in that screenshot, just world. In your configuration you are connection to the test database.

Try this

JdbcUrl=jdbc:mysql://localhost/world?characterEncoding=utf8

 

[jeffkwells]

I don't see a test database in that screenshot, just world. In your configuration you are connection to the test database.

Try this

JdbcUrl=jdbc:mysql://localhost/world?characterEncoding=utf8

Thanks for this. I've changed the config file but http://localhost:17178/test
still gives me

RemoteServer is running (03/01/2018 17:27:34)
Error fetching connection.

and

http://localhost:17178/world gives:

HTTP ERROR 404
Problem accessing /world. Reason:

Not Found

 

[RWK]

The request will be pointing to the test handler:

http://localhost:17178/test

Only in config.properties change to world (or create a table in mysql: test )

#DATABASE CONFIGURATION
DriverClass=com.mysql.jdbc.Driver
JdbcUrl=jdbc:mysql://localhost:3306/test?characterEncoding=utf8   ' replace 'test' with 'world'  or other name of DB
User=root          ' the mysql user
Password=usbw      ' the password
#Java server port
ServerPort=17178

 

[OliverA]

What is the log output in B4J? You should find information pertaining to the issue of not connecting to the mysql server.

 

[jeffkwells]

Here is the relevant part of the config file
#DATABASE CONFIGURATION
DriverClass=com.mysql.jdbc.Driver
#JdbcUrl=jdbc:mysql://localhost/test?characterEncoding=utf8
JdbcUrl=jdbc:mysql://localhost:3306/world?characterEncoding=utf8
JdbcUrl=jdbc:mysql://localhost:/world?characterEncoding=utf8
User=jeffkwells
Password=alberich1
#Java server port
ServerPort=17178

when I test this with

localhost:17178/world

I get:

HTTP ERROR 404
Problem accessing /world. Reason:

Not Found

when I use

localhost:3306/world

I get:

This page isn’t working

 

[OliverA]

Out of the box JRDC2 has only two "pages".
1) /rdc - used for applications to communicate with the JRDC2 server
2) /test - browser accessible site for testing access to the JRDC2 server and for testing database connectivity.

/world does not exist. The only reason I came up with world is that the screenshot of the MySQL studio showed that you had a database named world set up. But the configuration file's JdbcUrl configuration was using a database named test. Since it looked like you had no test database, I had you change the word test to world in the JdbcUrl. Changing the database that you are connecting to, has nothing to do with the "pages" that are accessible from JRDC2 (as posted by @Erel). So when you try to access localhost:17178/world, the server responds correctly (404). Now port 3306 is the port of your MySQL server, not JRDC2. Since this port has no web server behind it, the error message "This page isn't working" is appropriate (and comes from your browser).

 

[OliverA]

BTW, did you stop and start the JRDC2 server after you made changes to the configuration file?

 

[jeffkwells]

Thanks very much for this. Very helpful. What should I do next?

 

[OliverA]

Use this for you database configuration

#DATABASE CONFIGURATION
DriverClass=com.mysql.jdbc.Driver
JdbcUrl=jdbc:mysql://localhost/test?characterEncoding=utf8
User=jeffkwells
Password=alberich1
#Java server port
ServerPort=17178

Make sure that you use the correct username/password

Stop all instances of JRDC2. Run JRDC2 in Debug mode. Use a web browser on the same machine that you have JRDC2 and MySQL and try

http://localhost:17178/test

If there are any errors, check the log output of your JRDC2 server in B4J and post it here including your config.properties file.

 

[jeffkwells]

I rebooted the pc
Started JRDC2 again (see screen shot)
Config file attached
Tried localhost:3306/test
Got
RemoteServer is running (03/01/2018 18:38:37)
Error fetching connection.

Thanks

 

[OliverA]

Ok, this is weird. At minimum, your log should look different. Plus, your showing a sort of proper response from a web page, but the port makes not sense (did you accidentally type 3306? Because the port to the JRDC server is 17178).

0) Restart computer
1) Re-download JRDC2
2) Change only the AdditionalJar for your mysql driver (I'm using mysql-connector-java-5.1.40-bin)
3) Use the default configuration file and replace the #DATABASE CONFIGURATION portion with what I supplied above (post #14)
4) restart the server in debug mode
5) go to http://localhost:17178/test
6) screenshot that page
7) Post your logs from B4J

 

[jeffkwells]

Yes, done all that. Here are the screenshots. (Yes, 3306 was a typo in my post)

 

[OliverA]

Yes! We have logs! Now, right click on the logs, copy all and paste here as a quote.

 

[jeffkwells]

"
Waiting for debugger to connect...
Program started.
2018-03-01 19:28:16.900:INFO::main: Logging initialized @608ms to org.eclipse.jetty.util.log.StdErrLog
Mar 01, 2018 7:28:17 PM com.mchange.v2.log.MLog
INFO: MLog clients using java 1.4+ standard logging.
Mar 01, 2018 7:28:17 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]
2018-03-01 19:28:17.540:INFOejs.Server:main: jetty-9.4.z-SNAPSHOT
2018-03-01 19:28:17.602:INFOejs.session:main: DefaultSessionIdManager workerName=node0
2018-03-01 19:28:17.602:INFOejs.session:main: No SessionScavenger set, using defaults
2018-03-01 19:28:17.604:INFOejs.session:main: Scavenging every 600000ms
2018-03-01 19:28:17.612:INFOejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@a3107a{/,file:///C:/Android/JRDC2/jRDC/Objects/www,AVAILABLE}
2018-03-01 19:28:17.628:INFOejs.AbstractNCSARequestLog:main: Opened C:\Android\JRDC2\jRDC\Objects\logs\b4j-2018_03_01.request.log
2018-03-01 19:28:17.652:INFOejs.AbstractConnector:main: Started ServerConnector@874448{HTTP/1.1,[http/1.1]}{0.0.0.0:17178}
2018-03-01 19:28:17.653:INFOejs.Server:main: Started @1364ms
Emulated network latency: 100ms
jRDC is running (version = 2.1)
Mar 01, 2018 7:29:08 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 -> 1hge13d9t1qzszaq1bf6uf6|1690aa4, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge13d9t1qzszaq1bf6uf6|1690aa4, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost/test?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeE...
Mar 01, 2018 7:30:41 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@90fd9f -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.GeneratedConstructorAccessor5.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:341)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2186)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2219)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2014)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor2.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:589)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:300)
... 20 more
Mar 01, 2018 7:30:41 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@171660c -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.GeneratedConstructorAccessor5.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:341)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2186)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2219)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2014)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor2.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:589)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:300)
... 20 more
Mar 01, 2018 7:30:41 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1ef7c4a -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.GeneratedConstructorAccessor5.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:341)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2186)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2219)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2014)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor2.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:589)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:300)
... 20 more
Mar 01, 2018 7:30:41 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@4d6e83 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
Mar 01, 2018 7:30:41 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@4d6e83 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
Mar 01, 2018 7:30:41 PM com.mchange.v2.resourcepool.BasicResourcePool
WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@4d6e83 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
"

 

[OliverA]

Caused by: java.net.ConnectException: Connection refused: connect

Are you sure your MySQL server is up and running?