[Solved-Partially] Connecting Base to a Remote MySQL Server

[GowerMick]

I have a Windows PC and a Linux Laptop on my local network, both running a MySQL Server and Client.

I want to access a database on the Linux Server from ooBase running on the Windows PC, and sometimes vice versa, and sometime from my iPad.

Is this possible, and if so, what is needed to allow this to happen?

At present, whenever I try to connect to a database on the linux server from either the iPad or the Windows PC I get (the same) communication error!
(NB Windows MySQL Workbench CAN see both Windows and Linux Servers)

Any guidance would be appreciated.

[Villeroy]

Without technical information (URL, port number, firewall etc.) it is impossible to give an answer. But I think the answer has nothing to do with the OpenOffice client.

[GowerMick]

I tend to agree with you with respect to the openoffice client. But I must be missing some setting on server to allow remote ooBase to act as a client. I use a local IP (192.168.1.98) and usual port 3306. As both servers can be seen by both MySQL workbenches, I don't think firewall can be the problem, unless Workbench uses a different port to communicate.

NB Both ooBase can connect to their own local server, just not to the remote server.

[Villeroy]

menu:Edit>Database>Properties...

[GowerMick]

menu:Edit>Database>Properties...

I am aware of that! I had supplied the Wizard with exact same information for the remote MySQL server, but still get communication error!
The error reads:

"The connection to the data source "databasename" could not be established.
Communication 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.e when I set hostname to IP address of remote server I get above error, when I set it to localhost I don't, and can connect to local server.

NB I also think there is a bug in ooBase! The 'databasename' in the error message was ooBase filename, NOT the MySQL databasename entered in the property field! Notwithstanding that, after renaming ooBase filename to match MySQL database name, I still got same error message, but this time with the correct MySQL databasename shown.

[Villeroy]

By googling the error message I know now that you are using the JDBC driver for MySQL.

[Sliderule]

Mick:

Let me attempt to explain, how I have set up OpenOffice Base to connect to my running MySQL java server:

1. Start OpenOffice and from the Menu: Tools -> Options...
2. Expand the first item . . . OpenOffice.org and click on Java
3. Click on Class Path...
4. If not already present . . . Add the folder on your machine . . . where the file: mysql-connector-java-5.1.24.bin.jar is located . . . of course . . . your file/version name may be different.
5. Add the above jar file Archive ( file ) . . . the reason . . . OpenOffice will know where it is located ( the class path )
6. Now, add the connection information from the Base Menu: Edit -> Database -> Properties...
7. In the pop-up . . . under . . . Database URL, I entered: mysql://127.0.0.1:3306/bldtest . . . since . . . java: was already present . . . and . . . note: of course, . . . my db name is bldtest . . . yours will be different
8. Enter your user name: for example, root
9. Enter the JDBC driver class: com.mysql.jdbc.Driver
10. Press the button: Test class and you should see a pop-up, like: The JDBC driver was loaded successfully.
11. Press the button: Test Connection and you should see a pop-up like: The connection was established successfully.
12. Press OK
13. Save your OpenOffice Base file.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

[GowerMick]

My java path is ok, and I CAN connect to a local MySQL database.
The problem is changing destination from 'localhost 'to the IP address (192.168.1.98) of the remote server.

I CAN communicate with this remote server from MySQL workbench, but not from ooBase! (I am assuming workbench uses same port of 3306).

[Sliderule]

So, in the step from Sliderule above, number 7, did you change it to read:

mysql://192.168.1.98:3306/the_file_name

and change the_file_name to the real file name of your running JDBC server ?

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this issue has ben resolved.

[GowerMick]

By googling the error message I know now that you are using the JDBC driver for MySQL.

Any chance you could give me a link of where you found this?

Thanks
Mike

[GowerMick]

So, in the step from Sliderule above, number 7, did you change it to read:

mysql://192.168.1.98:3306/the_file_name

and change the_file_name to the real file name of your running JDBC server ?

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this issue has ben resolved.

Yes! I have copies of all databases on both MySQL servers, so I just changed 'local host' to IP address of remote server. Database name is the same. (The details are entered into three fields:- host: 192.168.1.98; Port : 3306; Database: Fred)

NB for host I just enter 'local host' or '192.168.1.98' NOT 'Mysql://192.168.1.98'. Is the 'mysql://' part important?

( I wish to get rid of one of the servers, to avoid duplication of these databases, but until I can connect to one server remotely, I am stuck with the duplication! Furthermore, I am unable to access either server from my IPad app, which I assume uses same protocols, as it gives same error message)
Mike

[GowerMick]

Slierule

Edit/database/properties popup, there is no longer an entry for database URL.
It now asks for host name; port number; class and character set.
For host name I enter local host ( this works) or 192.168.1.98 ( this fails)

[GowerMick]

Update:
I changed my.cnf on Linux Laptop, and amended [mysqld]bind-address to 192.168.1.98 (IP of Laptop) and this cured one half of problem.(PC's ooBase can now open databases on Laptop's MySQL Server)

I am now playing with my.ini on PC, to see if I can seolve the other half of problem.

[rudolfo]

The problem with MySQL is that the network and IP configuration sucks. It allows you only to bind the server port to a single network interface. If you have two internal network interfaces that should be allowed to process connections for MySQL you can't neither add both internal IP addresses (192.168.1.2 and 192.168.10.2) nor the two internal network interfaces (eth0 and eth0:0). Your only chance to get this working is to activate the classical TCP_ANY wildcard IP address 0.0.0.0 in my.cnf and implement all your security restrictions in the firewall.
I don't know why MySQL is so stubborn in this area, all other common servers on Linux are able to bind their server port to multiple IP-addresses or ports: PostgreSQL, sshd, samba, SMTP-server, ...

But this is surely a network problem. netstat is you friend here ... on both operating systems. The options are only slightly different. On the server execute:

Code: Select all

netstat -na --tcp

or on a Windows system

Code: Select all

netstat -na -p TCP

Look for lines with :3306 to check on which port the MySQL server is listening.

Then run a second test with MySQL Workbench to check which IPs and ports it is using. I am not a friend of MySQL Workbench, but based on all that I know about it, I would say that it keeps an open connection to the database server even if there is currently no active query. You can see this kind of connections if you limit the netstat options to show only the established IP connections:

Code: Select all

netstat -n --tcp

It doesn't really matter if you run this netstat command on the client or on the server machine. It should show you data in both cases. The connection that MySQL workbench uses the <ip-address>:3306 on the server side.
To compare what OOo Base is doing different then the MySQL workbench, you terminate all Workbench sessions and start OOo Base. Klick on one of the tables that you see, to make sure that a connection can really be established. Then run the same netstat command to show the active IP connections as before.