• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

PHP SQL Database connection Reset

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
Leo Torres
Asked:
Leo Torres
  • 20
  • 10
1 Solution
 
Julian HansenCommented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Julian HansenCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Julian HansenCommented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
Thank you I will test when I get home tonight or in the Morning thank you!
0
 
Leo TorresSQL DeveloperAuthor Commented:
Tested same results. Still not inserting
0
 
Julian HansenCommented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Julian HansenCommented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Julian HansenCommented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
Update?
0
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Julian HansenCommented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
He has he cant find it. Posting here was desperation.
0
 
Julian HansenCommented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Julian HansenCommented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Julian HansenCommented:
Ok - can you post that stored procedure.
0
 
Leo TorresSQL DeveloperAuthor Commented:
email me here here please I will reply with code. I cant post this code on open internet
0
 
Leo TorresSQL DeveloperAuthor Commented:
ltorres321@gmail.com
0
 
Leo TorresSQL DeveloperAuthor Commented:
Waiting on your email.
0
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
Anyone got an Idea on post. Have not seen any activity in days
0
 
Leo TorresSQL DeveloperAuthor Commented:
Moved to asp.net
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 20
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now