MySQL query returning truncated results could be because of group_concat

I recently encountered an issue where my MySQL query was returning truncated results… and by truncated I mean it was returning only a fraction of the rows it should have been returning and the last of those rows was actually missing data.

The query in question uses a few LEFT OUTER JOIN(s) and the GROUP_CONCAT function to eliminate duplicate results due to a many-to-one relationship as shown below:

Turns out that the results were getting truncated due to a GROUP_CONCAT memory limitation configured by: group_concat_max_len

I therefore had a few options:

• Remove GROUP_CONCAT from the query and deal with the duplicate results programmatically
• Split up the query in perhaps 2 or 3 individual queries
• Overwrite the group_concat_max_len variable in PHP for the session

I chose the latter by adding the following snippet in my DB configuration file.

if (!mysqli_query($link, 'SET SESSION group_concat_max_len = 102400;')) {
	$error = 'Error setting group_concat_max_len: ' . mysqli_error($link);
	include $_SERVER['DOCUMENT_ROOT'].'/error.html.php';
	exit();
}

I ended up setting the value to 102400 bytes which was about the max size I could foresee the group_concat having to process.

You can check your MySQL value was by running the following in the terminal:
$ mysqld --verbose --help

Hope this helps someone!