Search code examples
mysqltruncategroup-concat

MySQL Group_Concat still truncating results after group_concat_max_len is increased

I'm dynamically pivoting a table in MySQL (well in php but 1 step at a time).

My concat looks like such:

SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(name = "', p.name,'",te.hours, 0)) AS  "', p.name, '"')) 
FROM time_entries te 
inner join projects p on p.id = te.project_id 
where te.spent_on >= '2016-07-01'; -- test date

My return is truncated quite early (some info redacted but same # of chars):

MAX(IF(name = "redact",te.hours, 0)) AS  "redact",MAX(IF(name = "red",te.hours, 0)) AS  "red",MAX(IF(name = "redactednamebutsamel",te.hours, 0)) AS  "redactednamebutsamel",MAX(IF(name = "red",te.hours, 0)) AS  "red",MAX(IF(name = "redactednamebutsameleng...

Now I know all about max_allowed_packet and group_concat_max_len, both are set to 16777216 on the server (found by running select queries on them, e.g Select @@max_allowed_packet;)

Here's the kicker, in the early phases of getting this right, I definitely returned the full string. But I can't at all reproduce the result.

Is there a glaring issue with my code? I am pretty new to MySQL and most of this was stitched together from google searches.

Thanks.

Edit: Issue must be elsewhere. I was able to view the full string and it seems everything is indeed there. Thank you.

Solution

  • What tools / languages are you using?

    It is quite usual for e.g. phpMyAdmin to show the text truncated in row view and only the full text when viewing a record.