your database front-end

MySQL group_concat() Length Exceeded

On MySQL, dbFront will use group_concat() to retrieve some results, especially when dealing with multi-selects 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 an 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 an M-M join might result in a VERY large dataset.  For example, an invoice would normally only contain a few product items.  So a multi-select 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 multi-select would not be reasonable.  The solution would be to remove the multi-select field from the product form.

Content you want the user to see goes here.
close