MySQL Bugs: #47307: slave breaks if larger session group_concat_max

Bug #47307	slave breaks if larger session group_concat_max_len is set on master
Submitted:	14 Sep 2009 11:15	Modified:	13 Oct 2009 0:46
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.66, 5.1.38, 5.0, 5.1, next bzr	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any

Description:
when setting a larger session value for group_concat_max_len, the master will accept the query, but the slave breaks with:

090914 13:08:04 [ERROR] Slave SQL: Error '%d line(s) were cut by GROUP_CONCAT()' on query. Default database: 'test'. Query: 'insert into t1 select group_concat(a)

How to repeat:
run master: mysqld --no-defaults --console --skip-grant-tables --skip-name-resolve --log-bin --server-id=1 --sql_mode=strict_all_tables

run slave: mysqld --no-defaults --console --skip-grant-tables --skip-name-resolve  --server-id=2 --port=3307 --sql_mode=strict_all_tables

on slave, enable replication: change master to master_host='127.0.0.1', master_port=3306, master_user='root', master_password='';
start slave;

on master: 

delimiter //
drop procedure if exists p1 //
create procedure p1()
begin

set session group_concat_max_len=1024*100*1024;

drop table if exists t1,t2;
create table t1(a varchar(20000))engine=innodb;
create table t2(a varchar(20000),b varchar(20000))engine=innodb;
insert into t2 values (repeat('a',2000),repeat('b',2000));
insert into t2 values (repeat('c',2000),repeat('d',2000));
insert into t1 select group_concat(a) from t2 group by b;

end //

delimiter ;

call p1();

Suggested fix:
fix: make sure the set session group_concat_max_len.. enters the binlog.
workaround 1: set group_concat_max_len = max_allowed_packet on both master and slave.
workaround 2: use row based binlogging

Thank you for the report.

Verified as described.

a side note: i'm quite sure the error message "Error '%d line(s).." should get an argument tell how many lines are cut ? users don't know what %d is.

See also: BUG#31168.

Related: BUG#36785.

ANALYSIS
========

  Issue #1
  --------

  The master server is using SBL and during its workload, it sets
  the system session variable:

  "set session group_concat_max_len=1024*100*1024;"

  Later in the execution, it executes

  "[...]
   insert into t2 values (repeat('c',2000),repeat('d',2000));
   insert into t1 select group_concat(a) from t2 group by b;
   [...]"

  This statement works on the master, but breaks the slave. Why?
  Because the slave will not set the group_concat_max_len, since
  it is not one of the listed variables that are supported in
  statement based replication:

  http://dev.mysql.com/doc/refman/5.1/en/replication-features-variables.html

  As a consequence the slave does not set dynamically the new
  variable value, and when it replays the offending statement
  it raises an error.

  Issue #2
  --------
 
  Given Issue #1, the slave fails with an incomplete error
  message in the error log:

  "090914 13:08:04 [ERROR] Slave SQL: Error '%d line(s) were cut
   by GROUP_CONCAT()' on query."

DECISIONS
=========

  1. Issue #1 is related to the limited support for replication
     of system variables when using statement based
     replication (SBR).  For additional details, see:

     - http://dev.mysql.com/doc/refman/5.1/en/replication-features-variables.html    
     - http://dev.mysql.com/doc/refman/5.1/en/binary-log.html

     We will not fix issues related to replication of system
     variables in individual bug reports, instead we are
     analyzing the entire set of server variables and how to
     solve their replication issues in SBR.

     Fix should be the outcome from WL#5135 and be released in a
     future server version (not 5.1 GA).

  2. Issue #2 was already addressed in BUG#36785. The fix will be
     released in a future version (not 5.1 GA).