Verifying GROUP_CONCAT limit without using variables

June 10, 2010

I have a case where I must know if group_concat_max_len is at its default value (1024), which means there are some operation I cannot work out. I've ranted on this here.

Normally, I would simply:

SELECT @@group_concat_max_len

However, I am using views, where session variables are not allowed. Using a stored function can do the trick, but I wanted to avoid stored routines. So here's a very simple test case: is the current group_concat_max_len long enough or not? I'll present the long version and the short version.

The long version

SELECT
  CHAR_LENGTH(
    GROUP_CONCAT(
      COLLATION_NAME SEPARATOR ''
    )
  )
FROM
  INFORMATION_SCHEMA.COLLATIONS;

If the result is 1024, we are in a bad shape. I happen to know that the total length of collation names is above 1800, and so it is trimmed down. Another variance of the above query would be:

SELECT
  CHAR_LENGTH(
    GROUP_CONCAT(
      COLLATION_NAME SEPARATOR ''
    )
  ) = SUM(CHAR_LENGTH(COLLATION_NAME))
    AS group_concat_max_len_is_long_enough
FROM
  INFORMATION_SCHEMA.COLLATIONS;

+-------------------------------------+
| group_concat_max_len_is_long_enough |
+-------------------------------------+
|                                   0 |
+-------------------------------------+

The COLLATIONS, CHARACTER_SETS or COLLATION_CHARACTER_SET_APPLICABILITY tables provide with known to exist variables (assuming you did not compile MySQL with particular charsets). It's possible to CONCAT, UNION or JOIN columns and tables to detect longer than 1800 characters in group_concat_max_len. I admit this is becoming ugly, so let's move on.

The short version

Don't want to rely on existing tables? Not sure what values to expect? Look at this:

SELECT CHAR_LENGTH(GROUP_CONCAT(REPEAT('0', 1025))) FROM DUAL

GROUP_CONCAT doesn't really care about the number of rows. In the above example, I'm using a single row (retrieved from the DUAL virtual table), making sure it is long enough. Type in any number in place of 1025, and you have a metric for your group_concat_max_len.

SELECT
  CHAR_LENGTH(GROUP_CONCAT(REPEAT('0', 32768))) >= 32768 As group_concat_max_len_is_long_enough
FROM
  DUAL;
+-------------------------------------+
| group_concat_max_len_is_long_enough |
+-------------------------------------+
|                                   0 |
+-------------------------------------+

The above makes a computation with REPEAT. One can replace this with a big constant.

tags: , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

11 Comments to "Verifying GROUP_CONCAT limit without using variables"

  1. Roland Bouman wrote:

    Hi Shlomi,

    why not simply use one of the %_VARIABLES views in the information_schema?

    mysql> select * from information_schema.global_variables where variable_name = 'group_concat_max_len';
    +----------------------+----------------+
    | VARIABLE_NAME | VARIABLE_VALUE |
    +----------------------+----------------+
    | GROUP_CONCAT_MAX_LEN | 1024 |
    +----------------------+----------------+
    1 row in set (0.00 sec)

  2. shlomi wrote:

    Hi Roland,
    Right, didn't even mention it... :D
    Only available as of 5.1, while I have a requirement for 5.0 - 5.1.

    Thank you for noting this down.

  3. Sheeri Cabral wrote:

    Why not just set the group_concat_max_len variable for the session so that you won't have an issue with the operation no matter what?

  4. shlomi wrote:

    Hi Sheeri,
    Fair question. Actually, it all comes up by issues I'm having with developing the openark-kit & mycheckpoint, two open source projects I'm working on.
    The problem is, I have no idea who will install these, what their MySQL expertise will be; will they have privileges to change these settings, and so on.
    I want the tools to handle these cases where the settings are insufficient.

    Who knows how the next installation will go? Since the defaults are so low, how can I be sure that the next machine anyone sets up will have a manual setting for group_concat_max_len?

    As long as the machines are mine, or under my control, yes - I just simple set up the variables as I please.

  5. Sheeri Cabral wrote:

    Shlomi -- I figured it was in those tools. But if it's just the tool checking a few things, then setting it *within* the tool will ensure that the tool always has it set appropriately. I'm talking about setting the *session* variable, not the *global* variable.

  6. shlomi wrote:

    @Sheeri,
    I figured you might have figured it ;), but there's more to it.
    The mycheckpoint monitoring tool works in a special way. The whole point of the tool is to create a sophisticated (if I may say so) views hierarchy; such that for getting monitored data (reports, charts, full blown HTML pages) -- you don't actually use the tool; you just query for the data, e.g.
    SELECT html FROM sv_report_html_brief;
    or
    SELECT report FROM sv_report_human_sample;

    So - no tool here. Nowhere to set the session variable. I hastily dropped in the openark-kit. You are correct: I do not have this issue there.

  7. Justin Swanhart wrote:

    It is kind of (well especially, perhaps) evil, but you could create a stored function that sets the session variables. Then instruct users to select from a view that invokes that SP before they select from any other views.

    DELIMITER ;;
    DROP FUNCTION IF EXISTS test.set_gc_len;;

    CREATE FUNCTION test.set_gc_len (
    )
    RETURNS TINYTEXT
    NO SQL
    BEGIN
    SET SESSION group_concat_max_len=65535;
    RETURN '';
    END
    ;;

    DELIMITER ;

    CREATE VIEW `test`.`z1` AS select group_concat(`test`.`set_gc_len`() separator ',') AS `group_concat(test.set_gc_len())`,(char_length(group_concat(repeat('0',32768) separator ',')) >= 32768) AS `group_concat_max_len_is_long_enough`;

    -- this first call to the view increases the max GC len
    mysql> select * from test.z1\G
    *************************** 1. row ***************************
    group_concat(test.set_gc_len()):
    group_concat_max_len_is_long_enough: 0
    1 row in set, 1 warning (0.00 sec)

    mysql> select * from test.z1\G
    *************************** 1. row ***************************
    group_concat(test.set_gc_len()):
    group_concat_max_len_is_long_enough: 1
    1 row in set (0.01 sec)

  8. Justin Swanhart wrote:

    Oh, and you don't have to call it in group_concat, it can just be a select from dual.. I was just trying to trick MySQL into somehow evaluating the session var change in the same SQL statement, but that doesn't work.

  9. shlomi wrote:

    @Justin,

    Yes, I was referring to this type of solution on "...Using a stored function can do the trick...".

    It *is* somewhat evil :)

  10. SQL trick: overcoming GROUP_CONCAT limitation in special cases | code.openark.org wrote:

    [...] Verifying GROUP_CONCAT limit without using variables [...]

  11. Mik wrote:

    Use SUBSTRING_INDEX for GROUP_COCAT result to limit count of grouping items

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org