mc-magento2: Unknown column 'at_special_from_date_default.value' in 'on clause'
Versions installed
ebizmarts/mailchimp-lib 3.0.21
mailchimp/mc-magento2 1.0.28
Problem: In cron_schedule and /var/www/var/log/magento.cron.log a lot de errors
SELECT e., IF(at_special_price.value_id > 0, at_special_price.value, at_special_price_default.value) AS special_price, at_special_from_date.value AS special_from_date, at_special_to_date.value AS special_to_date, mc. FROM catalog_product_entity AS e
LEFT JOIN catalog_product_entity_decimal AS at_special_price_default
ON (at_special_price_default.entity_id = e.entity_id) AND (at_special_price_default.attribute_id = ‘567’) AND at_special_price_default.store_id = 0
LEFT JOIN catalog_product_entity_decimal AS at_special_price
ON (at_special_price.entity_id = e.entity_id) AND (at_special_price.attribute_id = ‘567’) AND (at_special_price.store_id = 7)
LEFT JOIN catalog_product_entity_datetime AS at_special_from_date
ON (at_special_from_date.entity_id = e.entity_id)
AND (at_special_from_date.attribute_id = ‘568’)
AND (at_special_from_date.store_id = 0)
LEFT JOIN catalog_product_entity_datetime AS at_special_to_date
ON (at_special_to_date.entity_id = e.entity_id)
AND (at_special_to_date.attribute_id = ‘569’)
AND (at_special_to_date.store_id = 0)
LEFT JOIN mailchimp_sync_ecommerce AS mc ON mc.type = ‘PRO’
AND mc.related_id = e.entity_id
AND mc.mailchimp_sync_modified = 0
AND mc.mailchimp_store_id = ‘5cf8dfcd757d60254b1298e2bb26a2d3’
and mc.mailchimp_sync_delta < (IF(at_special_from_date.value_id > 0, at_special_from_date.value, at_special_from_date_default.value))
WHERE (IF(at_special_price.value_id > 0, at_special_price.value, at_special_price_default.value) > 0)
AND (((((at_special_from_date.value <= ‘2018-05-16 23:59:59’) OR (at_special_from_date.value IS null)))))
AND (((((at_special_to_date.value >= ‘2018-05-16 00:00:00’) OR (at_special_to_date.value IS null)))))
AND (mc.mailchimp_sync_delta is not null)
The problem is “Unknown column ‘at_special_from_date_default.value’ in ‘on clause’” I can’t find this table (or alias)
We have not synchronized with Mailchimp for 3 weeks
About this issue
- Original URL
- State: closed
- Created 6 years ago
- Comments: 45
Commits related to this issue
- closes #309 — committed to mailchimp/mc-magento2 by gonzaloebiz 6 years ago
- closes #309 — committed to mailchimp/mc-magento2 by gonzaloebiz 6 years ago
- Merge pull request #390 from mailchimp/Issue309 closes #309 — committed to mailchimp/mc-magento2 by gonzaloebiz 6 years ago
- Merge (#2) * add counters for resume the cron activity #361 * closes #361 * closes #361 * Move mailchimp_sales_model_service_quote_submit_after observer to frontend scope to avoid throwing ... — committed to maximbaibakov/mc-magento2 by maximbaibakov 6 years ago
- closes #309 — committed to maximbaibakov/mc-magento2 by gonzaloebiz 6 years ago
- closes #309 — committed to maximbaibakov/mc-magento2 by gonzaloebiz 6 years ago
Hi guys, I got a temporary solution for it and maybe someone else can make it better in the future. I will show my solution first and then explain the probable reason then. Solution: find the file :/app/code/Ebizmarts/MailChimp/Model/Api/Product.php. Search “at_special_from_date_default” or “at_special_from_date_default”, and it is up to which table your sql cannot find. It should be in an if condition like:
mc.mailchimp_sync_delta < (IF(at_special_from_date.value_id > 0, at_special_from_date.value, at_special_from_date_default.value))change it tomc.mailchimp_sync_delta < at_special_from_date.valuedoneI know I am lazy and just change the original code and even don’t want to create another custom model to overwrite it 😛. You can do that if you want. Now I will explain what I found:
When magento uses “$collection->addAttributeToFilter” to left join an attribute’s values from a table to a sql query, it seems try to create a default view like “at_special_from_date_default” to keep its store_id=0 values, and then creates another one like “at_special_from_date” to keep its store_id=x values. But it doesn’t create “at_special_from_date_default” for my website and saves all store_id=0 values to “at_special_from_date”. However, Ebizmarts assumes all of our websites have created both of them here, so the exception comes up then.
In my case, I don’t know why my website doesn’t use store_id=1 values for special_from_date. But I guess using stroe=0 values is fine to me because I only have one Website View and one Store, and all values for store=1 and =0 are the same. Anyway, it is not relevant to the attribute scope or default value because I have tested them. Maybe someone else can explain it? And hopefully someone can fix that piece of String so it will concern if “at_special_from_date_default” has generated or not.
Hi @antoniogarcia78 , I have the same problem:
Versions: Magento 2.2.3 ebizmarts/mailchimp-lib 3.0.21 mailchimp/mc-magento2 1.0.29
[2018-06-15 08:40:32] main.CRITICAL: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'at_special_from_date_default.value' in 'on clause', query was: SELECTe.* ...,