PHP Fatal error: 1055 p.products_image

Hi Guys,

I was hoping you could offer a tip or 2 on how to fix the following issue. I have searched the forums without any luck so I am assuming it may of been caused by my actions when adding a mod rather than a script issue. I just noticed a large number of error log files similar to the one below, the only variance between the errors is the product id. I have replaced paths and DB names with xxx's.

[17-Nov-2015 00:33:25 UTC] PHP Fatal error: 1055:'xxx.p.products_image' isn't in GROUP BY :: select p.products_id, p.products_image
from orders_products opa, orders_products opb, orders o, products p
where opa.products_id = '678'
and opa.orders_id = opb.orders_id
and opb.products_id != '678'
and opb.products_id = p.products_id
and opb.orders_id = o.orders_id
and p.products_status = 1
group by p.products_id
order by o.date_purchased desc
limit 6 ==> (as called by) /xxx/xxx/public_html/includes/modules/also_purchased_products.php on line 16 <== in /xxx/xxx/public_html/includes/classes/db/mysql/query_factory.php on line 155

Many Thanks for any assistance you can offer. :)

Rock-n-Roll:

I was hoping you could offer a tip or 2 on how to fix the following issue. I have searched the forums without any luck so I am assuming it may of been caused by my actions when adding a mod rather than a script issue.

Perhaps - but possibly not.

Rock-n-Roll:

I just noticed a large number of error log files similar to the one below, the only variance between the errors is the product id. I have replaced paths and DB names with xxx's.

[17-Nov-2015 00:33:25 UTC] PHP Fatal error: 1055:'xxx.p.products_image'

It may it my not be important, but I would have liked to seen the actual contents that you've replaced by these 'xxx'.

Rock-n-Roll:

select p.products_id, p.products_image
from orders_products opa, orders_products opb, orders o, products p
where opa.products_id = '678'
and opa.orders_id = opb.orders_id
and opb.products_id != '678'
and opb.products_id = p.products_id
and opb.orders_id = o.orders_id
and p.products_status = 1
group by p.products_id
order by o.date_purchased desc
limit 6

I've copied and pasted this into the SQL box in one of our own servers, and the SQL query itself appears to work just fine. I get the expected results when using a valid product_id for our store, and zero results (no errors) when using an id that doesn't exist.

This lead me to a Google search, which then led me to here:
http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

In other words, the problem looks like it may be related to a MySQL version issue.

It looks like there are three possible solutions

1. Revert back to an earlier version of MySQL
2. enable or disable the (apparently new) ONLY_FULL_GROUP_BY option (not sure which or how, at the moment) or
3. Update the SQL so that complies with the new requirements.

To prove/disprove this theory/possibilty, my approach would be

Load the file /includes/classes/db/mysql/define_queries.php into a text editor, and edit it to read

DEFINE('SQL_ALSO_PURCHASED', "select p.products_id, p.products_image
                     from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, "
                            . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p
                     where opa.products_id = '%s'
                     and opa.orders_id = opb.orders_id
                     and opb.products_id != '%s'
                     and opb.products_id = p.products_id
                     and opb.orders_id = o.orders_id
                     and p.products_status = 1
                     group by p.products_id[B] , p.products_image  [/B]
                     order by o.date_purchased desc
                     limit " . MAX_DISPLAY_ALSO_PURCHASED); 

IOW, add the stuff I've highlighted.

Please let me know your results. I could be barking up the wrong tree.

Cheers
RodG

Hi RodG,

Many thanks for your reply. :)

the full error log entry for the above was:

17-Nov-2015 00:33:25 UTC] PHP Fatal error: 1055:'xxx.p.products_image' isn't in GROUP BY :: select p.products_id, p.products_image
from orders_products opa, orders_products opb, orders o, products p
where opa.products_id = '678'
and opa.orders_id = opb.orders_id
and opb.products_id != '678'
and opb.products_id = p.products_id
and opb.orders_id = o.orders_id
and p.products_status = 1
group by p.products_id
order by o.date_purchased desc
limit 6 ==> (as called by) /home10/rockandr/public_html/includes/modules/also_purchased_products.php on line 16 <== in /home10/rockandr/public_html/includes/classes/db/mysql/query_factory.php on line 155

The initial error logs have stop when changing the define_queries.php to the highlights above, thank you! :) :)

Not sure if its related but I just found another issue when trying to add attributes to a product: query_factory.php on line 155 seem to be a common the common file and line error in both log entries?

[17-Nov-2015 01:55:23 UTC] PHP Fatal error: 1062:Duplicate entry '0' for key 'PRIMARY' :: insert into products_attributes (products_attributes_id, products_id, options_id, options_values_id, options_values_price, price_prefix, products_options_sort_order, product_attribute_is_free, products_attributes_weight, products_attributes_weight_prefix, attributes_display_only, attributes_default, attributes_discounted, attributes_image, attributes_price_base_included, attributes_price_onetime, attributes_price_factor, attributes_price_factor_offset, attributes_price_factor_onetime, attributes_price_factor_onetime_offset, attributes_qty_prices, attributes_qty_prices_onetime, attributes_price_words, attributes_price_words_free, attributes_price_letters, attributes_price_letters_free, attributes_required)
values (0,
'697',
'24',
'139',
'0',
'+',
'10',
'1',
'0',
'+',
'0',
'0',
'1',
'',
'1',
'0',
'0',
'0',
'0',
'0',
'',
'',
'0',
'0',
'0',
'0',
'0') ==> (as called by) /home10/rockandr/public_html/Jhau875HlG68Ty9KKuj/attributes_controller.php on line 340 <== in /home10/rockandr/public_html/includes/classes/db/mysql/query_factory.php on line 155

It seems my sites MySQL is failing on a number of different fronts? :(

Kind Regards

les :)

Rock-n-Roll:

The initial error logs have stop when changing the define_queries.php to the highlights above, thank you! :) :)

That's excellent news.

Note: DrByte, Ajeh, Zencart team - I realise that you may be aware of this problem with the later versions of MySQL, but if not, it is probably something to be aware of.

Rock-n-Roll:

Not sure if its related but I just found another issue when trying to add attributes to a product: query_factory.php on line 155 seem to be a common the common file and line error in both log entries?

My initial thoughts are that this would be an unrelated error - but I can't say for sure, and I need to head out of the office, so I've no time to research it.

Having said that, the fact that my 'fix' worked for your original problem has revealed a compatibility issue, so it wouldn't surprise me that there will be additional problems as a result.

Your best option at this point would be (if possible) revert back to an older version of MySQL, or look into what needs to be done with
ONLY_FULL_GROUP_BY option as a 'global' type fix and then see if the other errors go away.

Hopefully someone else can step in here with more info... I really must be leaving...

Cheers
RodG

The most recent set of errors is a result of the mode NO_AUTO_VALUE_ON_ZERO being set. One solution to this is to change the 0 for attribute_id to NULL, but some SQL servers do not like NULL and instead prefer ''. That said, because the field in question, an autonumber, increments automatically, completly omitting the attribute_id on insert mitigates any issue with the above and allows the database to manage providing the next value.

To me this makes sense, allow the database to identify the next number (the intention of providing 0, NULL, or '').

From review of the intended use of that mode, it appears as if it is more to prevent reassignment of values possibly intentionally set to 0 when the data is pulled and then subsequently pushed back to the server... This may be appropriate if the primary key of the data table is a combination of fields, but ZC predominantly uses the autnumber as a primary key. (In attributes, there is a case where a previously autogenerated number is used in conjunction with the language_id to serve as the primary key.) But long and short, for the base install of ZC, this mode is not needed and not preferred...

Hi mc,

Many thanks for all your suggestions.