Cannot connect to mysql programmatically

Question

I have setup mysql (mariadb) on linux machiche. I have created a user 'newuser' like:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

when i do :

select user,password from user;

i can see:

+--------------+-------------------------------------------+
| user         | password                                  |
+--------------+-------------------------------------------+
| root         | *password                                 |
| newuser      | *password                                 |
+--------------+-------------------------------------------+

and when i execute this command :

select user();

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

I am trying to connect to my database using java but i am getting below exception:

java.sql.SQLException: Cannot create connection:Access denied for user 'newuser'@'localhost' (using password: YES)

+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for newuser@localhost                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' IDENTIFIED BY PASSWORD '*7ABDF971526E9441B919C9FE77D50DB0363B3509' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------------+

Java code to connect to db:

try {
        Class.forName("com.mysql.jdbc.Driver");
    //  Connection connection = DriverManager.getConnection(connectionString);
        Connection connection = DriverManager.getConnection("jdbc:mariadb://localhost:3306/iot?autoReconnect=true","newuser","password");
        System.out.println("Connected --->"  + connection);
        return connection;
    } catch (SQLException e) {
        throw new SQLException("Cannot create connection:" + e.getMessage());
    }

Can anyone help me what i am missing?

Using GRANT does not require flushing - stackoverflow.com/questions/36463966/… — tomaytotomato
– tomaytotomato, Commented Apr 4, 2018 at 15:40
@tomaytotomato yep, I found that while looking for documentation to point OP to. I'll just leave my comment there as a testament to my own ignorance. — Federico klez Culloca
– Federico klez Culloca, Commented Apr 4, 2018 at 15:41
@Rohitesh try this and see what it says SHOW GRANTS FOR 'newuser'@'localhost' — tomaytotomato
– tomaytotomato, Commented Apr 4, 2018 at 15:44

Alexius DIAKOGIANNIS · Accepted Answer · 2018-04-04 16:37:37Z

1

Try to connect to mysql and do

update user set host = '%' where user = 'newuser';
flush privileges;

Try to connect again. If you succeed then (as I suspect) you are NOT running the code on the same machine as mysql. You need to find the ip of your local machine (ifconfig on linux, ipconfig on windows) and execute

update user set host = 'xxx.xxx.xxx.xxx.' where user = 'newuser';
flush privileges;

to allow connections from your ip (xxx.xxx.xxx.xxx) only.

To revert changes execute

update user set host = 'localhost' where user = 'newuser';
flush privileges;

answered Apr 4, 2018 at 16:37

Alexius DIAKOGIANNIS

2,5842 gold badges22 silver badges32 bronze badges

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

2 Comments

Rohitesh Over a year ago

now its getting connected but one thing i want to know i am running mysql on rashbeery and trying to connect using remote machine using mysql workbench why its not connecting from workbench . Do i need to allow the tcp connection for remote connection?

Alexius DIAKOGIANNIS Over a year ago

You are probably trying to connect from workbench as root so you need to re-run the procedure for the root user or use the option in workbench in order to use an ssh tunnel. Since you managed to connect and my answer help you to identify and solve the issue can you be so kind to mark my answer as a solution? Thanks.

Daniel Metzger · Accepted Answer · 2018-04-04 15:46:26Z

0

Do you actually connect from the local machine? If not, you should substitute the "localhost" with a description of your host.

answered Apr 4, 2018 at 15:46

Daniel Metzger

11 bronze badge

1 Comment

Rohitesh Over a year ago

@danielmetzer what actually you mean to say can you please elobrate ?

Himanshu Sharma · Accepted Answer · 2018-04-04 16:36:35Z

0

As per your scenario, I think you should have used

    FLUSH PRIVILEGES;

In case of modification of privilege FLUSH not needed as it will be reloaded but here you are creating new user and grant new privilege to new user so try with FLUSH PRIVILEGES.

I have used the following steps and it is working fine here:-

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Now login with new newuser:-

     mysql -u newuser -p
     password
     show GRANTS;

    Output:- 
    +------------------------------------------------------+
    | Grants for newuser@localhost                         |
    +------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' |
    +------------------------------------------------------+

As select user() -- It will show current user which is logged in.
SELECT User FROM mysql.user ---It will show all users created in mysql.

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        //  Connection connection = DriverManager.getConnection(connectionString);
            Connection connection = DriverManager.getConnection("jdbc:mariadb://localhost:3306/iot?autoReconnect=true","newuser","password");
            System.out.println("Connected --->"  + connection);
        } catch (SQLException e) {
            throw new SQLException("Cannot create connection:" + e.getMessage());
        } 

        }

It is running without error.I don't see any issues here.Have a look on it.

answered Apr 4, 2018 at 16:36

Himanshu Sharma

5903 silver badges12 bronze badges

2 Comments

Alexius DIAKOGIANNIS Over a year ago

FLUSH PRIVILEGES; is NOT needed and if you read carefully the comments he has tried it with no success.

Himanshu Sharma Over a year ago

Yeah ! Then It is IP issue as 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0 must be connecting on different addresses. It is not privilege problem. java.sql.SQLException: Cannot create connection means it must be connecting to some other host as As password and user name seems correct.

Collectives™ on Stack Overflow

Cannot connect to mysql programmatically

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related