Solved

PHP SQL Database connection Reset

Posted on 2014-02-23
30
359 Views
Last Modified: 2014-11-29
I have attached 2 SQL profile logs. Both do the same thing they on was done on a php webpage the other directly on SQL Studio.

My issue is that the webpage call to procedure connection gets reset by  command 'exec sp_reset_connection'.

To be specific Profiler3.txt is done from web page
Profiler4.txt done directly from SQL

The Webpage procedure call gets cut off before all the transactions can finish there are 2 more inserts that need to get done and that does not happen. Granted the primary procedure that the page calls a second procedure that ultimately calls a 3rd procedure that does the insert.

When I say I did this from SQL Server I did it using the same user account the webpage uses to enter data

Best if opened in Excel (Comma Delimited)
Files are very messing so here are the lines of interest to my process Profile3.txt are Line 6467 all the way to Line 6880 where you can see Connection reset.


Profile4.txt
Starts at 742 and line 1146 is where Similarities ends the Insert on line 1194 does not happen on Profile3.txt.

My apologies on how the Profile data is given I dont know of another way. If expert want me to post this data in a different fashion please advise and I will do so.

I did not post here but if some needs me to post PHP code as well, I will do so.  
Please advise why you think connection is being killed in the middle or if I need to post more info thanks in advance
Profiler4.txt
Profiler3.txt
0
Comment
Question by:Leo Torres
  • 20
  • 10
30 Comments
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Is it not that your script is timing out.

If you call set_time_limit(300) just before your call to the procedure does that make a difference.
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
not sure but I can give it a shot. and you suggest I do this from the webpage end on the PHP code. I dont think is the best idea to be honest with you to keep a transaction opt for 300 sec but I will give it a shot tonight when I get home.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
That's not what the call does.

By default the PHP engine will timeout a script that is taking too long to execute. The timeout length is set in PHP.INI.

The set_time_limit call - just extends this - so the only effect will be to  wait for a maximum of 5 minutes before terminating this script - if the script terminates before this then it terminates - the set_time_limit does not have an affaect - i.e. it does not extend the time the script takes to run naturally.

It is common practice to do this on scripts that take a long time to complete. Usually if you are looping through data you call set_time_limit(30) on each iteration to keep the script from timing out.
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Alright I am not a PHP programmer so I really dont know where I should insert your Code. I have inserted the page code here feel free to edit it as you need. The adduser function is the one I am having all the issues with because that stored procedure has procedure that calls another procedure it gets to the second level procedure but not the third which does an insert.
Thanks for all your Help
user-m.php
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
The position must be somewhere before the query is executed. A suggested change is shown below
public function add_user($userData = false)
	{
		if($userData):
			$refferalId = (isset($userData['referal_id'])) ? $userData['referal_id'] : "NULL";
			$query = "USE [eCRM]; Exec [dbo].[sp_CreateUser]
						@userName ='$userData[userName]',
						@password ='$userData[password]',
						@emailAddress ='$userData[emailAddress]',
						@firstName ='$userData[firstName]',
						@lastName ='$userData[lastName]',
						@country ='$userData[country]',
						@address1 ='$userData[address1]',
						@address2 ='$userData[address2]',
						@city ='$userData[city]',
						@stateProvince ='$userData[stateProvince]',
						@postalCode ='$userData[postalCode]',
						@refferalID = $refferalId,
						@U_Question1ID ='$userData[U_Question1ID]',
						@U_Answer1 ='$userData[U_Answer1]',
						@U_Question2ID ='$userData[U_Question2ID]',
						@U_Answer2 ='$userData[U_Answer2]',
						@U_Question3 ='$userData[U_Question3]',
						@U_Answer3 = '$userData[U_Answer3]',
						@IPAddress ='".$this->input->ip_address()."'";

			// ADD set_time_limit CALL HERE
			set_time_limit(300);

			$result = $this->db->query($query);
			// echo $query;
			$ret = get_data($result);
			// var_dump($ret);
			return $ret;
		else:
			return false;
		endif;
	}

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Thank you I will test when I get home tonight or in the Morning thank you!
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Tested same results. Still not inserting
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Have you tried splitting your query into
$query = "USE [eCRM]";
$this->db->query($query);

Open in new window

and
$query = " ... call SP

Open in new window

Have you tried using PDO for the DB calls (http://www.php.net/pdo.prepared-statements)
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
NOt sure I understand what you mean I am not an Expert on PHP. Can you please edit the file I posted or can you just post the text here on the page. Please, Thank You!
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Can you post the code where you make a connection to your database.

In your code you reference a property db

$this->db->query

Can you post the code where that is initialised.
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Sure, I will try to look for it.. and post it Thank sorry I just Don't any php my programmer did this and he doesn't know what's going on
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Then I would ask him if he can try using the PDO library for the query using the link I posted earlier for reference.
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
I have a database folder I am not sure what your looking for so I zipped the entire folder.

Let me know if you still need something else
database.zip
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Update?
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Need any help the files or other questions. Please help I am fighting a battle here I have not idea. I feel like the issue is php, and I dont know PHP.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
The database files you posted where the framework library files - not the files that show what connection library you are using - so not much to glean from those.

It is going to be difficult to debug this remotely especially when dealing with non-developer. My recommendation is you get a developer in to have a look at the problem locally.
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
He has he cant find it. Posting here was desperation.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Ok lets review this again - forget the profile logs lets look at it from the perspective of what actually happens.

When you run the manual test - you are obviously running queries against the database. Can you describe how you are doing this.
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Sorry about the delay. I am running procedure directly on the database. Dont have access to Machine from here. Once I get home I will give you detail by detail screen shot and I will ask the programmer to identify the file your asking and publish it here. I am even will to do a team viewer to my environment is you are will to take the time.
 If we need to discuss a consulting fee so be it.
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
I went ahead and inserted a bunch of inserts inside the Procedures to see where the processes get cut off. See Image below the web was a call from the web. The SQL was a call directly from SSMS. Again The SQL execution was done using the Web user Account  to be consistent with the web.

 Log
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Please let me know I dont have an issue providing Procedures if your need them but I may have to email you or get them to you some how I dont want to post this logic here.

Sorry for any inconvenience. thank you!
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
I went ahead and inserted a bunch of inserts inside the Procedures to see where the processes get cut off

Please elaborate on this - I need to know exactly what you did here - can you describe it in more detail. The screenshot is useful but all it says is that a prcoess was cutoff - but says nothing about what the process was doing or how you manually ran the process through SMSS - what did you do inside SMSS?
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
The web calls 1 Stored Procedure called SP_CreateUser. I ran that same procedure from SSMS with the same values I passed thru the web page. User activation start is the second Procedure and as you can see web version stops there. SQL Version is the complete cycle
0
 
LVL 51

Accepted Solution

by:
Julian Hansen earned 500 total points
Comment Utility
Ok - can you post that stored procedure.
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
email me here here please I will reply with code. I cant post this code on open internet
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
ltorres321@gmail.com
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Waiting on your email.
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
I never got an email from you to send procedures its not 1 procedure its like 5 and I cant post it here due to Privacy. I have no issue sending it to you.

FYI.. I did Find this database config file. Is this what you were looking for?
<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/*
| -------------------------------------------------------------------
| DATABASE CONNECTIVITY SETTINGS
| -------------------------------------------------------------------
| This file will contain the settings needed to access your database.
|
| For complete instructions please consult the 'Database Connection'
| page of the User Guide.
|
| -------------------------------------------------------------------
| EXPLANATION OF VARIABLES
| -------------------------------------------------------------------
|
|	['hostname'] The hostname of your database server.
|	['username'] The username used to connect to the database
|	['password'] The password used to connect to the database
|	['database'] The name of the database you want to connect to
|	['dbdriver'] The database type. ie: mysql.  Currently supported:
				 mysql, mysqli, postgre, odbc, mssql, sqlite, oci8
|	['dbprefix'] You can add an optional prefix, which will be added
|				 to the table name when using the  Active Record class
|	['pconnect'] TRUE/FALSE - Whether to use a persistent connection
|	['db_debug'] TRUE/FALSE - Whether database errors should be displayed.
|	['cache_on'] TRUE/FALSE - Enables/disables query caching
|	['cachedir'] The path to the folder where cache files should be stored
|	['char_set'] The character set used in communicating with the database
|	['dbcollat'] The character collation used in communicating with the database
|				 NOTE: For MySQL and MySQLi databases, this setting is only used
| 				 as a backup if your server is running PHP < 5.2.3 or MySQL < 5.0.7
|				 (and in table creation queries made with DB Forge).
| 				 There is an incompatibility in PHP with mysql_real_escape_string() which
| 				 can make your site vulnerable to SQL injection if you are using a
| 				 multi-byte character set and are running versions lower than these.
| 				 Sites using Latin-1 or UTF-8 database character set and collation are unaffected.
|	['swap_pre'] A default table prefix that should be swapped with the dbprefix
|	['autoinit'] Whether or not to automatically initialize the database.
|	['stricton'] TRUE/FALSE - forces 'Strict Mode' connections
|							- good for ensuring strict SQL while developing
|
| The $active_group variable lets you choose which connection group to
| make active.  By default there is only one group (the 'default' group).
|
| The $active_record variables lets you determine whether or not to load
| the active record class
*/

$active_group = 'default';
$active_record = false;

$db['default']['hostname'] = '192.168.1.100';
$db['default']['username'] = 'SomeUSer;
$db['default']['password'] = 'SomeString';
$db['default']['database'] = '';
$db['default']['dbdriver'] = 'mssql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;


/* End of file database.php */
/* Location: ./application/config/database.php */

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Anyone got an Idea on post. Have not seen any activity in days
0
 
LVL 8

Author Closing Comment

by:Leo Torres
Comment Utility
Moved to asp.net
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now