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)
[CODE lang="b4x" highlight="10,11,12,13,17-38"]
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[/CODE]

 

[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