Allegro.cc - Online Community

Allegro.cc Forums » Programming Questions » [PHP/SQL] GROUP_CONCAT truncation problem

This thread is locked; no one can reply to it. rss feed Print
[PHP/SQL] GROUP_CONCAT truncation problem
ngiacomelli
Member #5,114
October 2004
Posted on 10/29/2008 6:37 AM   View Profile

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?

Fladimir da Gorf
Member #1,565
October 2001
avatar
Posted on 10/29/2008 6:41 AM   View Profile

May I ask why you're doing this in the first place?

OpenLayer has reached a random SVN version number ;) | Online manual | Installation video!| MSVC projects now possible with cmake | Now alvailable as a Dev-C++ Devpack! (Thanks to Kotori)

ngiacomelli
Member #5,114
October 2004
Posted on 10/29/2008 7:04 AM   View Profile
Fladimir da Gorf said:

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.

bamccaig
Member #7,536
July 2006
avatar
Posted on 10/29/2008 8:26 AM   View Profile
MySQL :: MySQL 5.1 Reference Manual :: 11.12.1 GROUP BY (Aggregate) Functions#GROUP_CONCAT(expr) said:

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).

???

--
I mean the best with what I say. It doesn't always sound that way.

acc.js | al4anim - Allegro 4 Animation library | Allegro 5 VS/NuGet Guide | Allegro.cc Mockup | Allegro.cc <code> Tag | Allegro 4 Timer Example (w/ Semaphores) | Allegro 5 "Winpkg" (MSVC readme) | Bambot | Blog | C++ STL Container Flowchart | Castopulence Software | Check Return Values | Derail? | Is This A Discussion? Flow Chart | Filesystem Hierarchy Standard | Clean Code Talks - Global State and Singletons | How To Use Header Files | GNU/Linux (Debian, Fedora, Gentoo) | rot (rot13, rot47, rotN) | Streaming

ngiacomelli
Member #5,114
October 2004
Posted on 10/29/2008 8:43 AM   View Profile

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?

Go to: