MySQL Bugs: #27048: Replication fails for LONGBLOB type columns between two 5.0.22 servers

Bug #27048	Replication fails for LONGBLOB type columns between two 5.0.22 servers
Submitted:	12 Mar 2007 14:17	Modified:	22 Mar 2007 11:24
Reporter:	Cameron Smith	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.22-Debian_0ubuntu6.06.2-log	OS:	Linux (Ubuntu 6.06.1 LTS)
Assigned to:		CPU Architecture:	Any

Description:
I have a MASTER and SLAVE replication setup working fine on a local network.  Data volumes are low but all inserts, updates, and deletes insert correctly.   One of the tables which replicates OK has datatype BLOB.

However, when we try to replicate INSERTs into another table which has a LONGBLOB column, the SLAVE bails every time it reaches that point in the binary log, with Error 1064.  The specific cause of the error is always that it has mixed up the binary data in the INSERT statement with the syntax characters, and so has got lost.

If I look at the original statement by using mysqlbinlog on the binary log on the MASTER, the statement looks fine - it appears that the transfer of the logs is corrupting them.

The only bug I could possibly find which might be related is: http://bugs.mysql.com/bug.php?id=26489.   

How to repeat:
1. Set up replication between two servers, on different machines.  They should both have the default server charsets and collation sets.
2. Create a table with a LONGBLOB column on the master
3. The actual table creation should replicate fine.
4. Via a Java application, using JDBC 3.1.12, insert a gzipped item into the table e.g. a small JPEG).  The zipping should be done with java.util.zip.GZIPOutputStream
5. The data should be inserted uncorrupted into the table on the master
6. But on the slave, executing "show slave status" should give show Error 1064.

Suggested fix:
The replication should work as normal.

Thank you for the report.

But version 5.0.22 is quite old. Please try with current 5.0.37 and say us result.

If you can repeat problem with new version, please, provide content of configuration files for master and slave.

Dear Sveta, 5.0.22 is the latest version in .deb format on Ubuntu.  I /would/ go through the (non-trivial) effort of doing a source build to install 5.0.37, /if/ I thought that it might resolve the problem.

However, I read through all the changelogs from 5.0.22 up to 5.0.37, and as I already noted, there does not appear to be any bugfix for this or a similar issue.  Except for 26489, which is still open in 5.0.37.

Anyway, I will keep investigating locally, and update this bug if I get anywhere.

Dear Cameron,

you can use Linux binaries for Ubuntu.

By the way, please, provide your configuration files for both master and slave.

Hi again Sveta.

1. I have attached my server .cnf files as requested.
2. I have also pasted in below, the exact table definition I am using.
3. I am by now almost certain that it is a character-related issue, as I have now changed my app to Base64-encode the gzipped data before insertion into the LONGBLOB column, and vice-versa on the way out.  This is not the fastest solution, but it allows the replication to work perfectly!  I believe that this is because the base64 data contains a very limited range of characters.  This would fit with your developer's description of the incorrect charset assumption mysql currently makes when it inserts statements into the binary log.
4. Once I have put my current workaround onto our servers, I will have a moment to  install 5.0.37 on our test servers and retest this problem.   However from the

create table document_attachment
  	(
  		id bigint NOT NULL AUTO_INCREMENT,
  	    document_id bigint(20),
  		binary_data LONGBLOB,
  		description varchar(255),
  		type bigint(20),
  	 	mime_type varchar(100),
  	  	created_by varchar(100),
  	  	created_on TIMESTAMP,
	  	PRIMARY KEY(id),
	  	CONSTRAINT fk_document_attachment_document_id FOREIGN KEY(document_id) REFERENCES document(id),
	  	CONSTRAINT fk_document_attachment_type FOREIGN KEY(type) REFERENCES attachment_type(id),
	  	CONSTRAINT fk_document_attachment_created_by FOREIGN KEY(created_by) REFERENCES OS_USER(USERNAME) 
  	)ENGINE=InnoDB;

Thank you for the feedback.

Which charset do you use? Also check if value you insert into LONGBLOB column is less than max_allowed_packet.

Dear Sveta, with all due respect, I don't believe that your questions make sense...
1. As you can see from the attached .cnf files, I did not specify a charset.  Therefore the server will use the mysql default, latin1.  I verified this via SELECT @@global.character_set_server
2. max_allowed_packet is irrelevant in this situation, as it is only used by the driver, at the moment in which the client app performs the initial insert into the MASTER.  If my binary data had exceeded max_allowed_packet, then my app would not have been able to perform the initial insert into the master.    

In fact, as I already explained, the initial insert always succeeds.  The problem happens with the binary log, which is a sequence of SQL statements.   Hypothetically, the attachment size could be the source of the problem, if the attachment exceeded the remaining available space in the current master binary log.   However...
 a. According to the MySQL manual, in this situation MySQL will always expand the log as necessary to be able to cleanly write the current transaction.
 b. In any case, I am able to reproduce this problem with ver small sizes of binary data (ex. 16k before applying compression).

Dear Cameron,

>  b. In any case, I am able to reproduce this problem with ver small sizes of
> binary data (ex. 16k before applying compression).

Could you please provide your data and table structures to we can try to repeat it.

We already tried with ourself data without success.

Also at least one charset bug has fixed in 5.0.36/5.0.37 versions. It is why I ask you to upgrade. See also http://lists.mysql.com/announce/443

I have just uploaded dumps from which you should be able to reproduce the problem. They include the relevant binlog from the MASTER in each case. If you can't reproduce with 5.0.36, that would indicate that the problem is fixed in this problem.

Note that, these dumps were produced on the same MASTER-SLAVE pair.  Before producing each one, I ran through the following steps.
1. Reset SLAVE to point at master's current log pos.
2. Rebuild MASTER tables cleanly, from our application's release script.
3. Verify that, up to this point, the SLAVE has been replicating successfully.
4. Via our application, insert the binary record (in the column document_attachment.binary_data).

In step 4, the original document (a small JPEG image) was the same file.  The only difference was that in the BAD case, this file's data was GZIP-compressed before insertion.  Whereas in the GOOD case, the file's data GZIP-compressed AND THEN base64-encoded, before insertion into the DB.

Thank you for the files.

I can not repeat error on 32-bit machine even with 5.0.22 version. Please indicate if you run 64 or 32-bit binaries. Also please provide exact name of package you downloaded.

The exact version is 5.0.22-Debian_0ubuntu6.06.2-log.deb, as indicated already in the Version field of this bug report.

It is a 32-bit version on a 32-bit processor and Operating System.  Do you want the linux kernel version?

Tried with current 5.0.24a-Debian_9ubuntu0.1-log and can not repeat.

So I'll close this report as "Can't repeat". If you meet this problem again after upgrading feel free to reopen the report.