Server/ Client Socket Programming and mysql database

Question

I am working on client/server socket programming. Android Client and Java Server on my laptop. I want to send several images through socket from Android device to a Java server which saves each image in a MySQL database as blob. I close the client socket for each image transfer and reconnect again for the next image transfer.

My first image transfer works fine and is saved in the MySQL database but when I try to reconnect socket to server and transfer a different image I get Connection reset.

    java.sql.SQLException: Error reading from InputStream java.net.SocketException
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at com.mysql.jdbc.PreparedStatement.readblock(PreparedStatement.java:3099)
at com.mysql.jdbc.PreparedStatement.streamToBytes(PreparedStatement.java:5106)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2575)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2366)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2350)
at Uploadpic.run(Uploadpic.java:113)
at java.lang.Thread.run(Unknown Source)
    Caused by: java.net.SocketException: Connection reset
at java.net.SocketInputStream.read(Unknown Source)
at java.net.SocketInputStream.read(Unknown Source)
at com.mysql.jdbc.PreparedStatement.readblock(PreparedStatement.java:3097)
... 7 more










 public class Uploadpic extends Thread  {
private Socket socket1 =null;

//public  Client user = new Client();
public Uploadpic(Socket socket1)
{
    this.socket1 = socket1;
}

public void run() {
    int imagenum = 0;
    String mes = null;
    BufferedReader buff;
    int imagesize = 0;
    InputStream is = null;
    try {
        buff = new BufferedReader(newInputStreamReader(socket1.getInputStream()));
        while ((mes = buff.readLine())!= null)
        {
            if(!mes.equals(null))
            break;
        }
        System.out.println("image size is =" + mes);

    } catch (IOException e) {

        e.printStackTrace();
    }

    imagesize = Integer.parseInt(mes);

    try {

         is = socket1.getInputStream();

    } catch (IOException e) {

        e.printStackTrace();
    }

    String connectionUrl = "jdbc:mysql://localhost:3306/users";
    String dbUser = "root";
    String dbPwd = "daniere";
    boolean check = false ;
    Connection conn = null;
    ResultSet rs;    
    PreparedStatement ps = null;
    PreparedStatement stmt = null;
    byte [] mybytearray  = new byte [imagesize];
    int rowCount = -1;

  String INSERT_PICTURE = 
                    "INSERT INTO USERPICS (id_PIC,user_id,picname)values (?, ?, ?)";

    try
    {
        conn = DriverManager.getConnection(connectionUrl, dbUser, dbPwd);
        try {
            while(check == false)


            conn.setAutoCommit(true);

        ps = (PreparedStatement)conn.prepareStatement(INSERT_PICTURE);

            System.out.println("     ");
            ps.setInt(1,imagenum);
            ps.setInt(2, DataBase.user.getClientId());
            ps.setBinaryStream(3, is, (int) mybytearray.length);

              **ERROR HAPPENS HERE**int s = ps.executeUpdate();

            if(s>0) {
                System.out.println("Uploaded successfully !");
                }
                else {
                System.out.println("unsucessfull to upload image.");
                }
        } finally {

            try {
                ps.close();
                ps = null;
                is.close();
                //socket1.close();
                socket2.close();

            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();

            }
        }

        if (conn != null)
        {

            conn.close();
            conn = null;

        }
    }
    catch (SQLException sqle) 
    {
        sqle.printStackTrace();
    }

}

   }

Answer 1

I guess this is due to multi-threading issues. I guess that you spawn one thread for inserting each picture, because of Uploadpic extends Thread (btw it's recommended to implement Runnable instead of extending Thread), and also I suspect that

DriverManager.getConnection(connectionUrl, dbUser, dbPwd)

is returning same connection if one is already open for this URL.

If this is the case, a possible race condition can appear where two threads are using the same Connection object referenced by conn, and one closes it while the other is still working with it.

Solution: No need to open/close the connection for each picture, that's useless work: open DB connection when you start the server and close it when you exit.