Unable to import more than 50 products when using PostgreSQL as the underlying database [#1391868]

I'm trying to import a number of Commerce products using the commerce_feeds module, but when I use PostgreSQL as the underlying database, I can only import 50 items before the importer comes back with a SQL error. (I'm assuming that the number 50 relates to the default batch size.)

The SQL error that I'm getting is:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "C0861260" LINE 1: ... entity_type = 'commerce_product' AND entity_id = 'C0861260' ^

If I turn on full query logging in PostgreSQL, I see that the query causing the problem is:

SELECT hash FROM feeds_item WHERE entity_type = 'commerce_product' AND entity_id = 'C0861260'

This is really strange -- it appears to be using my SKU (C0861260) instead of the entity_id, and complaining because it's not finding an integer value for the entity_id.

(To further complicate matters, I turned on full query logging in MySQL, and I don't see any sort of query looking up the hash value from the feeds_item table.)

I can easily reproduce this at will, and I'm more than willing to help debug this further if someone can help me figure out what's happening.

Comment	File	Size	Author
#9	product_id.patch	593 bytes	dema502
#9

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

joeredhat-at-yahoo.com CreditAttribution: joeredhat-at-yahoo.com commented 2 January 2012 at 17:21

Just to make sure it wasn't something wrong with my product type or feed importer, I use the standard product importer and pointed it at Randy Fay's test feed at http://d7.randyfay.com/books/feed, and it is only importing 38 items before throwing a very similar error.

Error message
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "046502596X" at character 84

At least this shows that it's likely an error in the code, and not a problem in my setup.

Comment #2

pcambra

he/him

Spanish

Asturies

CreditAttribution: pcambra commented 2 January 2012 at 20:42

I haven't tried to do the imports myself in Postgre.

I'd advise you to both test the same product import using MySQL and to test whatever other import > 50 items in postgre, a simple node import would fit.

Don't you get any errors but the SQL one when running the importer?

Comment #3

joeredhat-at-yahoo.com CreditAttribution: joeredhat-at-yahoo.com commented 3 January 2012 at 16:15

I've tried doing the exact same import in MySQL, and it works just fine. I've also tried importing regular nodes using PostgreSQL and that doesn't seem to trigger the problem. I can't figure out why, but this does seem to be related to commerce_feeds and not just feeds in general.

Comment #4

rfay

English

Palisade, CO, USA

CreditAttribution: rfay commented 3 January 2012 at 23:35

@joeredhat@yahoo.com, could you please verify that this has something to do with Commerce Feeds? Are you able to do regular feeds imports (of content, for example) without this problem when using Postgres?

Comment #5

joeredhat-at-yahoo.com CreditAttribution: joeredhat-at-yahoo.com commented 4 January 2012 at 01:43

Yes, I've double checked that I'm able to take the exact same feed and create nodes with it, so it must be somehow related to commerce_feeds, even if it's just commerce_feeds triggering different behavior in feeds or something like that.

I've gone through the code of commerce_feeds, and can't find anything that looks like it would be the culprit.

I've also asked a friend to try to reproduce the problem, and he's seeing the same thing, so I know it's not something weird about my hosting setup. (I've reproduced it on two of my own machines, one with RHEL 5 and the other w/ Fedora 16, both with slightly different versions of PHP and PostgreSQL -- just to make sure it's not my own setup.)

Comment #6

joeredhat-at-yahoo.com CreditAttribution: joeredhat-at-yahoo.com commented 4 January 2012 at 04:16

Doing more testing tonight, it definitely appears to be caused by batching of large imports. When I edit the definition for FEEDS_PROCESS_LIMIT in feeds/plugins/FeedsProcessor.inc and change the value from 50 to 40 and then re-import the products, it imports the first 40 products just fine then throws the error.

If I import less products than defined in FEEDS_PROCESS_LIMIT, the import works correctly.

Comment #7

joeredhat-at-yahoo.com CreditAttribution: joeredhat-at-yahoo.com commented 4 January 2012 at 05:10

For the record, I've filed a bug against feeds itself, even though I can only seem to trigger the bug when I'm using commerce_feeds and not when importing nodes with feeds itself. See http://drupal.org/node/1393394

Comment #9

dema502 CreditAttribution: dema502 commented 10 January 2012 at 18:03

File	Size
product_id.patch	593 bytes

I solved same problem "ERROR: invalid input syntax for integer: "C0861260" LINE 1: ... entity_type = 'commerce_product' AND entity_id = ..." with this patch

Comment #10

pcambra

he/him

Spanish

Asturies

CreditAttribution: pcambra commented 13 January 2012 at 08:47

@dema502 seems to me that the error is not the same here, are you using postgre as well?, for what it looks you're patching, seems that what you really want to map is the product_id and not the SKU, and you've got already a mapper for product ids.

Comment #11

dema502 CreditAttribution: dema502 commented 13 January 2012 at 10:41

Yes i use postgresql.

When i import fresh database - all work fine, but if i update existing products then i catch error

SELECT hash FROM feeds_item WHERE entity_type = 'commerce_product' AND entity_id = 'XXXXXX';

Look at the code - error in "plugins/FeedsProcessor.inc-646"
Who requested function getHash? Answer is in plugins/FeedsProcessor.inc:110
If $entity_id = SKU then we're caching error!

plugins/FeedsProcessor.inc-101-  public function process(FeedsSource $source, FeedsParserResult $parser_result) {
plugins/FeedsProcessor.inc-102-    $state = $source->state(FEEDS_PROCESS);
plugins/FeedsProcessor.inc-103-
plugins/FeedsProcessor.inc-104-    while ($item = $parser_result->shiftItem()) {
plugins/FeedsProcessor.inc-105-      if (!($entity_id = $this->existingEntityId($source, $parser_result)) ||
plugins/FeedsProcessor.inc-106-           ($this->config['update_existing'] != FEEDS_SKIP_EXISTING)) {
plugins/FeedsProcessor.inc-107-
plugins/FeedsProcessor.inc-108-        // Only proceed if item has actually changed.
plugins/FeedsProcessor.inc-109-        $hash = $this->hash($item);
plugins/FeedsProcessor.inc:110:        if (!empty($entity_id) && $hash == $this->getHash($entity_id)) {
plugins/FeedsProcessor.inc-111-          continue;
plugins/FeedsProcessor.inc-112-        }


plugins/FeedsProcessor.inc:645:  protected function getHash($entity_id) {
plugins/FeedsProcessor.inc-646-    if ($hash = db_query("SELECT hash FROM {feeds_item} WHERE entity_type = :type AND entity_id = :id", array(':type' => $this->entityType(), ':id' => $entity_id))->fetchField()) {
plugins/FeedsProcessor.inc-647-      // Return with the hash.
plugins/FeedsProcessor.inc-648-      return $hash;
plugins/FeedsProcessor.inc-649-    }
plugins/FeedsProcessor.inc-650-    return '';
plugins/FeedsProcessor.inc-651-  }

Request method existingEntityId only once for check existing entity_id.

Look at database sheme entity_id is bigint

t1=> \d drupal_feeds_item
                       Table "public.drupal_feeds_item"
   Column    |          Type          |               Modifiers                
-------------+------------------------+----------------------------------------
 entity_type | character varying(32)  | not null default ''::character varying
 entity_id   | bigint                 | not null
 id          | character varying(128) | not null default ''::character varying
 feed_nid    | bigint                 | not null
 imported    | integer                | not null default 0
 url         | text                   | not null
 guid        | text                   | not null
 hash        | character varying(32)  | not null default ''::character varying

And it working for me! I just propose my solution