MySQL Issue - Printable Version

MySQL Issue - 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 Issue (/showthread.php?tid=592472)

MySQL Issue - ChromeAmazing - 25.10.2015

Hello,

I've just started working on a script again after a long break from sa-mp, and it appears an error occurred while I was working on the MySQL part of things.

Код:

[09:49:53] [DEBUG] CMySQLHandle::Create - creating new connection..
[09:49:53] [DEBUG] CMySQLHandle::CMySQLHandle - constructor called
[09:49:53] [DEBUG] CMySQLHandle::Create - connection created (id: 1)
[09:49:53] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[09:49:53] [DEBUG] CMySQLConnection::Connect - connection was successful
[09:49:53] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[09:49:53] [DEBUG] mysql_errno - connection: 1
[09:49:53] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[09:49:53] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[09:49:53] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[09:49:53] [DEBUG] CMySQLConnection::Connect - connection was successful
[09:49:53] [DEBUG] CMySQLConnection::Connect - connection was successful
bled
[09:49:53] [DEBUG] CMySQLConnection::Connect - connection was successful
[09:49:53] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[09:49:53] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[09:52:20] [DEBUG] mysql_format - connection: 1, len: 128, format: "SELECT 'Password', 'PlayerID' FROM 'samp_accounts' WHERE 'Username'='%e' LIMIT 1"
[09:52:20] [DEBUG] mysql_tquery - connection: 1, query: "SELECT 'Password', 'PlayerID' FROM 'samp_accounts' WHERE 'Userna", callback: "OnAccountCheck", format: "i"
[09:52:20] [DEBUG] CMySQLQuery::Execute[OnAccountCheck] - starting query execution
[09:52:20] [ERROR] CMySQLQuery::Execute[OnAccountCheck] - (error #1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''samp_accounts' WHERE 'Username'='R' LIMIT 1' at line 1
[09:52:20] [DEBUG] CMySQLQuery::Execute[OnAccountCheck] - error will be triggered in OnQueryError
[09:53:23] [DEBUG] mysql_format - connection: 1, len: 2048, format: "UPDATE 'samp_accounts' SET 'MaskOn'='%d', 'Admin'='%d', 'Helper'='%d', 'Money'='%d', 'Level'='%d', 'Age'='%d', 'Gender'='%d', 'O..."
[09:53:23] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE 'samp_accounts' SET 'MaskOn'='0', 'Admin'='0', 'Helper'='", callback: "(null)", format: "(null)"
[09:53:23] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[09:53:23] [ERROR] CMySQLQuery::Execute[] - (error #1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''samp_accounts' SET 'MaskOn'='0', 'Admin'='0', 'Helper'='0', 'Money'='0', 'Level' at line 1
[09:53:23] [DEBUG] CMySQLQuery::Execute[] - error will be triggered in OnQueryError
[09:54:46] [DEBUG] mysql_connect - host: "localhost", user: "root", database: "roleplay", password: "****", port: 3306, autoreconnect: true, pool_size: 2
[09:54:46] [DEBUG] CMySQLHandle::Create - creating new connection..
[09:54:46] [DEBUG] CMySQLHandle::CMySQLHandle - constructor called
[09:54:46] [DEBUG] CMySQLHandle::Create - connection created (id: 1)
[09:54:46] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[09:54:46] [DEBUG] CMySQLConnection::Connect - connection was successful
[09:54:46] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[09:54:46] [DEBUG] mysql_errno - connection: 1
[09:54:46] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[09:54:46] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[09:54:46] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[09:54:46] [DEBUG] CMySQLConnection::Connect - connection was successful
[09:54:46] [DEBUG] CMySQLConnection::Connect - connection was successful
[09:54:46] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[09:54:46] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[10:07:56] [DEBUG] mysql_connect - host: "localhost", user: "root", database: "roleplay", password: "****", port: 3306, autoreconnect: true, pool_size: 2
[10:07:56] [DEBUG] CMySQLHandle::Create - creating new connection..
[10:07:56] [DEBUG] CMySQLHandle::CMySQLHandle - constructor called
[10:07:56] [DEBUG] CMySQLHandle::Create - connection created (id: 1)
[10:07:56] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[10:07:56] [DEBUG] CMySQLConnection::Connect - connection was successful
[10:07:56] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[10:07:56] [DEBUG] mysql_errno - connection: 1
[10:07:56] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[10:07:56] [DEBUG] CMySQLConnection::Connect - connection was successful
[10:07:56] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[10:07:56] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[10:07:56] [DEBUG] CMySQLConnection::Connect - connection was successful
[10:07:56] [DEBUG] CMySQLConnection::Connect - connection was successful
[10:07:56] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[10:07:56] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[10:08:23] [DEBUG] mysql_format - connection: 1, len: 128, format: "SELECT 'Password', 'PlayerID' FROM 'samp_accounts' WHERE 'Username'='%e' LIMIT 1"
[10:08:23] [DEBUG] mysql_tquery - connection: 1, query: "SELECT 'Password', 'PlayerID' FROM 'samp_accounts' WHERE 'Userna", callback: "OnAccountCheck", format: "i"
[10:08:23] [DEBUG] CMySQLQuery::Execute[OnAccountCheck] - starting query execution
[10:08:23] [ERROR] CMySQLQuery::Execute[OnAccountCheck] - (error #1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''samp_accounts' WHERE 'Username'='R' LIMIT 1' at line 1
[10:08:23] [DEBUG] CMySQLQuery::Execute[OnAccountCheck] - error will be triggered in OnQueryError
[10:08:35] [DEBUG] mysql_format - connection: 1, len: 2048, format: "UPDATE 'samp_accounts' SET 'MaskOn'='%d', 'Admin'='%d', 'Helper'='%d', 'Money'='%d', 'Level'='%d', 'Age'='%d', 'Gender'='%d', 'O..."
[10:08:35] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE 'samp_accounts' SET 'MaskOn'='0', 'Admin'='0', 'Helper'='", callback: "(null)", format: "(null)"
[10:08:35] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[10:08:35] [ERROR] CMySQLQuery::Execute[] - (error #1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''samp_accounts' SET 'MaskOn'='0', 'Admin'='0', 'Helper'='0', 'Money'='0', 'Level' at line 1
[10:08:35] [DEBUG] CMySQLQuery::Execute[] - error will be triggered in OnQueryError

Thank you for whoever helps me!

Re: MySQL Issue - AndySedeyn - 25.10.2015

Column/table/database names are not supposed to be in quotation marks but in between grave accents or no accents at all. Only their value is supposed to be in single quotation marks and only if that value is a string or character.

Example:

PHP код:



SELECT Password, ID FROM accounts WHERE Username = 'Name';

PHP код:



SELECT `Password`, `ID` FROM `accounts` WHERE `Username` = 'Name';

Re: MySQL Issue - ChromeAmazing - 25.10.2015

Quote:

Originally Posted by AndySedeyn

Column/table/database names are not supposed to be in quotation marks but in between grave accents or no accents at all. Only their value is supposed to be in single quotation marks and only if that value is a string or character.

Example:

PHP код:



SELECT Password, ID FROM accounts WHERE Username = 'Name';

PHP код:



SELECT `Password`, `ID` FROM `accounts` WHERE `Username` = 'Name';

I see now, it would look something similar to this?

PHP код:



mysql_format(mysql, query, sizeof(query), "SELECT Password, PlayerID FROM samp_accounts WHERE Username = %e LIMIT 1", playername);

Re: MySQL Issue - AndySedeyn - 25.10.2015

The '%e' specifier is a string specifier and should be between quotation marks. The rest is correct.

Re: MySQL Issue - ChromeAmazing - 25.10.2015

Quote:

Originally Posted by AndySedeyn

The '%e' specifier is a string specifier and should be between quotation marks. The rest is correct.

I see my mistake now, thank you.

Re: MySQL Issue - ChromeAmazing - 25.10.2015

I appear to be getting another error.

Код:

[12:59:55] [DEBUG] mysql_format - connection: 1, len: 512, format: "INSERT INTO `samp_accounts` (`Username`,`Password`, `PlayerIP`, `Admin`, `Helper`) VALUES ('%e', '%e', '%e', 0, 0, 0, 0)"
[12:59:55] [DEBUG] mysql_tquery - connection: 1, query: "INSERT INTO `samp_accounts` (`Username`,`Password`, `PlayerIP`, ", callback: "OnAccountRegister", format: "i"
[12:59:55] [DEBUG] CMySQLQuery::Execute[OnAccountRegister] - starting query execution
[12:59:55] [ERROR] CMySQLQuery::Execute[OnAccountRegister] - (error #1136) Column count doesn't match value count at row 1
[12:59:55] [DEBUG] CMySQLQuery::Execute[OnAccountRegister] - error will be triggered in OnQueryError

Re: MySQL Issue - AndySedeyn - 25.10.2015

You are setting the value for column 1, column 2, column 3, column 4 and column 5, but you seem to be specifying a lot more values.

PHP код:



INSERT INTO samp_accounts (column_count) VALUES (value_count);

The column_count must be equal to the value_count.

Re: MySQL Issue - ChromeAmazing - 25.10.2015

Alright, this will probably be the last problem.
I am able to register and input my information, but when I exit the game it saves into the MySQL (PHPMyAdmin), then when I try to login again it makes me re-register.

Код:

[13:27:35] [DEBUG] CMySQLHandle::Create - creating new connection..
[13:27:35] [DEBUG] CMySQLHandle::CMySQLHandle - constructor called
[13:27:35] [DEBUG] CMySQLHandle::Create - connection created (id: 1)
[13:27:35] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[13:27:35] [DEBUG] CMySQLConnection::Connect - connection was successful
[13:27:35] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[13:27:35] [DEBUG] mysql_errno - connection: 1
[13:27:35] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[13:27:35] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[13:27:35] [DEBUG] CMySQLConnection::Connect - connection was successful
[13:27:35] [DEBUG] CMySQLConnection::Connect - connection was successful
[13:27:35] [DEBUG] CMySQLConnection::Connect - connection was successful
[13:27:35] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[13:27:35] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[13:31:29] [DEBUG] mysql_format - connection: 1, len: 128, format: "SELECT `Password`, `PlayerID` FROM `samp_accounts` WHERE `Username` = '%e' LIMIT 1"
[13:31:29] [DEBUG] mysql_tquery - connection: 1, query: "SELECT `Password`, `PlayerID` FROM `samp_accounts` WHERE `Userna", callback: "OnAccountCheck", format: "i"
[13:31:29] [DEBUG] CMySQLQuery::Execute[OnAccountCheck] - starting query execution
[13:31:29] [DEBUG] CMySQLQuery::Execute[OnAccountCheck] - query was successfully executed within 0.501 milliseconds
[13:31:29] [DEBUG] CMySQLResult::CMySQLResult() - constructor called
[13:31:29] [DEBUG] Calling callback "OnAccountCheck"..
[13:31:29] [DEBUG] cache_get_data - connection: 1
[13:31:29] [DEBUG] CMySQLResult::~CMySQLResult() - deconstructor called
[13:31:39] [DEBUG] mysql_format - connection: 1, len: 512, format: "INSERT INTO `samp_accounts` (`Username`,`Password`, `PlayerIP`, `Admin`, `Helper`) VALUES ('%e', '%e', '%e', 0, 0)"
[13:31:39] [DEBUG] mysql_tquery - connection: 1, query: "INSERT INTO `samp_accounts` (`Username`,`Password`, `PlayerIP`, ", callback: "OnAccountRegister", format: "i"
[13:31:39] [DEBUG] CMySQLQuery::Execute[OnAccountRegister] - starting query execution
[13:31:39] [DEBUG] CMySQLQuery::Execute[OnAccountRegister] - query was successfully executed within 35.989 milliseconds
[13:31:39] [DEBUG] CMySQLResult::CMySQLResult() - constructor called
[13:31:39] [DEBUG] Calling callback "OnAccountRegister"..
[13:31:39] [DEBUG] cache_insert_id - connection: 1
[13:31:39] [DEBUG] CMySQLResult::~CMySQLResult() - deconstructor called
[13:32:24] [DEBUG] mysql_format - connection: 1, len: 2048, format: "UPDATE `samp_accounts` SET `MaskOn`='%d', `Admin`='%d', `Helper`='%d', `Money`='%d', `Level`='%d', `Age`='%d', `Gender`='%d', `O..."
[13:32:24] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE `samp_accounts` SET `MaskOn`='0', `Admin`='0', `Helper`='", callback: "(null)", format: "(null)"
[13:32:24] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[13:32:24] [DEBUG] CMySQLQuery::Execute[] - query was successfully executed within 0.399 milliseconds
[13:32:24] [DEBUG] CMySQLQuery::Execute[] - no callback specified, skipping result saving

This is under OnPlayerConnect

PHP код:



    mysql_format(mysql, query, sizeof(query), "SELECT `Password`, `PlayerID` FROM `samp_accounts` WHERE `Username` = '%e' LIMIT 1", playername);

mysql_tquery(mysql, query, "OnAccountCheck", "i", playerid);

This is under OnAccountCheck

PHP код:



public OnAccountCheck(playerid)

{

    new rows, fields, string[380];

    cache_get_data(rows, fields, mysql);

    if(rows)

    {

        cache_get_field_content(0, "Password", PlayerInfo[playerid][pPassword], mysql, 129);

        PlayerInfo[playerid][pPlayerID] = cache_get_field_content_int(0, "ID");

        format(string, sizeof(string), "{FFFFFF}Welcome Back, %s\nPlease type in your password used when registerting.\n\nYou have 60 seconds to login or you will be kicked.", RemoveUnderScore(playerid));

        ShowPlayerDialog(playerid, LoginDialog, DIALOG_STYLE_INPUT,"{6688FF}Authentication Area",string, "Login", "Quit");

        SetPlayerCameraPos(playerid, 1813.1046,-1449.2799,107.7444);

         SetPlayerCameraLookAt(playerid, 1649.3676,-1306.5536,132.4665);

    }

    else

    {

        format(string, sizeof(string), "{FFFFFF}Welcome, %s\nYou have entered the regisration area, please type your desired password to begin.\n\nRemember! Never give out your password out to anyone.", RemoveUnderScore(playerid));

        ShowPlayerDialog(playerid, RegisterDialog, DIALOG_STYLE_INPUT,"{6688FF}Registration Area",string,"Continue", "Quit");

        SetPlayerCameraPos(playerid, 363.3605,173.5211,1008.3828);

        SetPlayerCameraLookAt(playerid, 363.3605,173.5211,1008.3828);

    }

    return 1;

}

Re: MySQL Issue - ChromeAmazing - 25.10.2015

Question still stands!

Re: MySQL Issue - ChromeAmazing - 28.10.2015

Question... still stands!!!