Bug #24223 max_allowed_packet cannot change blob field maxsize
Submitted: 12 Nov 2006 14:39 Modified: 5 Dec 2006 20:09
Reporter: lucols lucols Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 or later OS:Linux (linux redhat ,debain)
Assigned to: CPU Architecture:Any

[12 Nov 2006 14:39] lucols lucols
Description:
1. SET THE SERVER'S PARAM: max_allowed_packet=1M ,and restart mysqld.
2.create a table ,fields like this: id  integer ,photo blob; 
3.use mysql-connector-java-5.0.4.jar and insert  a picture(size=100K) into my tabe.
then, i got the exception: 
 Data truncation: Data too long for column 'PHOTO' at row 1 .

The mysql's document said "max_allowed_packet can change the blbo max size",but why i got the exception? 

any one tell me how to slove this problem,thanks.

How to repeat:
do it with above  descriptions, my java code :

public void blobWriteTest() {
		Transaction tx = null;
		Session session = null;
		try {
			String fname = "d:\\ss.jpg"; 
			File f = new File(fname);
			InputStream fin = new FileInputStream(f);

			byte[] data = new byte[fin.available()];
			fin.read(data);			
                        //get hibernate session 

                        session = HibernateSessionFactory.openSession();			        tx = session.beginTransaction();
                        // a hibernate entity:
			PersonalDossier dossier = new PersonalDossier();
			
			dossier.setPhoto(Hibernate.createBlob(data));
			session.save(dossier);
			tx.commit();
			fin.close();

		} catch (Exception e) {
			tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}

	}
-----------------------------------------------------
Class PersonalDossier {
        private int id;
 	private Blob photo;
        /**
	 * @hibernate.id
	 *  column="ID"
	 *  generator-class="native"
	 * @hibernate.generator-param
	 *  name="sequence"
	 *  value="OPTS_PERSONAL_DOSSIER_SEQUENCE"
	 */
	 public int getId() {
		return id;
	 }
         public void setId(int id) {
		this.id = id;
	}
        /**
	 * @hibernate.property
	 *  column="PHOTO"
	 */
	public Blob getPhoto() {
		return photo;
	}
	
	public void setPhoto(Blob photo) {
		this.photo = photo;
	}
    } 

Suggested fix:
max_allowed_packet no be detected.
[12 Nov 2006 14:42] lucols lucols
hibernate version : 3.2.0.ga
[12 Nov 2006 14:48] lucols lucols
when the picture <=64K ,this operator is ok.
[13 Nov 2006 5:34] lucols lucols
After change the column type to MEDIUMBLOB from BLOB ,and then  a jpg image that size greate than 1M can be inserted , the max_allowed_packet=1M can not stop this.
[13 Nov 2006 21:31] Mark Matthews
Hi!

Where does the manual state that max_allowed_packet changes the allowed blob size (because if it does, it needs to be fixed). It might state that max_allowed_packet controls the size of any query (and thus the size of a blob in that query).

The blob types have intrinsic limits to their size, which is the issue you're running into (so you need to use MEDIUM or LONG BLOBs in your application), see:

http://dev.mysql.com/doc/refman/5.0/en/blob.html
[14 Nov 2006 2:26] lucols lucols
Thanks Mark.
But the variable "max_allowed_packet" doesnot work ,because Mysql Server allow  insert greate thand 1M data even I set max_allowed_packet=1M(column type is MediumBlob),and no  exception be throwed.
[5 Dec 2006 20:09] Sveta Smirnova
Thank you for the report.

I can not repeat error you noticed in last comment with current BK sources.