首页 > 代码库 > Access denied for user 'Lzj'@'localhost' 问题的解决
Access denied for user 'Lzj'@'localhost' 问题的解决
使用SpringMvc + Mybatis + Mysql搭建的架构，调试时出现了以下错误：
HTTP Status 500 - Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Access denied for user ‘Lzj‘@‘localhost‘ (using password: YES) type Exception report message Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Access denied for user ‘Lzj‘@‘localhost‘ (using password: YES) description The server encountered an internal error that prevented it from fulfilling this request. exception org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Access denied for user ‘Lzj‘@‘localhost‘ (using password: YES) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982) org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) javax.servlet.http.HttpServlet.service(HttpServlet.java:622) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) javax.servlet.http.HttpServlet.service(HttpServlet.java:729) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) root cause org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Access denied for user ‘Lzj‘@‘localhost‘ (using password: YES) org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:289) org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373) org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:447) org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:277) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) com.sun.proxy.$Proxy15.list(Unknown Source) com.lzj.controller.HelloController.hello(HelloController.java:25) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:498) org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:116) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) javax.servlet.http.HttpServlet.service(HttpServlet.java:622) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) javax.servlet.http.HttpServlet.service(HttpServlet.java:729) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) root cause java.sql.SQLException: Access denied for user ‘Lzj‘@‘localhost‘ (using password: YES) com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873) com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1710) com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226) com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2205) com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2236) com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2035) com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790) com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) java.lang.reflect.Constructor.newInstance(Constructor.java:423) com.mysql.jdbc.Util.handleNewInstance(Util.java:425) com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400) com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330) java.sql.DriverManager.getConnection(DriverManager.java:664) java.sql.DriverManager.getConnection(DriverManager.java:208) org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:153) org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:144) org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:196) org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:159) org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:246) org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373) org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:447) org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:277) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) com.sun.proxy.$Proxy15.list(Unknown Source) com.lzj.controller.HelloController.hello(HelloController.java:25) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) java.lang.reflect.Method.invoke(Method.java:498) org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:116) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) javax.servlet.http.HttpServlet.service(HttpServlet.java:622) org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) javax.servlet.http.HttpServlet.service(HttpServlet.java:729) org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
网上的一些说法是数据库没有访问权限，当然这是一种情况。
起始还有另一种可能，就是框架没有拿到datasource正确的username，这个和配置有关：
我原先的applicationContext.xml文件配置是这样的：
<!-- 加载properties --> <context:property-placeholder location="classpath:datasource.properties" /> <!-- 配置datasource --> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" > <property name="driverClassName" value="${driverClassName}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </bean>
datasource.properties文件代码如下：
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
username=root
password=test
这样框架在构造AbstractDriverBasedDataSource实例时，拿到的username始终是当前机器名。
我也没有找到构造username的代码！
最后将datasource.properties文件的username改为jdbc.username,即：
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
jdbc.username=root
password=test
applicationContext.xml文件的dataSource的bean配置如下：
<!-- 配置datasource --> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" > <property name="driverClassName" value="${driverClassName}"/> <property name="url" value="${url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${password}"/> </bean>
这样就解决了，每次都拿错username的问题。具体是什么原因，我也不知道。还需要在研读源码。
各位如有知道原因的，请留言。
后续如有答案，我会补上。
Access denied for user 'Lzj'@'localhost' 问题的解决
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。