SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help

Reply
 
Thread Tools Display Modes
Old 29/01/2015, 04:59 PM   #1
Glenn332
Big Clucker
 
Join Date: Jun 2013
Location: NL- DUTCHLANDS
Posts: 116
Reputation: 12
Default MySQL connection problem.

Code:
[18:29:42]  ** MySQL Debugging enabled (01/29/15)
[18:29:42]  
[18:30:03] >> mysql_format( Connection handle: 0 )
[18:30:03] >> mysql_format() - Invalid connection handle. (You set: 0, Highest connection handle ID is 1).
[18:30:03] >> mysql_query_callback( Connection handle: 0 )
[18:30:03] >> mysql_query_callback() - Invalid connection handle. (You set: 0, Highest connection handle ID is 1).
[18:30:03] >> mysql_store_result( Connection handle: 1 )
[18:30:03] CMySQLHandler::StoreResult() - No data to store.
[18:30:03] >> mysql_num_rows( Connection handle: 1 )
[18:30:03] CMySQLHandler::NumRows() - You cannot call this function now. (Reason: Dead Connection)
[18:30:03] >> mysql_free_result( Connection handle: 1 )
[18:30:03] CMySQLHandler::FreeResult() - The result is already empty.
[18:30:08] >> mysql_real_escape_string( Connection handle: 1 )
[18:30:08] CMySQLHandler::EscapeString(gdsgds); - Escaped 6 characters to gdsgds.
[18:30:08] >> mysql_format( Connection handle: 0 )
[18:30:08] >> mysql_format() - Invalid connection handle. (You set: 0, Highest connection handle ID is 1).
[18:30:08] >> mysql_store_result( Connection handle: 1 )
[18:30:08] CMySQLHandler::StoreResult() - No data to store.
[18:30:08] >> mysql_num_rows( Connection handle: 1 )
[18:30:08] CMySQLHandler::NumRows() - You cannot call this function now. (Reason: Dead Connection)
[18:30:08] >> mysql_format( Connection handle: 0 )
[18:30:08] >> mysql_format() - Invalid connection handle. (You set: 0, Highest connection handle ID is 1).
[18:30:08] >> mysql_store_result( Connection handle: 1 )
[18:30:08] CMySQLHandler::StoreResult() - No data to store.
[18:30:08] >> mysql_fetch_row_format( Connection handle: 1 )
[18:30:08] CMySQLHandler::FetchRow() - You cannot call this function now. (Reason: Empty Result)
[18:30:08] >> mysql_free_result( Connection handle: 1 )
[18:30:08] CMySQLHandler::FreeResult() - The result is already empty.
[18:30:08] >> mysql_free_result( Connection handle: 1 )
[18:30:08] CMySQLHandler::FreeResult() - The result is already empty.
[18:30:14] >> mysql_query_callback( Connection handle: 0 )
[18:30:14] >> mysql_query_callback() - Invalid connection handle. (You set: 0, Highest connection handle ID is 1).
Since I changed my script it does not load up my user stats at all from the MySQL does anyone know how I could fix this, The script cant call back anything on the MySQL.
Glenn332 is offline   Reply With Quote
Old 29/01/2015, 05:39 PM   #2
PowerPC603
High-roller
 
Join Date: Nov 2010
Location: Belgium
Posts: 1,733
Reputation: 238
Default Re: MySQL connection problem.

You're not connecting to the database before sending queries.
This is a log from my own server, it clearly shows "mysql_connect", along with all data required to connect and which connectionhandle is returned.
Your first entry already says the connection handle is "0", which means you don't have a connection to the database.

Code:
[19:16:12] [DEBUG] mysql_connect - host: "127.0.0.1", user: "PowerPC603", database: "ppctrucking", password: "****", port: 3306, autoreconnect: true
[19:16:12] [DEBUG] CMySQLHandle::Create - creating new connection..
[19:16:12] [DEBUG] CMySQLHandle::CMySQLHandle - constructor called
[19:16:12] [DEBUG] CMySQLHandle::Create - connection created with ID = 1
[19:16:12] [DEBUG] CMySQLConnection::Connect - connection was successful
[19:16:12] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[19:16:12] [DEBUG] CMySQLConnection::Connect - connection was successful
[19:16:12] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[19:16:12] [DEBUG] mysql_errno - connection: 1
[19:16:12] [DEBUG] mysql_query - connection: 1, query: "SELECT * FROM serversettings", use_cache: true
[19:16:12] [DEBUG] CMySQLQuery::CMySQLQuery() - constructor called
[19:16:12] [DEBUG] CMySQLQuery::Execute[()] - starting query execution
[19:16:12] [DEBUG] CMySQLQuery::Execute[()] - query was successful
[19:16:12] [DEBUG] CMySQLResult::CMySQLResult() - constructor called
[19:16:12] [DEBUG] CMySQLHandle::SaveActiveResult - cache saved with ID = 1
[19:16:12] [DEBUG] CMySQLQuery::~CMySQLQuery() - deconstructor called
[19:16:12] [DEBUG] cache_get_field_content - row: 0, field_name: "GameModeName", connection: 1, max_len: 50
[19:16:12] [DEBUG] CMySQLResult::GetRowDataByName - row: '0', field: "GameModeName", data: "PowerPC603's Trucking Server V2"
[19:16:12] [DEBUG] cache_get_field_content - row: 0, field_name: "GameModeMode", connection: 1, max_len: 50
[19:16:12] [DEBUG] CMySQLResult::GetRowDataByName - row: '0', field: "GameModeMode", data: "Trucking Mode V2"
[19:16:12] [DEBUG] cache_get_field_content_int - row: 0, field_name: "AutoLogin", connection: 1
[19:16:12] [DEBUG] CMySQLResult::GetRowDataByName - row: '0', field: "AutoLogin", data: "0"
[19:16:12] [DEBUG] cache_get_field_content_int - row: 0, field_name: "MaxLoginTries", connection: 1
[19:16:12] [DEBUG] CMySQLResult::GetRowDataByName - row: '0', field: "MaxLoginTries", data: "3"
[19:16:12] [DEBUG] cache_get_field_content_int - row: 0, field_name: "PasswordMinSize", connection: 1
[19:16:12] [DEBUG] CMySQLResult::GetRowDataByName - row: '0', field: "PasswordMinSize", data: "4"
[19:16:12] [DEBUG] cache_delete - cache_id: 1, connection: 1
[19:16:12] [DEBUG] CMySQLResult::~CMySQLResult() - deconstructor called
[19:16:12] [DEBUG] CMySQLHandle::DeleteSavedResult - result deleted
__________________
Gamemode: PPC_Trucking
Extras-file (for PPC_Trucking): /delproperty, /evict, /propertyid, /porthouse, /portbus, /properties
------------------------------------------------------------------------------------
PPC-Trucking-V2 (home-hosted, only open on occasion for testing purposes): ppctruckingv2.no-ip.info:7777
Youtube channel: https://www.youtube.com/channel/UC-4...Mpglma8f0Ld-YQ
------------------------------------------------------------------------------------
Filterscripts: PPC_Speedometer - PPC_Housing - PPC_Business
PowerPC603 is offline   Reply With Quote
Old 29/01/2015, 05:42 PM   #3
Glenn332
Big Clucker
 
Join Date: Jun 2013
Location: NL- DUTCHLANDS
Posts: 116
Reputation: 12
Default Re: MySQL connection problem.

Code:
public OnGameModeInit()
{
    g_mysql_handle = mysql_connect(mysql_host,mysql_user,mysql_database,mysql_password);
	mysql_debug(true);
	SetGameModeText("Blank Script");
	AddPlayerClass(0, 1958.3783, 1343.1572, 15.3746, 269.1425, 0, 0, 0, 0, 0, 0);
	return 1;
}
Isnt this the connecting part ?
Glenn332 is offline   Reply With Quote
Old 29/01/2015, 05:47 PM   #4
PowerPC603
High-roller
 
Join Date: Nov 2010
Location: Belgium
Posts: 1,733
Reputation: 238
Default Re: MySQL connection problem.

Yeah, that's the part.
Now the reason why it didn't show up in your log, it that you're starting the log after connecting.

Put "mysql_debug(true);" as first line (before mysql_connect) and see what the log tells you.
Something must be wrong with connecting, otherwise your connection handle would be "1".

It may be some data is faulty (wrong username or password probably).
__________________
Gamemode: PPC_Trucking
Extras-file (for PPC_Trucking): /delproperty, /evict, /propertyid, /porthouse, /portbus, /properties
------------------------------------------------------------------------------------
PPC-Trucking-V2 (home-hosted, only open on occasion for testing purposes): ppctruckingv2.no-ip.info:7777
Youtube channel: https://www.youtube.com/channel/UC-4...Mpglma8f0Ld-YQ
------------------------------------------------------------------------------------
Filterscripts: PPC_Speedometer - PPC_Housing - PPC_Business
PowerPC603 is offline   Reply With Quote
Old 29/01/2015, 05:54 PM   #5
Glenn332
Big Clucker
 
Join Date: Jun 2013
Location: NL- DUTCHLANDS
Posts: 116
Reputation: 12
Default Re: MySQL connection problem.

Code:
[19:32:41]  
[19:32:41]  ** MySQL Debugging enabled (01/29/15)
[19:32:41]  
[19:32:41] >> mysql_connect(localhost, root, test, ******) on port 3306
[19:32:41] CMySQLHandler::CMySQLHandler() - constructor called.
[19:32:41] CMySQLHandler::CMySQLHandler() - Connecting to "localhost" | DB: "test" | Username: "root"
[19:32:41] CMySQLHandler::Connect() - Connection was successful.
[19:32:41] CMySQLHandler::Connect() - Auto-Reconnect has been enabled.
There seems to be no problem connecting, Let me try and login and ill give you what happens then.

Code:
[19:34:08] >> mysql_format( Connection handle: 0 )
[19:34:08] >> mysql_format() - Invalid connection handle. (You set: 0, Highest connection handle ID is 1).
[19:34:08] >> mysql_store_result( Connection handle: 1 )
[19:34:08] CMySQLHandler::StoreResult() - No data to store.
[19:34:08] >> mysql_num_rows( Connection handle: 1 )
[19:34:08] CMySQLHandler::NumRows() - You cannot call this function now. (Reason: Dead Connection)
[19:34:08] >> mysql_free_result( Connection handle: 1 )
[19:34:08] CMySQLHandler::FreeResult() - The result is already empty.
[19:34:14] >> mysql_real_escape_string( Connection handle: 1 )
[19:34:14] CMySQLHandler::EscapeString(reqrqr); - Escaped 6 characters to reqrqr.
[19:34:14] >> mysql_format( Connection handle: 0 )
[19:34:14] >> mysql_format() - Invalid connection handle. (You set: 0, Highest connection handle ID is 1).
[19:34:14] >> mysql_store_result( Connection handle: 1 )
[19:34:14] CMySQLHandler::StoreResult() - No data to store.
[19:34:14] >> mysql_num_rows( Connection handle: 1 )
[19:34:14] CMySQLHandler::NumRows() - You cannot call this function now. (Reason: Dead Connection)
[19:34:14] >> mysql_format( Connection handle: 0 )
[19:34:14] >> mysql_format() - Invalid connection handle. (You set: 0, Highest connection handle ID is 1).
[19:34:14] >> mysql_store_result( Connection handle: 1 )
[19:34:14] CMySQLHandler::StoreResult() - No data to store.
[19:34:14] >> mysql_fetch_row_format( Connection handle: 1 )
[19:34:14] CMySQLHandler::FetchRow() - You cannot call this function now. (Reason: Empty Result)
[19:34:14] >> mysql_free_result( Connection handle: 1 )
[19:34:14] CMySQLHandler::FreeResult() - The result is already empty.
[19:34:14] >> mysql_free_result( Connection handle: 1 )
[19:34:14] CMySQLHandler::FreeResult() - The result is already empty.
[19:34:21] >> mysql_query_callback( Connection handle: 0 )
[19:34:21] >> mysql_query_callback() - Invalid connection handle. (You set: 0, Highest connection handle ID is 1).
This is what happens after I login, It tries to read if my account exists, But I automatically go to the login window whilst I dont even have an account yet.
Glenn332 is offline   Reply With Quote
Old 29/01/2015, 05:58 PM   #6
Smally
Huge Clucker
 
Smally's Avatar
 
Join Date: Jul 2011
Posts: 400
Reputation: 71
Default Re: MySQL connection problem.

Two Things:
  • Is OnGameModeInit towards the top of your script? It should be one of the first functions.
  • Post some actual code to help us, looking at logs is all good, but to resolve it some code is needed.
__________________

Head of Web Development
If I have assisted you in anyway, please kindly click the '' button to the left of this text.
Smally is offline   Reply With Quote
Old 29/01/2015, 06:00 PM   #7
PowerPC603
High-roller
 
Join Date: Nov 2010
Location: Belgium
Posts: 1,733
Reputation: 238
Default Re: MySQL connection problem.

Can you show all the code which takes you from OnPlayerConnect to the login dialog?
It seems you might be using another variable as connection handle in mysql_format (and everywhere else), as the connection handle is "0" instead of "1".
__________________
Gamemode: PPC_Trucking
Extras-file (for PPC_Trucking): /delproperty, /evict, /propertyid, /porthouse, /portbus, /properties
------------------------------------------------------------------------------------
PPC-Trucking-V2 (home-hosted, only open on occasion for testing purposes): ppctruckingv2.no-ip.info:7777
Youtube channel: https://www.youtube.com/channel/UC-4...Mpglma8f0Ld-YQ
------------------------------------------------------------------------------------
Filterscripts: PPC_Speedometer - PPC_Housing - PPC_Business
PowerPC603 is offline   Reply With Quote
Old 29/01/2015, 06:02 PM   #8
Glenn332
Big Clucker
 
Join Date: Jun 2013
Location: NL- DUTCHLANDS
Posts: 116
Reputation: 12
Default Re: MySQL connection problem.

Code:
public OnPlayerConnect(playerid)
{
	new string[164];
	new Query[256];
	GetPlayerName(playerid,PlayerInfo[playerid][Username],24);
	mysql_format(mysql, Query, Query,"SELECT `Username` FROM `Users` WHERE `Username` = %s", PlayerInfo[playerid][Username]);
	mysql_store_result();
	if(mysql_num_rows() != 0)//if number of rows is different from 0 then continue
	{
	format(string,sizeof(string),"Hey, %s! \nYour account is registered.\nPlease enter the password to log in!",PlayerInfo[playerid][Username]);
	ShowPlayerDialog(playerid,0,DIALOG_STYLE_INPUT,"Log in",string,"Login","");
	}
	else
	{
	format(string,sizeof(string),"Hey, %s! \nYour account is not registered. \nPlease register to continue!",PlayerInfo[playerid][Username]);
	ShowPlayerDialog(playerid,1,DIALOG_STYLE_INPUT,"Register",string,"Register","");
	}
	mysql_free_result();
	GivePlayerMoney(playerid, 5000);
	return 1;
}

Code:
public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[]) {
    if(dialogid == 0) {
		if(strlen(inputtext) == 0) {
			return 1;
		} else {
		LoginPlayer(playerid,inputtext);
		}
	}
    if(dialogid == 1) {
		if(strlen(inputtext) == 0)	{
		ShowPlayerDialog(playerid,1,DIALOG_STYLE_INPUT,"Register - Enter your password","You are about to register a new account! \nPlease choose the password for it! \n","Register!","");
		} else {
		new EscapedText[60];
		new Query[256];
		new connhandle = playerConnectHandle[playerid];
 		if(connhandle == -1) {
  			connhandle = g_mysql_handle;
 		}
		mysql_real_escape_string(inputtext, EscapedText);
		format(Query, sizeof(Query), "INSERT INTO `Users` (Username,Password,Money) VALUES ('%s','%s,'0')",PlayerInfo[playerid][Username],EscapedText);
		
        mysql_function_query(connhandle, Query, true, "", "d",playerid);
		SendClientMessage(playerid,green,"You have been successfully registered!");
		GivePlayerMoney(playerid,5000);
		SetPlayerScore(playerid,100);
		}
	}
	return 1;
}
^^First one is login second dialog is register.

Code:
stock LoginPlayer(playerid,const password[]) {
	new EscapedText[60];
	new Query[256];
	mysql_real_escape_string(password, EscapedText);
	mysql_format(mysql, Query, Query,"SELECT * FROM `Users` WHERE `Username` = '%s' AND `Password` = '%s'",PlayerInfo[playerid][Username],EscapedText);
	mysql_store_result();
	if(mysql_num_rows() != 0) {
		SendClientMessage(playerid,green,"You have been logged in!");
		LoadStats(playerid);
	} else {
		SendClientMessage(playerid,red,"Wrong password!");
		Kick(playerid);
	}
	mysql_free_result();
	return 1;
}

stock LoadStats(playerid) {
	new Query[256];
	mysql_format(mysql, Query, Query,"SELECT * FROM `Users` WHERE `Username` = '%s' ", PlayerInfo[playerid][Username]);
	mysql_store_result();
	mysql_fetch_row_format(Query, "|");
	sscanf(Query, "e<p<|>s[24]s[23]i>", PlayerInfo[playerid]);
	mysql_free_result();
	GivePlayerMoney(playerid,PlayerInfo[playerid][Money]);
	return 1;
}
*** Just to add I grabbed this code from the internet... YES. I am trying to learn mysql, at first I noticed that the code was fucked up, but I edited it. so yeah there could be something wrong, But no errors or warnings when compiled. ***
Glenn332 is offline   Reply With Quote
Old 29/01/2015, 06:08 PM   #9
PowerPC603
High-roller
 
Join Date: Nov 2010
Location: Belgium
Posts: 1,733
Reputation: 238
Default Re: MySQL connection problem.

Just as expected:
When connecting, you're saving the connection handle "1" in variable "g_mysql_handle".
In many mysql-statements, you're using "mysql" as connection handle, which will be 0.
You're also using "connHandle" as connection handle during OnDialogResponse, which you also take from "playerConnectHandle[playerid]", which is weird as the script only needs 1 connection to the database, not different handles per player.

You should ALWAYS use the same variable, you can expect problems such as this when you use 4 different variables as connection handle to one database.

The solution is simple: replace all your connection handles with "g_mysql_handle" in every mysql-command and it should work.
__________________
Gamemode: PPC_Trucking
Extras-file (for PPC_Trucking): /delproperty, /evict, /propertyid, /porthouse, /portbus, /properties
------------------------------------------------------------------------------------
PPC-Trucking-V2 (home-hosted, only open on occasion for testing purposes): ppctruckingv2.no-ip.info:7777
Youtube channel: https://www.youtube.com/channel/UC-4...Mpglma8f0Ld-YQ
------------------------------------------------------------------------------------
Filterscripts: PPC_Speedometer - PPC_Housing - PPC_Business
PowerPC603 is offline   Reply With Quote
Old 29/01/2015, 06:11 PM   #10
Glenn332
Big Clucker
 
Join Date: Jun 2013
Location: NL- DUTCHLANDS
Posts: 116
Reputation: 12
Default Re: MySQL connection problem.

Alright so I should change the mysql to g_mysql_handle, What else should I do?


** Just tried it with changing mysql to g_mysql_handle and now I get these errors in the mysql log, They are different but still when I try to spawn I get the login screen and I can just put in whatever I want without having an account and all.

*** Ive also changed the conhandle and everything but still it says that it has nothing to store in the mysql log and I still have the issue that I get the login screen when I first login, I need to get the register screen.

Code:
[19:52:16]  
[19:52:16]  ** MySQL Debugging enabled (01/29/15)
[19:52:16]  
[19:52:16] >> mysql_connect(localhost, root, test, ******) on port 3306
[19:52:16] CMySQLHandler::CMySQLHandler() - constructor called.
[19:52:16] CMySQLHandler::CMySQLHandler() - Connecting to "localhost" | DB: "test" | Username: "root"
[19:52:16] CMySQLHandler::Connect() - Connection was successful.
[19:52:16] CMySQLHandler::Connect() - Auto-Reconnect has been enabled.
[19:52:42] >> mysql_format( Connection handle: 1 )
[19:52:42] >> mysql_store_result( Connection handle: 1 )
[19:52:42] CMySQLHandler::StoreResult() - No data to store.
[19:52:42] >> mysql_num_rows( Connection handle: 1 )
[19:52:42] CMySQLHandler::NumRows() - You cannot call this function now. (Reason: Dead Connection)
[19:52:42] >> mysql_free_result( Connection handle: 1 )
[19:52:42] CMySQLHandler::FreeResult() - The result is already empty.
[19:52:48] >> mysql_real_escape_string( Connection handle: 1 )
[19:52:48] CMySQLHandler::EscapeString(asdads); - Escaped 6 characters to asdads.
[19:52:48] >> mysql_format( Connection handle: 1 )
[19:52:48] >> mysql_store_result( Connection handle: 1 )
[19:52:48] CMySQLHandler::StoreResult() - No data to store.
[19:52:48] >> mysql_num_rows( Connection handle: 1 )
[19:52:48] CMySQLHandler::NumRows() - You cannot call this function now. (Reason: Dead Connection)
[19:52:48] >> mysql_format( Connection handle: 1 )
[19:52:48] >> mysql_store_result( Connection handle: 1 )
[19:52:48] CMySQLHandler::StoreResult() - No data to store.
[19:52:48] >> mysql_fetch_row_format( Connection handle: 1 )
[19:52:48] CMySQLHandler::FetchRow() - You cannot call this function now. (Reason: Empty Result)
[19:52:48] >> mysql_free_result( Connection handle: 1 )
[19:52:48] CMySQLHandler::FreeResult() - The result is already empty.
[19:52:48] >> mysql_free_result( Connection handle: 1 )
[19:52:48] CMySQLHandler::FreeResult() - The result is already empty.
[19:52:55] >> mysql_query_callback( Connection handle: 0 )
[19:52:55] >> mysql_query_callback() - Invalid connection handle. (You set: 0, Highest connection handle ID is 1).
Glenn332 is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MYSQl Connection problem ShadowDark Scripting Help 2 22/04/2014 01:23 AM
MYSQL problem - no connection RaydeX Scripting Help 2 23/10/2011 07:16 PM
Mysql connection problem. Vvolk Help Archive 2 07/05/2011 08:34 AM
MYSQL connection problem vection Help Archive 1 12/10/2010 05:50 PM
MYSQL connection problem - please take a look at that Ed2ka49 Help Archive 4 04/08/2010 09:09 PM


All times are GMT. The time now is 02:27 PM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.