Mysql: Syntax Error when using GRANT ALL PRIVILEGES

Question

I'm desperately trying to access a Mysql Database 8.0.18 (Test Environment) with user root via from my java webapp (bot running on same host). However, i'm always getting the following Stacktrace:

SQLException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:?]
    at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:?]
    at jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:?]
    at java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500) ~[?:?]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:481) ~[?:?]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2096) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2021) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:?]
    at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:?]
    at jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:?]
    at java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500) ~[?:?]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:481) ~[?:?]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at java.sql.DriverManager.getConnection(DriverManager.java:677) ~[java.sql:?]
    at java.sql.DriverManager.getConnection(DriverManager.java:189) ~[java.sql:?]
    at de.aedsicad.vermunterlagenportal.auftragsverwaltung.DBconnection.openDbConnection(DBconnection.java:108) [onlineadapterservice.jar:6_5_0_0_RC16]
    at de.aedsicad.vermunterlagenportal.auftragsverwaltung.AuftragsverwaltungDBOperation.insertNewAuftrag(AuftragsverwaltungDBOperation.java:48) [onlineadapterservice.jar:6_5_0_0_RC16]
    at de.aedsicad.vermunterlagenportal.portaladapter.webservice.PortaladapterWebservice.executeJob(PortaladapterWebservice.java:44) [onlineadapterservice.jar:6_5_0_0_RC16]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
    at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.lang.reflect.Method.invoke(Method.java:567) ~[?:?]
    at com.sun.xml.ws.util.Trampoline.invoke(MethodUtil.java:82) [?:?]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
    at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.lang.reflect.Method.invoke(Method.java:567) ~[?:?]
    at com.sun.xml.ws.util.MethodUtil.invoke(MethodUtil.java:107) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.api.server.MethodUtil.invoke(MethodUtil.java:64) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.api.server.InstanceResolver$1.invoke(InstanceResolver.java:250) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.server.InvokerTube$2.invoke(InvokerTube.java:149) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.server.sei.SEIInvokerTube.processRequest(SEIInvokerTube.java:88) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.api.pipe.Fiber.__doRun(Fiber.java:1136) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:1050) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:1019) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:877) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.server.WSEndpointImpl$2.process(WSEndpointImpl.java:419) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.transport.http.HttpAdapter$HttpToolkit.handle(HttpAdapter.java:868) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.transport.http.HttpAdapter.handle(HttpAdapter.java:422) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.transport.http.servlet.ServletAdapter.invokeAsync(ServletAdapter.java:225) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.transport.http.servlet.WSServletDelegate.doGet(WSServletDelegate.java:161) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.transport.http.servlet.WSServletDelegate.doPost(WSServletDelegate.java:197) [jaxws-rt-2.3.0.jar:2.3.0]
    at com.sun.xml.ws.transport.http.servlet.WSServlet.doPost(WSServlet.java:81) [jaxws-rt-2.3.0.jar:2.3.0]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:660) [servlet-api.jar:?]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) [servlet-api.jar:?]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [catalina.jar:9.0.27]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.27]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) [tomcat-websocket.jar:9.0.27]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.27]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.27]
    at de.aedsicad.filters.EncodingFilter.doFilter(EncodingFilter.java:46) [onlineadapterservice.jar:6_5_0_0_RC16]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.27]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.27]
    at org.apache.logging.log4j.web.Log4jServletFilter.doFilter(Log4jServletFilter.java:71) [log4j-web-2.11.0.jar:2.11.0]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.27]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.27]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) [catalina.jar:9.0.27]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [catalina.jar:9.0.27]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:526) [catalina.jar:9.0.27]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [catalina.jar:9.0.27]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [catalina.jar:9.0.27]
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:678) [catalina.jar:9.0.27]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [catalina.jar:9.0.27]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [catalina.jar:9.0.27]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) [tomcat-coyote.jar:9.0.27]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-coyote.jar:9.0.27]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861) [tomcat-coyote.jar:9.0.27]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1579) [tomcat-coyote.jar:9.0.27]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-coyote.jar:9.0.27]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:9.0.27]
    at java.lang.Thread.run(Thread.java:830) [?:?]
Caused by: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1714) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1224) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2190) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2037) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    ... 71 more

So i tried granting access rights to root user like this:

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

However, MySQLWorkbench gives me this error as an answer:

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 'mysql' WITH GRANT OPTION' at line 1

Whats wrong with my statement? I was searching all over the internet and to me it looks like all the examples and tutorials i could find?

The `root` or as it is sometimes called the `SuperUser` ALREADY has the rights to do absolutely everything. — RiggsFolly, Dec 09 '19 at 13:38
MySQL 8 has changed the syntax for doing this anyway. See the manual if all else fails — RiggsFolly, Dec 09 '19 at 13:38
I assume you did the obvious things? 1) check MySQL is running. 2) check MySQL is running on port 3306? 3) Check that firewall is not getting in your way?? — RiggsFolly, Dec 09 '19 at 13:41
Surely this `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 'mysql' WITH GRANT OPTION'` made you think you should check the syntax — RiggsFolly, Dec 09 '19 at 13:42

score 2 · Accepted Answer · answered Dec 09 '19 at 13:51

2

The IDENTIFIED BY clause is for creating a user and it assigns the password. GRANT statement is not creating a user.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

should work.

Although it seems more likely that your application is configured with the wrong mysql password for root. Make sure the password is correct, or change the password for root.

See here: MySQL change root user password

answered Dec 09 '19 at 13:51

NorthernDev

339
1
6

ok, thanks. It indeed seemed like i used the wrong root password and thought the problem was somewhere else. However, i still cannot connect from a remote host with Tools DBVisualizer because i still get the following error: `com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException SQL State: 08001`. Do you know what's causing that problem? – flixe Dec 09 '19 at 14:04
Ok, i was finally able to solve my problem by doing what is described here: [link](https://stackoverflow.com/questions/50387952/how-to-resolve-unable-to-load-authentication-plugin-caching-sha2-password-issu) – flixe Dec 09 '19 at 15:01

Mysql: Syntax Error when using GRANT ALL PRIVILEGES

1 Answers1