All Products
Search
Document Center

Issues related to the group_concat function in apsaradb RDS for MySQL instances

Last Updated: Nov 23, 2020

Overview

This article describes issues related to the group_concat function in RDS for MySQL.

Description

Alibaba Cloud reminds you that:

  • Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

The following describes the issues related to the group_concat function in RDS for MySQL.

The length of the returned group_concat file.

The length of the result returned by the group_concat function is controlled by the group_concat_max_len parameter. The default value is 1024, that is, 1024 bytes in length is returned by default. The parameter values are as follows:

  • Parameter: group_concat_max_len
  • Default value: 1024
  • Value range: 4-1844674407370954752
  • Note: The maximum length of the result returned by the group_concat function, in bytes.

(: can reconfigure parameters the in the RDS group_concat_max_len global entry into force or session-level entry into force of the, the following is the Global Entry into force and session-level entry into force of the details:

  • Global settings: you can modify the parameters on the parameter settings page in the console.
  • The session-level effective commands are as follows. The first SQL statement sets the group_concat_max_len the current session to 90 bytes. The second SQL statement is to view the group_concat_max_len value of the current session. The third SQL statement is the result returned by group_conca. The fourth SQL statement is the length of the result returned by group_concat.
    set group_concat_max_len=90;
    show variables like 'group_concat_max_len'; 
    select group_concat(distinct concat_ws(' ', t1.col0, t1.col2, t1.col3, t1.col4) separator "---")
    from grp_con_test t1,
         grp_con_test t2 \G
    select length(group_concat(distinct concat_ws(' ', t1.col0, t1.col2, t1.col3, t1.col4) separator "---"))
    from grp_con_test t1,
         grp_con_test t2 \G
    The following command output is returned.
    当前会话生效

group_concat(distinct) removes duplicate data failures

The following lists the causes and solutions for duplicate data failures.

Cause of failure

If you set the group_concat_max_len to a large value, you cannot run the group_concat(distinct) command to remove duplicate data in the result.
select group_concat(distinct concat_ws(' ', t1.col0, t1.col2, t1.col3, t1.col4) separator "---")
from grp_con_test t1,
   grp_con_test t2 \G
A similar output is displayed. Multiple duplicate values are displayed. This issue occurs because when the group_concat result set returned by group_concat is large, the temporary table in memory cannot hold all the data in the result set, and then the temporary table on disk is used. However, group_concat triggers a Bug when using the disk temporary table, causing it to fail to remove duplicate data.
数据重复

Fixes

Run the following commands in sequence to modify the parameter settings of the tmp_table_size and increase the maximum size of the temporary table in the memory: The first SQL statement sets the current session tmp_table_size to 1MB. The second SQL statement is to view the tmp_table_size settings of the current session.
set tmp_table_size=1*1024*1024
show variables like 'tmp_table_size'
select group_concat(distinct concat_ws(' ', t1.col0, t1.col2, t1.col3, t1.col4) separator "---")
from grp_con_test t1,
     grp_con_test t2 \G
Note: you can modify the tmp_table_size parameters on the parameter settings page in the console.

group_concat and concat returns an exception

The following describes the causes for and solutions to errors that are returned when group_concat and group_concat are used in combination.

Cause

group_concat and concat can be used in combination to return BLOB field types, as shown below.
select concat('{', group_concat(concat('\"payMoney', t.signature, '\":', ifnull(t.money, 0))), '}') payType
from my_money t
where cash_id='989898989898998898'
group by cash_id;
The command output is as follows. This is because concat returns the results by byte. If concat's input has multiple types, the results are unpredictable.返回BLOB字段

Fixes

The cast function is used to constrain the result. The return value of concat is of the string type. Modify the content in the example described in the previous section as follows:
select concat('{', cast(group_concat(concat('\"payMoney', t.signature, '\":', IFNULL(t.money, 0))) as char), '}') payType
from my_money y t
where cash_id='989898989898998898'
group by cash_id;
The following command output is returned.

Application scope

  • ApsaraDB RDS for MySQL