• 0

Invalid SQL Errors.


Question

I have a script on my forum where members can send a text to a number (from their mobile phone) they receive a password, they enter it into my script which 'upgrades' them. However it's randomly stopped working and i can't seem to find a fix... i receive this email every time a member tries to enter the code:

Database error in vBulletin 3.8.4:

Invalid SQL:

UPDATE session
SET lastactivity = 1286331448, inforum = 0, inthread = 0, incalendar = 0, badlocation = 0, useragent = 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/533.16 (KHTML, like Gecko) Version/5.0 Safari/533.16'
WHERE sessionhash = '30dbc038a03d3361569fe927a9cc021f';

MySQL Error   : MySQL server has gone away
Error Number  : 2006
Request Date  : Wednesday, October 6th 2010 @ 03:17:28 AM
Error Date    : Wednesday, October 6th 2010 @ 03:20:37 AM
Script        : /sms_signup.php
Referrer      : /sms_signup.php
IP Address    : ..........
Username      :....................
Classname 	: vB_Database
MySQL Version :

The only thing i can think of regarding the database is i recently disabled pconnect for security reasons, and started using connect. I changed this in includes/class_core.php - maybe the script relies on it?

Link to comment
Share on other sites

10 answers to this question

Recommended Posts

  • 0

I would say you're right, sort of. :)

pconnect keeps the connection open, or 'persistent'. Does your script establish a connection prior to attempting the table update?

I'm unfamiliar with the the Vb database class, but it *should* be checking for an active connection before allowing you to perform query.

Link to comment
Share on other sites

  • 0

I'm not sure. it does some pre-caching?


// ####################### SET PHP ENVIRONMENT ###########################
error_reporting(E_ALL & ~E_NOTICE);

// #################### DEFINE IMPORTANT CONSTANTS #######################
define('NO_REGISTER_GLOBALS', 1);
define('THIS_SCRIPT', 'mobilebill_sms');

// ################### PRE-CACHE TEMPLATES AND DATA ######################
// get special phrase groups
$phrasegroups = array(

);

// get special data templates from the datastore
$specialtemplates = array(

);

// pre-cache templates used by all actions
$globaltemplates = array(
    'mobilebill_sms',
);

// pre-cache templates used by specific actions
$actiontemplates = array(

);

// ######################### REQUIRE BACK-END ############################
require_once('./global.php');
require(DIR . '/includes/functions_mobilebill_sms.php');


// #######################################################################
// ######################## START MAIN SCRIPT ############################
// #######################################################################


// ### SELECT MOBILEBILL DATA FROM DB ######################################
$mb_query = $db->query_first("SELECT * FROM ".TABLE_PREFIX."mobilebill_sms");
$mb_inactive_text = $mb_query['active_text'];
$mb_vip_text = $mb_query['vip_text'];
$mb_unregistered_text = $mb_query['unregistered_text'];
$mb_page_title = $mb_query['page_title'];
$mb_signup_title = $mb_query['signup_title'];
$mb_signup_text = $mb_query['signup_text'];
$mb_Upgrade_UserGroup = $mb_query['upgrade_usergroupid'];
$mb_Upgrade_UserTitle = $mb_query['upgrade_usertitle'];
$mb_keyword = $mb_query['mobilebill_keyword'];
$mb_shortcode = $mb_query['mobilebill_shortcode'];
$mb_billing_amount = $mb_query['billing_amount'];
$mb_billing_freq = $mb_query['billing_freq'];
$mb_campaign_mode = $mb_query['mb_campaign_mode'];
$mb_donation_title = $mb_query['mb_donation_title'];
$mb_donation_text = $mb_query['mb_donation_text'];


// ### DISPLAY DEBUG INFO IF ENABLED #####################################
if($mb_query['debug']=="1")
{
	$mb_debug = true;
}

// ### MOBILEBILL INACTIVE? DISPLAY UNAVAILABLE MESSAGE ##################
if($mb_query['active']=="0")
{
	$mb_inactive = true;
}

// ### USER ALREADY VIP?, DISPLAY ERROR ##################################
if($vbulletin->userinfo['usergroupid']=="$mb_query[upgrade_usergroupid]")
{
	$mb_vip = true;
}

// ### LOGGED OUT OR UNREGISTERED? DISPLAY LOGIN/SIGNUP BOX ##############
if($vbulletin->userinfo['usergroupid']=="$mb_query[unregistered_usergroupid]")
{
	$mb_unreg = true;
}


// ### ASSIGN OTHER VARIBLES #############################################
$mb_forumUserID = $vbulletin->userinfo['userid'];
$mb_userName = ucwords($vbulletin->userinfo['username']);
$mb_userTitle = $vbulletin->userinfo['usertitle'];
$mb_phpself = $_SERVER['PHP_SELF'];
$mb_sms_signup_date = date("M-j-y H:i");


// ### SET BREADCRUMB TRAIL ##############################################
$navbits = array();
$navbits[$parent] = $mb_query[navbar_breadcrumb];
$navbits = construct_navbits($navbits);
eval('$navbar = "' . fetch_template('navbar') . '";');


// ### ASSIGN POST VALUES FROM SMS SIGNUP PAGE ############################
$smsPassword = $_POST['smsPassword'];
$ForumUserID = $_POST['ForumUserID'];
$bulk = $_POST['bulk'];
$transID = "MBSMS".$vboptions[bbtitle]."-".rand();



if($mb_campaign_mode=="subscription")
{
	// ### SUBSCRIPTION + UPGRADE ###########################################
	if($smsPassword)
	{

		$server = "https://xml1.mobilebill.net/mobilebill/verify";
		$xmlString = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><verifycode><username>".$mb_query[mobilebill_user]."</username><password>".$mb_query[mobilebill_pass]."</password><memberusername>".$ForumUserID."</memberusername><memberpassword>".$smsPassword."</memberpassword><usergroupid>".$vbulletin->userinfo['usergroupid']."</usergroupid><reference>".$transID."</reference><bulk>".$bulk."</bulk></verifycode>";

		// ### SUBMIT XML TO SMS GATEWAY AND RETURN RESPONSE ##################
		$forum2sms = postXMLtoURL($server,$xmlString);

		// ### SEND XML 2 ARRAY ###############################################
		//$smsArray = simplexml_load_string($forum2sms); //PHP5
		$smsArray = GetXMLTree($forum2sms);

		// ### ASSIGN VALUES FROM ARRAY #######################################
		$mb_status = $smsArray['verifycoderesponse']['status'];
		$forumUserID = $smsArray['verifycoderesponse']['memberusername'];

		// ### PRINT XML STRING ### DEBUG #####################################
		//echo "<pre>";
		//print_r($smsArray);
		//echo "</pre>";

		// ### MOBILEBILL STATUS ##############################################
		if($mb_status=="active" && $vbulletin->userinfo['usergroupid']!="6")
		{
			$db->query_first("UPDATE ".TABLE_PREFIX."user SET `usergroupid` = '".$mb_Upgrade_UserGroup."', `usertitle` = '".$mb_Upgrade_UserTitle."', `sms_signup_date` = '".$mb_sms_signup_date."' WHERE `userid`='".$ForumUserID."'");
			$mb_vip = true;
			$headers  = "MIME-Version: 1.0\r\n";
			$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
			$headers .= "From: Mobilebill <sms@mobilebill.net>\r\n";
			$headers .= "Bcc: info@mobilebill.net" . "\r\n";
			$message = 'Username: <a href="'.$vbulletin->options['bburl'].'/admincp/mobilebill_sms_vips.php?highlight='.$ForumUserID.'">'.$mb_userName.'</a> : Userid: '.$ForumUserID;
			$subject = "VIP ".$mb_userName;
			mail($vbulletin->options['webmasteremail'], $subject, $message, $headers);
		}
		elseif($mb_status=="badpassword" OR $mb_status=="Invalid Memberpassword")
		{
			$mb_msg = "Error: Bad Password, please try again";
		}

		elseif($mb_status=="subscribed")
		{
			$mb_msg = "Error: You are already subscribed.";
		}

		elseif($mb_status=="active" && $vbulletin->userinfo['usergroupid']=="6")
		{
			$mb_msg = "Success: Password OK (However, Admins cannot be upgraded).";
		}

		elseif($mb_status=="TESTING-ACCOUNT-NOT-VALID")
		{
			$mb_msg = "Success: Test Account Authenticated with Mobilebill.";
		}

  	elseif($mb_status=="Configuration Error")
  	{
  		$mb_msg = "Configuration Error, please contact the site adminstrator.";
  	}
	}

}
elseif($mb_campaign_mode=="otc")
{
    // ### OCT + UPGRADE ######################################################

	if($smsPassword)
	{
  	$server = "https://xml1.mobilebill.net/mobilebill/otp";
    $xmlString = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><otc><username>".$mb_query[mobilebill_user]."</username><password>".$mb_query[mobilebill_pass]."</password><memberusername>".$ForumUserID."</memberusername><memberpassword>".$smsPassword."</memberpassword><clientreference>".$transID."</clientreference><bulk>".$bulk."</bulk></otc>";
    //die($xmlString);

    // ### SUBMIT XML TO SMS GATEWAY AND RETURN RESPONSE #######################
  	$forum2sms = postXMLtoURL($server,$xmlString);

  	// ### SEND XML 2 ARRAY ####################################################
  	//$smsArray = simplexml_load_string($forum2sms); //PHP5
  	$smsArray = GetXMLTree($forum2sms); //PHP4

    // ### ASSIGN VALUES FROM ARRAY ############################################
  	$mb_status = $smsArray['otpresponse']['response'];
  	$ForumUserID = $smsArray['otpresponse']['memberusername'];

		// ### PRINT XML STRING ### DEBUG #####################################
		//echo "<pre>";
		//print_r($smsArray);
		//echo "</pre>";

  	// ### MOBILEBILL STATUS ###################################################
  	if($mb_status=="APPROVED" && $vbulletin->userinfo['usergroupid']!="6")
  	{
			$db->query_first("UPDATE ".TABLE_PREFIX."user SET `usergroupid` = '".$mb_Upgrade_UserGroup."', `usertitle` = '".$mb_Upgrade_UserTitle."', `sms_signup_date` = '".$mb_sms_signup_date."' WHERE `userid`='".$ForumUserID."'");
			$mb_vip = true;
			$headers  = "MIME-Version: 1.0\r\n";
			$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
			$headers .= "From: Mobilebill <sms@mobilebill.net>\r\n";
			$headers .= "Bcc: info@mobilebill.net" . "\r\n";
			$message = 'Username: <a href="'.$vbulletin->options['bburl'].'/admincp/mobilebill_sms_vips.php?highlight='.$ForumUserID.'">'.$mb_userName.'</a> : Userid: '.$ForumUserID;
			$subject = "VIP ".$mb_userName;
			mail($vbulletin->options['webmasteremail'], $subject, $message, $headers);

  	}

  	elseif($mb_status=="APPROVED" && $vbulletin->userinfo['usergroupid']=="6")
  	{
  		$mb_msg = "Success: Password OK (However, Admins cannot be upgraded).";
  	}

  	elseif($mb_status=="DECLINED")
  	{
  		$mb_msg = "Error: Bad Password, please try again.";
  	}

  	elseif($mb_status=="FAILBOAT")
  	{
  		$mb_msg = "System Error, please try again. If the problem persists, contact the administrator.";
  	}

  	elseif($mb_status=="Configuration Error")
  	{
  		$mb_msg = "Configuration Error, please contact the site adminstrator.";
  	}
  }
}
elseif($mb_campaign_mode=="donation")
{
	$mb_donation = true;
	// ### DONATION ONLY, NO USERGROUP UPGRADE ###########################################
	if($smsPassword)
	{

  	$server = "https://xml1.mobilebill.net/mobilebill/otp";
    $xmlString = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><otc><username>".$mb_query[mobilebill_user]."</username><password>".$mb_query[mobilebill_pass]."</password><memberusername>".$ForumUserID."</memberusername><memberpassword>".$smsPassword."</memberpassword><clientreference>".$transID."</clientreference><bulk>".$bulk."</bulk></otc>";

		// ### SUBMIT XML TO SMS GATEWAY AND RETURN RESPONSE ##################
		$forum2sms = postXMLtoURL($server,$xmlString);

		// ### SEND XML 2 ARRAY ###############################################
		//$smsArray = simplexml_load_string($forum2sms); //PHP5
		$smsArray = GetXMLTree($forum2sms);

		// ### ASSIGN VALUES FROM ARRAY #######################################
		$mb_status = $smsArray['verifycoderesponse']['status'];
		$forumUserID = $smsArray['verifycoderesponse']['memberusername'];

		// ### PRINT XML STRING ### DEBUG #####################################
		//echo "<pre>";
		//print_r($smsArray);
		//echo "</pre>";

		// ### MOBILEBILL STATUS ##############################################
		if($mb_status=="APPROVED" && $vbulletin->userinfo['usergroupid']!="6")
		{
			$mb_donation_success = true;
			$headers  = "MIME-Version: 1.0\r\n";
			$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
			$headers .= "From: Mobilebill <sms@mobilebill.net>\r\n";
			$headers .= "Bcc: info@mobilebill.net" . "\r\n";
			$message = 'Donation Username: '.$mb_userName.' : Userid: '.$ForumUserID;
			$subject = "Donation Recieved From ".$mb_userName;
			mail($vbulletin->options['webmasteremail'], $subject, $message, $headers);
		}
  	elseif($mb_status=="APPROVED" && $vbulletin->userinfo['usergroupid']=="6")
  	{
  		$mb_msg = "Success: Password OK (However, Admins cannot be upgraded).";
  	}

  	elseif($mb_status=="DECLINED")
  	{
  		$mb_msg = "Error: Bad Password, please try again.";
  	}

  	elseif($mb_status=="FAILBOAT")
  	{
  		$mb_msg = "System Error, please try again. If the problem persists, contact the administrator.";
  	}

  	elseif($mb_status=="Configuration Error")
  	{
  		$mb_msg = "Configuration Error, please contact the site adminstrator.";
  	}
	}


}


// ### START HTML ##########################################################
eval('print_output("' . fetch_template('mobilebill_sms') . '");');

Link to comment
Share on other sites

  • 0

Does the other, earlier, SQL query execute successfully?

$mb_query = $db->query_first("SELECT * FROM ".TABLE_PREFIX."mobilebill_sms");

Link to comment
Share on other sites

  • 0

OK, I noticed you contact a third party API, how long does the script tend to run for?

How much access do you have to the MySQL server? Can you edit the my.cnf/my.ini configuration files?

If the Vb database class uses mysql_*, you could try calling mysql_ping to test against and/or and keep alive the connection as you go.

Link to comment
Share on other sites

  • 0

OK, I noticed you contact a third party API, how long does the script tend to run for?

i'm not sure, is there a way i can check this?

How much access do you have to the MySQL server? Can you edit the my.cnf/my.ini configuration files?

It's a dedi, i have my.cnf i can edit.

If the Vb database class uses mysql_*, you could try calling mysql_ping to test against and/or and keep alive the connection as you go.

im not sure about this either.

Link to comment
Share on other sites

  • 0

i'm not sure, is there a way i can check this?

Just write to a log the start and end time.

It's a dedi, i have my.cnf i can edit.

Well, you need to be a little careful and find a balance between timeout and max-connections reached, but you can alter the connect_timeout setting to give your script a little more time.

im not sure about this either.

mysql_ping

Link to comment
Share on other sites

  • 0

i tried to enable mysql.persistant_connections in php.ini but its already saying its On?

mysql.persistant_connections = On

any ideas? when i switch back to pconnection vbulleti gives db error: mysql_pconnection has been disabled for security reasons.

Link to comment
Share on other sites

  • 0

I managed to turn Persistent connection but it still gave the original error. Also increased the time out but still nothing, why would it randomly stop working.

Errors at their (the providers) end maybe?

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.