[RESOLVED] MySQL connection closing during/after large image upload?

[bunner_bob]

I'm working on a script that upload a (possibly) large image to the server, then FTPs it to a separate server. Once the upload/FTP is complete, PHP updates the database to record the new image name & location. When the image file is larger than a certain size, the upload and FTP seems to go fine but the subsequent updating of the MySQL database (and querying/displaying of the results) fails.

I've tracked it down to either the db connection resource created at the beginning of the script (via mysql_connect($host, $user, $pw)) no longer existing, or the subsequent mysql_select_db($dbname) failing (which could just be due to the db connection resource no longer existing).

If I omit the FTP transfer step the problem doesn't seem to happen (at least on uncompressed TIFF uploads up to about 18M😎. If I DON'T omit the FTP transfer step, the problem happens when uploading (compressed) TIFFs somewhere between 9 and 10 MB in size (smaller ones don't cause the problem).

I'm not sure whether it's a time-related issue or a filesize related issue. I get the feeling that the MySQL connection is timing out, but that's a wild guess at this point. Successful uploads seem to take up to 53 seconds or so. One of the failures was about 54 seconds from submit to error message. This amount of seconds doesn't seem to correspond to any standard setting for script time limits.

max_execution_time and max_input_time are both set to 360

Any thoughts on what I should try, or look at, or ask the hosting company to do (they seem willing to set anything for us)?

• Bob

[bradgrafelman]

If the MySQL query is failing, then what is the MySQL error message being returned? (Are you even retrieving and displaying/logging the MySQL error message upon failure?)

[bunner_bob]

The MySQL error I'm getting (via my error handler) is from one of the subsequent queries, and is something like

2011-06-19 13:23:28 Error (256): SQL SELECT * FROM content WHERE section='products' AND subsection='layout' AND contentId=113 AND count > 0    

REQUEST_URI = /order/ file: /home/zipyour/public_html/webroot/inc/core/db/dbBase.class.php (line 415)
Request URI: /order/
Trace: (dbBase.class.php:415),(dbBase.class.php:518),(submitOrderItem.class.php:157),(order.php:444)

There is nothing wrong with this query that should cause it to fail. My data access layer thinks a db connection resource is already in place so it isn't failing at the db connect stage.

Let me see if I can force it to try reconnecting and get a more meaningful error.

[bradgrafelman]

bunner bob;10982608 wrote:

The MySQL error I'm getting (via my error handler)

But what you've shown isn't a MySQL error - it looks more like a general PHP error handler (which, in this case, is useless other than to say "Hey! Something went wrong, but who knows what that is, really?"). Even if the MySQL server is aborting the query prematurely, there should be an error message left behind that explains why it did so. For that, you'd have to use something like [man]mysql_error/man.

[bunner_bob]

The error I get at mysql_select_db is 2006: MySQL server has gone away

[bradgrafelman]

Can you show us the PHP script that's doing all of this uploading and subsequent DB queries?

[bunner_bob]

Trolling around I found this:
http://bogdan.org.ua/2008/12/25/how-to-fix-mysql-server-has-gone-away-error-2006.html
Suggests a couple of settings to try in MySQL's my.cnf file. Don't suppose that can be done via php directives. You think these are valid suggestions, or know of another possible culprit to try?

• Bob

[bradgrafelman]

There are a variety of causes (and thus, fixes) for the 2006 error message; there's even a page dedicated to this error in the MySQL manual - C.5.2.9. MySQL server has gone away.

For example, what is the order of operations in your script? Are you doing a lot of time-consuming operations between the time you open a connect to MySQL and the time where you execute the failing query?

[bunner_bob]

bradgrafelman;10982611 wrote:

Can you show us the PHP script that's doing all of this uploading and subsequent DB queries?

Not easily - it's all pretty modular OO stuff, but I can throw a few parts of it up...

Here's the func that runs the image uploading:

public function uploadImageRemote($photoSizeArray, $publicPath = NULL)
{
	if (!empty($_FILES['filename']['tmp_name']))
	{
		require_once(CORE_FILE_INCLUDE_PATH . 'uploadImage.class.php');

	$this->fieldarray['itemId'] = $this->fieldarray['orderItemId'];
	$this->fieldarray['imageType'] = 'order';

	$upload = new uploadImage($this->fieldarray, $photoSizeArray);

	if (!empty($publicPath))
	{
		$upload->setPathPrefix('');
	}

	if (!$this->filename = $upload->upload()) // the functionality in here (resizing, thumbnailing) is bypassed for large file uploads
	{
		return 'Error: possible file upload attack!';
	}
	else
	{
		if ($upload->uploadRemoteFtp($this->filename)) // this seems to work fine
		{
			$data = new db_OrderItems();// beginning of the post-FTP db update that ends up failing
			$data->setSelect('orderItemId, photos');
			$data->setWhere('orderItemId=' . $this->fieldarray['itemId']);
			$pRow = $data->getSingle();
			if (!empty($pRow['photos']))
			{
				$photos = unserialize($pRow['photos']);
			}
			else
			{
				$photos = array();
			}
			$photo = array(
						'photoId' => time(),
						'slot' => $this->fieldarray['photoSlot'],
						'filename' => $this->filename,
						'fullsizeFileOrderItemId' => $this->fieldarray['itemId'],
						'fullsizeFilePath' => PHOTO_SERVER_UPLOAD_DIR . '/' . $this->fieldarray['itemId'] . '/' . $this->filename
						);


			if ($dim = $upload->getImageDimensions())
			{
				$photo['width'] = $dim['width'];
				$photo['height'] = $dim['height'];
			}

			$photos[] = $photo;
			$pRow['photos'] = serialize($photos);
			$data->updateRecord($pRow); // this is what fails
		}
	}
}
else
{
	$this->filename = $this->fieldarray['filename'];
}
}

Here's the actual FTP transfer process:

public function uploadRemoteFtp($filename)
{
	if ($conn = ftp_connect(PHOTO_SERVER))
	{
		if (ftp_login($conn, PHOTO_SERVER_USER, PHOTO_SERVER_PW))
		{
			ftp_pasv($conn, true);
			if (ftp_chdir($conn, PHOTO_SERVER_UPLOAD_DIR))
			{
				$sourceFile = $this->pathPrefix . 'photos/' . $this->fieldarray['imageType'] . '/original/' . $this->fieldarray['itemId'] . '/' . $filename;

			if ($dims = getimagesize($sourceFile))
			{
				$this->dimensions['width'] = $dims[0];
				$this->dimensions['height'] = $dims[1];
			}

			$image_dir = $this->fieldarray['itemId'];

			$rawList = ftp_nlist($conn, ".");
			if (!in_array($image_dir, $rawList))
			{
				ftp_mkdir($conn, $image_dir);
				//@ftp_chmod($conn, 0777, $image_dir);
			}

			ftp_chdir($conn, $image_dir);

			if (ftp_put($conn, $filename, $sourceFile, FTP_BINARY))
			{
				unlink($sourceFile);
				return true;
			}
			else
			{
				return 'Error uploading photo';
			}
		}
	}
	else
	{
		return 'Error: imageserver login error';
	}
}
else
{
	return 'Error: imageserver connect error';
}
}

[bradgrafelman]

What class/library are you using for the underlying DB access? One suggestion I saw was that if there is a lengthy delay between the time you connect and the time you send a query, you might first try pinging the MySQL server (e.g. [man]mysqli_ping/man). Another suggestion was to use the 'CLIENT_INTERACTIVE' flag (e.g. MYSQLI_CLIENT_INTERACTIVE when using [man]mysqli_real_connect/man) when connecting to the MySQL server.

[bunner_bob]

bradgrafelman;10982613 wrote:

There are a variety of causes (and thus, fixes) for the 2006 error message; there's even a page dedicated to this error in the MySQL manual - C.5.2.9. MySQL server has gone away.

For example, what is the order of operations in your script? Are you doing a lot of time-consuming operations between the time you open a connect to MySQL and the time where you execute the failing query?

Definitely some time-consuming stuff happening between opening the connection and executing the failing query - the FTPing of the uploaded image, specifically.

The server is running MySQL 5.0.92-community. MySQL client is 4.1.22 - not sure if that'd be an issue.

Reading that page, could be that reconnect is disabled I suppose. If it is NOT disabled, do I need to manually re-issue my query or should that automatically happen (somehow - not sure how that'd work)?

--- just saw your latest post. The class I'm using for db operations is my own, based on a guy named Tony Marston's work. The db connection script I'm using is pretty simple:

function db_connect($dbname)
{
	global $dbconnect, $dbhost, $dbusername, $dbuserpass;

if (!$dbconnect)
{
	$dbconnect = mysql_connect($dbhost, $dbusername, $dbuserpass);
	mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");
}

if (!$dbconnect)
{
	return 0;
}
elseif (!mysql_select_db($dbname, $dbconnect))
{
	return 0;
}
else
{
	return $dbconnect;
}
}

[bradgrafelman]

First thing I notice is that you're using the [man]mysql[/man] library - you do realize that it's pretty old/outdated nowadays, right? 😉

If possible, you might consider upgrading to [man]MySQLi[/man] (or even [man]PDO[/man]). At the very least, keep those two in mind for future projects.

Otherwise, try doing a [man]mysql_ping/man just before you execute the query and see if any errors are generated. Also, try adding the MYSQL_CLIENT_INTERACTIVE flag to the [man]mysql_connect/man call to see if that helps any.

EDIT: The [man]mysqli.overview[/man] manual page spells it out pretty clearly:

PHP Manual wrote:

What are the main PHP API offerings for using MySQL?

There are three main API options when considering connecting to a MySQL database server:

• PHP's MySQL Extension

• PHP's mysqli Extension

• PHP Data Objects (PDO)

Each has its own advantages and disadvantages. The following discussion aims to give a brief introduction to the key aspects of each API.

What is PHP's MySQL Extension?

This is the original extension designed to allow you to develop PHP applications that interact with a MySQL database. The mysql extension provides a procedural interface and is intended for use only with MySQL versions older than 4.1.3. This extension can be used with versions of MySQL 4.1.3 or newer, but not all of the latest MySQL server features will be available.

Note:

If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.

[bunner_bob]

Alright - think the mysql_ping was the trick. It won't reopen the connection but it lets me check it and then I can throw in another connect loop if it fails. Thought about recursing the dbconnect function but wasn't sure how ugly it could get if it started infinite looping. This seems to be working though. Thanks for the tips/pointers!

function db_connect($dbname)
{
	global $dbconnect, $dbhost, $dbusername, $dbuserpass;

if (!$dbconnect)
{
	$dbconnect = mysql_connect($dbhost, $dbusername, $dbuserpass);
	mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");
}

if (!$dbconnect)
{
	return 0;
}
elseif (!mysql_ping($dbconnect))
{
	mysql_close($dbconnect); // have to close the connection (even though its not currently working) for it to recreate properly.

	if ($dbconnect = mysql_connect($dbhost, $dbusername, $dbuserpass))
	{
		mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");
		if (!mysql_select_db($dbname,$dbconnect))
		{
			return 0;
		}
		else
		{
			return $dbconnect;
		}
	}
	else
	{
		return 0;
	}
}
elseif (!mysql_select_db($dbname, $dbconnect))
{
	return 0;
}
else
{
	return $dbconnect;
}
}