B4J Question Error Attempting to Use MySQL with Server Login Example

[cklester]

I've installed the latest version of MySQL for Windows to my PC. It seems to be up and running.

I'm getting the following error when trying to run the Server Login Example.

I've got my MySQL root credentials specified in the settings.txt file.

This is in my code:

    #AdditionalJar: mysql-connector-java-8.0.22

Anybody know what the error below means or what I might have missed along the way?

Waiting for debugger to connect...
Program started.
2020-12-29 19:15:11.413:INFO::main: Logging initialized @650ms to org.eclipse.jetty.util.log.StdErrLog
Dec 29, 2020 7:15:11 PM com.mchange.v2.log.MLog 
INFO: MLog clients using java 1.4+ standard logging.
Dec 29, 2020 7:15:11 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]
Dec 29, 2020 7:15:11 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 -> 1br5o1tae1d3zlwe4y2yml|647e447, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1br5o1tae1d3zlwe4y2yml|647e447, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost/b4j_server?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleT...
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Error occurred on line: 15 (DB)
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
    at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPool.java:45)
    at b4j.example.db._createusertableifneeded(db.java:68)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:78)
    at b4j.example.main.main(main.java:29)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@7f485fda -- timeout at awaitAvailable()
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
    ... 18 more

 

[aeric]

Have you created a database name b4j_server?

 

[cklester]

I updated the settings.txt file with

DriverClass=com.mysql.cj.jdbc.Driver

to take care of the error message in the log:

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

It resolved the warning but did not fix the issue.

 

[cklester]

Have you created a database name b4j_server?

No, I haven't. There's a sub, CreateUserTableIfNeeded, so I thought the app would create the database for me if it didn't exist.

I will give it a try and report back!

 

[cklester]

It worked! Thank you, @aeric !

For future n00bs, I used these docs for creating a new database with the MySQL Workbench.

 

[aeric]

To check if the database name "b4j_server" is exist then create it if it is not, you can use this:
(DB Code Module)

Sub Process_Globals
    Public pool As ConnectionPool
End Sub

Public Sub init
    Dim JdbcUrl As String = Main.settings.Get("JdbcUrl")
    Dim driverClass As String = Main.settings.Get("DriverClass")
    Dim dbuser As String = Main.settings.Get("DBUser")
    Dim dbpassword As String = Main.settings.Get("DBPassword")
    JdbcUrl = JdbcUrl.Replace("b4j_server", "information_schema")
    pool.Initialize(driverClass, JdbcUrl, dbuser, dbpassword)
    CreateDatabaseIfNotExist("b4j_server")
    JdbcUrl = JdbcUrl.Replace("information_schema", "b4j_server")
    pool.Initialize(driverClass, JdbcUrl, dbuser, dbpassword)
End Sub

Public Sub CreateDatabaseIfNotExist(DBName As String)
    Dim DBFound As Boolean
    Try
        Dim con As SQL = pool.GetConnection
        If con.IsInitialized Then
            Dim qry As String = "SELECT SCHEMA_NAME FROM `SCHEMATA` WHERE SCHEMA_NAME = ?"
            Dim res As ResultSet = con.ExecQuery2(qry, Array As String(DBName))
            Do While res.NextRow
                DBFound = True
            Loop
            res.Close
            If DBFound = False Then
                Dim qry2 As String = $"CREATE DATABASE ${DBName} CHARACTER SET utf8 COLLATE utf8_unicode_ci"$
                con.ExecNonQuery(qry2)
            End If
        End If
    Catch
        LogError(LastException)
    End Try
    If con <> Null And con.IsInitialized Then con.Close
    If pool.IsInitialized Then pool.ClosePool
End Sub

Public Sub CreateUserTableIfNeeded
    Dim sql1 As SQL = pool.GetConnection
    If sql1.ExecQuerySingleResult("SELECT count(*) FROM information_schema.tables WHERE table_name = 'b4j_users'") = 0 Then
        Dim ct As String = "CREATE TABLE `b4j_users` (`name` VARCHAR(50) PRIMARY KEY, " _
             & " `hash` BLOB, `salt` BLOB)"
        sql1.ExecNonQuery(ct)
    End If
    sql1.Close
End Sub

Public Sub CheckUserExist (name As String) As Boolean
    Dim sq As SQL = pool.GetConnection
    Dim count As Int = sq.ExecQuerySingleResult2("SELECT count(name) FROM b4j_users WHERE name = ? COLLATE utf8_unicode_ci", _
        Array As String(name))
    sq.Close
    Return count > 0
End Sub

Public Sub CheckCredentials(User As String, Password As String) As Boolean
    Dim sq As SQL = pool.GetConnection
    Dim rs As ResultSet = sq.ExecQuery2("SELECT hash, salt FROM b4j_users WHERE name = ? COLLATE utf8_unicode_ci", _
        Array As Object(User))
    Dim res As Boolean = False
    If rs.NextRow Then
        Dim hash() As Byte = CalcHash(Password, rs.GetBlob("salt"))
        Dim storedHash() As Byte = rs.GetBlob("hash")
        If hash.Length = storedHash.Length Then
            res = True
            For i = 0 To hash.Length - 1
                If hash(i) <> storedHash(i) Then
                    res = False
                    Exit
                End If
            Next
        End If
    End If
    rs.Close
    sq.Close
    Return res
End Sub

Public Sub AddUser(User As String, Password As String)
    Dim salt(48) As Byte
    Dim sr As SecureRandom
    sr.GetRandomBytes(salt)
    Dim hash() As Byte = CalcHash(Password, salt)
    Dim sq As SQL = pool.GetConnection
    sq.ExecNonQuery2("INSERT INTO b4j_users VALUES (?, ?, ?)", _
        Array As Object(User, hash, salt))
    sq.Close
End Sub

Public Sub CalcHash(Password As String, salt() As Byte) As Byte()
    Dim md As MessageDigest
    Dim spassword() As Byte = md.GetMessageDigest(Password.GetBytes("UTF8"), "SHA-512")
    Dim pbAndSalt(spassword.Length + salt.Length) As Byte
    Dim bc As ByteConverter
    bc.ArrayCopy(spassword, 0, pbAndSalt, 0, spassword.Length)
    bc.ArrayCopy(salt, 0, pbAndSalt, spassword.Length, salt.Length)
    Return md.GetMessageDigest(pbAndSalt, "SHA-512")
End Sub

 

[aeric]

If in case anyone get error with utf8, then try replace with utf8mb4.

CREATE DATABASE b4j_server CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci