I am trying to upload a large file (2GB) to MySQL 8.0.34 on Linux using mysql-connector-j 8.0.33 with the following code:
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class InsertBlobIntoMySQL {
public static void main(String[] args) {
String jdbcUrl = "jdbc:mysql://localhost:3306/blobtest?blobSendChunkSize=50000&useServerPrepStmts=true" +
"&emulateUnsupportedPstmts=false&maxAllowedPacket=1073741824";
String username = "test";
String password = "test";
String filePath = "/tmp/2GB.dat";
try {
Class.forName("com.mysql.cj.jdbc.Driver");
try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
String sql = "INSERT INTO blobtest (blob_column) VALUES (?)";
try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
File file = new File(filePath);
try (InputStream inputStream = new FileInputStream(file)) {
preparedStatement.setBinaryStream(1, inputStream, file.length());
preparedStatement.executeUpdate();
System.out.println("BLOB inserted successfully.");
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
This leads to the following exception:
java.sql.SQLException: Parameter of prepared statement which is set through mysql_send_long_data() is longer than 'max_allowed_packet' bytes
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:555)
at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:339)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1054)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1003)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1312)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:988)
at InsertBlobIntoMySQL.main(InsertBlobIntoMySQL.java:27)
max_allowed_packet is set to 25GB in mysql.cnf, resulting in it to be shortened to 1G as can be seen in the log, so it is at maximum allowed size:
2023-12-14T10:25:01.046546+01:00 0 [Warning] [MY-000081] [Server] option 'max_allowed_packet': unsigned value 26843545600 adjusted to 1073741824.
Any ideas?