Access denied for user 'root'@'localhost' with two DataSources in context.xml

Question

I'm facing a problem with Java 8, MySQL Server 5.7.23, MySQL Connector for Java v5.1.45 and Tomcat v9.0.12 (running on Ubuntu Server 18.04).

I've built a simple Java application (war) that executes a query in a MySQL database.

My context.xml (I've put 2 resources nodes, one for production and one for developing environment), from the real application scenario I read from CATALINA_OPTS a parameter called "env" and reading it with System.getProperty("env"), the application can choose the appropriate datasource to use.

<Context>
    <!-- MySQL - Production -->
    <Resource
        name="prod-jdbc/ds-1" auth="Container"
        type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/testdb?useSSL=false"
        username="dbuser" password="dbUserPwd!"
        autoReconnect="true" maxTotal="10"
        minIdle="3" maxIdle="5" maxActive="10" maxWaitMillis="10000"
        removeAbandonedOnBorrow="true" removeAbandonedOnMaintenance="true"
        logAbandoned="true" removeAbandonedTimeout="1000"
        closeMethod="close" testOnBorrow="true"
        testWhileIdle="true" timeBetweenEvictionRunsMillis="60000"
        validationQueryTimeout="10" validationInterval="6000"
        validationQuery="SELECT 1"
    />

    <!-- MySQL - Development -->
    <Resource
        name="dev-jdbc/ds-1" auth="Container"
        type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/localdb?useSSL=false"
        username="root" password="root"
        autoReconnect="true" maxTotal="10"
        minIdle="3" maxIdle="5" maxActive="10"
        maxWaitMillis="10000" removeAbandonedOnBorrow="true"
        removeAbandonedOnMaintenance="true" logAbandoned="true"
        removeAbandonedTimeout="1000" closeMethod="close"
        testOnBorrow="true" testWhileIdle="true"
        timeBetweenEvictionRunsMillis="60000" validationQueryTimeout="10"
        validationInterval="6000" validationQuery="SELECT 1"
    />
</Context>

A simple tester class:

package main;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;
import javax.ws.rs.core.Application;

public class ConnectionTester extends Application implements ServletContextListener {

    @Override
    public void contextInitialized(ServletContextEvent arg0) {
        Context ctx = null; Connection con = null; Statement stmt = null; ResultSet rs = null;
        try{
            ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:/comp/env/prod-jdbc/ds-1");
            con = ds.getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT nome FROM Customers");
            while(rs.next()) {
                System.out.println(rs.getString("name"));
            }
        }
        catch(NamingException | SQLException e){
            e.printStackTrace();
        } 
        finally{
            try {
                rs.close(); stmt.close(); con.close(); ctx.close();
            } catch (SQLException e) {
                System.out.println("Exception in closing DB resources");
            } catch (NamingException e) {
                System.out.println("Exception in closing Context");
            }
        }
    } // contextInitialized
} // ConnectionTester

I've set MySQL permission this way (I also need to allow external access for "dbuser" because I have an external client that connects to this MySQL database):

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'veryStrongPwd';
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'dbUserPwd!';
GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'%' IDENTIFIED BY 'dbUserPwd!' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Tomcat doesn't have any specific configuration or resource configured, it's simply the v9.0.12 unzipped in a folder.

I've tried to put in context.xml both the "root" and the "dbuser" credentials but when I start Tomcat, I get the following problem:

ConnectionPool.init Unable to create initial connections of pool.
 java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873)
        at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1710)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226)
...

And after throwing those exeptions, it prints the recordset, but why does it require the root login even if in the context.xml I set dbuser credentials?

I've read searched here in StackOverflow and I've read that I've to check the mysql.user table, if I run:

SELECT user, host, plugin, authentication_string FROM mysql.user;

I get this ("plugin" column has "mysql_native_password" value for every record):

If I try to login into MySQL server from the shell with:

mysql -u root (or dbuser) -p

I can login succesfully.

EDIT: I've found that deleting the second "Resource" node from context.xml (the one made for development environment), the problem disappears, but I still don't uderstand why it happens because in the lookup I choose the prod resource (ctx.lookup("java:/comp/env/prod-jdbc/ds-1")).

What am I missing? Thanks in advance

Answer 1

In your context.xml you have the root user password as root. In your MySQL script, you've set the root user password to veryStrongPwd.

If your question is more about why you get the error even though you don't use the Resource - you're setting the minimum size of the connection pool to 3. So Tomcat will create the connection pool, and try to add 3 connections to it at startup. Perhaps if you set minIdle to 0 it would work how you want it to.

UPDATE

There's another setting - initialSize which defaults to 10. Setting that to 0 should stop Tomcat trying to use that DataSource at startup.

Answer 2

Try to check the permission [IP] from the client that you use to connect. Sometimes need to be specific when you create a new cliente that connect from another terminal when is not a localhost.