数据库连接报错求助：Access denied for user 'root'@'localhost'排查

Hey there, let's work through this connection issue together—since you’ve already double-checked your username and password, we can focus on some less obvious but common causes for this error!

First, let’s highlight the core error message we’re dealing with:

javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)"

This tells us MySQL is rejecting the connection from root@localhost even though a password was provided. Here’s how to troubleshoot step by step:

1. Verify MySQL User Host Permissions #

MySQL ties user accounts to specific hosts—root@localhost and root@127.0.0.1 are actually two separate user entries!

• Log into your MySQL server via the command line (using a working connection method), then run this query to check root’s host permissions:

  SELECT user, host FROM mysql.user WHERE user = 'root';
  

• If the result only shows root@localhost, but your code uses 127.0.0.1 in the JDBC URL, this is likely the problem—root@127.0.0.1 might not exist or have no access rights.
• Fix options:
  • Either update your JDBC URL to use localhost instead of 127.0.0.1: jdbc:mysql://localhost/project
  • Or grant permissions to root@127.0.0.1:

    GRANT ALL PRIVILEGES ON project.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'your-actual-password';
    FLUSH PRIVILEGES;
    

2. Check for Password Special Character Escaping #

If your password includes special characters like !@#$%^&*, they might need escaping in the JSP <sql:setDataSource> tag. For example, # can be misinterpreted as a comment or tag delimiter.

• If your real password uses such characters, try replacing them with XML escape codes (e.g., # becomes #) or ensure the password string is fully enclosed in quotes (you already have this, but double-check for typos).

3. Compare Working vs. Non-Working Project Environments #

Since you have another project that connects successfully, spot the differences:

• Port mismatch: Is the new project using a non-default MySQL port? If so, add it to the URL: jdbc:mysql://127.0.0.1:3307/project (replace 3307 with your actual port)
• Driver version conflict: If you’re using MySQL 8.0+, the old com.mysql.jdbc.Driver is deprecated—switch to com.mysql.cj.jdbc.Driver and add a timezone parameter to the URL: jdbc:mysql://127.0.0.1/project?serverTimezone=UTC
• Database existence: Double-check the project database exists in MySQL with:

  SHOW DATABASES;
  

4. Confirm MySQL Service Bind Address #

Rarely, MySQL might be configured to only listen on a specific IP. Check your MySQL config file (my.cnf or my.ini) for the bind-address setting—if it’s set to an IP other than 127.0.0.1 or 0.0.0.0, localhost connections could fail.

Start with these steps—they’re straightforward for new developers, and most "Access Denied" issues for root users boil down to one of these fixes. If you hit a wall, share the output of the SELECT user, host query and we’ll dig deeper!

内容的提问来源于stack exchange，提问作者NMatijevic1