Mysql: Bug fixing - GROUP_CONCAT

Problem

GROUP_CONCAT can not get complete data from DB

Reason

Default limit length of GROUP_CONCAT: 1024

Solution

1. SET SESSION group_concat_max_len=102400; SET GLOBAL group_concat_max_len=102400;

   • SET SESSION: this setting will work immediately, but affect only for current session.
   • SET GLOBAL: this setting will work after restarting the Mysql server.
   • Checking: SHOW VARIABLES LIKE '%group_concat%';

   So, we should use them together.

   Attention: the upper limit of max_length depends on the parameter "max_allowed_packet"

   To check: SHOW VARIABLES LIKE '%max_allowed_packet%';

2. Change the my.cnf config file.

   • find the file in /etc/my.cnf(Address different possible)
   • increase the group_concat_max_len = 102400
   • Checking: SHOW VARIABLES LIKE '%group_concat%';