Bug #79976 ERROR 1045 (28000): Access denied for user 'root'@'localhost'
Submitted: 14 Jan 2016 3:49 Modified: 24 Mar 2016 3:23
Reporter: bing xu Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.6.19 OS:CentOS
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: mysql 1045 connection access spring springmvc mybatis

[14 Jan 2016 3:49] bing xu
Description:
mysql will return 1045 error after running for some time then any client can not connect mysql.But if I logged in mysql in SSL terminal(like Xshell), then I can operate normally like executing sql.
1.this issue occurs often but not in same period(like 8 hours), sometimes 3 hours, sometims 4 hours after restart.
2.we have to restart mysql then it works.
3.we just use mysql for developing, so no many connections.
4.we set connect pool for tomcat to ping mysql to avoid 8 hours disconnect of mysql, details see below.
-----------------------------------------------
jdbc.driver=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://124.207.225.126:3306/qdw?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&allowMultiQueries=true
jdbc.username=root
jdbc.password=root

#connection pool settings
jdbc.pool.maxActive=190
jdbc.pool.maxIdle=20
jdbc.pool.minIdle=10
jdbc.pool.maxWait=10000

jdbc.pool.testWhileIdle=true

jdbc.pool.testOnBorrow=true

jdbc.pool.testOnReturn=false

jdbc.pool.validationQuery=SELECT 1

jdbc.pool.validationInterval=30000

jdbc.pool.timeBetweenEvictionRunsMillis=30000

jdbc.pool.minEvictableIdleTimeMillis=30000
-----------------------------------------------
//mybatis config xml
<!-- 数据源配置, 使用Tomcat JDBC连接池 -->
    <bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
        <!-- Connection Info -->
        <property name="driverClassName" value="${jdbc.driver}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />

        <!-- Connection Pooling Info -->
        <property name="maxActive" value="${jdbc.pool.maxActive}" />
        <property name="maxIdle" value="${jdbc.pool.maxIdle}" />
        <property name="minIdle" value="${jdbc.pool.minIdle}" />
        <property name="defaultAutoCommit" value="false" />
        <property name="maxWait" value="${jdbc.pool.maxWait}" />
        
        <property name="testWhileIdle" value="${jdbc.pool.testWhileIdle}" />
        <property name="testOnBorrow" value="${jdbc.pool.testOnBorrow}" />
        <property name="testOnReturn" value="${jdbc.pool.testOnReturn}" />
        <property name="validationQuery" value="${jdbc.pool.validationQuery}" />
        <property name="validationInterval" value="${jdbc.pool.validationInterval}" />
        <property name="timeBetweenEvictionRunsMillis" value="${jdbc.pool.timeBetweenEvictionRunsMillis}" />
        <property name="minEvictableIdleTimeMillis" value="${jdbc.pool.minEvictableIdleTimeMillis}" />
    </bean>
-----------------------------------------------
5.enviroment:
CentOS linux v2.6.32
Tomcat v7.0.64
Mysql v5.6.19
Spring v4.0.5
Mybatis v3.2.5

How to repeat:
1.after restart, we just access mysql normally via program or client
2.then for some time radomly mysql will not be available.

Suggested fix:
1.impossible it's setup issue because the system already is running some weeks.
2.impossible it's spring or springmvc or mybatis issue because we used them for other projects successfully
3.impossible it's tomcat issue because when I output the mysql log I can see the ping works (like "select 1" surly was executed periodly)
4.so we doubt there are wrong somewhere like:
A.is it possible that app could cause this issue? e.g. app execute wrong sql or app error like null point could make mysql server died?
but we can see mysql in process list
B.maybe we setup mysql wrongly?
5.we know how to solve this problem like reset password but we want to know how to avoid this issue. 
6.there are no error logs in mysql error log or tomcat log files.
[16 Jan 2016 6:09] bing xu
I found in my.cnf there is:
skip-grant-tables

maybe it's the root cause?
I delete it and not reproduced, but found another issue:
"Packet for query is too large (8742 > 1024). You can change this value on the server by setting the 'max_allowed_packet' variable."
I reset max_allowed_packet by command but it's changed back to 1024 after one day. no error, no exception, memory and disk space is enough.

why max_allowed_packet will be reset after some time? and how to keep one value I set?
[16 Jan 2016 6:10] bing xu
by the way, why "skip-grant-tablesa" should not be set in my.cnf?
[21 Mar 2016 22:08] MySQL Verification Team
The skip-grant-tables option means that all privileges are ignored which leaves the service open to many forms of attack. This also places some restrictions on what can/can't be done. In any normal setup the skip-grant-tables should only be used to recover a system where the password is no longer known.

As for the other issue, you are trying to send blobs or large text fields and the packet size is not large enough. Increase it using:

max_allowed_packet=10M
[24 Mar 2016 3:23] bing xu
solved. some unknown account from web changed my configuration and it's not reproduecd after I changed login account.

regarding skip-grant-table, Jonathon is rihgt and thank you!