[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

jsp mysql, change db connection form localhost to remote IP address

Hello experts

I'm using mysql and jsp. when I connect to the db :

java.sql.DriverManager.getConnection("jdbc:mysql://localhost/myproject","userName", "userPassword");

the above statement will work.

but , java.sql.DriverManager.getConnection("jdbc:mysql://myIPaddress:3306/myproject","userName", "userPassword");

it won't work.

May you give me suggestion about this?

Thank you for your help.
0
lilyyan
Asked:
lilyyan
  • 11
  • 9
  • 3
1 Solution
 
bloodredsunCommented:
Have you tried without the port number
0
 
lilyyanAuthor Commented:
yeah, i've tried without the port number.

0
 
bloodredsunCommented:
Also try:

java.sql.DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/myproject","userName", "userPassword");
and check that you don't have any firewalls that could affect this connection
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
bloodredsunCommented:
What driver are you using, the Connector/J one?
0
 
lilyyanAuthor Commented:
127.0.0.1:3306 is working
0
 
bloodredsunCommented:
And what is your exception error...
0
 
lilyyanAuthor Commented:
The connetcor driver is : mysql-connector-java-3.1.7-bin.jar
0
 
lilyyanAuthor Commented:
The null point exception thrown out from sqlRst.close();
0
 
bloodredsunCommented:
>> The null point exception thrown out from sqlRst.close();
You're getting nothing back from the connection.

>> 127.0.0.1:3306 is working
This means that the code is correct.  127.0.0.1:3306 resolves to localhost for mysql which shows that your code is correct and the error is caused by somehting else. Either the IP is incorrect or you have a network issue such as a proxy or firewall which has closed the access to that port.
0
 
bloodredsunCommented:
PS you should always do a null check on the ResultSet object before you call close. Same goes for statement and connection.

e.g.
if ( sqlRst!=null ){
    sqlRst.close() ;
}
0
 
lilyyanAuthor Commented:
Thanks for your reply.

From antivirus and windows firewall which are installed in my pc, I did't find port 3306 is blocked.

Coud it be some issue related to the grant Privilege?  But the user name I currrently used is root.
0
 
bloodredsunCommented:
>>Coud it be some issue related to the grant Privilege?  But the user name I currrently used is root.

I doubt it as if it were access, it would not work at all if it were and you can get it to work on "locahost" and "127.0.0.1". How are you getting your IP address, are you using "ipconfig"?

It looks like an annoying network issue. Are you on a home machine or a work one, i.e. are you using a LAN?
0
 
lilyyanAuthor Commented:
The IP address is correct. I'm using  it in tomcat. Also I checked with ipconfig in dos command  line. It's same IP.

 I'm using a pc within a LAN.
0
 
bloodredsunCommented:
>>I'm using a pc within a LAN.

Then you might have issues with a proxy server and NAT. Can you ping that address?
0
 
lilyyanAuthor Commented:
Yeah, I just did a test.

ping myipaddress

I got something like : Packets: Sent = 4, Received = 4, Lost = 0 (0% loss)
0
 
bloodredsunCommented:
So the address is a viable one but for some reason the datbase is not responding. Weird. Check your hosts file to see that the IP is not being mapped to somewhere else.

What are you trying to do with this db?
0
 
kupra1Commented:
Hey lilyyan,
  This is because your mysql database access is restricted to the localhost for that user. To confirm this, goto the user table in the mysql database. See how many rows are there for the user you are trying to connect to the database. You will certainly see one record with the host as "localhost" and user as "youruser". So, this record allows you to access the database from the localhost only. To access it from anywhere, either you add one more record in  the table for the same user but with the host as "%" or execute the following query:

mysql> grant all privileges on myproject.* TO 'username'@'%' identified by 'userpassword';  //assuming test is your database name.
mysql> flush privileges;
0
 
lilyyanAuthor Commented:
Thanks all for your replies,

Currently my db only have one user and the user name is root.

>>mysql> grant all privileges on myproject.* TO 'username'@'%' identified by 'userpassword';  //assuming test is your database name.
>>mysql> flush privileges;

I used above command, and added another user.  Now everything works fine. But somehow the program runs much slower than before.

Do you have any suggestion about this ?

Thanks for your help.
0
 
lilyyanAuthor Commented:
>>either you add one more record in  the table for the same user but with the host as "%"

The user root only has localhost access. If I changed it to %, will this be a good idea for program speed and safety?

Thanks for your reply.
0
 
lilyyanAuthor Commented:
I am not sure the reason. Now , the speed is just as fast as before.

And it seems that I don't need add port 3306 in the db connection statement.
0
 
kupra1Commented:
Performance will not go down due to the fact that you gave more access. It will go down because now your db can be accessed from anywhere and hence, more traffic and slow.
Yea.. You dont need the port.

>> The user root only has localhost access. If I changed it to %, will this be a good idea for program speed and safety?

Well.. of course by giving access from anywhere certainly reduces your safety. If you want to give access only from let say "http://lilyyan.com", use the following:

grant all on myproject.* to 'username'@'lilyyan.com' identified by 'userpassword'.

But the safe way is to creata another user with the above rights to access your database and not use the superuser i.e. root.
0
 
lilyyanAuthor Commented:
Hi kupra1 ,
Can you help me check the question I posted under mysql topic. Thanks a lot .
0
 
kupra1Commented:
I am going there. Hold on.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now