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 15:17	Modified:	22 Mar 2007 12:24
Reporter:	Cameron Smith
Status:	Can't repeat
Category:	Server: Replication	Severity:	S2 (Serious)
Version:	5.0.22-Debian_0ubuntu6.06.2-log	OS:	Linux (Ubuntu 6.06.1 LTS)
Assigned to:		Target Version:

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.