Database update error: Problematic GROUP BY clause

Any update on this issue?

Any chance you could test this patch?

Same patch, removing an unused variable.

No change, sorry. Here is the new error message:

>  [notice] Update started: commerce_pricelist_update_8206
>  [error]  SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'drupal_kuovi_test.cpi.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT "cpi"."id" AS "id", "cpi"."price_list_id" AS "price_list_id", "cpi"."purchasable_entity" AS "purchasable_entity", "cpi"."quantity" AS "quantity", "cpi"."price__currency_code" AS "price__currency_code", COUNT(*) AS "cnt"
> FROM
> "drupal_commerce_pricelist_item" "cpi"
> GROUP BY "type", "price_list_id", "purchasable_entity", "quantity", "price__currency_code"
> HAVING (cnt > 1); Array
> (
> )
>
>  [error]  Update failed: commerce_pricelist_update_8206

Using group by in MySQL with only_full_group_by requires all columns to be part of the group or being aggregated in any way, like count(). The column ID was not aggregated like that, thus the resulting error.

The attached patch should do the trick.

I don't understand the purpose of the update hooks, though.

commerce_pricelist_update_8206(): If there are any results, the storage schema will not be set and the message "Update to the commerce_pricelist_item schema was skipped due to duplicate prices." will be returned instead.

commerce_pricelist_update_8207(): Will try to set the storage schema regardless of what commerce_pricelist_update_8206() did. As I see it, assumed the query in update 8206 returned a result before, update 8207 WILL catch an exception "Integrity constraint violation: Duplicate entry" which is returned as a message.

In this case the update does essentially nothing and returns error messages, which gives the impression that something is missing or didn't work out, but the update cannot be repeated without further ado.

I think providing an informal message or view, in case of duplicate products with same quantity and currency, would be a better approach and gives the user the opportunity to act on it.

Thanks, works fine!

I think providing an informal message or view, in case of duplicate products with same quantity and currency, would be a better approach and gives the user the opportunity to act on it.

Then how do you change the schema and add the constraint? The update used to delete prices, but I then rolled back this change as I was concerned about deleting prices without the customer knowledge.

Or maybe we should just apply the schema change, and have it fail so the update can be re-ran.

Do you need a schema with this constraint at all?

If yes: Since you cannot decide which of the duplicates should be removed, maybe you could provide a form, that can be submitted once, if the conditions for setting the schema are met. But I don't know if there are best practices for adding schemas afterward, like in this case.

But maybe it's sufficient to display a message like "We have duplicates detected, please check" and let the user deal with it? Just a thought.

Do you need a schema with this constraint at all?

Well, to me having the possibility to set prices for the same quantity, for the same product variation within the same pricelist is a bug, and shouldn't be supported.

On a project where I have custom logic for tracking price history/price changes, the logic failed as there was a different price returned/detected everytime.

But maybe it's sufficient to display a message like "We have duplicates detected, please check" and let the user deal with it? Just a thought.

This is precisely what we're doing?

Update to the commerce_pricelist_item schema was skipped due to duplicate prices.

This is precisely what we're doing?

Yes, but only in update hook, where it's shown once. I thought about a message repeatedly shown in the backend, e.g. when editing a pricelist.

After using patch #6 (On Acquia):

[notice] Update started: commerce_pricelist_update_8206
>  [error]  Exception thrown while performing a schema update. SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.: ALTER TABLE "commerce_pricelist_item" ADD UNIQUE KEY `price_list_purchasable_currency` (`type`, `price_list_id`, `purchasable_entity`, `quantity`, `price__currency_code`); Array
> (
> )
>
>  [error]  Update failed: commerce_pricelist_update_8206

The patch #6 fixed the error from me. The approach seems correct as well, i.e. select the fields that you're grouping by.

Committed, thanks @ktpm!

Automatically closed - issue fixed for 2 weeks with no activity.

I filled a regression on PostgreSQL, caused by this issue.

🐛 Undefined function: function group_concat(integer) does not exist on PostgreSQL Active

Thanks!