the web front-end for your database

MySQL group_concat() Length Exceeded

On MySQL, dbFront will use group_concat() to retrieve some results, expecially when dealing with multiselects for M-M relationships.  If the amount of data returned exceeds the default max length set by MySQL then the data will get truncated.  dbFront monitors for the data truncation situation and if needed will raise an error similar to the following. 

  • Can't load form parent info, Incomplete M-M item data returned, expected 17786 values, but got 171.
  • For more help see: https://dbfront.com/MySQL_GroupConcat_Exceeded.

Solutions

There are two solutions.

1) Update the Max

The first solution is to update the MySQL settings to extend the amount of data returned by group_concat().   The temptation might be to extend the MAX to a excessively large value but that would be a mistake since it could cause MySQL to choke on a poorly written query.  The best advice would be to double the current max to see if that resolves the issue.

This can be done by running the following statement:

SET GLOBAL group_concat_max_len=4294967295;

For more details see: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len

2) Remove the problem Multiselect field.

Sometimes, one side of a M-M join might result in a VERY large dataset.  For example: And invoice would normally only contain a few products items.  So a multiselect might make sense.  But the other side of the M-M join would contain all invoices that ever contained a specific product item.  For that side of the M-M join, a multiselect would not be reasonable.

Content you want the user to see goes here.
close