Hello,

I want to connect Oracle SQL Developer to my MySql db but don't know the hostname value.

My configuration is...
.VM Virtualbox with:
.Ubuntu Server 11.10 (64) with LAMP server.
.zc 1.5

I access my db through phpMyAdmin on the vm from http://192.168.0.107/phpmyadmin/ with no problems.

I have already:
1.Installed Sql Developer
2.Installed the driver(to get the new tab in 'new connection')

Now i need to enter:

1. Connection name: some name for my connection
2. Username: Same value as i use to access phpMyadmin
3. Password: same as point 2
4. Hostname: (defaults to 'localhost')
5. Port: (defaults to 3306)

My problem is that with this configuration it doesn't work.

I'm quite a newby so probably that's why I don't see it... Not sure where to get the value I need for the localhost... can somebody pls give me a clue?

thanks!
Leo

Have you tried using 127.0.0.1 instead of localhost

leocoppens:

I access my db through phpMyAdmin on the vm from http://192.168.0.107/phpmyadmin/ with no problems.

<snip>

Not sure where to get the value I need for the localhost..

Gee, and you are so close.. You've even provided the answer yourself :)

Hostname: ('192.168.0.107')

Another option, is if you have a valid hosts file with something like

192.168.0.107 MyComputer MyComputer.localdomain

Then either
Hostname: ('MyComputer')
Hostname: ('MyComputer.localdomain')

Should also both work.

Caveate: by default MySQL only allows user access from 'localhost', so 1st thing you'll need to do is log into the database as the DATABASE ADMINISTRATOR, and update the Zencart DB ACCOUNT USER so they can access from anywhere (%), or, for better security, from 192.168.0.% (just your private network)
These seeting can be found under the 'Privileges' tab via phpmyadmin.

Cheers
Rod.

Thanks for the replies guys.

Unfortunatelly I tried both 127.0.0.1 and 192.168.0.107 and it keeps on failing. The error msg is in the following img:

leocoppens:

Unfortunatelly I tried both 127.0.0.1 and 192.168.0.107 and it keeps on failing.

Did you do this (from my previous post)...............

"1st thing you'll need to do is log into the database as the DATABASE ADMINISTRATOR, and update the Zencart DB ACCOUNT USER so they can access from anywhere (%), or, for better security, from 192.168.0.% (just your private network) "

If so, then which did you use? "%" or "192.168.0.%" ?

Also, based on your screenshot I see you are trying to connect to your database as 'root'. Therefore, rather than the ZenCart DB account user, you will need to assign these permissions for user 'root' (which by default also resticts access to localhost/127.0.0.1 only).

If that doesn't solve the problem, my next question/comment is going to be ....
You previously stated "I access my db through phpMyAdmin on the vm" . Is your Oracle SQL Developer running on the same vm? (if not, then please fully explain your system. What OS is running 'bare metal'. What VM software are you using VirtualPC, VirtualBox, VMware, other?

Is your SQL server on the virtualised machine or the native machine?
Is your Webserver (and therefore phpmyadmin) on the virtualised machine or the native machine?
Are you accessing phpmyadmin from the virtualised machine or the native machine?
Is your Oracle SQL Developer running on the virtualised machine or the native machine?
As well as '127.0.0.1' both the virtual machine and the native machine will have thier own IP addresses. What are they, and which machine is using which IP address?

Cheers
Rod

Hello Rod, Thanks a lot for your help! unfortunatelly I still didn't get it working...

I tried going into phpMyAdmin, selecting privileges, then assigning % to each line of my root user by clicking on "Edit Privileges" as below:

My configuration is the following:

Main OS: Win7Ultimate64 (IP: 192.168.0.104)
VM Soft: VirtualBox 4.1.14
Machine on the VM: Ubuntu server 11.10 (IP: 192.168.0.107)
Software on the virtualized Ubuntu: . LAMP server
. Zen Cart

So, what I do is access the 192.168.0.107 ip of my virtual server as if it was on the net for zen cart, for zen cart admin and for phpMyAdmin with no problems or complications, I found it nice and easy, until I tried to connect sqldeveloper to it... :(

Is inbound port 3306 blocked on the vm's firewall?

Perhaps do some searching about Ubuntu and firewall controls and port 3306 for TCP or TCP_IN ?

Hello DrByte,
To be sincere I'm not sure how to check that, by looking at the vm network configuration I see this:

leocoppens:

Hello Rod, Thanks a lot for your help! unfortunatelly I still didn't get it working...

I tried going into phpMyAdmin, selecting privileges, then assigning % to each line of my root user by clicking on "Edit Privileges" as below:

Wrong Privileges. Those you set are database speciific. A user needs ACCESS Privileges before the database-specific privileges even get a looked at.

On the same screen that you found the Database specific settings (which you probably don't need to set, unless you wish even finer control over who can access a specific database) the next section has the Change password settings, and the one after that is the one you need to edit.
The default is Host: Local -> localhost.
Change this to
Host : and from the dropdown select 'Any Host'

leocoppens:

My configuration is the following:

Main OS: Win7Ultimate64 (IP: 192.168.0.104)
VM Soft: VirtualBox 4.1.14
Machine on the VM: Ubuntu server 11.10 (IP: 192.168.0.107)
Software on the virtualized Ubuntu: . LAMP server
. Zen Cart

For best performance and reliability, I would suggest you consider reversing your Main OS and VM. IE, Run Ubuntu on bare metal and virtuialise your Win7. :) Not that this has anything to do with your problem.

leocoppens:

So, what I do is access the 192.168.0.107 ip of my virtual server as if it was on the net for zen cart, for zen cart admin and for phpMyAdmin with no problems or complications, I found it nice and easy, until I tried to connect sqldeveloper to it... :(

As stated above, you need to set the user permissions to log on from anywhere (or, in your case, for better security, the IP address of your Windoze box (192.168.0.104), because that is where are connecting from.

If this STILL doesn't work, then follow up on DrByte's suggestion regarding a "firewall" blocking port 3306.
On your ubuntu server, if you type "iptables -L | grep 3306" on the command line and nothing shows then this isn't the machine doing the blocking.

Offhand I couldn't tell you where to check on the Win7 machine, but it'll be somewhere under your network settings.

Cheers
Rod

leocoppens:

Hello DrByte,
To be sincere I'm not sure how to check that, by looking at the vm network configuration I see this:

VM network config is/was fine, else you wouldn't have been able to connect with phpmyadmin.

Firewall ports are a different beast entirely.

Cheers
Rod

RodG:

As stated above, you need to set the user permissions to log on from anywhere (or, in your case, for better security, the IP address of your Windoze box (192.168.0.104), because that is where are connecting from.

Amend this. Set to log in from anywhere. It just occured to me that if you set to use just the aforementioned IP address you'll actually be denying access from 'localhost', and that will cause an issue with phpmyadmin connecting to the SQL server (since both are running on the same 'localhost') and by default, this is what both phpmyadmin are expecting to use.

There are way around this, but I'm not 100% sure of the correct syntax to use in the host field (it's probably text input and "127.0.0.1; 192.168.0.104"), buty I could be wrong.

Cheers
Rod

Thank you Rod for your help. Unfortunately I changed the place you told me (finally found it :)... ) but things are still pretty much the same.

I tried the command "iptables -L | grep 3306" but it doesn't seem to be recognized see below.

I'm giving up, will try to take your advice and change the situation by installing windows as a vm and linux as the base system.

Thanks again for all.

regards,
Leo

Also tried the iptables command with sudo and, although it didn't give any error, it didn't return anything either.

Cheers mate!
Leo

leocoppens:

I changed the place you told me (finally found it :)... ) but things are still pretty much the same.

'pretty much the same' implies that it's not exactly the same. This can often be a good sign in that one issue has been solved, but another still exists.

leocoppens:

I tried the command "iptables -L | grep 3306" but it doesn't seem to be recognized see below.

Bugger... iptables can only be run by the root user. You'll need to use

sudo "iptables -L | grep 3306"

Edit: "Also tried the iptables command with sudo and, although it didn't give any error, it didn't return anything either".

This is GOOD. It wasn't supposed to return anything! (re-read my original text).

leocoppens:

I'm giving up,

Not so fast, we've still a few more things to test and check before giving up. There are so many things that need to be 'right' (and therefore so many things that can be wrong) that we need to step through them one by one, otherwise you'll end up with information overload.

leocoppens:

will try to take your advice and change the situation by installing windows as a vm and linux as the base system.

Whoa!! Hold your horses, My advice was that this would provide better performance and reliability. I explicitely stated that this will NOT solve your problem.

Until now I have been trying to ensure that your server permissions have been set up correctly. Networking issues were next on the list (this is where firewalls may or may not be a factor).

One step at a time.

You next task is the 'ping' tests, but before I can guide you through that, I still need a few of my original questions answered.

Is your SQL server on the virtualised machine or the native machine?
Is your Webserver (and therefore phpmyadmin) on the virtualised machine or the native machine?
Are you accessing phpmyadmin from the virtualised machine or the native machine?
Is your Oracle SQL Developer running on the virtualised machine or the native machine?

The only one you've answered so far is

As well as '127.0.0.1' both the virtual machine and the native machine will have thier own IP addresses. What are they, and which machine is using which IP address?

Until/unless you provide answers to ALL of those questions I could be leading you down the wrong path. I am basing my advice/suggestions only on the information provided so far, and what I would consider to be a 'typical' scenario. This means I'm making several assumptions, and you know what they say about assumptions, don't you?

Cheers
Rod

Hello Rod!
thanks for the message! you have more faith in me than myself! :)

Ok let's see. the answers are very easy:

Is your SQL server on the virtualised machine or the native machine? The vm holds linux on which I installed the LAMP server (Linux-Apache-MySQL-PHP)

Is your Webserver (and therefore phpmyadmin) on the virtualised machine or the native machine? Answered above (LAMP on the vm ubuntu)

Are you accessing phpmyadmin from the virtualised machine or the native machine? (As above (LAMP on the vm ubuntu)

Is your Oracle SQL Developer running on the virtualised machine or the native machine? Oracle SQL Dev is on my base machine (Win7 Ultimate).

Regards,
Leo

Oh hold on, I understood wrongly the third question, I'm accessing phpmyadmin from windows (the base machine).

So when I ping from windows to 192.168.0.107 it comes back perfectly, I can also access zencart on that same ip with no issues.

OK, slight change of tactics.

Please download/install the following program (MySQL navigator).

http://sourceforge.net/projects/mysqlnavigator/files/mysqlnavigator-windows/

See if that lets you connect to the database or not.

This is merely for fault finding purposes, as it's ability to connect (or not) will inform me as to whether the issue is related to the windows inbuilt firewall, among other things.

Cheers
Rod

leocoppens:

1.Is your SQL server on the virtualised machine or the native machine? The vm holds linux on which I installed the LAMP server (Linux-Apache-MySQL-PHP)

2.Is your Webserver (and therefore phpmyadmin) on the virtualised machine or the native machine? Answered above (LAMP on the vm ubuntu)

3.Are you accessing phpmyadmin from the virtualised machine or the native machine?
I'm accessing phpmyadmin from windows (the base machine).

4.Is your Oracle SQL Developer running on the virtualised machine or the native machine? Oracle SQL Dev is on my base machine (Win7 Ultimate).

5. when I ping from windows to 192.168.0.107 it comes back perfectly, I can also access zencart on that same ip with no issues.

1, 2, 3, 5 - those are all on the vm, with the vm talking to the vm, and is as expected. Even phpMyAdmin is running ON the vm, accessed remotely from windows just like accessing zen cart remotely from windows

4 - That's the windows computer not able to talk to the vm over the MySQL port, which is 3306 normally.
Hence the suggestion that this is a firewall issue. Or your MySQL is configured to disallow direct access from external/remote addresses. Or both. And such disallowing is PERFECTLY NORMAL since any secure webserver will indeed want to prevent that sort of access for security reasons, with rare exceptions.

Tried with MySQL Nav but couldn't connect either...

(Didn't know what port to put though)

And the err:

leocoppens:

Tried with MySQL Nav but couldn't connect either...

Sweet :)

leocoppens:

(Didn't know what port to put though)

3306 (it is rarely anything different)

Solution:

On your VM locate the file /etc/mysql/my.cnf, and line#47 (or thereabouts) you'll probably see:

bind-address = 127.0.0.1

Just add a hash character before this line (to comment it out), so it reads

bind-address = 127.0.0.1

Restart the mysql server: (From the linux command line type "service mysql restart")

You should now be able to connect.

Cheers
Rod