Is there any way to insert a large value in a mysq

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?

Answer 1

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();
?>

Answer 2

I don't think that there is a way. Maybe splitting the blob will do the trick.

But I think that reading this article http://blogs.msdn.com/b/oldnewthing/archive/2007/03/01/1775759.aspx from Reymond Chen is the best answer to your question.

Relational databases are not designed for that kind of use. If you need to store a gigabyte+ blob in a database, well it is better usually to store it on the NFS and just have a short file location string in your DB. Will save you much troubles down the road.