CommunicationsException with Java and MYSQL

CommunicationsException with JAVA and MYSQL

I wrote this post to show I solve this exception. There are a number of reasons why this error may come up and there is not a universal solution for all cases. Perhaps your are not using the same technology as me but you can find some clues here that may help you find a solution.

* I used MariaDb 10.10, Java JDK 1.6 and Linux Ubuntu 12 LTS and a pooled connection configured in Tomcat.

Error: The API makes clear that there is an error in trying to communicate with the data base.

This error can appear in a number of scenarios.

You cannot connect to the database locally #scenario1 .
You cannot connect to the database when trying to connect to a database set up in a different machine but works OK locally #scenario2 .
You can connect to the database but every now and then you get this error #scenario3.
You can connect to the database but every now and then you get this error when using a connection Pool #scenario4.

* Knowing and changing your scenario can help you track down what is causing the problem.

You cannot connect to the database locally

Your database is installed in the same computer your code is but you cannot connect.

The database is down.
You connection URL or port is wrong.
There is some element in between that is blocking the connection (ex. a firewall).
The database is not properly configured and it is rejecting connections.

Steps:

Use any monitor tool to verify that the database is working. ex. top, grep, taskmanager, taskmonitor, etc. or try to access the MySQL console ex. mysql -u root -pmypassword
Check that your database is listening and ready to receive new connections #checkListen.
Telnet your database #telnet.
Use the Code Test Snippet #testsnippet to verify that the problem is not in your code.
Check the MySQL configuration files in which the database is installed #configfiles.

You cannot connect to the database when trying to connect to a database set up in a different machine but works OK locally

Your database is installed in a different machine than the one you are running your code.

A firewall is preventing your machine from getting to the database.
The port is not open in the router or is not properly forwarded.
MySQL is not accepting the connection for the remote IP.

Steps:

Check from the inside. Check that your database is listening are ready to receive new connections #checkListen.
Check from the outside. Telnet your database #telnet.
Check your routing configurations.
Check firewalls and open the database port if needed.
Check the MySQL configuration files in which the database is installed #configfiles.

You can connect to the database but every now and then you get this error

The database timeout is closing your open connections.
There is something your code that is causing the database to shutdown. (ex. too many open connections, not enough memory for the database, etc.)
There is some external element that is shutting down the database.
There is a firewall that is cutting off the connection.

Steps:

Check that the database is not down when this error happens.
Check that your database is listening are ready to receive new connections #checkListen.
Telnet your database #telnet.
Check the timeout #timeout
Use the Code Test Snippet #testsnippet to find out on what frecuency what is causing the problem is acting up. Try to make sure that the code snippet connection is the only code running.
Check the amount of running processes #showactive and also check that you are not exceeding the maximum amount set #configfiles
Check the MySQL logs ex. in linux /var/logs

You can connect to the database but every now and then you get this error when using a connection Pool

The connection URL is not well written or is not the right port.
The pool configuration is out of synchronization with MySQL or the settings don't match, ex. you have specified a higher timeout in the connection pool than in the database, or exceeded the amount of connexions possible. Not all connetion pools query the database to prevent the timeout from happening or don't have the option to set a timeout for the pool and another for its internal timeout.
The pool is buggy.

Steps:

Use the Code Test Snippet #testsnippet to verify that is the pool that is not working properly.
Check the timeout #timeout.
Check your pool configuration API.
Remove the ?autoReconnect=true in the connection URL if it's there.
Use a validation query if your pool has that option ex. validationQuery="SELECT 1"
Check your code for any possible Connection, Statement and ResultSet that have not being closed.

Samples for Tomcat:

Click here to see some working examples with tomcat #tomcat

Check if the database is listening for new connections.

Linux example change port accordingly: sudo netstat -anltp|grep :3306 or sudo nmap -p 3306 localhost
Windows your database port should be among the ones listed: netstat -an

* The port should be listening if not the problems is that the database installation is wrong or is blocked by a firewall.

Test Code Snippet

import java.sql.*;
public class Connect {
    private static final long PAUSE_MILLIS = 70000;
    public static void main(String[] args) {
        Connection conn = null;
        try {
            String userName = "root";
            String password = "mypassword";//change accordingly
            String url = "jdbc:mysql://localhost:3306";//change accordingly
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
            PreparedStatement ps1;
            System.out.println("Database connection established");            
            while(true){            
                try {
                    conn.setAutoCommit(true);
                    ps1 = conn
                            .prepareStatement("SELECT CURRENT_TIMESTAMP() as Date");
                    ResultSet rs = ps1.executeQuery();
                    while (rs.next()) {
                        System.out.println(rs.getString("Date"));
                    }
                    rs.close();
                    ps1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                System.out.println("Wating for next iteration...");
                Thread.sleep(PAUSE_MILLIS);                
            }            
        } catch (Exception e) {
            System.err.println("Cannot connect to database server");
            System.err.println(e.getMessage());
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    System.out.println("Database Connection Terminated");
                } catch (Exception e) {
                }
            }
        }
    }
}

* Change the URL, PORT, PASSWORD, PAUSE_MILLIS accordingly

Telnet Data Base

Using telnet can be very use full to find out if you can reach the database. ex. in console type telnet youdatabaseip port. or telnet youdatabaseip:port if you are using windows. If telnet is not installed in your windows google windows install telnet. search install telnet

Press Intro twice if you are getting this means that your database can receive connections.

Try different combinations such us.

telnet localhost 3306
telnet 127.0.0.1 3306
telnet 192.168.1.10 3306
telnet 82.16.23.22 3306

* Change accordingly to your local network ip, external IP and database port.

* With this you can determine whether it is a network-related problem or not.

MySql Configuration files

Linux MySQL ex: /etc/my.cnf
Windows ex: MySQL: C:\Program Files\mysql\bin\my.ini
Comment out or Change "bind-address" attribute and change it to Ips: bind-address="127.0.0.1" or bind-address="0.0.0.0"
Comment out "skip-networking"

* If you are getting the exception every now than then perhaps adding the following properties will help you.

wait_timeout = number
interactive_timeout = number
connect_timeout = number

MySQL TimeOut

Log to your MySQL Console Linux ex. mysql -u root -pmypassword
From the MySQL Console show the time variables show global variables like '%time%'

* The wait time out is in seconds and will close the the opened connections. You can update in the configuration files #configfiles or ex. for ten minutes set global wait_timeout=600;

Show active connections

In MySQL console show full processlist; or show full processlist\G; or SELECT host,count(host) FROM information_schema.processlist GROUP BY host;

* The amount of open connections and traffic can shutdown the database.

Info shows the MySQL command being run
use kill id_process to remove processes.

Pool Tomcat examples

Pool with validation query, 2 maxIdle connections

        <Resource name="jdbc/mysql" auth="Container" type="javax.sql.DataSource"
        driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/blog"
        username="root" password="apple" maxActive="10" maxIdle="2" validationQuery="SELECT 1"/>

Pool with no idle connections that removes all the unclosed connections

        <Resource name="jdbc/mysql" auth="Container" type="javax.sql.DataSource"
        driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/blog"
        username="root" password="apple" maxActive="10" maxIdle="2" maxWait="45" 
        maxAge="45" removeAbandoned="true" removeAbandonedTimeout="45"/>

JSP For Testing Purposes Only.

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.naming.NamingException" %>
<%@ page import="javax.sql.DataSource" %>    
<%
Context context = new InitialContext();
DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/mysql");
if(ds!=null){    
    Connection con = ds.getConnection();
    con.setAutoCommit(true);
    PreparedStatement ps1 = con
            .prepareStatement("SELECT CURRENT_TIMESTAMP() as Date");
    ResultSet rs = ps1.executeQuery();
    while (rs.next()) {
        out.println("Date From DataBase " +rs.getString("Date"));
    }
    rs.close();
    ps1.close();
    con.close();
}
%>