2

I have a string with concatenated values done on MySQL as below:

CONCAT("[\"",GROUP_CONCAT(DISTINCT(concat_ws("=>",val_1, val_2,val_3)) ORDER BY val_4 DESC SEPARATOR "\",\""),"\"]")

I have noticed that the output of each row seems incomplete, from a var_dump it seems like that:

"["CONFECTIONERY=>BISCUITS=>CRACKERS",
  "CONFECTIONERY=>BISCUITS=>COOKIES",
  "ORIENTAL GROCERY=>KOREAN GROCERY=>KOREAN FOOD (DRY, PACKET)",
  "BEVERAGE=>COFFEE=>PREMIX COFFEE POWDER",
  "CLEANING AGENT & PEST CONTROL=>LAUNDRY=>LAUNDRY LIQUID",
  "CLEANING AGENT & PEST CONTROL=>LAUNDRY=>LAUNDRY POWDER",
  "BEVERAGE=>TEA=>WESTERN TEA",
  "CEREAL & SPREADS=>CEREAL=>"

It is missing the ending square bracket and also the third element after the => character. And also the other lines are interrupted at the same position. If I try to json_decode in PHP this output I get NULL probably because is not well encoded, since the longest lines (more than 345 characters) are cutted.

Or maybe is a problem related to the length of the string? Cause I have noticed that are very long and they interrupt at the same position as characters. See the screenshot below of the output on mysqlworkbench: enter image description here

I have also counted and exactly each row has 345 characters and it breaks at the same point. It doesn't seems a coincidence:

enter image description here

From what I can see I should have escaped correctly the characters. Any suggestions?

UgoL
  • 839
  • 2
  • 13
  • 37
  • [Do you have xdebug installed?](http://www.php.net/manual/en/function.var-dump.php#82419) If so, you may need to set the `xdebug.var_display_max_data` setting. –  Sep 08 '18 at 19:11
  • I don't have xdebug installed. And the problem related to length limit starts when I select the data from MySQL tables in mysqlworkbench. I supposed that is not correlated to the output data displayed within PHP. – UgoL Sep 08 '18 at 19:14
  • Could be a driver issue; are you checking for errors in php? Also see https://stackoverflow.com/a/20457466/5051310 –  Sep 08 '18 at 19:25
  • I think is a limit on MySQL side probably, cause the limit length starts from there, from the SELECT. – UgoL Sep 08 '18 at 19:31
  • 1
    aha: https://stackoverflow.com/questions/5545875/mysql-group-concat-not-bringing-entire-data –  Sep 08 '18 at 19:37
  • hmmmm.. it could be my case! let me check it.. – UgoL Sep 08 '18 at 19:41
  • If I put SET GLOBAL group_concat_max_len = 9999999; before the selection I encounter the privileges error... it tells that I need the SUPER privileges for this operation.. – UgoL Sep 08 '18 at 19:49
  • 1
    that sounds like a new question (possibly answered [here](https://stackoverflow.com/a/6950265/5051310)). try `SET SESSION` –  Sep 08 '18 at 19:52
  • 1
    @Terminus SESSION works!! Thank you – UgoL Sep 08 '18 at 19:55

1 Answers1

0

Its not problem with escaping or php code, it's a mysql server limitation added to grouping mechanism function, for this example group_concat.

For more information read manual: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len

You can change this sys_option for this query or globally, but grouping that so many data on db side is a bad idea.

Daredzik
  • 422
  • 2
  • 9
  • 21