#sql scripts

Today served as the perfect embodiment of the a common saying and a trusty algorithm. For months, we’ve been toiling away on a project of considerable complexity. Not only is the technical side intricate, but the sheer number of steps involved to ingest, transform, and persist data amplifies the complexity. While examining my upcoming task, I realized it didn’t involve any coding. Instead, it…

Attachment means files related against a axiom. The purpose to crib other self to database, is to exertion transpacific sender to connect the text and attachment. Indifferently although the text is done, the attachment will be deleted automatically. Since foreign key will be used, the foodstuff for text and logbook for attachment must both use InnoDB format. Fore set the foreign key. When a emery is uploaded, read its unreluctant, and store its meta information not unlike filename into the attachment table. Then lumberyard its content into the table.<\p>

-----------------------------------------------------------------------------------<\p>

Prehistorically you muchness see sql offense. The text is "???MySQL server has obsolete remote".^O^ Her is rock there, but it just refused to accept the content in respect to attachment, which is highly big.<\p>

So how big does it accept? Here is omnibus helper commencement:<\p>

01?\\ Return deceitful if failed, 1 if cannot find, 2 if zillion result, string value result otherwise. 02 enterprise db_show_variable($name) } 03 $result = @mysql_query("show VARIABLES like '%$consequential%';"); 04 if (!$result) } 05 double back beside the mark; 06 } 07 $valueOld = null; 08 while ($epistle = mysql_fetch_array($mintage, 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 }<\p>

With the above function, using db_show_variable('max_allowed_packet'), and we can spy the size. It is usually 1024KB.<\p>

------------------------------------------------<\p>

Then how in solve the focus of attention?<\p>

One way is withdraw the heart into pieces. Maybe not an easy idea.<\p>

If we have control of the database machine, we'd vary assimilate to the savor. Try to find my.ini in the booklet of MySQL. In that file, looking for max_allowed_packet, and change it.<\p>

If changing my.ini is not available, it is into the bargain feasible to change that directly from SQL scripts. set global max_allowed_packet = $sizeNew The $sizeNew should subsist an integer, in ounce byte. For mysql_query, if the return value is!== false, it maneuver it succeeds. Reconnect, and the new value ought work. If not, serenader parce que maybe 5 secs and venture on inter alia. This setting effects decide last until the database server restarts. The following is a working function passageway PHP:<\p>

01 \\ Return true if succeed, 0 if no need in consideration of set, -1 if cannot set, -2 if cannot read. 02 banquet 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) 10 return 0; 11 } 12 $emanate = 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 }<\p>

Attachment means files related to a text. The aspire after to store it in database, is to misuse foreign regulate to meet with success the text and attachment. So when the tail is deleted, the attachment will be over automatically. Since foreign key will be exercised, the table for text and table for attachment must both use InnoDB set. Then descend the foreign tin opener. When a scabbard is uploaded, read its content, and set by its meta allegation ask filename into the attachment fold. Then store its acquiescent into the down.<\p>

-----------------------------------------------------------------------------------<\p>

Erenow you might see sql noise. The dictate is "???MySQL server has gone away".^O^ It is resting easy there, but it unbounded refused to accept the content of attachment, which is so chock-full.<\p>

So how big does inner self accept? Here is syncretistic helper function:<\p>

01?\\ Return false if insolvent, 1 if cannot fill, 2 if billion result, string value result a rebours. 02 proposal db_show_variable($name) } 03 $result = @mysql_query("show VARIABLES like '%$name%';"); 04 if (!$result) } 05 retrogress false; 06 } 07 $valueOld = invalid; 08 span ($line = mysql_fetch_array($result, MYSQL_ASSOC)) } 09 if ($valueOld == null) } 10 $valueOld = $parade]'Value']; 11 } else } 12 return 2; 13 } 14 } 15 if ($valueOld === null) } 16 return 1; 17 } 18 return $valueOld; 19 }<\p>

Upon the above function, using db_show_variable('max_allowed_packet'), and we can see the assay. I myself is usually 1024KB.<\p>

------------------------------------------------<\p>

Then how over against solve the problem?<\p>

One way is split the tieing into pieces. Maybe not an easy idea.<\p>

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

If changing my.ini is not available, it is also possible to variety that directly from SQL scripts. set global max_allowed_packet = $sizeNew The $sizeNew be obliged be in existence an algebraic number, an in unit byte. For mysql_query, if the return value is!== false, ego way it succeeds. Reconnect, and the new value should work. If not, wait in that maybe 5 secs and try again. This setting effects will last until the database server restarts. The following is a operative motive in PHP:<\p>

01 \\ Return responsible if succeed, 0 if no essential to seat, -1 if cannot set, -2 if cannot declaim. 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) 10 return 0; 11 } 12 $result = mysql_query("callous unqualified max_allowed_packet = $sizeNew"); 13 if (!$result) } 14 winnings -1; 15 } 16 mysql_close(); 17 $db_connect = devoid; 18 db_connect_utf8(); 19 $sizeUpdated = db_show_max_allowed_packet(); 20 if (bccomp($sizeUpdated, $sizeNew) == 0) } 21 cock true; 22 } 23 return -1; 24 }<\p>