search

mailchimp / mc-magento2

MailChimp for Magento 2. Syncs all data (subscriber, customers, orders, products) and enables marketing automation with email campaigns, automations, ads, postcards and more.
Open Software License 3.0
157 stars 111 forks source link

Unknown column 'at_special_from_date_default.value' in 'on clause' #309

Closed antoniogarcia78 closed 6 years ago

antoniogarcia78 commented 6 years ago

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

gonzaloebiz commented 6 years ago

Hi @antoniogarcia78

what version of magento?

Best

antoniogarcia78 commented 6 years ago

Sorry I forgot this

Magento 2.1.4

gonzaloebiz commented 6 years ago

ok thanks, i will test it and back to you

gonzaloebiz commented 6 years ago

Hi @antoniogarcia78

sorry about the delay, we are working in new releases of both m4m1 and m4m2 these days I check your issue in Magento 2.1.4 and 2.1.10 with sample data and i can't reproduce it. Any other specific data of your installation? Which version of mysql do you have?

Best

antoniogarcia78 commented 6 years ago

Hi gonzaloebiz We use 10.0.29-MariaDB in Ubuntu 14.0.5 LTS but we use this plugin in magento without problem before update. I try to test in other machine.

gonzaloebiz commented 6 years ago

OK, let me know. We have a lot of installations of 1.0.28 and no other reports of this issue, so i think is like a mysql version issue or something like this (perhaps other magento extension)

Best

antoniogarcia78 commented 6 years ago

I have tried something else. Before I had only one list for all the websites (we have 4) and when using 4 lists (one per website) it may have stopped working. I have deleted all the settings and I have started again Now the configuration says "This account is currently syncing" but in system.log I have a lot of errors "main.CRITICAL: Resource Not Found: The requested resource could not be found." without any type of detail

gonzaloebiz commented 6 years ago

Hi @antoniogarcia78

you can modify this line https://github.com/ebizmarts/mailchimp-lib/blob/master/src/Mailchimp.php#L222 with

throw new Mailchimp_Error($result['title'].' : '.$result['detail'].' url: '.$this->_root.$url);

We has an issue with the promo codes, but was fixed in 1.0.29 version, so i recommend update the extension

Best

antoniogarcia78 commented 6 years ago

Tonight I'm going to update but I want to install clean. Can I delete all the bd mailchimp_ * tables without losing information for the ecommerce synchronization with mailchimp?

gonzaloebiz commented 6 years ago

Well, if you delete all the mailchimp tables, a lot of errors will appear (basically already exists errors)

antoniogarcia78 commented 6 years ago

Hi The problem in the cron persist but now we know when fails. In the moment we activate the Ecommerce settings. Without this the sync of clients works ok,

gonzaloebiz commented 6 years ago

you already make the change in the lib? if yes, take a look at the cron_schedule table

antoniogarcia78 commented 6 years ago

Change done

antoniogarcia78 commented 6 years ago

After the change in the log I have many errors

The requested resource could not be found. url: https://us10.api.mailchimp.com/3.0/ecommerce/stores/de4e62f47aac5af43e3793e4b0aa1c7a

This is the strange thing. I have deleted all but one and still making requests to stores that do not exist anymore.

If you create a store in Magento it is created without problems in Mailchimp (checked using the api). This point OK

Could it be that it is not possible to have a storeview connected to Mailchimp? Because the first storeview connects well but the second one seems not to.

gonzaloebiz commented 6 years ago

Hi @antoniogarcia78

do you found this MailChimp store in the core_config_data table?

Best

inelukipg commented 6 years ago

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.* ...,

antoniogarcia78 commented 6 years ago

This SQL is in the class Product (Product.php) I believe and this is an assumption because I am analyzing what makes the code of the module that brings prices that are in promotion. I have all the product promotions deleted. Will this be? or is it a SQL error? I'm still investigating

gonzaloebiz commented 6 years ago

Hi,

you can view the query here https://github.com/mailchimp/mc-magento2/blob/develop/Model/Api/Product.php#L159-L208 As you can view here https://github.com/mailchimp/mc-magento2/blob/develop/Model/Api/Product.php#L169-L174 the query filter by attribute. Check the definition of special_from_date in eav_attribute and compare your definition with a magento vanilla installation.

Best

antoniogarcia78 commented 6 years ago

Vanilla vs My Magento (both 2.14) Two differences

  1. entity_id: 79 vs 568 (I understand is not important)
  2. entity_type_id: 4 vs 10. Can it be important?
dan-ding commented 6 years ago

i see this as well 2.2.4

at_special_from_date_default doesn't exist.

gonzaloebiz commented 6 years ago

@dan-ding

are you already check the definition of the attributes?

inelukipg commented 6 years ago

Hi, Any news about this issue?

aneshranjit commented 6 years ago

@gonzaloebiz I'm getting this exact issue when trying to execute cron job ebizmarts_ecommerce.

Magento version 2.1.9 mailchimp/mc-magento2 version 1.0.28 ebizmarts/mailchimp-lib version 3.0.21

It occurred after I upgraded mailchimp/mc-magento2 version from 1.0.25 to 1.0.28

I applied changes from https://github.com/gundamkid/mc-magento2/commit/920cd7357bfd4703a0e0a7f3c7a4410bfc2e985a and https://github.com/gundamkid/mc-magento2/commit/a3da4645fce6354f86e3749b613d7862d245ac8c

Anything you can suggest me here? This issue has been troubling me for days.

gonzaloebiz commented 6 years ago

To all with this issue, as the tags shows, we can't replicate the issue and we can't resolve it if we can't replicate. My suggestion is (as in previous post), check the definition of the attributes, i think is an attribute definition issue (perhaps some extension is affecting this definitions) Could be useful if each of you can attach the extensions you has (run composer show and attach the result)

Best

antoniogarcia78 commented 6 years ago

aplazame/aplazame-api-sdk v0.2.2 Aplazame API SDK aplazame/magento2 v0.8.2 Aplazame Magento 2 module braintree/braintree_php 3.7.0 Braintree PHP Client Library colinmollenhour/cache-backend-file 1.4 The stock Zend_Cache_Backend_File backend has extremely poor performan... colinmollenhour/cache-backend-redis 1.9 Zend_Cache backend using Redis with full support for tags. colinmollenhour/credis 1.6 Credis is a lightweight interface to the Redis key-value store which w... colinmollenhour/php-redis-session-abstract v1.2 A Redis-based session handler with optimistic locking composer/ca-bundle 1.0.8 Lets you find a path to the system CA bundle, and includes a fallback ... composer/composer 1.0.0-beta1 Composer helps you declare, manage and install dependencies of PHP pro... composer/semver 1.4.2 Semver library that offers utilities, version constraint parsing and v... composer/spdx-licenses 1.1.6 SPDX licenses list and validation library. doctrine/instantiator 1.0.5 A small, lightweight utility to instantiate objects in PHP without inv... drewm/mailchimp-api v2.5 Super-simple, minimum abstraction MailChimp API v3 wrapper ebizmarts/mailchimp-lib 3.0.21 API client library for the MailChimp fabpot/php-cs-fixer v1.13.3 A tool to automatically fix PHP code style geoip2/geoip2 v2.6.0 MaxMind GeoIP2 PHP API justinrainbow/json-schema 1.6.1 A library to validate a json schema. league/climate 2.6.1 PHP's best friend for the terminal. CLImate allows you to easily outpu... lillik/magento2-price-decimal 1.0.1 Magento 2 Price Decimal Precision lusitanian/oauth v0.7.0 PHP 5.3+ oAuth 1/2 Library magehost/performance-dashboard 1.12.2 Performance Dashboard for Magento 2.x by MageHost.pro magento/composer 1.0.3 Magento composer library helps to instantiate Composer application and... magento/data-migration-tool 2.1.4 Migration Tool magento/framework 100.1.4 N/A magento/language-de_de 100.1.0 German (Germany) language magento/language-en_us 100.1.0 English (United States) language magento/language-es_es 100.1.0 Spanish (Spain) language magento/language-fr_fr 100.1.0 French (France) language magento/language-nl_nl 100.1.0 Dutch (Netherlands) language magento/language-pt_br 100.1.0 Portuguese (Brazil) language magento/language-zh_hans_cn 100.1.0 Chinese (China) language magento/magento-composer-installer 0.1.12 Composer installer for Magento modules magento/magento2-base 2.1.4 Magento 2 Base (Community Edition) magento/module-admin-notification 100.1.1 N/A magento/module-advanced-pricing-import-export 100.1.1 N/A magento/module-authorization 100.1.1 Authorization module provides access to Magento ACL functionality. magento/module-authorizenet 100.1.3 N/A magento/module-backend 100.1.2 N/A magento/module-backup 100.1.1 N/A magento/module-braintree 100.1.4 N/A magento/module-bundle 100.1.1 N/A magento/module-bundle-import-export 100.1.2 N/A magento/module-cache-invalidate 100.1.2 N/A magento/module-captcha 100.1.2 N/A magento/module-catalog 101.0.4 N/A magento/module-catalog-import-export 100.1.2 N/A magento/module-catalog-inventory 100.1.3 N/A magento/module-catalog-rule 100.1.3 N/A magento/module-catalog-rule-configurable 100.1.2 N/A magento/module-catalog-search 100.1.3 N/A magento/module-catalog-url-rewrite 100.1.2 N/A magento/module-catalog-widget 100.1.1 N/A magento/module-checkout 100.1.4 N/A magento/module-checkout-agreements 100.1.1 N/A magento/module-cms 101.0.3 N/A magento/module-cms-url-rewrite 100.1.1 N/A magento/module-config 100.1.2 N/A magento/module-configurable-import-export 100.1.1 N/A magento/module-configurable-product 100.1.4 N/A magento/module-contact 100.1.2 N/A magento/module-cookie 100.1.1 N/A magento/module-cron 100.1.2 N/A magento/module-currency-symbol 100.1.1 N/A magento/module-customer 100.1.4 N/A magento/module-customer-import-export 100.1.1 N/A magento/module-deploy 100.1.4 N/A magento/module-developer 100.1.2 N/A magento/module-dhl 100.1.2 N/A magento/module-directory 100.1.2 N/A magento/module-downloadable 100.1.1 N/A magento/module-downloadable-import-export 100.1.1 N/A magento/module-eav 100.1.3 N/A magento/module-email 100.1.2 N/A magento/module-encryption-key 100.1.1 N/A magento/module-fedex 100.1.2 N/A magento/module-gift-message 100.1.2 N/A magento/module-google-adwords 100.1.1 N/A magento/module-google-analytics 100.1.1 N/A magento/module-google-optimizer 100.1.1 N/A magento/module-grouped-import-export 100.1.1 N/A magento/module-grouped-product 100.1.2 N/A magento/module-import-export 100.1.2 N/A magento/module-indexer 100.1.2 N/A magento/module-integration 100.1.2 N/A magento/module-layered-navigation 100.1.1 N/A magento/module-marketplace 100.1.1 N/A magento/module-media-storage 100.1.1 N/A magento/module-msrp 100.1.2 N/A magento/module-multishipping 100.1.1 N/A magento/module-new-relic-reporting 100.1.2 N/A magento/module-newsletter 100.1.1 N/A magento/module-offline-payments 100.1.1 N/A magento/module-offline-shipping 100.1.2 N/A magento/module-page-cache 100.1.2 N/A magento/module-payment 100.1.4 N/A magento/module-paypal 100.1.3 N/A magento/module-persistent 100.1.2 N/A magento/module-product-alert 100.1.2 N/A magento/module-product-video 100.1.3 Add Video to Products magento/module-quote 100.1.3 N/A magento/module-reports 100.1.1 N/A magento/module-require-js 100.1.2 N/A magento/module-review 100.1.1 N/A magento/module-rss 100.1.1 N/A magento/module-rule 100.1.2 N/A magento/module-sales 100.1.4 N/A magento/module-sales-inventory 100.1.0 N/A magento/module-sales-rule 100.1.2 N/A magento/module-sales-sequence 100.1.2 N/A magento/module-sample-data 100.1.2 Sample Data fixtures magento/module-search 100.1.1 N/A magento/module-security 100.1.2 Security management module magento/module-send-friend 100.1.1 N/A magento/module-shipping 100.1.2 N/A magento/module-sitemap 100.1.2 N/A magento/module-store 100.1.4 N/A magento/module-swagger 100.1.1 N/A magento/module-swatches 100.1.3 Add Swatches to Products magento/module-swatches-layered-navigation 100.1.1 N/A magento/module-tax 100.1.1 N/A magento/module-tax-import-export 100.1.1 N/A magento/module-theme 100.1.3 N/A magento/module-translation 100.1.2 N/A magento/module-ui 100.1.3 N/A magento/module-ups 100.1.2 N/A magento/module-url-rewrite 100.1.1 N/A magento/module-user 100.1.2 N/A magento/module-usps 100.1.2 N/A magento/module-variable 100.1.1 N/A magento/module-vault 100.2.0
magento/module-version 100.1.1 N/A magento/module-webapi 100.1.1 N/A magento/module-webapi-security 100.1.1 WebapiSecurity module provides option to loosen security on some webap... magento/module-weee 100.1.1 N/A magento/module-widget 100.1.1 N/A magento/module-wishlist 100.1.3 N/A magento/product-community-edition 2.1.4 eCommerce Platform for Growth (Community Edition) magento/theme-adminhtml-backend 100.1.1 N/A magento/theme-frontend-blank 100.1.2 N/A magento/theme-frontend-luma 100.1.3 N/A magento/zendframework1 1.12.16-patch3 Magento Zend Framework 1 magepal/magento2-googletagmanager 2.2.4 Google Tag Manager (GTM) for Magento 2 with Advance Data Layer mailchimp/mc-magento2 1.0.29 Connect MailChimp with Magento maxmind-db/reader v1.1.3 MaxMind DB Reader API maxmind/web-service-common v0.4.0 Internal MaxMind Web Service API monolog/monolog 1.16.0 Sends your logs to files, sockets, inboxes, databases and various web ... oyejorge/less.php v1.7.0.14 PHP port of the Javascript version of LESS http://lesscss.org (Origina... pdepend/pdepend 2.2.2 Official version of pdepend to be handled with Composer pelago/emogrifier v0.1.1 Converts CSS styles into inline style attributes in your HTML code phpmd/phpmd 2.5.0 PHPMD is a spin-off project of PHP Depend and aims to be a PHP equival... phpseclib/phpseclib 2.0.6 PHP Secure Communications Library - Pure-PHP implementations of RSA, A... phpunit/php-code-coverage 2.2.4 Library that provides collection, processing, and rendering functional... phpunit/php-file-iterator 1.3.4 FilterIterator implementation that filters files based on a list of su... phpunit/php-text-template 1.2.1 Simple template engine. phpunit/php-timer 1.0.9 Utility class for timing phpunit/php-token-stream 1.4.11 Wrapper around PHP's tokenizer extension. phpunit/phpunit 4.1.0 The PHP Unit Testing framework. phpunit/phpunit-mock-objects 2.3.8 Mock Object library for PHPUnit psr/log 1.0.2 Common interface for logging libraries sebastian/comparator 1.2.4 Provides the functionality to compare PHP values for equality sebastian/diff 1.4.3 Diff implementation sebastian/environment 1.3.8 Provides functionality to handle HHVM/PHP environments sebastian/exporter 1.2.2 Provides the functionality to export PHP variables for visualization sebastian/finder-facade 1.2.1 FinderFacade is a convenience wrapper for Symfony's Finder component. sebastian/phpcpd 2.0.0 Copy/Paste Detector (CPD) for PHP code. sebastian/recursion-context 1.0.5 Provides functionality to recursively process PHP variables sebastian/version 1.0.6 Library that helps with managing the version number of Git-hosted PHP ... seld/cli-prompt 1.0.3 Allows you to prompt for user input on the command line, and optionall... seld/jsonlint 1.6.1 JSON Linter seld/phar-utils 1.0.1 PHAR file format utilities, for when PHP phars you up sjparkinson/static-review 4.1.1 An extendable framework for version control hooks. squizlabs/php_codesniffer 1.5.3 PHP_CodeSniffer tokenises PHP, JavaScript and CSS files and detects vi... strategery/magento2-infinitescroll 0.2.0 Magento2 InfiniteScroll module to load the next catalog page or result... symfony/config v2.8.28 Symfony Config Component symfony/console v2.6.13 Symfony Console Component symfony/dependency-injection v2.8.28 Symfony DependencyInjection Component symfony/event-dispatcher v2.8.28 Symfony EventDispatcher Component symfony/filesystem v2.8.28 Symfony Filesystem Component symfony/finder v3.3.10 Symfony Finder Component symfony/process v2.8.39 Symfony Process Component symfony/stopwatch v3.3.10 Symfony Stopwatch Component symfony/yaml v2.8.28 Symfony Yaml Component tedivm/jshrink v1.0.1 Javascript Minifier built in PHP theseer/fdomdocument 1.6.6 The classes contained within this repository extend the standard DOM t... tubalmartin/cssmin v2.4.8-p4 A PHP port of the YUI CSS compressor zendframework/zend-code 2.4.13 provides facilities to generate arbitrary code using an object oriente... zendframework/zend-config 2.4.13 provides a nested object property based user interface for accessing t... zendframework/zend-console 2.4.13
zendframework/zend-crypt 2.4.13
zendframework/zend-di 2.4.13
zendframework/zend-escaper 2.4.13
zendframework/zend-eventmanager 2.4.13
zendframework/zend-filter 2.4.13 provides a set of commonly needed data filters zendframework/zend-form 2.4.13
zendframework/zend-http 2.4.13 provides an easy interface for performing Hyper-Text Transfer Protocol... zendframework/zend-i18n 2.4.13
zendframework/zend-inputfilter 2.4.13
zendframework/zend-json 2.4.13 provides convenience methods for serializing native PHP to JSON and de... zendframework/zend-loader 2.4.13
zendframework/zend-log 2.4.13 component for general purpose logging zendframework/zend-math 2.4.13
zendframework/zend-modulemanager 2.4.13
zendframework/zend-mvc 2.4.13
zendframework/zend-serializer 2.4.13 provides an adapter based interface to simply generate storable repres... zendframework/zend-server 2.4.13
zendframework/zend-servicemanager 2.4.13
zendframework/zend-soap 2.4.13
zendframework/zend-stdlib 2.4.13
zendframework/zend-text 2.4.13
zendframework/zend-uri 2.4.13 a component that aids in manipulating and validating » Uniform Resour... zendframework/zend-validator 2.4.13 provides a set of commonly needed validators zendframework/zend-view 2.4.13 provides a system of helpers, output filters, and variable escaping

antoniogarcia78 commented 6 years ago

At this point it would be best to uninstall the plugin completely and perform a clean installation (code and database) Every time I have updated it has been worse.

Is there a complete guide to uninstall the plugin? Thank you very much

gonzaloebiz commented 6 years ago

Hi @antoniogarcia78

i don't think re installing the extension is a solution on this case. The issue is with the attributes definition and reinstall don't touch it. My approach would be

Best

inelukipg commented 6 years ago

@antoniogarcia78 I have the same problem. I looked at your module-list and the only module that is on my installation is:

magepal/magento2-googletagmanager 2.2.4 Google Tag Manager (GTM) for Magento 2 with Advance Data Layer

Maybe this module is causing the error..??

duyang79 commented 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 to mc.mailchimp_sync_delta < at_special_from_date.value done

I know I am lazy and just change the original code and even don't want to create another custom model to overwrite it :P. 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.

PiroozMB commented 6 years ago

I get the exact error on magento2.2.3 at my cron table. Any updates and solution for this except for duyang79 solution?

gonzaloebiz commented 6 years ago

Hi @PiroozMB

do you have any more information to share and help to find the solution?

Best

PiroozMB commented 6 years ago

duyang79 solution didn't work for me.
@gonzaloebiz I get this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'at_special_from_date.value' in 'on clause', query was: SELECT e.*, IF(at_special_price.value_id > 0, at_special_price.value, at_special_price_default.value) AS special_price, at_special_to_date.value AS special_to_date 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 = 1) 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 = 'daa358111be17e2b78a091740ca65363' and mc.mailchimp_sync_delta < at_special_from_date.value WHERE (IF(at_special_price.value_id > 0, at_special_price.value, at_special_price_default.value) > 0) AND (((((at_special_to_date.value <= '2018-08-06 00:00:00'))))) AND (mc.mailchimp_sync_delta is not null)

gonzaloebiz commented 6 years ago

Please read the comments https://github.com/mailchimp/mc-magento2/issues/309#issuecomment-402154372 and https://github.com/mailchimp/mc-magento2/issues/309#issuecomment-403871087

Best

duyang79 commented 6 years ago

Hi @PiroozMB, I don't think your issue is the same as ours. The column you couldn't find was 'at_special_from_date.value' instead 'at_special_from_date_default.value'.

But I think you can use the same way like what we did to resolve it by your self: find this file: Ebizmarts/MailChimp/Model/Api/Product.php and locate the php code that output that SQL qurey, then figure out why the 'at_special_from_date' has not been created.

mikehenze commented 6 years ago

I get this error.

Latest magento2, latest mailchimp plugin, latest lib

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'at_special_to_date_default.value' in 'on clause', query was: SELECT e.*, IF(at_special_price.value_id > 0, at_special_price.value, at_special_price_default.value) AS special_price, at_special_to_date.value AS special_to_date 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 = '76') 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 = '76') AND (at_special_price.store_id = 38) 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 = '78') 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 = 'xxxxxxxxx' and mc.mailchimp_sync_delta < (IF(at_special_to_date.value_id > 0, at_special_to_date.value, at_special_to_date_default.value)) WHERE (IF(at_special_price.value_id > 0, at_special_price.value, at_special_price_default.value) > 0) AND (((((at_special_to_date.value <= '2018-09-05 00:00:00'))))) AND (mc.mailchimp_sync_delta is not null)

girishtiwari commented 6 years ago

I had faced the same issue and it was resolved when I set the "Use Flat Catalog Product - NO" and "Use Flat Catalog Category - NO" from the stores -> configuration -> catalogue settings.

PiroozMB commented 6 years ago

I solved this problem. I put my whole code here for those who have this problem. Just open magento2/../mailchimp/mc-magento2/Model/Api/Product.php file and remove all the code, then copy and paste the below code instead and clear the cache. @mikehenze

`<?php /**

namespace Ebizmarts\MailChimp\Model\Api;

class Product { const DOWNLOADABLE = 'downloadable'; const PRODUCTIMAGE = 'product_small_image'; const MAX = 100;

protected $_parentImage = null;
protected $_childtUrl   = null;
/**
 * @var \Ebizmarts\MailChimp\Helper\Data
 */
protected $_helper;
/**
 * @var \Magento\Catalog\Model\ProductRepository
 */
protected $_productRepository;
/**
 * @var \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory
 */
protected $_productCollection;
/**
 * @var \Magento\Catalog\Helper\Image
 */
protected $_imageHelper;
/**
 * @var \Magento\CatalogInventory\Api\StockRegistryInterface
 */
protected $_stockRegistry;
/**
 * @var \Magento\Catalog\Model\CategoryRepository
 */
protected $_categoryRepository;
/**
 * @var string
 */
protected $_batchId;
/**
 * @var \Ebizmarts\MailChimp\Model\MailChimpSyncEcommerceFactory
 */
protected $_chimpSyncEcommerce;
/**
 * @var \Magento\ConfigurableProduct\Model\Product\Type\Configurable
 */
protected $_configurable;
/**
 * @var \Magento\Catalog\Model\Product\Option
 */
protected $_option;
/**
 * @var \Magento\Catalog\Model\ResourceModel\Category\CollectionFactory
 */
protected $_categoryCollection;

/**
 * Product constructor.
 * @param \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollection
 * @param \Magento\Catalog\Model\ProductRepository $productRepository
 * @param \Ebizmarts\MailChimp\Helper\Data $helper
 * @param \Magento\Catalog\Helper\Image $imageHelper
 * @param \Magento\CatalogInventory\Api\StockRegistryInterface $stockRegistry
 * @param \Magento\Catalog\Model\CategoryRepository $categoryRepository
 * @param \Ebizmarts\MailChimp\Model\MailChimpSyncEcommerceFactory $chimpSyncEcommerce
 * @param \Magento\ConfigurableProduct\Model\Product\Type\Configurable $configurable
 * @param \Magento\Catalog\Model\ResourceModel\Category\CollectionFactory $categoryCollection
 * @param \Magento\Catalog\Model\Product\Option $option
 */
public function __construct(
    \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollection,
    \Magento\Catalog\Model\ProductRepository $productRepository,
    \Ebizmarts\MailChimp\Helper\Data $helper,
    \Magento\Catalog\Helper\Image $imageHelper,
    \Magento\CatalogInventory\Api\StockRegistryInterface $stockRegistry,
    \Magento\Catalog\Model\CategoryRepository $categoryRepository,
    \Ebizmarts\MailChimp\Model\MailChimpSyncEcommerceFactory $chimpSyncEcommerce,
    \Magento\ConfigurableProduct\Model\Product\Type\Configurable $configurable,
    \Magento\Catalog\Model\ResourceModel\Category\CollectionFactory $categoryCollection,
    \Magento\Catalog\Model\Product\Option $option
) {

    $this->_productRepository   = $productRepository;
    $this->_helper              = $helper;
    $this->_productCollection   = $productCollection;
    $this->_imageHelper         = $imageHelper;
    $this->_stockRegistry       = $stockRegistry;
    $this->_categoryRepository  = $categoryRepository;
    $this->_chimpSyncEcommerce  = $chimpSyncEcommerce;
    $this->_configurable        = $configurable;
    $this->_option              = $option;
    $this->_categoryCollection  = $categoryCollection;
    $this->_batchId             = \Ebizmarts\MailChimp\Helper\Data::IS_PRODUCT. '_' . $this->_helper->getGmtTimeStamp();
}
public function _sendProducts($magentoStoreId)
{
    $batchArray = [];
    $counter = 0;
    $mailchimpStoreId = $this->_helper->getConfigValue(\Ebizmarts\MailChimp\Helper\Data::XML_MAILCHIMP_STORE, $magentoStoreId);
    $this->_markSpecialPrices($magentoStoreId,$mailchimpStoreId);
    $collection = $this->_getCollection();
    $collection->setStoreId($magentoStoreId);
    $collection->getSelect()->joinLeft(
        ['m4m' => $this->_helper->getTableName('mailchimp_sync_ecommerce')],
        "m4m.related_id = e.entity_id and m4m.type = '".\Ebizmarts\MailChimp\Helper\Data::IS_PRODUCT.
            "' and m4m.mailchimp_store_id = '".$mailchimpStoreId."'",
        ['m4m.*']
    );
    $collection->getSelect()->where("m4m.mailchimp_sync_delta IS null OR (m4m.mailchimp_sync_delta > '".$this->_helper->getMCMinSyncDateFlag().
        "' and m4m.mailchimp_sync_modified = 1)");
    $collection->getSelect()->limit(self::MAX);
    foreach ($collection as $item) {
        /**
         * @var $product \Magento\Catalog\Model\Product
         */
        $product = $this->_productRepository->get($item->getSku());

// $productSyncData = $this->_chimpSyncEcommerce->getByStoreIdType($mailchimpStoreId,$product->getId(), // \Ebizmarts\MailChimp\Helper\Data::IS_PRODUCT); if ($item->getMailchimpSyncModified() && $item->getMailchimpSyncDelta() && $item->getMailchimpSyncDelta() > $this->_helper->getMCMinSyncDateFlag()) { $batchArray = array_merge($this->_buildOldProductRequest($product,$this->_batchId,$mailchimpStoreId, $magentoStoreId),$batchArray); $this->_updateProduct($mailchimpStoreId, $product->getId()); continue; } else { $data = $this->_buildNewProductRequest($product, $mailchimpStoreId, $magentoStoreId); } if (!empty($data)) { $batchArray[$counter] = $data; $counter++;

            //update product delta
            $this->_updateProduct($mailchimpStoreId, $product->getId());
        } else {
            $this->_updateProduct(
                $mailchimpStoreId,
                $product->getId(),
                $this->_helper->getGmtDate(),
                "This product type is not supported on MailChimp.",
                0
            );
        }
    }
    return $batchArray;
}
protected function _markSpecialPrices($magentoStoreId, $mailchimpStoreId)
{
    /**
     * get the products with current special price that are not synced and mark it as modified
     */
    $collection = $this->_getCollection();
    $collection->setStoreId($magentoStoreId);
    $collection->addAttributeToFilter(
        'special_price',
        ['gt'=>0], 'left'
    )->addAttributeToFilter(
        'special_from_date',['or' => [ 0 => ['date' => true,
        'to' => date('Y-m-d',time()).' 23:59:59'],
        1 => ['is' => new \Zend_Db_Expr(
            'null'
        )],]], 'left'
    )->addAttributeToFilter(
        'special_to_date',  ['or' => [ 0 => ['date' => true,
        'from' => date('Y-m-d',time()).' 00:00:00'],
        1 => ['is' => new \Zend_Db_Expr(
            'null'
        )],]], 'left'
    );
    $collection->getSelect()->joinLeft(['mc' => $collection->getTable('mailchimp_sync_ecommerce')],
        "mc.type = 'PRO' AND mc.related_id = e.entity_id AND mc.mailchimp_sync_modified = 0 ".$collection->getConnection()->quoteInto(" AND  mc.mailchimp_store_id = ?",$mailchimpStoreId) ." and mc.mailchimp_sync_delta <  at_special_from_date.value");
    $collection->getSelect()->where('mc.mailchimp_sync_delta is not null');
    foreach ($collection as $item) {
        $this->_updateProduct($mailchimpStoreId, $item->getEntityId(),null,null, 1);
    }
    /**
     * get the products that was synced when it have special price and have no more special price
     */
    $collection2 = $this->_getCollection();
    $collection2->setStoreId($magentoStoreId);
    $collection2->addAttributeToFilter(
        'special_price',
        ['gt'=>0], 'left'
    )->addAttributeToFilter(
        'special_to_date',  ['or' => [ 0 => ['date' => true,
        'to' => date('Y-m-d',time()).' 00:00:00'],
        ]], 'left'
    );
    $collection2->getSelect()->joinLeft(['mc' => $collection2->getTable('mailchimp_sync_ecommerce')],
        "mc.type = 'PRO' and mc.related_id = e.entity_id and mc.mailchimp_sync_modified = 0 ".$collection->getConnection()->quoteInto(" AND  mc.mailchimp_store_id = ?",$mailchimpStoreId) ." and mc.mailchimp_sync_delta < at_special_to_date.value",[]);
    $collection2->getSelect()->where('mc.mailchimp_sync_delta is not null');
    foreach ($collection2 as $item) {
        $this->_updateProduct($mailchimpStoreId, $item->getEntityId(),null,null, 1);
    }

}
/**
 * @return \Magento\Catalog\Model\ResourceModel\Product\Collection
 */
protected function _getCollection()
{
    return $this->_productCollection->create();
}
protected function _buildNewProductRequest(
    \Magento\Catalog\Model\Product $product,
    $mailchimpStoreId,
    $magentoStoreId
) {

    $variantProducts = [];
    switch ($product->getTypeId()) {
        case \Magento\Catalog\Model\Product\Type::TYPE_SIMPLE:
            $variantProducts[] = $product;
            break;
        case \Magento\ConfigurableProduct\Model\Product\Type\Configurable::TYPE_CODE:
            $childProducts = $product->getTypeInstance()->getChildrenIds($product->getId());
            $variantProducts[] = $product;
            if (count($childProducts[0])) {
                foreach ($childProducts[0] as $childId) {
                    $variantProducts[] = $this->_productRepository->getById($childId);
                }
            }
            break;
        case \Magento\Catalog\Model\Product\Type::TYPE_VIRTUAL:
            $variantProducts[] = $product;
            break;
        case self::DOWNLOADABLE:
            $variantProducts[] = $product;
            break;
        default:
            return [];
    }
    $bodyData = $this->_buildProductData($product, $magentoStoreId, false, $variantProducts);
    try {
        $body = json_encode($bodyData, JSON_HEX_APOS|JSON_HEX_QUOT);

    } catch (\Exception $e) {
        //json encode failed
        $this->_helper->log("Product " . $product->getId() . " json encode failed");
        return [];
    }
    $data = [];
    $data['method'] = "POST";
    $data['path'] = "/ecommerce/stores/" . $mailchimpStoreId . "/products";
    $data['operation_id'] = $this->_batchId . '_' . $product->getId();
    $data['body'] = $body;
    return $data;
}
protected function _buildOldProductRequest(
    \Magento\Catalog\Model\Product $product,
    $batchId,
    $mailchimpStoreId,
    $magentoStoreId
) {
    $operations = [];
    $variantProducts = [];
    if ($product->getTypeId() == \Magento\Catalog\Model\Product\Type::TYPE_SIMPLE ||
        $product->getTypeId() == \Magento\Catalog\Model\Product\Type::TYPE_VIRTUAL ||
        $product->getTypeId() == "downloadable") {
        $data = $this-> _buildProductData($product, $magentoStoreId);
        $variantProducts [] = $product;

// $parentIds = $product->getTypeInstance()->getParentIdsByChild($product->getId()); $parentIds = $this->_configurable->getParentIdsByChild($product->getId());

        //add or update variant
        foreach ($parentIds as $parentId) {
            $productSync = $this->_chimpSyncEcommerce->create()->getByStoreIdType($mailchimpStoreId,
                                                                                $parentId,
                                                                          \Ebizmarts\MailChimp\Helper\Data::IS_PRODUCT);
            if($productSync->getMailchimpSyncDelta()) {
                $variendata = [];
                $variendata["id"] = $data["id"];
                $variendata["title"] = $data["title"];
                $variendata["url"] = $data["url"];
                $variendata["sku"] = $data["sku"];
                $variendata["price"] = $data["price"];
                $variendata["inventory_quantity"] = $data["inventory_quantity"];
                $variendata["image_url"] = $data["image_url"];
                $variendata["backorders"] = $data["backorders"];
                $variendata["visibility"] = $data["visibility"];
                $productdata = [];
                $productdata['method'] = "PUT";
                $productdata['path'] = "/ecommerce/stores/" . $mailchimpStoreId . "/products/" . $parentId . '/variants/' . $data['id'];
                $productdata['operation_id'] = $batchId . '_' . $parentId;
                try {
                    $body = json_encode($variendata);
                } catch (\Exception $e) {
                    //json encode failed
                    $this->_helper->log("Product " . $product->getId() . " json encode failed");
                    continue;
                }

                $productdata['body'] = $body;
                $operations[] = $productdata;
            }
        }
    } elseif($product->getTypeId() == \Magento\ConfigurableProduct\Model\Product\Type\Configurable::TYPE_CODE) {
        $childProducts = $product->getTypeInstance()->getChildrenIds($product->getId());
        $variantProducts[] = $product;
        if (count($childProducts[0])) {
            foreach ($childProducts[0] as $childId) {
                $variantProducts[] = $this->_productRepository->getById($childId);
            }
        }
    } else {
        return [];
    }

    $bodyData = $this->_buildProductData($product, $magentoStoreId, false, $variantProducts);
    try {
        $body = json_encode($bodyData, JSON_HEX_APOS|JSON_HEX_QUOT);

    } catch (\Exception $e) {
        //json encode failed
        $this->_helper->log("Product " . $product->getId() . " json encode failed");
        return [];
    }
    $data = [];
    $data['method'] = "PATCH";
    $data['path'] = "/ecommerce/stores/" . $mailchimpStoreId . "/products/".$product->getId();
    $data['operation_id'] = $this->_batchId . '_' . $product->getId();
    $data['body'] = $body;
    $operations[] = $data;
    return $operations;
}
protected function _buildProductData(
    \Magento\Catalog\Model\Product $product,
    $magentoStoreId,
    $isVarient = true,
    $variants = null
) {

    $data = [];

    //data applied for both root and varient products
    $data["id"] = $product->getId();
    $data["title"] = $product->getName();
    $data["url"] = $product->getProductUrl();
    if ($product->getImage()) {
        //mijn aanpassing image url hier
        $filePath = 'catalog/product'.$product->getImage();
        $data["image_url"] = $this->_helper->getBaserUrl($magentoStoreId, \Magento\Framework\UrlInterface::URL_TYPE_MEDIA).$filePath;
    //$store = $this->_objectManager->get('Magento\Store\Model\StoreManagerInterface')->getStore();
    //$data["image_url"] = $store->getBaseUrl(\Magento\Framework\UrlInterface::URL_TYPE_MEDIA) . 'catalog/product/cache/small_image/300x400/beff4985b56e3afdbeabfc89641a4582/' . $product->getImage();
    } elseif ($this->_parentImage) {
        $data['image_url'] = $this->_parentImage;
    } else {
        $data['image_url'] = '';
    }
    $data["published_at_foreign"] = "";
    if ($isVarient) {
        //this is for a varient product
        $data["sku"] = $product->getSku();
        $today = $this->_helper->getGmtDate("Y-m-d");
        if($product->getSpecialFromDate() && $product->getSpecialFromDate() <= $today)
        {
            if(!$product->getSpecialToDate() || ($product->getSpecialToDate() && $today <= $product->getSpecialToDate())) {
                $data["price"] = $product->getSpecialPrice();
            } else {
                $data["price"] = $product->getPrice();
            }
        } else {
            $data["price"] = $product->getPrice();
        }

        //stock
        $stock = $this->_stockRegistry->getStockItem($product->getId(), $magentoStoreId);
        $data["inventory_quantity"] = (int)$stock->getQty();
        $data["backorders"] = (string)$stock->getBackorders();
        if ($product->getVisibility() == \Magento\Catalog\Model\Product\Visibility::VISIBILITY_NOT_VISIBLE) {
            $tailUrl = '';
            $data["visibility"] = 'false';
            $parentIds =$this->_configurable->getParentIdsByChild($product->getId());
            /**
             * @var $parent \Magento\Catalog\Model\Product
             */
            $parent = null;
            foreach ($parentIds as $id) {
                $parent = $this->_productRepository->getById($id);
                if ($parent->getTypeId() == \Magento\ConfigurableProduct\Model\Product\Type\Configurable::TYPE_CODE) {
                    $options = $parent->getTypeInstance()->getConfigurableAttributesAsArray($parent);
                    foreach ($options as $option) {
                        if (strlen($tailUrl)) {
                            $tailUrl .= '&';
                        } else {
                            $tailUrl .= '?';
                        }
                        $tailUrl.= $option['attribute_code']."=".$product->getData($option['attribute_code']);
                    }
                }
                if ($tailUrl!='') {
                    break;
                }
            }
            if ($parent) {
                $this->_childtUrl = $data['url'] = $parent->getProductUrl() . $tailUrl;
                if(!isset($data['image_url'])) {
                    $filePath = 'catalog/product'.$parent->getImage();
                    $data["image_url"] = $this->_helper->getBaserUrl($magentoStoreId, \Magento\Framework\UrlInterface::URL_TYPE_MEDIA).$filePath;
                    //$store = $this->_objectManager->get('Magento\Store\Model\StoreManagerInterface')->getStore();
                    //$data["image_url"] = $store->getBaseUrl(\Magento\Framework\UrlInterface::URL_TYPE_MEDIA) . 'catalog/product/cache/small_image/300x400/beff4985b56e3afdbeabfc89641a4582/' . $product->getImage();
                }
            }
        } else {
            $data["visibility"] = 'true';
        }
    } else {
        //this is for a root product
        if ($product->getData('description')) {
            $data["description"] = $product->getData('description');
        }

        $categoryName = $this->getProductCategories($product,$magentoStoreId);
        if($categoryName) {
            $data['type'] = $data['vendor'] = $categoryName;
        }

        //missing data
        $data["handle"] = "";
        if (isset($data['image_url'])) {
            $this->_parentImage = $data['image_url'];
        }
        //variants
        $data["variants"] = [];
        foreach ($variants as $variant) {
            if ($variant) {
                $data["variants"][] = $this->_buildProductData($variant, $magentoStoreId);
            }
        }
        if ($this->_childtUrl) {
            if ($product->getTypeId() == \Magento\Catalog\Model\Product\Type::TYPE_SIMPLE ||
                $product->getTypeId() == \Magento\Catalog\Model\Product\Type::TYPE_VIRTUAL ||
                $product->getTypeId() == "downloadable") {
                $data["url"] = $this->_childtUrl;
            }
            $this->_childtUrl = null;
        }
        $this->_parentImage = null;
    }

    return $data;
}

/**
 * @param \Magento\Catalog\Model\Product $product
 * @param $storeId
 */
protected function getProductCategories(\Magento\Catalog\Model\Product $product,$storeId)
{
    $categoryIds = $product->getCategoryIds();
    $categoryNames = [];
    $categoryName = null;
    if( is_array($categoryIds) && count($categoryIds)) {
        $collection = $this->_categoryCollection->create();
        $collection->addAttributeToSelect(['name'])
            ->setStoreId($storeId)
            ->addAttributeToFilter('is_active',['eq'=>'1'])
            ->addAttributeToFilter('entity_id',['in'=>$categoryIds])
            ->addAttributeToSort('level','asc');
        foreach ($collection as $category) {
            $categoryNames[] = $category->getName();
        }
        $categoryName = (count($categoryNames)) ? implode(" - ",$categoryNames) : 'None';
    }
    return $categoryName;
}
/**
 * @param \Magento\Sales\Model\Order $order
 * @param $mailchimpStoreId
 * @return array
 * @throws \Magento\Framework\Exception\NoSuchEntityException
 */
public function sendModifiedProduct(\Magento\Sales\Model\Order $order, $mailchimpStoreId, $magentoStoreId)
{
    $data = [];
    $batchId = \Ebizmarts\MailChimp\Helper\Data::IS_PRODUCT . '_' . $this->_helper->getGmtTimeStamp();
    $items = $order->getAllVisibleItems();
    foreach ($items as $item) {
        //@todo get from the store not the default
        $product = $this->_productRepository->getById($item->getProductId());
        $productSyncData = $this->_chimpSyncEcommerce->create()->getByStoreIdType(
            $mailchimpStoreId,
            $product->getId(),
            \Ebizmarts\MailChimp\Helper\Data::IS_PRODUCT
        );
        if ($product->getId()!=$item->getProductId() || $product->getTypeId()=='bundle' || $product->getTypeId()=='grouped') {
            continue;
        }
        if ($productSyncData->getMailchimpSyncModified() &&
            $productSyncData->getMailchimpSyncDelta() > $this->_helper->getMCMinSyncDateFlag()) {
            $data[] = $this->_buildOldProductRequest($product, $batchId, $mailchimpStoreId, $magentoStoreId);
            $this->_updateProduct($mailchimpStoreId, $product->getId());
        } elseif (!$productSyncData->getMailchimpSyncDelta() ||
            $productSyncData->getMailchimpSyncDelta() < $this->_helper->getMCMinSyncDateFlag()) {
            $data[] = $this->_buildNewProductRequest($product, $mailchimpStoreId, $magentoStoreId);
            $this->_updateProduct($mailchimpStoreId, $product->getId());
        }
    }
    return $data;
}

public function sendQuoteModifiedProduct(\Magento\Quote\Model\Quote $quote, $mailchimpStoreId, $magentoStoreId)
{
    $data = [];
    $batchId = \Ebizmarts\MailChimp\Helper\Data::IS_PRODUCT . '_' . $this->_helper->getGmtTimeStamp();
    $items = $quote->getAllVisibleItems();
    /**
     * @var $item \Magento\Quote\Model\Quote\Item
     */
    foreach ($items as $item) {
        //@todo get from the store not the default
        $product = $this->_productRepository->getById($item->getProductId());
        $productSyncData = $this->_chimpSyncEcommerce->create()->getByStoreIdType(
            $mailchimpStoreId,
            $product->getId(),
            \Ebizmarts\MailChimp\Helper\Data::IS_PRODUCT
        );

        if ($product->getId()!=$item->getProductId() || $product->getTypeId()=='bundle' || $product->getTypeId()=='grouped') {
            continue;
        }

        if ($productSyncData->getMailchimpSyncModified() &&
            $productSyncData->getMailchimpSyncDelta() > $this->_helper->getMCMinSyncDateFlag()) {
            $data[] = $this->_buildOldProductRequest($product, $batchId, $mailchimpStoreId, $magentoStoreId);
            $this->_updateProduct($mailchimpStoreId, $product->getId());
        } elseif (!$productSyncData->getMailchimpSyncDelta() ||
            $productSyncData->getMailchimpSyncDelta() < $this->_helper->getMCMinSyncDateFlag()) {
            $data[] = $this->_buildNewProductRequest($product, $mailchimpStoreId, $magentoStoreId);
            $this->_updateProduct($mailchimpStoreId, $product->getId());
        }
    }
    return $data;
}
/**
 * @param $storeId
 * @param $entityId
 * @param $sync_delta
 * @param $sync_error
 * @param $sync_modified
 */
protected function _updateProduct($storeId, $entityId, $sync_delta=null, $sync_error=null, $sync_modified=null)
{
    $this->_helper->saveEcommerceData(
        $storeId,
        $entityId,
        \Ebizmarts\MailChimp\Helper\Data::IS_PRODUCT,
        $sync_delta,
        $sync_error,
        $sync_modified
    );
}

} `

gonzaloebiz commented 6 years ago

Hi @girishtiwari thnks for your feedback, i will try if this is the cause. @PiroozMB thanks for you contribution, i will try your solution (are your test it in both cases?)

Best

PiroozMB commented 6 years ago

Hi @gonzaloebiz I've tested it. it's working. no errors anymore.

mikehenze commented 6 years ago

When using the new code from @PiroozMB i get this error:

Resource Not Found for Api Call: https://us1.api.mailchimp.com/3.0/lists/XXXXXXX/interest-categories - The requested resource could not be found.

mikehenze commented 6 years ago

Mailchimp errors show lots of these: variants.item:0.price : Schema describes number, NULL found instead

PiroozMB commented 6 years ago

@ mikehenze Have you copied and pasted the code correctly? github has made my code in seperated parts here. The error that you gave not found api call, says that your code cannot be found. just copy all the code from <?php until the end. Make sure, you're doing it correctly.

mikehenze commented 6 years ago

Yes i copied it correctly: https://paste.ofcode.org/38zSqsFuCDDpt6FP9tj9v3t

PiroozMB commented 6 years ago

@mikehenze That's weird. That can be another problem with another code somewhere else. I think your problem with at_special_price is solved. Now you should look for a solution for this.

mikehenze commented 6 years ago

@PiroozMB Orders after yesterday are syncing. Old orders are not.

Workable for me. Thank you for your solution.