java.sql.SQLException: 用户’ ‘被拒绝访问 @localhost(未使用密码)

huangapple go评论76阅读模式
英文:

java.sql.SQLException: Access denied for user ''@'localhost' (using password: NO)

问题

我遇到了错误java.sql.SQLException: Access denied for user ' ' @ 'localhost' (using password: NO),当我尝试从Java代码连接到MySQL数据库时。我能找到的所有信息都没有解决这个问题,而且代码非常基本。我以前在项目中使用过它,并且在许多教程中也找到了这样的代码。首先是相关的代码:

//在管理SQL连接的类内部
public SQLTransceiver(String url){
        this.connectionURL = url;
        this.reconnect();
}

public void reconnect(){
        try {
            Connection connection = DriverManager.getConnection(this.connectionURL); //发生错误的地方
            this.currentConnection = connection;
        } catch (SQLException e) {
            e.printStackTrace();
        }
}

//在主类内部,我为了测试目的调用了这个
String s = "jdbc:mysql://localhost:3306/tableName" +
                "?user=user" +
                "&password=thePassword" +
                "&serverTimezone=MET";

DataTransreciever transreciever = new SQLTransceiver(s);

注意:我可以完美地登录MySQL Workbench并在那里执行所有操作,从创建/更改表到插入等等。

整个堆栈跟踪如下:

java.sql.SQLException: Access denied for user ' ' @ 'localhost' (using password: NO)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	...

那么我为了解决这个问题做了什么呢?
它让我想知道为什么它没有说明被拒绝的用户,而是说明了“被拒绝的用户''@'localhost'”。所以我输入了错误的密码,出现了这个错误:

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	...

所以它在某个时候似乎确实检查了用户和密码。如果用户和密码正确,它只会给出第一个错误。

所以我查找了一些信息,得到了以下解决方案,对于这个问题有几次提到(尽管经常会说“使用密码:是”而不是“否”):

https://stackoverflow.com/questions/11922323/java-sql-sqlexception-access-denied-for-user-rootlocalhost-using-password

它说我应该运行以下语句:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

问题是我无法运行它。当我尝试在MySQL Workbench中运行它时,我收到以下错误:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'password' WITH GRANT OPTION' at line 1

另外,我尝试完全删除MySQL并重新安装它,但这没有帮助。

免责声明:
出于隐私原因,已更改密码和用户。在错误方面,使用连接字符串和Properties类调用DriverManager没有任何区别,我也尝试过。抱歉,这可能有点基本,而且英语可能不太好。
我使用的是MySQL Server 8.0.20 Community版本,使用InnoDB引擎。J/Connector和MySQL Notifier也是相同版本。我使用了从https://dev.mysql.com/downloads/mysql/下载的Windows Installer安装的所有内容(包括Workbench)。我使用的是Windows 10。

当我删除“serverTimezone”参数时,我收到以下错误:

java.sql.SQLException: The server time zone value 'Mitteleuropäische Sommerzeit' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specific time zone value if you want to utilize time zone support.
	...

我不知道这是否有帮助,但这是可能的。

非常感谢!

英文:

So I'm getting the error java.sql.SQLException: Access denied for user ''@'localhost' (using password: NO) when I try to connect to MySQL Database from Java code. Everything I was able to find didn't solve the Problem at all and the code is very basic. I used it in projects before and found it like that in many tutorials. So first the relevant code:

//Inside class that manages SQLConnection
public SQLTransceiver(String url){
        this.connectionURL = url;
        this.reconnect();
}

public void reconnect(){
        try {
            Connection connection = DriverManager.getConnection(this.connectionURL); //Where the error occurs
            this.currentConnection = connection;
        } catch (SQLException e) {
            e.printStackTrace();
        }
}

//Inside main class I call this for testing purpose
String s = "jdbc:mysql://localhost:3306/tableName" +
                "?user=user" +
                "&password=thePassword" +
                "&serverTimezone=MET";

DataTransreciever transreciever = new SQLTransceiver(s);

NOTE: I can perfectly log into the DB in MySQL workbench and do everything there from creating/altering tables to inserting and all that stuff.

The whole Stacktrace looks like this:

java.sql.SQLException: Access denied for user ''@'localhost' (using password: NO)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:197)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at main.data.SQLTransceiver.reconnect(SQLTransceiver.java:31)
	at main.data.SQLTransceiver.<init>(SQLTransceiver.java:20)
	at main.ui.toDoPane.ToDoPane.<init>(ToDoPane.java:208)
	at main.ui.Window.start(Window.java:40)
	at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$161(LauncherImpl.java:863)
	at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$174(PlatformImpl.java:326)
	at com.sun.javafx.application.PlatformImpl.lambda$null$172(PlatformImpl.java:295)
	at java.security.AccessController.doPrivileged(Native Method)
	at com.sun.javafx.application.PlatformImpl.lambda$runLater$173(PlatformImpl.java:294)
	at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
	at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
	at com.sun.glass.ui.win.WinApplication.lambda$null$147(WinApplication.java:177)
	at java.lang.Thread.run(Thread.java:748)

So what did I do to fix this:
It made me wonder why it doesn't state which user was denied note it statets "denied for user ''@'localhost'. So I did put in wrong password and this error came:

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:197)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at main.data.SQLTransceiver.reconnect(SQLTransceiver.java:31)
	at main.data.SQLTransceiver.<init>(SQLTransceiver.java:26)
	at main.Main.main(Main.java:29)

So it looks like it at some point does check the user and password. It just gives the first error if user and password are correct.

So I did some lookup and got the solution that is stated for this question a few times (Although often it does state the "Using password: Yes" insted of "No"):

https://stackoverflow.com/questions/11922323/java-sql-sqlexception-access-denied-for-user-rootlocalhost-using-password

It states I should run following statement:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '%password%' WITH GRANT OPTION;

The Problem is I can't run that. When I try to run it in MySQL Workbench I get the following error:

0	5	01:12:32	GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION	Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'password' WITH GRANT OPTION' at line 1	0.000 sec

Additionally I tried to remove MySQL completely and reinstall it. This didn't help.

Disclaimer:
Password and user was changed for privacy reasons.
In terms of errors it doesn't make a difference calling the DriverManager with connection String and Properties class I did try that to.
Sorry for more basic and maybe bad englisch.
I am using MySQL Server 8.0.20 Community with InnoDB Engine. The J/Connector and MySQL Notifier are for the same Version. I installed all (including the workbench) with the Windows Installer downloaded from: https://dev.mysql.com/downloads/mysql/
I am using Windows 10.

When I remove the "serverTimezone" parameter I get the following error:

java.sql.SQLException: The server time zone value 'Mitteleurop�ische Sommerzeit' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:197)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at main.data.SQLTransceiver.reconnect(SQLTransceiver.java:31)
	at main.data.SQLTransceiver.<init>(SQLTransceiver.java:26)
	at main.Main.main(Main.java:28)

I don't know if that helps, but it's possible.

Thanks very much in advance java.sql.SQLException: 用户’ ‘被拒绝访问 @localhost(未使用密码)

答案1

得分: 1

因此,至少解决问题的方法是重新安装MySQL服务器。

不同之处在于,这次我确保为每个组件(服务器、Connector/J、Notifier和Workbench)选择了“新”的密码加密方式(“caching_sha2_password”)。

尽管我不明白这会导致出现这样的错误,或者是否真的是这个错误造成的问题,但它解决了问题。

感谢所有尽力提供帮助的人 java.sql.SQLException: 用户’ ‘被拒绝访问 @localhost(未使用密码)

英文:

So what at least fixed the problem was to reinstall MySQL Server again.

The difference was this time I made sure to choose the 'new' password encryption ("caching_sha2_password") for every component (Server, Connector/J, Notifier and Workbench).

Altough I don't understand how this would cause an error like this or if that even was the error it fixed it.

Thanks for everyone who did there best to help java.sql.SQLException: 用户’ ‘被拒绝访问 @localhost(未使用密码)

答案2

得分: 0

请使用以下连接URL:

String s = "jdbc:mysql://localhost:3306/databasename?user=user&password=password&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";

希望能帮到您,谢谢。

英文:

Please use the following connection url:

 String s = "jdbc:mysql://localhost:3306/databasename?user=user&password=password&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";

I hope it's help you, thanks.

huangapple
  • 本文由 发表于 2020年5月30日 07:35:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/62096031.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定