[Solved] MySQL via OO- in one PC and up to across internet

[MSPhobe]

Silly me.

Two weeks getting OpenOffice Base to access a MySQL database server existing on my LAN...

After two weeks of struggle, I had it working, if the server concerned is running in the same PC as the Base front end.

Finally got everything right. I've revised the rest of THIS post to whittle it down to the questions you need to ask, and the answers to them. (The rest of the thread is more or less as it evolved. A few more details in my post of the evening of 24 Feb 13)

Umm... PPS... March 15th: Please say I haven't been fighting with this for so many weeks?

My "latest, greatest" guide to Open Office Base as front end to a MySQL server...

http://sheepdogguides.com/srv/s0MySqlDoInst.htm

Start there (MySQL server and Open Office front end (client) in one pc), if you are attempting similar. You have to have all those bits working before you can access the server remotely, i.e. across the internet. The tutorial does eventually get you up to that most complex capablility.

These are some of the ingredients, if "you want it all". To make a sensible start, you do NOT need many of these.

You need a way to connect to the LAN the server is on. For many, this will mean using DDNS.

The router on the LAN with the MySQL server needs to be set up to allow traffic for 3306 through. I suspect TCP traffic is enough, but I only tried it with TCP and UDP enabled.

The firewall on the PC with the MySQL server needs to be set up to allow traffic for 3306 through.

By the way... all of this is a risk to the security of your system. A "big" risk? I don't know.

I like using native tools where possible. And, at least for a connection and the editing of a datum in a table, the native MySQL connector WILL work, if you have a text URL to use, e.g. MyWebsite.dyndns.org. (There was some talk in fora about the connector not coping with numeric IP addresses. Maybe fixed? The text translates to a number, after all.)

While, as I say, I like to avoid adding too many layers of complication, ALONG THE WAY to "getting to know" my MySQL server, I did some experiments with using a JDBC connector with Open Office. When I was given my "eureka" inspiration, I was able to ABANDON that. My tutorial will take you "directly" to MySQL server/ Open Office client, using the fairly new Open Office "native" MySQL connector.

====
If for some reason you WANT to play with the JDBC...

First, fetch(from http://dev.mysql.com/downloads/connector/) the "Connector/J". Un-zip the .zip file with .rar archive. Store contents somewhere sensible. Modify OO configuration (Add Java class path, with "Add Archive" pointing to... un-ZIPped .rar) Stop and re-start OO, including QuickStarter.

When asked, in step two of "connect to existing database", you need something to put into "JDBC driver class".

Eventually discovered that "secret driver JDBC class name" is com.mysql.jdbc.Driver. At least, with that, if I click "test class", I get "Loaded successfully".

[eremmel]

If you want to connect to MySQL over the internet (and not the intranet (local network), then you take a large risk, but you might be able to control that by using the right security measures (but that part is pure MySQL).

I guess that for AOO a connection over internet is the same as over intranet (local lan) So if you are able to connect to MySQL from Base from a remote computer on your local connection you have proven the remote connection from Base to MySQL.

What is left is to make a connection over the internet. That is not the main topic for this forum (to say with some understanding). Depending on your network settings you need to do a few things. When you have NAT you need port forwarding. You need to control firewall(s). When you want to be on the save side I would go for a SSH connection with certificates, no plain user/passwords etc.

MySQL has an unusual user account system. A user is identified by {host-username} tuple. You need to set an account and grand access per tuple.

Test the connection first with the connection tools of MySQL that rules out the dependency on Base. I think for further questions go to a MySQL forum.

[MSPhobe]

I fully understand that elements of this are "non-OO"... but, for example, whether the native (OO supplied) MySQL connector works across the internet (yet), is, surely? And this sub-forum is, after all, "External Data Sources"?

Some progress made. Trying to use the JDBC connector still, given the suggestions, not countered, that the OO MySQL connector doesn't work, I'm now getting as far as "connecting"... sort of. But before any useful work, and even just if I do a "test connection", within 30 seconds I get "Communications Link Failure. The last packet sent successfully to the server was 0 miliseconds ago. The driver has not received any packets from the server."

(And I tried the MySQL direct (over internet, but with "native" connector) again just now, given the many changes to firewalls, etc over the past hours. Still hangs at "Test connection". Doesn't even prompt for password, which JDBC manages, at least. (After a long time... but less than to type this paragraph!... comes back with "can't connect".)

(Original post revised 23 Feb 13, 20:39, UK time, to reflect some progress)

[eremmel]

Please try first to make a connection via a tool like MySQL WorkBench over the internet, when that works bring Base to the game.

[MSPhobe]

Smack! Smack! That's me, smacking myself up alongside the head for not thinking of that. I know better!

And! Big party tonight! Yesterday's very frustrating four hours were not in vain!

Following eremmel's excellent suggestion... just when I'd Given Up... his idea gave me the energy to have One More Try, during which I quickly (!) found the final impediment. (Software firewall. I did "open a hole" in my hardware firewall, neglected the other one, and it didn't send me any "fred is trying to get in" messages. (I can always find someone to blame.))

Bottom line: Base 3.4.1, with native MySQL connector 1.0.1, Windows Win 7 machine for both server and client, MySQL ver 5.5

*** Working! *** (Well.. I managed to connect, change a value in a record.) Thank You Eremmel.

I have revised the original post to point others to answers I found.

Who knows? JDBC connector may be working now too!

[eremmel]

One last remark to protect your database /server from attacks...
- remove test accounts that are installed by default.
- Use TCP/IP over SSH and/or use SSL to connect from client to MySQL.
- Do not use a standard port on you internet modem for knowing the application makes things to easy. Just use a port number like 56723 and forward that to local server.