In-Portal Issue Tracker

Welcome to the In-Portal Open Source CMS Issue Tracker! This is a central management / tracking tool for all types of tasks / issues / bugs for the In-Portal Project. Before reporting any issues, please make sure to read the Guide into Issue Tracker and How to Properly Test and Report Bugs!

Viewing Issue Simple Details [ Jump to Notes ] [ Wiki ]

[ View Advanced ] [ Issue History ] [ Print ]

Tags

No tags attached.

Reference

https://groups.google.com/d/topic/in-portal-dev/-YTo16ihRy4/discussion

Change Log Message

Fixes database query error reporting

Estimate Points

Attached Files

reset_db_error_before_each_query.patch [^] (952 bytes) 2010-12-30 06:57 [Show Content]

Index: db_connection.php
===================================================================
--- db_connection.php	(revision 14094)
+++ db_connection.php	(working copy)
@@ -244,6 +244,7 @@
 					$this->Query('SET NAMES \''.SQL_CHARSET.'\' COLLATE \''.SQL_COLLATION.'\'');
 				}
 
+				$this->setError(0, ''); // reset error
 				$this->setDB($db);
 			}
 
@@ -277,8 +278,6 @@
 		 */
 		function showError($sql = '')
 		{
-			$this->setError(0, ''); // reset error
-
 			if (!$this->connectionID) {
 				return ;
 			}
@@ -436,6 +435,7 @@
 			}
 			// set 1st checkpoint: end
 
+			$this->setError(0, ''); // reset error
 			$this->queryID = $query_func($sql,$this->connectionID);
 			if (is_resource($this->queryID)) {
 				$ret = Array();
@@ -494,6 +494,7 @@
 			}
 			// set 1st checkpoint: end
 
+			$this->setError(0, ''); // reset error
 			$this->queryID = $query_func($sql, $this->connectionID);
 
 			if( is_resource($this->queryID) )

processing_of_mysql_has_gone_away_error.patch [^] (5,178 bytes) 2011-02-18 02:11 [Show Content]

Index: db_connection.php
===================================================================
--- db_connection.php	(revision 14234)
+++ db_connection.php	(working copy)
@@ -27,7 +27,6 @@
 		*/
 		var $Application;
 
-
 		/**
 		 * Current database type
 		 *
@@ -45,6 +44,13 @@
 		var $connectionID = null;
 
 		/**
+		 * Connection parameters, that were used
+		 *
+		 * @var Array
+		 */
+		var $connectionParams = Array ('host' => '', 'user' => '', 'pass' => '', 'db' => '');
+
+		/**
 		 * Handle of currenty processed recordset
 		 *
 		 * @var resource
@@ -230,8 +236,10 @@
 		 * @param string $db
 		 * @access public
 		 */
-		function Connect($host, $user, $pass, $db, $force_new = false)
+		function Connect($host, $user, $pass, $db, $force_new = false, $retry = false)
 		{
+			$this->connectionParams = Array ('host' => $host, 'user' => $user, 'pass' => $pass, 'db' => $db);
+
 			$func = $this->getMetaFunction('connect');
 			$this->connectionID = $func($host, $user, $pass, $force_new);
 
@@ -252,21 +260,44 @@
 			$this->errorCode = $this->connectionID ? $func($this->connectionID) : $func();
 
 			if ( !$this->hasError() ) {
-				return ;
+				return true;
 			}
 
 			$func = $this->getMetaFunction('error');
 			$this->errorMessage = $this->connectionID ? $func($this->connectionID) : $func();
 			$error_msg = 'Database connection failed, please check your connection settings.<br/>Error (' . $this->errorCode . '): ' . $this->errorMessage;
-			
-			trigger_error($error_msg, defined('IS_INSTALL') && IS_INSTALL ? E_USER_WARNING : E_USER_ERROR);
+
+			trigger_error($error_msg, (defined('IS_INSTALL') && IS_INSTALL) || $retry ? E_USER_WARNING : E_USER_ERROR);
+
+			return false;
 		}
 
-		function ReConnect($host, $user, $pass, $db, $force_new = false)
+		function ReConnect($force_new = false)
 		{
+			$retry_count = 0;
+
 			$func = $this->getMetaFunction('close');
 			$func($this->connectionID);
-			$this->Connect($host, $user, $pass, $db, $force_new);
+
+			while ($retry_count < 3) {
+				sleep(5); // wait 5 seconds before each reconnect attempt
+
+				$connected = $this->Connect(
+					$this->connectionParams['host'],
+					$this->connectionParams['user'],
+					$this->connectionParams['pass'],
+					$this->connectionParams['db'],
+					$force_new, true
+				);
+
+				if ($connected) {
+					break;
+				}
+
+				$retry_count++;
+			}
+
+			return $connected;
 		}
 
 		/**
@@ -275,36 +306,75 @@
 		 *
 		 * @access private
 		 */
-		function showError($sql = '')
+		function showError($sql = '', $key_field = null, $no_debug = false)
 		{
+			static $retry_count = 0;
+
 			$this->setError(0, ''); // reset error
+			$func = $this->getMetaFunction('errno');
 
 			if (!$this->connectionID) {
-				return ;
+				// no connection while doing mysql_query
+				$this->errorCode = $func();
+
+				if ( $this->hasError() ) {
+					$func = $this->getMetaFunction('error');
+					$this->errorMessage = $func();
+
+					$ret = $this->callErrorHandler($sql);
+
+					if (!$ret) {
+						exit;
+					}
+				}
+
+				return false;
 			}
 
-			$func = $this->getMetaFunction('errno');
+			// checking if there was an error during last mysql_query
 			$this->errorCode = $func($this->connectionID);
 
-			if ($this->hasError()) {
+			if ( $this->hasError() ) {
 				$func = $this->getMetaFunction('error');
 				$this->errorMessage = $func($this->connectionID);
 
-				if (is_array($this->errorHandler)) {
-					$func = $this->errorHandler[1];
-					$ret = $this->errorHandler[0]->$func($this->errorCode, $this->errorMessage, $sql);
+				$ret = $this->callErrorHandler($sql);
+
+				if ( ($this->errorCode == 2006 || $this->errorCode == 2013) && ($retry_count < 3) ) {
+					// #2006 - MySQL server has gone away
+					// #2013 - Lost connection to MySQL server during query
+					$retry_count++;
+
+					if ( $this->ReConnect() ) {
+						return $this->Query($sql, $key_field, $no_debug);
+					}
 				}
-				else {
-					$func = $this->errorHandler;
-					$ret = $func($this->errorCode, $this->errorMessage, $sql);
-				}
 
 				if (!$ret) {
 					exit;
 				}
 			}
+			else {
+				$retry_count = 0;
+			}
+
+			return false;
 		}
 
+		function callErrorHandler($sql)
+		{
+			if (is_array($this->errorHandler)) {
+				$func = $this->errorHandler[1];
+				$ret = $this->errorHandler[0]->$func($this->errorCode, $this->errorMessage, $sql);
+			}
+			else {
+				$func = $this->errorHandler;
+				$ret = $func($this->errorCode, $this->errorMessage, $sql);
+			}
+
+			return $ret;
+		}
+
 		/**
 		 * Default error handler for sql errors
 		 *
@@ -359,7 +429,7 @@
 		 * Returns first row of recordset
 		 * if query ok, false otherwise
 		 *
-		 * @param stirng $sql
+		 * @param string $sql
 		 * @param int $offset
 		 * @return Array
 		 * @access public
@@ -471,8 +541,8 @@
 				}
 				// set 2nd checkpoint: end
 			}
-			$this->showError($sql);
-			return false;
+
+			return $this->showError($sql, $key_field, $no_debug);
 		}
 
 		function ChangeQuery($sql)
@@ -541,8 +611,8 @@
 				}
 				// set 2nd checkpoint: end
 			}
-			$this->showError($sql);
-			return false;
+
+			return $this->showError($sql, $key_field);
 		}
 
 		/**

improved_db_error_processing.patch [^] (5,794 bytes) 2011-02-18 02:11 [Show Content]

Index: db_connection.php
===================================================================
--- db_connection.php	(revision 14234)
+++ db_connection.php	(working copy)
@@ -27,7 +27,6 @@
 		*/
 		var $Application;
 
-
 		/**
 		 * Current database type
 		 *
@@ -45,6 +44,13 @@
 		var $connectionID = null;
 
 		/**
+		 * Connection parameters, that were used
+		 *
+		 * @var Array
+		 */
+		var $connectionParams = Array ('host' => '', 'user' => '', 'pass' => '', 'db' => '');
+
+		/**
 		 * Handle of currenty processed recordset
 		 *
 		 * @var resource
@@ -230,8 +236,10 @@
 		 * @param string $db
 		 * @access public
 		 */
-		function Connect($host, $user, $pass, $db, $force_new = false)
+		function Connect($host, $user, $pass, $db, $force_new = false, $retry = false)
 		{
+			$this->connectionParams = Array ('host' => $host, 'user' => $user, 'pass' => $pass, 'db' => $db);
+
 			$func = $this->getMetaFunction('connect');
 			$this->connectionID = $func($host, $user, $pass, $force_new);
 
@@ -244,6 +252,7 @@
 					$this->Query('SET NAMES \''.SQL_CHARSET.'\' COLLATE \''.SQL_COLLATION.'\'');
 				}
 
+				$this->setError(0, ''); // reset error
 				$this->setDB($db);
 			}
 
@@ -252,21 +261,44 @@
 			$this->errorCode = $this->connectionID ? $func($this->connectionID) : $func();
 
 			if ( !$this->hasError() ) {
-				return ;
+				return true;
 			}
 
 			$func = $this->getMetaFunction('error');
 			$this->errorMessage = $this->connectionID ? $func($this->connectionID) : $func();
 			$error_msg = 'Database connection failed, please check your connection settings.<br/>Error (' . $this->errorCode . '): ' . $this->errorMessage;
-			
-			trigger_error($error_msg, defined('IS_INSTALL') && IS_INSTALL ? E_USER_WARNING : E_USER_ERROR);
+
+			trigger_error($error_msg, (defined('IS_INSTALL') && IS_INSTALL) || $retry ? E_USER_WARNING : E_USER_ERROR);
+
+			return false;
 		}
 
-		function ReConnect($host, $user, $pass, $db, $force_new = false)
+		function ReConnect($force_new = false)
 		{
+			$retry_count = 0;
+
 			$func = $this->getMetaFunction('close');
 			$func($this->connectionID);
-			$this->Connect($host, $user, $pass, $db, $force_new);
+
+			while ($retry_count < 3) {
+				sleep(5); // wait 5 seconds before each reconnect attempt
+
+				$connected = $this->Connect(
+					$this->connectionParams['host'],
+					$this->connectionParams['user'],
+					$this->connectionParams['pass'],
+					$this->connectionParams['db'],
+					$force_new, true
+				);
+
+				if ($connected) {
+					break;
+				}
+
+				$retry_count++;
+			}
+
+			return $connected;
 		}
 
 		/**
@@ -275,36 +307,74 @@
 		 *
 		 * @access private
 		 */
-		function showError($sql = '')
+		function showError($sql = '', $key_field = null, $no_debug = false)
 		{
-			$this->setError(0, ''); // reset error
+			static $retry_count = 0;
 
+			$func = $this->getMetaFunction('errno');
+
 			if (!$this->connectionID) {
-				return ;
+				// no connection while doing mysql_query
+				$this->errorCode = $func();
+
+				if ( $this->hasError() ) {
+					$func = $this->getMetaFunction('error');
+					$this->errorMessage = $func();
+
+					$ret = $this->callErrorHandler($sql);
+
+					if (!$ret) {
+						exit;
+					}
+				}
+
+				return false;
 			}
 
-			$func = $this->getMetaFunction('errno');
+			// checking if there was an error during last mysql_query
 			$this->errorCode = $func($this->connectionID);
 
-			if ($this->hasError()) {
+			if ( $this->hasError() ) {
 				$func = $this->getMetaFunction('error');
 				$this->errorMessage = $func($this->connectionID);
 
-				if (is_array($this->errorHandler)) {
-					$func = $this->errorHandler[1];
-					$ret = $this->errorHandler[0]->$func($this->errorCode, $this->errorMessage, $sql);
+				$ret = $this->callErrorHandler($sql);
+
+				if ( ($this->errorCode == 2006 || $this->errorCode == 2013) && ($retry_count < 3) ) {
+					// #2006 - MySQL server has gone away
+					// #2013 - Lost connection to MySQL server during query
+					$retry_count++;
+
+					if ( $this->ReConnect() ) {
+						return $this->Query($sql, $key_field, $no_debug);
+					}
 				}
-				else {
-					$func = $this->errorHandler;
-					$ret = $func($this->errorCode, $this->errorMessage, $sql);
-				}
 
 				if (!$ret) {
 					exit;
 				}
 			}
+			else {
+				$retry_count = 0;
+			}
+
+			return false;
 		}
 
+		function callErrorHandler($sql)
+		{
+			if (is_array($this->errorHandler)) {
+				$func = $this->errorHandler[1];
+				$ret = $this->errorHandler[0]->$func($this->errorCode, $this->errorMessage, $sql);
+			}
+			else {
+				$func = $this->errorHandler;
+				$ret = $func($this->errorCode, $this->errorMessage, $sql);
+			}
+
+			return $ret;
+		}
+
 		/**
 		 * Default error handler for sql errors
 		 *
@@ -359,7 +429,7 @@
 		 * Returns first row of recordset
 		 * if query ok, false otherwise
 		 *
-		 * @param stirng $sql
+		 * @param string $sql
 		 * @param int $offset
 		 * @return Array
 		 * @access public
@@ -436,6 +506,7 @@
 			}
 			// set 1st checkpoint: end
 
+			$this->setError(0, ''); // reset error
 			$this->queryID = $query_func($sql,$this->connectionID);
 			if (is_resource($this->queryID)) {
 				$ret = Array();
@@ -471,8 +542,8 @@
 				}
 				// set 2nd checkpoint: end
 			}
-			$this->showError($sql);
-			return false;
+
+			return $this->showError($sql, $key_field, $no_debug);
 		}
 
 		function ChangeQuery($sql)
@@ -494,6 +565,7 @@
 			}
 			// set 1st checkpoint: end
 
+			$this->setError(0, ''); // reset error
 			$this->queryID = $query_func($sql, $this->connectionID);
 
 			if( is_resource($this->queryID) )
@@ -541,8 +613,8 @@
 				}
 				// set 2nd checkpoint: end
 			}
-			$this->showError($sql);
-			return false;
+
+			return $this->showError($sql, $key_field);
 		}
 
 		/**

Relationships [ Relation Graph ] [ Dependency Graph ]

parent of	0001002	closed (5.1.2)	alex	Process critical database errors even when debug mode is off
related to	0000956	needs work (Icebox)	!COMMUNITY	Insufficient database permission handling
related to	0000973	needs work (Icebox)	!COMMUNITY	Daemon mode

Notes
(0003124) alex (manager) 2010-12-30 07:01	To test: 1. find any 2 subsequent SELECT database queries in debugger report 2. find places in PHP code, where they are invoked 3. make error in first query 4. set DBG_SQL_FAILURE in debug.php to 0 5. display $this->Conn->getErrorCode() after both queries When patch is not applied, then database error from 1st query will stay even after 2nd query execution was successful. After patch is applied, then there will be no error after 2nd query execution.

(0003270) alex (manager) 2011-02-18 02:12	Patch "improved_db_error_processing.patch" is combination of "reset_db_error_before_each_query.patch" and "processing_of_mysql_has_gone_away_error.patch" patches, since when applied one after another merging conflict was created. That's why patch "improved_db_error_processing.patch" should be tested here.

(0003271) alex (manager) 2011-02-18 02:16	Moving to 5.1.2, since it's database error handing and catching an error, when it happens is a high priority.

(0003277) Dmitry (manager) 2011-02-18 16:58	Patch "improved_db_error_processing.patch" tested good as part of 0001002 task and fully covers all patches in the task. Ready to be committed to 5.1.2-RC1

(0003278) alex (manager) 2011-02-19 06:13	Fix committed to 5.1.x branch. Commit Message: Fixes 0000953: Incorrect database query error reporting

(0003322) Dmitry (manager) 2011-03-30 09:53	Closing, since 5.1.2 release has been released.

Related Changesets
In-Portal CMS: 5.1.x r14235 Timestamp: 2011-02-19 06:13:06 Author: alex [ Details ] [ Diff ]	Fixes 0000953: Incorrect database query error reporting
	mod - /in-portal/branches/5.1.x/core/kernel/db/db_connection.php	[ Diff ] [ File ]

Issue History
Date Modified	Username	Field	Change
2011-03-30 09:53	Dmitry	Note Added: 0003322
2011-03-30 09:53	Dmitry	Status	resolved => closed
2011-02-19 06:13	alex	Note Added: 0003278
2011-02-19 06:13	alex	Status	reviewed and tested => resolved
2011-02-19 06:13	alex	Fixed in Version	=> 5.1.2-RC1
2011-02-19 06:13	alex	Resolution	open => fixed
2011-02-19 06:13	alex	Assigned To	!COMMUNITY => alex
2011-02-19 06:13	alex	Changeset attached	5.1.x r14235
2011-02-18 16:58	Dmitry	Note Added: 0003277
2011-02-18 16:58	Dmitry	Status	needs testing => reviewed and tested
2011-02-18 02:16	alex	Target Version	Icebox => 5.1.2
2011-02-18 02:16	alex	Note Added: 0003271
2011-02-18 02:13	alex	Relationship added	parent of 0001002
2011-02-18 02:12	alex	Note Added: 0003270
2011-02-18 02:11	alex	File Added: improved_db_error_processing.patch
2011-02-18 02:11	alex	File Added: processing_of_mysql_has_gone_away_error.patch
2011-01-25 04:22	alex	Relationship added	related to 0000973
2011-01-10 12:46	alex	Relationship added	related to 0000956
2010-12-30 07:01	alex	Note Added: 0003124
2010-12-30 07:01	alex	Assigned To	=> !COMMUNITY
2010-12-30 07:01	alex	Developer	=> alex
2010-12-30 07:01	alex	Status	active => needs testing
2010-12-30 06:57	alex	File Added: reset_db_error_before_each_query.patch
2010-12-14 10:20	alex	New Issue
2010-12-14 10:20	alex	Reference	=> https://groups.google.com/d/topic/in-portal-dev/-YTo16ihRy4/discussion
2010-12-14 10:20	alex	Change Log Message	=> Fixes database query error reporting

Web Development by Intechnic

In-Portal Open Source CMS