Solved

PHP SQL Database connection Reset

Posted on 2014-02-23
30
384 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 56

Expert Comment

by:Julian Hansen
ID: 39881884
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
ID: 39882355
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 56

Expert Comment

by:Julian Hansen
ID: 39882777
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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 8

Author Comment

by:Leo Torres
ID: 39885185
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 56

Expert Comment

by:Julian Hansen
ID: 39885216
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
ID: 39886544
Thank you I will test when I get home tonight or in the Morning thank you!
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39887810
Tested same results. Still not inserting
0
 
LVL 56

Expert Comment

by:Julian Hansen
ID: 39888039
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
ID: 39888046
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 56

Expert Comment

by:Julian Hansen
ID: 39888090
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
ID: 39888475
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 56

Expert Comment

by:Julian Hansen
ID: 39888556
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
ID: 39888835
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
ID: 39891481
Update?
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39893930
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
 
LVL 56

Expert Comment

by:Julian Hansen
ID: 39894081
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
ID: 39894363
He has he cant find it. Posting here was desperation.
0
 
LVL 56

Expert Comment

by:Julian Hansen
ID: 39894459
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
ID: 39894867
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
ID: 39896876
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
ID: 39896883
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 56

Expert Comment

by:Julian Hansen
ID: 39896899
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
ID: 39897089
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 56

Accepted Solution

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

Author Comment

by:Leo Torres
ID: 39897107
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
ID: 39897108
ltorres321@gmail.com
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39897125
Waiting on your email.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39897423
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
ID: 39914536
Anyone got an Idea on post. Have not seen any activity in days
0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 40471778
Moved to asp.net
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

680 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