B4J Question [Solved][Server] Run the ServerExample on Synology with MariaDB

[Elric]

Hello!

I'm trying to run the ServerExample (https://www.b4x.com/android/forum/threads/webapp-web-apps-overview.39811/#content) on my NAS Synology.

This device uses MariaDB10 instead of MySQL.

So, I've done some modification to ServerExample (renamed in ServerExampleMariaDB) code:

1. I've downloaded "mariadb-java-client-2.7.1.jar" library (from here) and I've added it in Additional libraries folder
2. in the Main module I've substituted
   #AdditionalJar: mysql-connector-java-5.1.27-bin
   with
   #AdditionalJar: mariadb-java-client-2.7.1
3. in the settings.txt file I've substituted
   JdbcUrl=jdbc:mysql://localhost/b4j_server?characterEncoding=utf8
   DriverClass=com.mysql.jdbc.Driver
   DBUser=root
   DBPassword=
   RecaptchaPrivateKey=
   Port=51042
   [*]
   with
   JdbcUrl=jdbc:mariadb://localhost/b4j_server?characterEncoding=utf8
   DriverClass=org.mariadb.jdbc.Driver
   DBUser=root
   DBPassword=
   RecaptchaPrivateKey=
   Port=51052
   [*]
4. I've run the ServerExampleMariaDB code in release mode and copied on web folder the ServerExampleMariaDB.jar, the "www" folder; I've created at same level of "www" folder on NAS, the folder "libs" with all the libraries listed by the info compile windows:

B4J Version: 8.50
Java Version: 8
Parsing code.    (0.10s)
Building folders structure.    (0.03s)
Compiling code.    (0.09s)
Compiling layouts code.    (0.00s)
Organizing libraries.    (0.00s)
Compiling generated Java code.    (0.01s)
Building jar file.    (0.05s)
    Jar file created: D:\B4X\B4J-Projects\ServerExample\Objects\ServerExample.jar
    Libraries were not merged into jar file.
The following libraries should be distributed in the libs folder:
    ByteConverter.jar
    Encryption.jar
    jCore.jar
    jCustomToast.jar
    jDateUtils.jar
    jExcel.jar
    jHTTP.jar
    jServer.jar
    Json.jar
    jSQL.jar
    jStringUtils.jar
    jxl.jar
    httpcore-4.0.jar
    commons-logging-1.1.3.jar
    jetty_b4j.jar
    c3p0-0.9.5.2.jar
    c3p0-oracle-thin-extras-0.9.5.2.jar
    mchange-commons-java-0.2.11.jar
    sqlite-jdbc-3.7.2.jar
    mariadb-java-client-2.7.1.jar
Running application.    (5.30s)
Completed successfully.

Then I opened the chosen port (51052 as set in setting.txt) on router and run the ServerExampleMariaDB.jar in Task Scheduler, following "my precedent success" for the HelloWorldServer example.

But the server program doesn't work and the task results "Interrupted" and I've gotten "ERR_CONNECTION_REFUSED".

This is my first attempt to manage a client-server DB; maybe do I miss some basic passage?

Any support will be welcome!

Thank you!

 

[Erel]

Start without the task scheduler. Are you able to start it from the command line? With java -jar <server.jar>

 

[Elric]

Thank you Erel.

I suppose I have to:

1. Use Putty or MRemoteNG
2. Enable SSH
3. Login via SSH and with an admin account.
4. in command line: "sudo su [same password as admin]"
5. replicate the command insert in the task scheduler.

Correct?

 

[Erel]

[server] Run a Server on a VPS

 

[josejad]

Correct?

Correct. Once you get the right commands, then you can insert it in the tas scheduler.

 

[Elric]

Thank you!

1. I've enabled SSH and used MRemoteNG to connect
2. I've logged with an admin account and then I've inserted the command "sudo su" then the same admin password
3. then
   cd /volume1/web/ServerExampleMariaDB
   (the path where is located the server) and then
   nohup /var/packages/Java8/target/j2sdk-image/bin/java -jar /volume1/web/ServerExampleMariaDB/ServerExampleMariaDB.jar >/volume1/web/ServerExampleMariaDB/nohup.out

The nohup.out is here attached.

It seems that the problem is in

WARNING: Could not load driverClass org.mariadb.jdbc.Driver
java.lang.ClassNotFoundException: org.mariadb.jdbc.Driver

. but I don't understand where is my error.

By the way, I've check the correctness of username (root) and password in settings.txt and the correspondence with the same credential I use to have access in phpMyAdmin.

 

[josejad]

I use in my Synology with MariaDB this driver: mysql-connector-java-5.1.44-bin and it works

 

[Elric]

I use in my Synology with MariaDB this driver: mysql-connector-java-5.1.44-bin and it works

Thank you José.

I've started by the original ServerExample; I've downloaded mysql-connector-java-5.1.44-bin, change

#AdditionalJar: mysql-connector-java-5.1.27-bin

with

#AdditionalJar: mysql-connector-java-5.1.44-bin

, run in release mode, copied the "ServerExample.jar", the "www" folder and "libs" folder in a new "ServerExample" folder, change "settings.txt" specify the port (51054) and the password but no success.

So, just two basic question:

1. if I've the server in
   volume1/web/ServerExample
   and the Jdbc library (e.g. mysql-connector-java-5.1.44-bin or mariadb-java-client-2.7.1.) in
   volume1/web/ServerExample/libs
   , what is the correct JdbcUrl path to insert in settings.txt?
2. Do I have to create a database table or other with phpMyAdmin before?

Thanks again!

 

[Elric]

So, I've tried to restart from zero: downloading the ServerExample.zip, extracting and running in release mode it, copying on NAS.

I've run ServerExample.jar by SSH and this is the nohup.out:

2020-12-17 23:17:12.472:INFO::main: Logging initialized @2094ms to org.eclipse.jetty.util.log.StdErrLog
Dec 17, 2020 11:17:13 PM com.mchange.v2.log.MLog
INFO: MLog clients using java 1.4+ standard logging.
Dec 17, 2020 11:17:14 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 17, 2020 11:17:15 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 -> z8kfltaevuhp1v1xcfzg6|140564, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> z8kfltaevuhp1v1xcfzg6|140564, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost/ServerExample?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
db._createusertableifneeded (java line: 151)
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:151)
    at b4j.example.main._appstart(main.java:64)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:78)
    at b4j.example.main.main(main.java:28)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@45f292 -- 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)
    ... 11 more

Really, I don't understand where is the problem...

Anyone has an idea?

 

[aeric]

db._createusertableifneeded (java line: 151)

The log shows this line causing error.

 

[Elric]

Thank you aeric.

I used the example without changing the code. Assuming that the code is correct (of course), maybe I'm missing to do some action in phpMyAdmin or other?

 

[Erel]

The full example depends on a MySQL database and some configuration. Check the code in DB.CreateUserTableIfNeeded.

 

[Elric]

Thank you guys, above all for patience: this is my first experience with MySQL, sorry!

This is the code:

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")
    pool.Initialize(driverClass, JdbcUrl, dbuser, dbpassword)
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

I checked if the SQL syntax was correct and it is ok for me but also for https://en.rakko.tools/tools/36/ and https://it.piliapp.com/mysql-syntax-check/.

However, my phpMyAdmin shows up like this:

The settings.txt file point to "JdbcUrl=jdbc:mysql://localhost/b4j_server?characterEncoding=utf8".

Because the generic format of the connection URL is protocol//[hosts][/database][?properties] I guess that I should see a "b4j_server" database in phpMyAdmin or a Sub dedicate to create such database, but searching for "b4j_server" in the code of "ServerExample.b4j" project I don't have any result.

However, the "Public Sub CreateUserTableIfNeeded" seems (if I've interpretated correctly) checking if the "b4j_users" table exist in "information_schema.tables" database and, if not, create such table.

"information_schema" exist (as per image above) and exist also the table "tables" but no "b4j_users", notwithstanding the syntax is correct.

I don’t know what to catch. A hard reset of my NAS? Abandon the project? Try to do that with SQLite?

 

[aeric]

If you are connecting the db with a user which is not root, you will end up with count = 0.

 

[aeric]

You need to assign the user to the database.

GRANT ALL PRIVILEGES ON `b4j_server`.* TO 'myuser'@'localhost';

Assuming myuser is your db user name.

 

[Elric]

Thank you aeric.

I'm connecting with root - this is the settings.txt file:

JdbcUrl=jdbc:mysql://localhost/b4j_server?characterEncoding=utf8
DriverClass=com.mysql.jdbc.Driver
DBUser=root
DBPassword=xxxxx
RecaptchaPrivateKey=
Port=51054

 

[aeric]

How do you create the database b4j_server ?

 

[josejad]

Ok Elric:

I have no experience with B4J (just jRDC2), and I have a Synology too, so I think we start from the same point, so I will try the example as the same time you and try to find the solution toghether.

I'm getting the same error as you, so, first of all, let's try the server in your own computer, before trying to setup in the NAS.
So, first of all, setup a XAMPP or Laragon in your development computer.
- You show your settings.txt, but the user is root and no password. You have to setup some right user there.

I have an error after that, but let's see first if you get the same error and then, let's ask here.

 

[Elric]

How do you create the database b4j_server ?

Thank you aeric. I do not create any database. I guess that should be the example to do that. It is not correct?

Ok Elric:

I have no experience with B4J (just jRDC2), and I have a Synology too, so I think we start from the same point, so I will try the example as the same time you and try to find the solution toghether.

I'm getting the same error as you, so, first of all, let's try the server in your own computer, before trying to setup in the NAS.
So, first of all, setup a XAMPP or Laragon in your development computer.
- You show your settings.txt, but the user is root and no password. You have to setup some right user there.

I have an error after that, but let's see first if you get the same error and then, let's ask here.

Thank you José. At present I've not my development computer. However, I've download Largon in portable version. Not sure how I can use that.

About "root" user, I suppose this should be sufficient - is the only user authorized to manage phpMyAdmin.

I've tried to use "mariadb-java-client-2.7.1" and set
JdbcUrl=jdbc:mysql://localhost/b4j_server?characterEncoding=utf8
DriverClass=com.mysql.jdbc.Driver

but if I do that, I get this nohup.out file:

2020-12-27 14:01:29.092:INFO::main: Logging initialized @2613ms to org.eclipse.jetty.util.log.StdErrLog
Dec 27, 2020 2:01:30 PM com.mchange.v2.log.MLog 
INFO: MLog clients using java 1.4+ standard logging.
Dec 27, 2020 2:01:32 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 27, 2020 2:01:33 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 -> z8kfltae19l1k6n1xyjltb|7f674d, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.mariadb.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> z8kfltae19l1k6n1xyjltb|7f674d, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:mariadb://localhost/b4j_server?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
Dec 27, 2020 2:01:33 PM com.mchange.v2.c3p0.DriverManagerDataSource 
WARNING: Could not load driverClass org.mariadb.jdbc.Driver
java.lang.ClassNotFoundException: org.mariadb.jdbc.Driver
    at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:264)
    at com.mchange.v2.c3p0.DriverManagerDataSource.ensureDriverLoaded(DriverManagerDataSource.java:143)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:173)
    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)
    ...

I don't understand why

WARNING: Could not load driverClass org.mariadb.jdbc.Driver
java.lang.ClassNotFoundException: org.mariadb.jdbc.Driver

I've put the library in a folder named "libs" together the other libraries.

 

[Erel]

Have you also added: #AdditionalJar: mariadb-java-client-2.7.1 .jar ?