Web Development Blog

24K Design Studio :: Blog :: Use MySQL to store attachment. MySQL server has gone away?
Author:
Password:
Use MySQL to store attachment. MySQL server has gone away?
17:46, 27 Oct, 2010
by David

Attachment means files related to a text.
The purpose to store it in database, is to use foreign key to connect the text and attachment. So when the text is deleted, the attachment will be deleted automatically.
Since foreign key will be used, the table for text and table for attachment must both use InnoDB format.
Then set the foreign key.
When a file is uploaded, read its content, and store its meta information like filename into the attachment table. Then store its content into the table.

-----------------------------------------------------------------------------------

Then you might see sql error. The text is "MySQL server has gone away".^O^
It is still there, but it just refused to accept the content of attachment, which is so big.

So how big does it accept? Here is one helper function:

01 // Return false if failed, 1 if cannot find, 2 if multiple result, string value result otherwise.
02 function db_show_variable($name) {
03   $result = @mysql_query("show VARIABLES like '%$name%';");
04   if (!$result) {
05     return false;
06   }
07   $valueOld = null;
08   while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
09     if ($valueOld == null) {
10       $valueOld = $line['Value'];
11     } else {
12       return 2;
13     }
14   }
15   if ($valueOld === null) {
16     return 1;
17   }
18   return $valueOld;
19 }

With the above function, using db_show_variable('max_allowed_packet'), and we can see the size. It is usually 1024KB.

------------------------------------------------

Then how to solve the problem?

One way is split the attachment into pieces. Maybe not an easy idea.

If we have control of the database machine, we'd better change the configuration. Try to find my.ini in the folder of MySQL. In that file, looking for max_allowed_packet, and change it.

If changing my.ini is not available, it is also possible to change that directly from SQL scripts.
set global max_allowed_packet = $sizeNew
The $sizeNew should be an integer, in unit byte.
For mysql_query, if the return value is !== false, it means it succeeds. Reconnect, and the new value should work. If not, wait for maybe 5 secs and try again. This setting effects will last until the database server restarts.
The following is a working function in PHP:

01 // Return true if succeed, 0 if no need to set, -1 if cannot set, -2 if cannot read.
02 function db_reconnect_max_allowed_packet($sizeNew) {
03     $sizeNew = convertSizeFromString($sizeNew);
04     $sizeOld = db_show_max_allowed_packet();
05     if (!is_string($sizeOld)) {
06         return -2;
07     }
08     $sizeNew = (int) ($sizeNew);
09     if (bccomp($sizeNew, $sizeOld) <= 0) {
10         return 0;
11     }
12     $result = mysql_query("set global max_allowed_packet = $sizeNew");
13     if (!$result) {
14         return -1;
15     }
16     mysql_close();
17     $db_connect = null;
18     db_connect_utf8();
19     $sizeUpdated = db_show_max_allowed_packet();
20     if (bccomp($sizeUpdated, $sizeNew) == 0) {
21         return true;
22     }
23     return -1;
24 }
If the return value === false, maybe this sql connection does not have sufficient permission, and that should be changed in other ways rather than SQL scripts.

Tags: PHP MySQL
Reply:
Your name:
Only visible to the author.

Tell us you are not a robot. What is captcha1captchaOcaptcha2 =

Reply
#1
axunbygt
05:03, 25 Nov, 2015
 
1
Reply
Your name:
Only visible to the author.

Tell us you are not a robot. What is captcha1captchaOcaptcha2 =

Reply
View the latest 5 entries.