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!