Bug #7745 Blob field set to null when updating BLOB larger than max_allowed_packet

• Submitted: 8 Jan 2005 16:36
• Modified: 20 Apr 2005 9:22
• Reporter: Michael Barker
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.8
• OS: Linux (Linux (2.6))
• Assigned to: Jani Tolonen
• CPU Architecture: Any

[8 Jan 2005 16:36] Michael Barker

Description:
If updating a blob field such that the blob will increase in size above the max_allowed_packet (but the update itself is less than max_allowed_packet) the value for the blob will be removed and replaced with a NULL value.

How to repeat:
E.g. Create a table with a blob.  Set max_allowed_packet to 1MB.  Update the Blob 4 times with byte array size of 512KB.  Check blob value in the database.  It will be NULL.

I have attached some Java code that demostrates the bug:
------>
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.sql.rowset.serial.SerialBlob;

/*
 * Created on Jan 8, 2005
 *
 */

/**
 * Demonstrates MySQL Blob bug.  Assumes max_allowed_packet is
 * set to 1MB in the MySQL configuration file.
 * 
 * @author Michael Barker
 *
 */
public class MySQLBlobBug
{
   public static void main(String[] args) throws Exception
   {
      int DATA_SIZE = 512 * 1024; // 512KB
      String CREATE_TABLE = "CREATE TABLE BLOB_TEST (ID VARCHAR(10) PRIMARY KEY, DATA LONGBLOB)";
      String SELECT = "SELECT ID, 'DATA' AS BLOB_DATA FROM BLOB_TEST WHERE ID = ?";
      String INSERT = "INSERT INTO BLOB_TEST (ID, DATA) VALUES (?, ?)";
      String URL = "jdbc:mysql://localhost/test?emulateLocators=true";
      String USER = "root";
      // Set this value in order to test.
      String PASS = "xxxxxxx";
      String DRIVER = "com.mysql.jdbc.Driver";
      String ID = "1";      
      byte[] TEST_DATA = new byte[DATA_SIZE];
      
      for (int i = 0; i < TEST_DATA.length; i++)
      {
         TEST_DATA[i] = (byte) ((i % 26) + 97);
      }
      
      DriverManager.registerDriver((Driver) Class.forName(DRIVER).newInstance());

      // Create a table with a blob.
      Connection con = DriverManager.getConnection(URL, USER, PASS);
      Statement stmt = con.createStatement();
      boolean result = stmt.execute(CREATE_TABLE);
      stmt.close();
      con.close();
            
      // Insert an empty blob.
      con = DriverManager.getConnection(URL, USER, PASS);
      PreparedStatement ps = con.prepareStatement(INSERT);
      ps.setString(1, ID);
      ps.setBlob(2, new SerialBlob(new byte[0]));
      result = ps.execute();
      ps.close();
      con.close();
      
      // Write 2MB to the blob
      con = DriverManager.getConnection(URL, USER, PASS);
      ps = con.prepareStatement(SELECT);
      ps.setString(1, ID);
      ResultSet rs = ps.executeQuery();
      
      if (rs.next())
      {
         Blob b = rs.getBlob("BLOB_DATA");
         
         for (int i = 0; i < 4; i++)
         {
            b.setBytes((i * TEST_DATA.length) + 1, TEST_DATA);
         }
      }
      
      rs.close();
      ps.close();
      con.close();

      // Read the data from the blob
      con = DriverManager.getConnection(URL, USER, PASS);
      ps = con.prepareStatement(SELECT);
      ps.setString(1, ID);
      rs = ps.executeQuery();
      
      if (rs.next())
      {
         Blob b = rs.getBlob("BLOB_DATA");
         
         // This will result in a null being returned, when it should be
         // a valid byte array.
         byte[] data = b.getBytes(1, DATA_SIZE);
         
         if (data == null)
         {
            System.out.println("Result from data base is null, this should be a valid byte array");
         }
      }
      
      rs.close();
      ps.close();
      con.close();
      
      
   }
}
<-------------

[8 Jan 2005 16:55] Mark Matthews

This is an issue with the _server_, if server-side prepared statments are being used (and in this case they are). The _server_ is the component truncating/setting the BLOB to null. Therefore, that's why I'm changing the category to 'MySQL Server', as it needs to be analyzed by someone on the server team.

[20 Apr 2005 9:22] Jani Tolonen

max_allowed_packet is the maximum size of a 'communication packet'
per query, settable by the user (admin). A larger value is considered as
a wrong packet and will be handled accordingly.

It does not make any difference whether the value is being concatenated
into a field, or updated, or inserted in any other way; if the result exceeds
max_allowed_packet, it is considered an error. Internally the server needs
to handle the value as whole and by setting 'max_allowed_packet' admin
has 'told' MySQL server not to handle larger values than that.

The rule of thumb is to set the max_allowed_packet always to at least the
size of the largest column in a table in the database, preferrably a bit larger.