B4J Question Having trouble connecting to mysql database

[j_o_h_n]

I first downloaded usbwebserver in order to create a wampserver on my windows 10 machine
I didn't install anything, I just ran the usbwebserver program.
Then using the phpMyAdmin that comes with it I was able to add a database called users and put some data in that.

Then I downloaded Peter Simpsons tutorial on connecting to various databases
Using this I was able to interact with a database on the postgresql server I have installed on my database without any problems at all.
However no matter what I try I'm not having any luck with mysql.

Here is what I think is the relevant part of the program:

Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True

    'MySQL Connector/J Driver
    #AdditionalJar: mysql-connector-java-5.1.47-bin.jar

#End Region

Sub Process_Globals
    Private SQL As SQL

#Region Database Location
    Private DBLocation As String = "localhost" 'Can use IP address or domain name
    Private DBUsername As String = "root"
    Private DBPassword As String = "usbw"
#End Region
End Sub

Sub AppStart (Args() As String)
    LogError("---------- NorthWind Database (MySQL) ----------")
    SQL.InitializeAsync("MySQL", "com.mysql.jdbc.Driver", $"jdbc:mysql://${DBLocation}/users?useSSL=false"$, DBUsername, DBPassword)
    SQL.Close
    StartMessageLoop 'only required in a console app

After the last line the program waits for a bit and then gives the a whole lot of error logging.

Waiting for debugger to connect...
Program started.
---------- NorthWind Database (MySQL) ----------
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 java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
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:342)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2197)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2230)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2025)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:778)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
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 java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
at anywheresoftware.b4j.objects.SQL.Initialize2(SQL.java:58)
at anywheresoftware.b4j.objects.SQL$1.call(SQL.java:99)
at anywheresoftware.b4j.objects.SQL$1.call(SQL.java:1)
at anywheresoftware.b4a.BA$4.run(BA.java:272)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.net.ConnectException: Connection refused: connect
at java.base/java.net.PlainSocketImpl.connect0(Native Method)
at java.base/java.net.PlainSocketImpl.socketConnect(PlainSocketImpl.java:101)
at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:399)
at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:242)
at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:224)
at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:403)
at java.base/java.net.Socket.connect(Socket.java:591)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:301)
... 23 more

The other day I was having what are probably similar issues with the third Harris tutorial on abMaterial.
I could not get the database interaction to happen then either. I'd be very grateful for any help anyone can give.

II#d

 

[OliverA]

1) You're closing you DB as quickly as you open it
2) Your async functions must happen outside of AppStart. See: https://www.b4x.com/android/forum/t...t-for-sleep-in-server-handlers.81833/#content

Sub AppStart (Args() As String)

    StartDB
    StartMessageLoop 'only required in a console app
    SQL.Close
End Sub

Sub StartDB
    LogError("---------- NorthWind Database (MySQL) ----------")
    SQL.InitializeAsync("MySQL", "com.mysql.jdbc.Driver", $"jdbc:mysql://${DBLocation}/users?useSSL=false"$, DBUsername, DBPassword)
End Sub

 

[j_o_h_n]

Thank you OliverA
I altered it as you suggested there but unfortunately the same output log appeared.
(Actually, earlier I commented out the SQL.Close line and that also made no difference.)
I think it's either something wrong with my connection string or with the way I setup (even though it's simply a matter of unzipping) usbwebserver
I've run usbwebserver both on a usb key and in a folder on drive C but that made no difference either.

 

[OliverA]

Looks like USBWebserver uses a non-default MySQL port of 3307 (instead of 3306). Makes sense for the purpose of USBWebserver, but it also means you have to include the port number in your JDBC URL

#Region Database Location
    Private DBLocation As String = "localhost" 'Can use IP address or domain name
    Private DBUsername As String = "root"
    Private DBPassword As String = "usbw"
    Private DBPort As Int = 3307
#End Region

Sub StartDB
    LogError("---------- NorthWind Database (MySQL) ----------")
    SQL.InitializeAsync("MySQL", "com.mysql.jdbc.Driver", $"jdbc:mysql://${DBLocation}:${DBPort}/users?useSSL=false"$, DBUsername, DBPassword)
End Sub

Edit: USBWebserver docs: http://www.usbwebserver.net/downloads/manual.pdf

 

[j_o_h_n]

Wow! Thank you so much for going to the trouble to find that out for me. It worked!

 

[Harris]

Looks like USBWebserver uses a non-default MySQL port of 3307 (instead of 3306)

I had the same issue 3 years ago when I installed usbwebserver. I use it everyday in my local development and it works great!