Set mysql user host in jdbc connection java

Question

I'm trying to connect to remote mysql server in java, from ip 111.111.111.111 to ip 111.111.111.222 with JDBC connector:

conn = DriverManager.getConnection("jdbc:mysql://"+serverAddress+":3306/" + this.dBName + "?" + "user=" + this.userName + "&password=" + this.password + "");

[email protected] is created in database and GRANTS are working fine.
user@localhost doesn't exist in database because I think is not neccesary.
Firewall is allowing connection.

For some reason when I try to start the connection an exception is generated:

java.sql.SQLException: The user specified as a definer ('user'@'localhost') does not exist

but I'm requesting the connection from ip 111.111.111.111

Note: when I write an @ in the username this shows the correct host ip but with an extra @, like this:

java.sql.SQLException: Access denied for user 'user@'@'111.111.111.111' (using password: YES)

So, What am I doing wrong? or how can set the host of the mysql username?

EDIT:

Additionally I've tested by terminal the mysql connection and it works properly: (so@SSS ip is 111.111.111.111)

so@SSS:~$ mysql -h 111.111.111.222 -u user -p dbname
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 85
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+---------------------+
| Tables_in_dbname |
+---------------------+
| wh_with_customer    |
+---------------------+
10 rows in set (0,00 sec)

user does't have privilege to connect with mysql. You have to give user the permission — Haifeng Zhang
– Haifeng Zhang, Commented Feb 13, 2017 at 18:20
" because I think is not neccesary." - check your assumptions. — duffymo
– duffymo, Commented Feb 13, 2017 at 18:21
@duffymo because I just connect to database remotely. So is it neccesary? — Sergio Baquero
– Sergio Baquero, Commented Feb 13, 2017 at 18:24
try mysql -h111.111.111.111 -uuser -p and see whether you can access to Mysql remotely — Haifeng Zhang
– Haifeng Zhang, Commented Feb 13, 2017 at 18:26
@HaifengZhang But I'm testing mysql connection by shell and this is the result: mysql -h 192.xxx.xxx.xxx -u user -p dbName Enter password: ... Welcome to the MySQL monitor. ... mysql> show tables; +---------------------+ | Tables_in_dbname | +---------------------+ ...| wh_with_customer | +---------------------+ 10 rows in set (0,00 sec) So it really works — Sergio Baquero
– Sergio Baquero, Commented Feb 13, 2017 at 18:27

Haifeng Zhang · Accepted Answer · 2017-02-13 18:43:11Z

1

You didn't grant privileges to user from my perspective

GRANT ALL PRIVILEGES ON *.* TO 'user'@'111.111.111.111' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Execute it and replace PASSWORD with your real password.

Alternatively, try using DriverManager.getConnection(String url, String user, String password) other than

DriverManager.getConnection("jdbc:mysql://"+serverAddress+":3306/" + this.dBName + "?" + "user=" + this.userName + "&password=" + this.password + "");

DriverManager.getConnection(String url) expects URL in jdbc:subprotocol:subname format, getConnection(url, username, password) is more precise

edited Feb 13, 2017 at 18:43

answered Feb 13, 2017 at 18:29

Haifeng Zhang

32.1k22 gold badges95 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sergio Baquero Over a year ago

This is the result of:

show grants     -> ; +------------------------------------------------------------------------------------------------------+ | Grants for [email protected].%                                                                       | +---------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user'@'111.111.111.%'                                                        | | GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW ON

dbname.* TO 'user'@'111.111.111.%' | 2 rows in set (0,00 sec)

Sergio Baquero Over a year ago

and I just tried with

conn = 					       DriverManager.getConnection("jdbc:mysql://"+serverAddress+":3306/" + this.dBName+"", this.userName+"" ,this.password );

but it still showing java.sql.SQLException: The user specified as a definer ('user'@'localhost') does not exist But i'm not requesting from localhost but from a different ip. Thanks @HaifengZhang

Haifeng Zhang Over a year ago

run GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION; and FLUSH PRIVILEGES

Sergio Baquero Over a year ago

It will work because the connection will be made by localhost. There is no possibility to connect remotely and set the host correctly (in this case [email protected])? I can't find out if JDBC always create connection as localhost user.

Haifeng Zhang Over a year ago

Access denied for user 'user@'@'111.111.111.111' what is the username ? is it user or user@? The user specified as a definer ('user'@'localhost') does not exist means user doesn't exist. is it possible that the username you use is misspelling?

|

Collectives™ on Stack Overflow

Set mysql user host in jdbc connection java

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related