MySQL - Login causes server crash

MySQL - Login causes server crash - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MySQL - Login causes server crash (/showthread.php?tid=358142)

MySQL - Login causes server crash - PCheriyan007 - 09.07.2012

Background
So I recently started converting a dudb based save system to SQLite. That proved to be a bit difficult but I managed to pull through except for one issue... It would never create the database. I then decided that I would convert it to MySQL since there seems to be more support for it. I followed [HiC]TheKiller's tutorial and I have run into a bit of a problem.

The Problem
When I register, everything goes well - account is inserted into the database, everything is updated on disconnect, etc. but when the time comes that I reconnect/log back in, the dialog appears but as soon as I enter the password and press 'Login' the server window closes and then client-side, the server disconnects and is attempting to reconnect.

Code
So here is the code that I think could shed a bit of light on the situation at hand.
OnPlayerConnect - I am led to believe that the problem isn't under this particular callback but here it is for reference.

pawn Код:

public OnPlayerConnect(playerid)
{
    new
        string[128],
        ipstring[24],
        str[128],
        query[256]
    ;

    PlayerInfo[playerid][Money] = -1;
    GetPlayerIp(playerid, ipstring, sizeof(ipstring));
    // MySQL DB Stuffs
    format(query, sizeof(query), "SELECT IP FROM accounts WHERE Username = '%s' LIMIT 1", PlayerName(playerid));
    PlayerName(playerid), ipstring);
    mysql_query(query);
    mysql_store_result();
    
    new rows = mysql_num_rows(); //We get how many rows the query returned.
    if(!rows)
    {
        ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_PASSWORD, "Registration Required", "Welcome to [SArcr]!\nThis server requires that you register an account before playing.", "Register", "Quit");
    }
    if(rows == 1)
    {
        new IP[2][16]; //We create a variable with two IP strings, one for retrieving the mysql field and one for GetPlayerIP.
        mysql_fetch_field_row(IP[0],"IP");
        GetPlayerIp(playerid, IP[1], 16);
        if(strlen(IP[0]) != 0 && !strcmp(IP[0], IP[1], true)) //Checks that the MySQL IP has a value and that they are the same.
        {
            MySQL_Login(playerid);
        }
        else if(!strlen(IP[0]) || strcmp(IP[0], IP[1], true))
        {
            ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "Authentication Required", "Welcome back to [SArcr]!\nBefore playing you must login\nPlease enter your password in the box below.", "Login", "Quit"); //Shows our login dialog :).
            IsRegistered[playerid] = 1; //Sets the registered variable to 1 (Shows that the player is registered).
        }
    }
    mysql_free_result();
    // loads messages/variables/etc.
    return 1;
}

ShowPlayerDialog - I don't believe that the problem is here either but still, pasted for reference.

pawn Код:

public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    if(dialogid == DIALOG_LOGIN)
    {
        if(!response)
        {
            ShowPlayerDialog(playerid, DIALOG_QUIT, DIALOG_STYLE_MSGBOX, "Confirmation Required", "Are you sure you want to quit?", "Yes", "No");
            return 1;
        }
        if(response)
        {
            new
                query[256],
                escapepass[24],
                hashpass[129]
            ;
            
            mysql_real_escape_string(inputtext, escapepass);
            WP_Hash(hashpass, sizeof(hashpass), escapepass);
            format(query, sizeof(query), "SELECT `Username` FROM accounts WHERE Username = '%s' AND Password = '%s'", PlayerName(playerid), hashpass);
            mysql_query(query);
            mysql_store_result();
            new numrows = mysql_num_rows();
            if(numrows == 1) MySQL_Login(playerid);
            if(!numrows)
            {
                ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "Authentication Required", "Please enter your password in the box below.\n{FF0000}ERROR: {A9C4E4}Incorrect password.", "Login", "Quit");
                CheckPasswordAttempts(playerid);
            }
            mysql_free_result();
        }
        return 1;
    }
}

MySQL_Login - the stock created for the purpose of logging in the player/getting variables from the database.

pawn Код:

stock MySQL_Login(playerid)
{
    new
        query[256],
        storage[24]
    ;
    
    format(query, sizeof(query), "SELECT * FROM accounts WHERE Username = '%s'", PlayerName(playerid));
    //We only select the variables that we want to use.
    //We don't need things like the password string or the user string.
    mysql_query(query); //Queries the result
    mysql_store_result(); //Store a result because it's a SELECT statement.
    while(mysql_fetch_row_format(query,"|"))
    {
        mysql_fetch_field_row(storage, "Score"); SetPlayerScore(playerid, strval(storage));
        mysql_fetch_field_row(storage, "Money"); PlayerInfo[playerid][Money] = strval(storage);
        mysql_fetch_field_row(storage, "BankMoney"); PlayerInfo[playerid][BankMoney] = strval(storage);
        mysql_fetch_field_row(storage, "AdminLevel"); PlayerInfo[playerid][AdminLevel] = strval(storage);
        mysql_fetch_field_row(storage, "RegularPlayer"); PlayerInfo[playerid][RegularPlayer] = strval(storage);
        mysql_fetch_field_row(storage, "SavedWantedLevel"); SetPlayerWantedLevel(playerid, strval(storage));
        mysql_fetch_field_row(storage, "SavedJailTime"); JailTime[playerid] = strval(storage);
        mysql_fetch_field_row(storage, "RobRank"); PlayerInfo[playerid][RobRank] = strval(storage);
        mysql_fetch_field_row(storage, "RapeRank"); PlayerInfo[playerid][RapeRank] = strval(storage);
        mysql_fetch_field_row(storage, "DrugDealerRank"); PlayerInfo[playerid][DrugDealerRank] = strval(storage);
        mysql_fetch_field_row(storage, "GunDealerRank"); PlayerInfo[playerid][GunDealerRank] = strval(storage);
        mysql_fetch_field_row(storage, "HitmanRank"); PlayerInfo[playerid][HitmanRank] = strval(storage);
        mysql_fetch_field_row(storage, "MedicRank"); PlayerInfo[playerid][MedicRank] = strval(storage);
        mysql_fetch_field_row(storage, "MechanicRank"); PlayerInfo[playerid][MechanicRank] = strval(storage);
        mysql_fetch_field_row(storage, "BountyHunterRank"); PlayerInfo[playerid][BountyHunterRank] = strval(storage);
        mysql_fetch_field_row(storage, "KidnapperRank"); PlayerInfo[playerid][KidnapperRank] = strval(storage);
        mysql_fetch_field_row(storage, "TerroristRank"); PlayerInfo[playerid][TerroristRank] = strval(storage);
        mysql_fetch_field_row(storage, "HasBriefcase"); PlayerInfo[playerid][HasBriefcase] = strval(storage);
        mysql_fetch_field_row(storage, "BriefcaseMoney"); PlayerInfo[playerid][BriefcaseMoney] = strval(storage);
        mysql_fetch_field_row(storage, "BriefcaseC4"); PlayerInfo[playerid][BriefcaseC4] = strval(storage);
        mysql_fetch_field_row(storage, "BriefcaseWeaponSlot1"); PlayerInfo[playerid][BriefcaseWeaponSlot1] = strval(storage);
        mysql_fetch_field_row(storage, "BriefcaseWeaponAmmoSlot1"); PlayerInfo[playerid][BriefcaseWeaponAmmoSlot1] = strval(storage);
        mysql_fetch_field_row(storage, "CanUseSWAT"); PlayerInfo[playerid][CanUseSWAT] = strval(storage);
        mysql_fetch_field_row(storage, "CanUseFBI"); PlayerInfo[playerid][CanUseFBI] = strval(storage);
        mysql_fetch_field_row(storage, "CanUseArmy"); PlayerInfo[playerid][CanUseArmy] = strval(storage);
        mysql_fetch_field_row(storage, "GrottiOwner"); PlayerInfo[playerid][GrottiOwner] = strval(storage);
        mysql_fetch_field_row(storage, "WangCarsOwner"); PlayerInfo[playerid][WangCarsOwner] = strval(storage);
        mysql_fetch_field_row(storage, "OttosAutosOwner"); PlayerInfo[playerid][OttosAutosOwner] = strval(storage);
        mysql_fetch_field_row(storage, "AutobahnOwner"); PlayerInfo[playerid][AutobahnOwner] = strval(storage);
        mysql_fetch_field_row(storage, "AerobahnOwner"); PlayerInfo[playerid][AerobahnOwner] = strval(storage);
        mysql_fetch_field_row(storage, "HydrobahnOwner"); PlayerInfo[playerid][HydrobahnOwner] = strval(storage);
        mysql_fetch_field_row(storage, "LSDrugHouseOwner"); PlayerInfo[playerid][LSDrugHouseOwner] = strval(storage);
        mysql_fetch_field_row(storage, "SFDrugHouseOwner"); PlayerInfo[playerid][SFDrugHouseOwner] = strval(storage);
        mysql_fetch_field_row(storage, "LVDrugHouseOwner"); PlayerInfo[playerid][LVDrugHouseOwner] = strval(storage);
        mysql_fetch_field_row(storage, "LSAirportOwner"); PlayerInfo[playerid][LSAirportOwner] = strval(storage);
        mysql_fetch_field_row(storage, "SFAirportOwner"); PlayerInfo[playerid][SFAirportOwner] = strval(storage);
        mysql_fetch_field_row(storage, "LVAirportOwner"); PlayerInfo[playerid][LVAirportOwner] = strval(storage);
    }
    mysql_free_result(); //We must always free a stored result
    SendClientMessage(playerid, COLOR_WHITE, "You have successfully logged into {33CCFF}[SArcr]{FFFFFF}.");
    PlayerInfo[playerid][Logged] = 1; //Sets our logged in variable to one
    return 1;
}

Debug.txt - Activated 'Debug' mode in the script to get a better look at things.

Quote:

[22:50:36]

[22:50:36] ---------------------------

[22:50:36] MySQL Debugging activated (07/08/12)

[22:50:36] ---------------------------

[22:50:36]

[22:50:36] >> mysql_connect( )

[22:50:36] CMySQLHandler::CMySQLHandler() - constructor called.

[22:50:36] CMySQLHandler::CMySQLHandler() - Connecting to "localhost" | DB: "sarcr" | Username: "root" ...

[22:50:36] CMySQLHandler::Connect() - Connection was successful.

[22:50:36] CMySQLHandler::Connect() - Auto-Reconnect has been enabled.

[22:50:36] >> mysql_query( Connection handle: 1 )

[22:50:36] CMySQLHandler::Query(CREATE TABLE IF NOT EXISTS accounts(Username VARCHAR(24), Password VARCHAR(129), Score INT(6), Money INT(10), BankMoney INT(10), IP VARCHAR(16), AdminLevel INT(4), RegularPlayer INT(4), SavedWantedLevel INT(3), SavedJailTime INT(4), RobRank INT(6), RapeRank INT(6), DrugDealerRank INT(6), GunDealerRank INT(6), HitmanRank INT(6), MedicRank INT(6), MechanicRank INT(6), BountyHunterRank INT(6), KidnapperRank INT(6), TerroristRank INT(6), HasBriefcase INT(4), BriefcaseMoney INT(7), BriefcaseC4 INT(2), BriefcaseWeaponSlot1 INT(2), BriefcaseWeaponAmmoSlot1 INT(5), CanUseSWAT INT(4), CanUseFBI INT(4), CanUseArmy INT(4), Tazes INT(6), Cuffs INT(6), Arrests INT(6), SWATRank INT(4), FBIRank INT(4), ArmyRank INT(4), GrottiOwner INT(4), WangCarsOwner INT(4), OttosAutosOwner INT(4), AutobahnOwner INT(4), AerobahnOwner INT(4), HydrobahnOwner INT(4), LSDrugHouseOwner INT(4), SFDrugHouseOwner INT(4), LVDrugHouseOwner INT(4))) - Successfully executed.

[22:50:36] >> mysql_query( Connection handle: 1 )

[22:50:36] CMySQLHandler::Query(CREATE TABLE IF NOT EXISTS bans(Username VARCHAR(24), IP VARCHAR(16), BanDate VARCHAR(

, BannedBy VARCHAR(24), BanReason VARCHAR(12

, BanLength VARCHAR(24))) - Successfully executed.

[22:50:36] >> mysql_query( Connection handle: 1 )

[22:50:36] CMySQLHandler::Query(CREATE TABLE IF NOT EXISTS businesses(GrottiOwner VARCHAR(24), WangCarsOwner VARCHAR(24), OttosAutosOwner VARCHAR(24), AutobahnOwner VARCHAR(24), AerobahnOwner VARCHAR(24), HydrobahnOwner VARCHAR(24), LSDrugHouseOwner VARCHAR(24), SFDrugHouseOwner VARCHAR(24), LVDrugHouseOwner VARCHAR(24))) - Successfully executed.

[22:53:17] >> mysql_query( Connection handle: 1 )

[22:53:17] CMySQLHandler::Query(SELECT IP FROM accounts WHERE Username = '[DR]Sc0pe' LIMIT 1) - Successfully executed.

[22:53:17] >> mysql_store_result( Connection handle: 1 )

[22:53:17] CMySQLHandler::StoreResult() - Result was stored.

[22:53:17] >> mysql_num_rows( Connection handle: 1 )

[22:53:17] CMySQLHandler::NumRows() - Returned 1 row(s)

[22:53:17] >> mysql_fetch_field_row( Connection handle: 1 )

server_log.txt - Here are the lines that show when I connect and onwards.

Quote:

[22:53:16] Incoming connection: 127.0.0.1:50224
[22:53:17] [join] [DR]Sc0pe has joined the server (0:127.0.0.1)

So to recap... the problem (in a nutshell) is that when I connect to the server/login, the server crashes.

Notes
- I am very new to MySQL so please, elaborate a little with the solution and try to resist flipping out on me because of a small mistake.

Re: MySQL - Login causes server crash - coole210 - 09.07.2012

Have you tried using crashdetect to get a better look at what's crashing the server?

Re: MySQL - Login causes server crash - PCheriyan007 - 10.07.2012

Actually... I never thought of that (or heard of it) ._. I'll give it a try and see where that gets me.