连接池连接数据库报Communications link failure错误

Question

本地无错误，发布到服务器上报如下错误：org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure Last packet sent to the server was 0 ms ago.)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at com.ycdx.zwzc.utils.BaseDao.getCon(BaseDao.java:163)
at com.ycdx.zwzc.utils.BaseDao.add(BaseDao.java:55)
at com.ycdx.zwzc.impl.ServiceTypeImpl.queryAllServiceType(ServiceTypeImpl.java:19)
at com.ycdx.zwzc.action.RequestAction.prepare(RequestAction.java:287)
at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:134)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:165)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:179)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:176)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)
at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:488)
at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)
at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:242)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:203)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:108)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:558)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:379)
at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:281)
at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:357)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:1671)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failureLast packet sent to the server was 0 ms ago.
at sun.reflect.GeneratedConstructorAccessor32.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2120)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:723)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
at sun.reflect.GeneratedConstructorAccessor24.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 36 moreGOOGLE，百度若干，有说8小时连接超时的，我的链接肯定不会超时，有说把localhost改为127.0.0.1，也无效，求助大家！

Answer 1

也说有可能是连接未关闭造成的，我的数据库操作是这么写的：import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;public class BaseDao {
    private static Connection con;
    private static PreparedStatement ps;
    private static ResultSet rs;
    private static final String DriverClassName = "com.mysql.jdbc.Driver";
    static {
        try {
            Class.forName(DriverClassName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }    /**
     *封装数据库的查操作
     * 
     * @param c
     *            反射类的对象
     * @param sql
     *            操作的查询SQL语句
     * @param parameters
     *            参数集，调用时无则写null
     * @return list 集合
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws SQLException
     * @throws InstantiationException
     * @throws NamingException 
     */
    @SuppressWarnings("unchecked")
    public static List add(Class c, String sql, List<String> parameters)
            throws IllegalArgumentException, IllegalAccessException,
            InvocationTargetException, InstantiationException, NamingException {
        List<Object> list = new ArrayList<Object>();
        Object o = null;
        try {
            con = getCon();
            ps = con.prepareStatement(sql);
            setParameter(parameters);
            rs = ps.executeQuery();
            // 得到列信息ResultSetMetaDate对象
            ResultSetMetaData rsmd = rs.getMetaData();            // 创建一个String的数组,用来保存所有的列名
            // rsmd.getColumnCount()为当前结果集中的列的总数,所以定义为长度
            String[] cName = new String[rsmd.getColumnCount()];
            for (int i = 0; i < cName.length; i++) {
                cName[i] = rsmd.getColumnName(i + 1);
            }            // 得到反射类中的所有的方法
            Method[] methods = c.getMethods();            while (rs.next()) {
                // 如果结果集得到了数据，则实例一个对象
                o = c.newInstance();
                for (int i = 0; i < cName.length; i++) {
                    for (Method m : methods) {
                        // 把从结果集中得到列名前面加上"set",并把第一位设置为大写，加上后面的，成为一个set的名称，
                        // 然后用反射得到的方法名与之比较，相同的话则激活此方法
                        if (m.getName().equals(
                                "set" + cName[i].substring(0, 1).toUpperCase()
                                        + cName[i].substring(1))) {
                            // 激活得到方法,并设置值
                            //System.out.println(rs.getObject(i + 1));
                            m.invoke(o, rs.getObject(i + 1));
                        }
                    }
                }
                // 添加到list集合中
                list.add(o);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            closeRs(rs);
            closePs(ps);
            closeCon(con);
        }
        
        
        return list;
    }    /**
     * 封装数据库的增，删，改操作
     * 
     * @param sql
     *            操作的SQL语句
     * @param parameters
     *            参数集
     * @return 影响行数
     */
    public static int update(String sql, List<String> parameters) {
        int result = 0;
        try {
            con = getCon();
            ps = con.prepareStatement(sql);
            setParameter(parameters);
            //System.out.println(ps.toString());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeRs(rs);
            closePs(ps);
            closeCon(con);
        }
        return result;
    }    /**
     * 设置参数
     * 
     * @param parameters
     *            参数集
     * @throws SQLException
     *             抛出SQL异常
     */
    private static void setParameter(List<String> parameters)
            throws SQLException {
        if (parameters != null && parameters.size() > 0) {
            for (int i = 0; i < parameters.size(); i++) {
                //System.out.println(parameters.get(i));
                ps.setString(i + 1, parameters.get(i));
            }
        }
    }    /**
     * 得到Connection连接
     * 
     * @return Connection连接
     * @throws SQLException
     * @throws NamingException 
     */
    public static Connection getCon() throws SQLException, NamingException {
        InitialContext ctx = new InitialContext();
        Context envContext = (Context) ctx.lookup("java:comp/env");
        DataSource ds = (DataSource) envContext.lookup("jdbc/zwzc"); 
        con = ds.getConnection();
        return con;
    }    /**
     * 关闭程序中的Connectin 连接
     * 
     * @param con
     *            Connection对象
     */
    public static void closeCon(Connection con) {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }    /**
     * 关闭程序中PreparedStatement对象
     * 
     * @param ps
     *            PreparedStatement对象
     */
    public static void closePs(PreparedStatement ps) {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }    /**
     * 关闭程序中ResultSet 对象
     * 
     * @param rs
     *            ResultSet对象
     */
    public static void closeRs(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}我的tomcat下context.xml文件是这么写的：<Resource name="jdbc/zwzc" 
              auth="Container" 
              type="javax.sql.DataSource" 
              driverClassName="com.mysql.jdbc.Driver" 
              maxActive="20" 
              maxIdle="20" 
              maxWait="10000" 
              username="root" 
              password="t2000vip"  
              url="jdbc:mysql://127.0.0.1:3306/support" 
              validationQuery="SELECT '1'"/>

Answer 2

http://lrq413-126-com.iteye.com/blog/790840

Answer 3

maxIdle和maxActive为何都是20？
maxIdle这里，设为5就足够了。