Java mySQL errors...

Post here your questions about SFS2X. Here we discuss all server-side matters. For client API questions see the dedicated forums.

Moderators: Lapo, Bax

User avatar
Zageron
Posts: 22
Joined: 23 Oct 2011, 02:20
Contact:

Java mySQL errors...

Postby Zageron » 31 Jan 2012, 00:06

I have been messing around this with chunk of code for hours... I've used the DBManager directly, and I've tried this 'lower level' method as well.
Problem: Initially, while using the SFSDBManager, the first attempt at accessing the database would fail. The second, would succeed. The third, fail. Fourth, success. etc... (Watching the admin tool, after the shown event. SFS Restart: 0act, 1 idle con. 1ConAtt: 0,0. 2ConAtt: 0,1. 3ConAtt: 0,0. etc...
So: I attempted to alter it into this form, but now I get
8 31 Jan 2012 00:01:34,434 WARN [pool-1-thread-2] entities.managers.SFSExtensionManager - com.smartfoxserver.v2.exceptions.S FSLoginException: A SQL Error occurred: Operation not allowed after ResultSet closed


Please help me identify the problem. I have searched the forum, and I attempted to use some of the 'solutions' I found, but nothing seemed to make much of a difference. Please try to answer BOTH questions in direct reply to this post.

Note: I censored the db request variables. Please assume that they have nothing to do with the problem.
Note2: This code, when applied using the SFSDBManager, successfully logs a user in 1 out of 2 times. (So, yes, I have made successful connections to the dB.
Note3: This is the error I get every second attempt at logging in, using the dBmanager.
3 31 Jan 2012 00:01:27,918 WARN [pool-1-thread-1] entities.managers.SFSExtensionManager - com.smartfoxserver.v2.exceptions.S FSLoginException: A SQL Error occurred: Communications link failure

Note4: I am more interested in solving the dB connection error, seen directly above, as I am aware of a few reasons as to why the other error is occurring.

Code: Select all

package ffr.chat;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.smartfoxserver.bitswarm.sessions.ISession;
import com.smartfoxserver.v2.entities.data.SFSObject;
import com.smartfoxserver.v2.core.SFSEventParam;
import com.smartfoxserver.v2.core.ISFSEvent;
import com.smartfoxserver.v2.db.IDBManager;
import com.smartfoxserver.v2.exceptions.SFSException;
import com.smartfoxserver.v2.exceptions.SFSLoginException;
import com.smartfoxserver.v2.exceptions.SFSErrorData;
import com.smartfoxserver.v2.exceptions.SFSErrorCode;
import com.smartfoxserver.v2.extensions.BaseServerEventHandler;

public class LoginHandler extends BaseServerEventHandler
{
   
   @Override
   public void handleServerEvent(ISFSEvent event) throws SFSException {
      String clientUsername = (String) event.getParameter(SFSEventParam.LOGIN_NAME);
      Integer clientId = ((SFSObject) event.getParameter(SFSEventParam.LOGIN_IN_DATA)).getInt("uid");
      String clientHash = (String) event.getParameter(SFSEventParam.LOGIN_PASSWORD);
      ISession clientSession = (ISession)event.getParameter(SFSEventParam.SESSION);
      
      String dbUsername = null;
      String dbHash = null;
      Integer dbGroup = null;
      
      if(clientHash == "") {
         SFSErrorData data = new SFSErrorData(SFSErrorCode.LOGIN_BAD_PASSWORD);
         throw new SFSLoginException("You are not logged in!", data);
      }
      
      IDBManager dbManager = getParentExtension().getParentZone().getDBManager();
      Connection connection = null;
      
      try {
         connection = dbManager.getConnection();
         PreparedStatement stmt = connection.prepareStatement("SELECT ******, ******, ******FROM ****** WHERE ******= ?");
         stmt.setInt(1, clientId);
         
         ResultSet result = stmt.executeQuery();
         connection.close();
         
         if(!result.first()) {
            SFSErrorData errData = new SFSErrorData(SFSErrorCode.LOGIN_BAD_USERNAME);
            errData.addParameter(clientUsername);
            throw new SFSLoginException("Bad id for user: " + clientUsername, errData);
         }
         
         dbUsername = result.getString("******");
         dbHash = result.getString("******");
         dbGroup = result.getInt("******");
         stmt.close();
         
         boolean verify = getApi().checkSecurePassword(clientSession, dbHash, clientHash);
   
         if (!verify) {
            SFSErrorData data = new SFSErrorData(SFSErrorCode.LOGIN_BAD_PASSWORD);
            throw new SFSLoginException("Incorrect password.", data);
         } else {
            if(dbUsername.compareTo(clientUsername) != 0) {
               throw new SFSLoginException("Nice try...");
            }
         }
         
         if(****** == 8) {
            SFSErrorData data = new SFSErrorData(SFSErrorCode.LOGIN_BAD_PASSWORD);
            data.addParameter(clientUsername);
            throw new SFSLoginException("You are banned from social interaction. Check the forums to find out how long your ban lasts."  + clientId, data);
         }
      }
      
      catch (SQLException e) {
         try {
            connection.close();
         }
         catch (SQLException e1) {}
         
         SFSErrorData errData = new SFSErrorData(SFSErrorCode.GENERIC_ERROR);
           errData.addParameter("SQL Error: " + e.getMessage());
           throw new SFSLoginException("A SQL Error occurred: " + e.getMessage(), errData);
      }
   }
}
User avatar
Zageron
Posts: 22
Joined: 23 Oct 2011, 02:20
Contact:

Postby Zageron » 31 Jan 2012, 00:40

I have now solved the Result Set problem, and I have solved "MOST of the connection issue. However, if no dB connection attempt is made for 30~ seconds, the dB connection fails.
In Short: Attempt connection, it fails. Any connection amount after this point, as long as they are close together are all successful. If a time of 30 seconds is waited before attempting another connection, it fails.
(utf_string_array) ep: [SQL Error: Communications link failure
The last packet successfully received from the server was 31,469 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.]


dBManager Settings:
Active: True
Driver: org.gjt.mm.mysql.Driver
ConStr: jdbc**************
un/pw: ***
Max # Active: 10
Max # Idle: 10
Exhausted: GROW (I have tried all three)
Block Time: 5000

---------------------------
I do hope that I have supplied you with enough information.

New Code:

Code: Select all

package ffr.chat;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.smartfoxserver.bitswarm.sessions.ISession;
import com.smartfoxserver.v2.entities.data.SFSObject;
import com.smartfoxserver.v2.core.SFSEventParam;
import com.smartfoxserver.v2.core.ISFSEvent;
import com.smartfoxserver.v2.db.IDBManager;
import com.smartfoxserver.v2.exceptions.SFSException;
import com.smartfoxserver.v2.exceptions.SFSLoginException;
import com.smartfoxserver.v2.exceptions.SFSErrorData;
import com.smartfoxserver.v2.exceptions.SFSErrorCode;
import com.smartfoxserver.v2.extensions.BaseServerEventHandler;

public class LoginHandler extends BaseServerEventHandler
{
   
   @Override
   public void handleServerEvent(ISFSEvent event) throws SFSException {
      String clientUsername = (String) event.getParameter(SFSEventParam.LOGIN_NAME);
      Integer clientId = ((SFSObject) event.getParameter(SFSEventParam.LOGIN_IN_DATA)).getInt("uid");
      String clientHash = (String) event.getParameter(SFSEventParam.LOGIN_PASSWORD);
      ISession clientSession = (ISession)event.getParameter(SFSEventParam.SESSION);
      
      String dbUsername = null;
      String dbHash = null;
      Integer dbGroup = null;
      
      if(clientHash == "") {
         SFSErrorData data = new SFSErrorData(SFSErrorCode.LOGIN_BAD_PASSWORD);
         throw new SFSLoginException("You are not logged in!", data);
      }
      
      IDBManager dbManager = getParentExtension().getParentZone().getDBManager();
      Connection connection = null;
      PreparedStatement stmt = null;
      try {
         connection = dbManager.getConnection();
         stmt = connection.prepareStatement("SELECT *****, *****, ***** FROM *****  WHERE ***** = ?");
         stmt.setInt(1, clientId);
         
         ResultSet result = stmt.executeQuery();
         
         if(!result.first()) {
            SFSErrorData errData = new SFSErrorData(SFSErrorCode.LOGIN_BAD_USERNAME);
            errData.addParameter(clientUsername);
            throw new SFSLoginException("Bad id for user: " + clientUsername, errData);
         }
         
         dbUsername = result.getString("*****");
         dbHash = result.getString("*****");
         dbGroup = result.getInt("*****");

         boolean verify = getApi().checkSecurePassword(clientSession, dbHash, clientHash);
   
         if (!verify) {
            SFSErrorData data = new SFSErrorData(SFSErrorCode.LOGIN_BAD_PASSWORD);
            throw new SFSLoginException("Incorrect password.", data);
         } else {
            if(dbUsername.compareTo(clientUsername) != 0) {
               throw new SFSLoginException("Nice try...");
            }
         }
         
         if(***** == 8) {
            SFSErrorData data = new SFSErrorData(SFSErrorCode.LOGIN_BAD_PASSWORD);
            data.addParameter(clientUsername);
            throw new SFSLoginException("You are banned from social interaction. Check the forums to find out how long your ban lasts."  + clientId, data);
         }
      }
      
      catch (SQLException e) {
         SFSErrorData errData = new SFSErrorData(SFSErrorCode.GENERIC_ERROR);
         errData.addParameter("SQL Error: " + e.getMessage());
         throw new SFSLoginException("A SQL Error occurred: " + e.getMessage(), errData);
      }
      
      finally {
         if (stmt != null) {
            try { stmt.close(); }
            catch (SQLException e) { }
         }
         if (connection != null) {
            try { connection.close(); }
            catch (SQLException e) { }
         }
      }
   }
}
User avatar
Lapo
Site Admin
Posts: 22475
Joined: 21 Mar 2005, 09:50
Location: Italy

Postby Lapo » 01 Feb 2012, 11:39

Check your MySQL configuration, you probably have a too sensitive connection timeout setting.
Lapo
--
gotoAndPlay()
...addicted to flash games

Return to “SFS2X Questions”

Who is online

Users browsing this forum: Bing [Bot] and 29 guests