Storing large values in MySQL fields with PHP

Sep 29 2009

Today a colleague at work ran into a problem when trying to store file contents into a MySQL database table. We are using this solution for quite some time now and it has always worked pretty good for documents and files up to 15 MB. The main reason for us to put files in the database is that it is easier to migrate a website to another server because the database contains all the data that we need.

The problem appeared at a customers' webserver that is running Fedora. First we thought there must be something wrong with our PHP code. But as the code worked fine on our servers (Debian) we soon found out that we couldn't store more than 1 MB in our content field in the database. After a little more investigation the only solution to the problem seems to be raising the mysql system variable max_allowed_packet to a value that is bigger than the largest content that should go into a single field.

For Debian the default max_allowed_packet size is 16 MB, but since the max_allowed_packet is not set for Fedora, MySQL defaults to 1 MB. We have no access to the MySQL server configuration so we had to solve problem this on the client side (our PHP code). An example of how this can be done is given here.

$fileName = '/file/name/of/uploaded/file.bin';
$fileSize = filesize($fileName);

// $db holds the (ADODB) database connection
// Fetch current packet size
$packetSize = (int) $db->GetOne('SELECT @@max_allowed_packet');
if ($packetSize < $fileSize + 2048) {
	$sql = sprintf('SET @@max_allowed_packet=%d', $fileSize + 2048);
	$db->Execute($sql);
}

$db->Execute('INSERT INTO `file`(`filename`, `filesize`) VALUES(?, ?)', array($fileName, $fileSize));
$fileId = $db->Insert_ID();

// Here you can store the file contents in database
$handle = fopen($fileName, 'r');
while (($data = fread($handle, 524288)) && strlen($data) > 0) {
	// Update record with chunks of 512K, until the file is completely in the database
	// This prevents PHP from running out of memory
	$sql = sprintf(
		'UPDATE `file` 
		SET `contents` = CONCAT(`contents`, ?) 
		WHERE `id` = ?');
	$this->db->Execute($sql, array($db->Quote($data), $fileId));
}
fclose($handle);

// now set the  max_allowed_packet back to old value
$sql = sprintf('SET @@max_allowed_packet=%d', $packetSize);
$db->Execute($sql);

It is possible to store values up to 1 GB in a field in MySQL (as 1 GB is currently the maximum allowed packet size). It is however not advisable to use the above method using CONCAT to put large files in the database, CONCAT gets very slow when the size of the blob gets bigger. If you want to put large files in the database you probably want to take a look at the LOAD_FILE function. This is however more complex if MySQL doesn't run on the same server as your PHP code is running on.

As you can see in the above PHP code the following MySQL queries can be used to retrieve and set the max_allowed_packet variable for the current connection.

-- Fetch maximum allowed packet size (in bytes)
SELECT @@max_allowed_packet;

-- Set maximum allowed packet size (in bytes)
SET @@max_allowed_packet=33554432;

We use the following table definition to store the files (I have left out some unimportant fields).

CREATE TABLE `file` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `filename` varchar(255) NOT NULL,
  `filesize` varchar(50) NOT NULL,
  `contents` longblob NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB CHARSET=utf8

0 Responses to "Storing large values in MySQL fields with PHP" Feed for this Entry

  1. There are no comments currently.