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?
The largest possible packet that can be transmitted to or from a MySQL 8.0 server or client is 1GB. When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection.
In MySQL, the 'LOAD DATA' in all forms is more efficient than the 'INSERT' because it loads rows in bulk.
Simple INSERT statement to add data to the table. Use INSERT Statement to add multiple rows in the table. INSERT INTO SELECT clause to insert the output generated by the SELECT query. INSERT IGNORE clause to ignore the error generated during the execution of the query.
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();
?>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With