I'm using GROUP_CONCAT like so:
GROUP_CONCAT(game_url".$urlPrefix." SEPARATOR '^ft^') AS platform_urls
All is fine, however I've recently discovered that results returned in this manner are truncated after 1024 characters. Initially I thought this was due to the length of the game_url/game_url_uk fields (VARCHARS, 1024). However, after increasing this to 2048, I'm still seeing truncated results.
Unless there's some query caching happening on the server end that I'm not aware of - how can I fix this?
May I ask why you're doing this in the first place?
May I ask why you're doing this in the first place?
To collect a list of urls in one query, without several. See this thread for more details.
The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:
SET [SESSION | GLOBAL] group_concat_max_len = val;
The type returned by GROUP_CONCAT() is always VARCHAR unless group_concat_max_len is greater than 512, in which case, it returns a BLOB.
- Source
On my default MySQL installation in Windows, max_allowed_packet appears to be set to 1MiB (~1M).
Ah. RTFM is always a good idea. I have no concept of SET or performing configuration changes 'at runtime', like this. Is it just a case of executing the SET command with each query? Or is it advisable to make a change to server configuration files, instead?
I've had a look at the SET manual page, but would be thankful for slightly more experienced opinions.
EDIT: For the moment, I'm using the following SQL query in the constructor of my database model class:
SET SESSION group_concat_max_len = 2048;
It's cleared the problem up nicely, and I'm considering it a temporary solution until I can get the server configuration updated! Are there any gotchas to this solution?