Is there any way to insert a large value in a mysql DB without changing max_allowed_packet?

Question

I know that in order to insert values larger than max_allowed_packet bytes into a MySQL database, the default solution would be to assure that both client and server side max_allowed_packet variables are bigger than the chunk of data that a query inserts into the DB.

However, is there any way to do so without changing the server side variable mentioned above? This would be useful when I have to insert data into a database that is hosted in an ISP that doesn't allow me to change the max_allowed_packet limit.

Another related question: MySql longblob limit is 4GB, but max_allowed_packet limit is 1GB. So, is it possible to insert values larger than 1GB in a longblob table column?

YudhiWidyatama YudhiWidyatama · Accepted Answer · 2011-01-31T00:39:58

I recently stumbled upon this problem. In my case, the server's max_allowed_packet was 1 MB and I could do nothing to change it. And I was inserting some data just above 1 MBs. I found two solution candidates.

1) First, using JDBC. Since MySQL Connector/J v3.1.9, there are a few parameters that you could set, here's my set of parameters in the JDBC URL:

Append these:

blobSendChunkSize=50000&useServerPrepStmts=true&emulateUnsupportedPstmts=false&maxAllowedPacket=20000000

Resulting in JDBC URL:

jdbc:mysql://serverip:3306/databasename?noDatetimeStringSync=true&blobSendChunkSize=50000&useServerPrepStmts=true&emulateUnsupportedPstmts=false&maxAllowedPacket=20000000

Then you must use PreparedStatement to do your inserts, and use the InputStream to pass the byte content as a parameter to setObject. Note that setObject using byte arrays won't enable the blob splitting. The combination of parameters, recent MySQL server (5.0.45 or later), and InputStream will send the blob data using LONG DATA mechanism, splitting the blob according to blobSendChunkSize.

The JDBC solution works and I have tested it.

2) Now, the second candidate, is to use PHP's mysqli driver and use mysqli_send_long_data. For your convenience, copied from PHP manual example :

<?php
$stmt = $mysqli->prepare("INSERT INTO messages (message) VALUES (?)");
$null = NULL;
$stmt->bind_param("b", $null);
$fp = fopen("messages.txt", "r");
while (!feof($fp)) {
    $stmt->send_long_data(0, fread($fp, 8192));
}
fclose($fp);
$stmt->execute();
?>

Is there any way to insert a large value in a mysql DB without changing max_allowed_packet?

2 Answers