B4J Question MariaDB connection

[johnB]

Hi

I haven't worked on MySQL for years and am having trouble connecting to a MySQL database on a Windows Server with B4J.

I have the following code

#AdditionalJar: mysql-connector-java-5.1.39-bin.jar

#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Dim Sql1 As SQL
   
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    Dim sqlCur As ResultSet
   
    MainForm = Form1
    MainForm.SetFormStyle("UNIFIED")
    MainForm.RootPane.LoadLayout("Main")
    MainForm.Show
    Sql1.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://localhost     /dividend_prices?characterEncoding=utf8")

I am getting the following error

"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."

I've attached the complete log.

Also, the DB now has a password, does this also have to be in the Initialization string

Regards

 

[JakeBullet70]

Take a look at this project. it has a working mysql connection

https://www.b4x.com/android/forum/t...kvs-synchronized-key-value-store-mysql.69042/

 

[giannimaione]

i use:

Sql.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/mysql_database","mysql_user","mysql_password")

 

[johnB]

Thanks guys, I've tried both of those but get the same result.
If I put in the wrong DB name, it gives a different error, so it must be recognizing the DB.

Is there a time out setting??? that it's breaching

Could I be using the wrong character Encoding on the DB. I'm using "utf8-bin" because I couldn't find "utf8" in the list of options

Thanks

 

[Erel]

It is not related to the timeout setting. Check this answer: http://stackoverflow.com/questions/...mmunicationsexception-communications-link-fai

I recommend using ConnectionPool to connect to remote databases. However you should first solve the connection issue.

 

[johnB]

I've been through the link Erel posted, tried most things there, but still have the problem.

Erel, do you think that it could be related to the problem I'm having with Port Forwarding. I still can't access using Port 17178 from an external site.

That's not a major issue at the moment because I only need that connection on the same external IP address.
So I guess that means it's not the issue because the server I'm having problems connection to is on the same external IP address.

Another thing is that it turns out the DB that comes with the program I'm trying to use at the moment isn't MySQL, it's a DB called MariaDB, supposedly better than MySQL???? but all references to it call it MySQL??? I'm using B4J to update the DB.

 

[billzhan]

Have you check JDBC URL? Your mysql port is 80. If not you have to specify the right port (default 3306).

Sql.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:port/mysql_database","mysql_user","mysql_password")

 

[johnB]

Hi Bill, I'm using port 3306 in my JDBC URL, originally I didn't have anything but changed that following an earlier post. The SQL config files also specify port 3306.
I'm going to update my Server to 2012 R2 tomorrow, not sure it will make a difference but that will be one more possible area ticked off.

 

[Erel]

You need to use MariaDB driver: https://downloads.mariadb.org/connector-java/

 

[johnB]

Erel - Thanks, still not solved but progressed further (I hope)

I've changed the code to

    #AdditionalJar: mariadb-java-client-1.5.0-RC1.jar

SQL1.Initialize2("org.mariadb.jdbc.Driver", "jdbc:mariadb://localhost/dividend_prices","root","2490")

and get the attached Log. Looks like it's actually got to the Db but been refused

 

[Erel]

Are you running it on a PC or Linux? The network access might be disabled.

 

[johnB]

The Server is Windows Server 2012 R2 and the B4J is being run on Windows10.

This morning (Asian time) I've made numerous (many, many) attempts to get it running, making changes to settings as found in numerous postings on the Web. Things that have apparently worked for others haven't worked for me.

I'm not sure I understand what you mean by "network access might be disabled". All my other network things seem to be working. I can connect to the Server using Windows Remote Desktop Connection. I can update the DB with a B4J program running on the Server so it has access there.

As I mentioned before, the only thing that isn't working properly is Port Forwarding but the strange thing there is that when I check I get an error on every Port, but my emails, browser and linking to other sites thru the browser work fine.

I'll attach a screen shot to show you what I mean about not recognising ports, though this isn't a real issue just at the moment and if the worst comes to the worse there, I'll go back to my previous provider.

I've just checked again that the program is working on the Server, and it does, interestingly it works with both the Maria driver and the MySQL driver.

Update 2 - Forgot to say that I can run a B4A app accessing a SQLite db on the same server

 

[billzhan]

You can try to use GUI software like HeidiSQL(windows) to test remote DB connections settings (ip port user passwd) . https://mariadb.com/kb/en/mariadb/heidisql/

Server (win 2012/or some liunx VPS) firewall may block the DB remote port (3306 by default) , you have to ensure that the port is available.

mariadb/mysql only allow local access by default. You have to enable it manually. https://mariadb.com/kb/en/mariadb/configuring-mariadb-for-remote-client-access/

 

[johnB]

You can try to use GUI software like HeidiSQL(windows) to test remote DB connections settings (ip port user passwd) . https://mariadb.com/kb/en/mariadb/heidisql/

Server (win 2012/or some liunx VPS) firewall may block the DB remote port (3306 by default) , you have to ensure that the port is available.

mariadb/mysql only allow local access by default. You have to enable it manually. https://mariadb.com/kb/en/mariadb/configuring-mariadb-for-remote-client-access/

OK, I'm doing that, I made a mistake before, I'm using Server 2008 R2

 

[johnB]

I've set up and run HeidiSQL and got the error shown on the attached file. I followed the instructions on how to fix the error by stopping and starting the Db from the command prompt but I still get the error

 

[billzhan]

Do you run HeidiSQL on the host of DB server(MS server)? Then the ip should be 127.0.0.1 (localhost).

If you run HeidiSQL on the client( win 10) where you run B4J (and app), the ip should be MS server public ip. You should also set up DB to allow remote connections.

Please provide more details if you can't fix it : server ip, DB port.

 

[johnB]

I mised the bit about the user names, I not sure that I understand this or maybe why root won't work.

I've set up 2 users jb@% AND jb@localhost and put them in my jdbc string but I still get the same error.

    SQL1.Initialize2("org.mariadb.jdbc.Driver", "jdbc:mariadb://localhost:3306/portfolio","jb@localhost","")

jb as the user name didn't work either

Update:

I've just swapped to a Windows 7 machine and I'm getting the same error message when I try to connect to the MariaDB thru a Database Browser

My DB Port is 3306 and my Server IP is 192.168.1.199

 

[billzhan]

jb@localhost is not the username. It's jb, who can only access DB from localhost(127.0.0.1, same machine of DB server).

If you are running client( HeidiSQL / B4J APP) on the same DB host, you should be able to connect with root user/passwd.
URL should be something like jdbc:mariadb://localhost:3306/dbname

If clients are running on a different host, you need to use the DB server ip
URL should be something like jdbc:mariadb://192.168.1.199:3306/dbname
And you have to enable remote access manually https://mariadb.com/kb/en/mariadb/configuring-mariadb-for-remote-client-access/

 

[johnB]

Thanks Bill for your patients and help.
What I'd missed was that the my.ini file had to be coppied to the same folder as my.cnf and to the data director as my.cnf.
I haven't run my B4J program yet because I'm still on the W7 machine and waiting for Dropbox to update files so I can get the latest version but I'll go back to W10 because it's much faster.

Thanks again to you and Erel

Erel is it posssible to cjange the heading of the post to "MariaDB connection" so that if somebody else has the problem there's somewhere to start

 

[Erel]

Erel is it posssible to cjange the heading of the post to "MariaDB connection"

Done. Note that you can change it too by clicking on the Edit Thread link under Thread Tools.