{"id":2534,"date":"2010-06-10T09:16:14","date_gmt":"2010-06-10T07:16:14","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2534"},"modified":"2010-06-10T09:16:14","modified_gmt":"2010-06-10T07:16:14","slug":"verifying-group_concat-limit-without-using-variables","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/verifying-group_concat-limit-without-using-variables","title":{"rendered":"Verifying GROUP_CONCAT limit without using variables"},"content":{"rendered":"

I have a case where I must know if group_concat_max_len<\/strong> is at its default value (1024<\/strong>), which means there are some operation I cannot work out. I’ve ranted on this here<\/a>.<\/p>\n

Normally, I would simply:<\/p>\n

\n
\r\nSELECT @@group_concat_max_len\r\n<\/pre>\n<\/blockquote>\n
However, I am using views, where session variables are not allowed. Using a stored function can do the trick<\/a>, but I wanted to avoid stored routines. So here’s a very simple test case: is the current group_concat_max_len<\/strong> long enough or not? I’ll present the long version and the short version.<\/p>\n
The long version<\/h4>\n
\n
\r\nSELECT\r\n  CHAR_LENGTH(\r\n    GROUP_CONCAT(\r\n      COLLATION_NAME SEPARATOR ''\r\n    )\r\n  )\r\nFROM\r\n  INFORMATION_SCHEMA.COLLATIONS;\r\n<\/pre>\n<\/blockquote>\n
If the result is 1024<\/strong>, we are in a bad shape. I happen to know that the total length of collation names is above 1800<\/strong>, and so it is trimmed down. Another variance of the above query would be:<\/p>\n
\n
\r\nSELECT\r\n  CHAR_LENGTH(\r\n    GROUP_CONCAT(\r\n      COLLATION_NAME SEPARATOR ''\r\n    )\r\n  ) = SUM(CHAR_LENGTH(COLLATION_NAME))\r\n    AS group_concat_max_len_is_long_enough\r\nFROM\r\n  INFORMATION_SCHEMA.COLLATIONS;\r\n\r\n+-------------------------------------+\r\n| group_concat_max_len_is_long_enough |\r\n+-------------------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n+-------------------------------------+\r\n<\/pre>\n<\/blockquote>\n
The COLLATIONS<\/strong>, CHARACTER_SETS<\/strong> or COLLATION_CHARACTER_SET_APPLICABILITY<\/strong> tables provide with known to exist variables (assuming you did not compile MySQL with particular charsets). It’s possible to CONCAT<\/strong>, UNION<\/strong> or JOIN<\/strong> columns and tables to detect longer than 1800<\/strong> characters in group_concat_max_len<\/strong>. I admit this is becoming ugly, so let’s move on.<\/p>\n
The short version<\/h4>\n
Don’t want to rely on existing tables? Not sure what values to expect? Look at this:<\/p>\n
\n
\r\nSELECT CHAR_LENGTH(GROUP_CONCAT(REPEAT('0', 1025))) FROM DUAL\r\n<\/pre>\n<\/blockquote>\n
GROUP_CONCAT<\/strong> doesn’t really care about the number of rows. In the above example, I’m using a single row (retrieved from the DUAL<\/strong> virtual table), making sure it is long enough. Type in any number in place of 1025<\/strong>, and you have a metric for your group_concat_max_len<\/strong>.<\/p>\n
\n
\r\nSELECT\r\n  CHAR_LENGTH(GROUP_CONCAT(REPEAT('0', 32768))) >= 32768 As group_concat_max_len_is_long_enough\r\nFROM\r\n  DUAL;\r\n+-------------------------------------+\r\n| group_concat_max_len_is_long_enough |\r\n+-------------------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n+-------------------------------------+\r\n<\/pre>\n<\/blockquote>\n
The above makes a computation with REPEAT<\/strong>. One can replace this with a big constant.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,24,21],"class_list":["post-2534","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-information_schema","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-ES","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2534","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2534"}],"version-history":[{"count":24,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2534\/revisions"}],"predecessor-version":[{"id":2579,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2534\/revisions\/2579"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2534"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2534"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2534"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}