I'm having a weird problem when trying to use feeds to import more than fifty items. (I say fifty, but I'll come back and explain that more below.) First, let me set the stage.

I'm trying to use the commerce_feeds module to import products into Drupal Commerce. If I import 49 products it work just fine, but if I try to import more than 50 products it gives me a SQL error. I'm using PostgreSQL as the backend database. 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 trying to query non-integer value for the entity_id. The sku in question is the first sku after the first 50 have been imported.

In short -- it appears that there's a problem with batching of imports. I see further evidence of this that when I change FEEDS_PROCESS_LIMIT to another value such as 40 in feeds/plugins/FeedsProcessor.inc, I can then only import 40 products before getting the same SQL error.

(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. The imports seem to work just fine in MySQL.)

I originally reported this issue against commerce_feeds at http://drupal.org/node/1391868, but after talking with some of the developers there and looking through the code, I can't see how commerce_feeds is to blame. It really looks like commerce_feeds is triggering some incorrect behavior in feeds itself.

I'm more than happy to give as many details as I can to help reproduce the issue. FYI, I reproduced this on at least three different systems (with different versions of PHP and PostgreSQL), so I'm fairly certain it's not an issue with my particular setup. If you have questions, please let me know and I'd be happy to give you the info you need to help track this problem down.

CommentFileSizeAuthor
#1 1393394-serialized-data-in-text-fields-1.patch1.74 KBjoeredhat-at-yahoo.com

Comments

joeredhat-at-yahoo.com’s picture

joeredhat-at-yahoo.com commented
StatusFileSize
new 1393394-serialized-data-in-text-fields-1.patch1.74 KB

I've found part of my problem here. When importing more than FEEDS_PROCESS_LIMIT items, the batch state was being serialized and stored in the database. I learned from http://drupal.org/node/690746 that when you serialize objects with protected members, PHP puts a null byte (\0), an asterisk, and another null byte before the name of the object member, to let you know that it is protected. Unfortunately, both PostgreSQL and sqlite see that first null byte as the end of the text string, so the serialized data gets truncated in the database.

The attached patch changes the proper columns to be blob fields instead of text fields, so that the null bytes can effectively be stored in the database. I've tested the attached patch on both MySQL and PostgreSQL and it seems to solve the truncated batch information in the database.

I still don't know why I'm getting the SQL error above, but I'm making forward progress, and wanted to share this patch with those who might be affected.

joeredhat-at-yahoo.com’s picture

joeredhat-at-yahoo.com commented

Has anyone had a chance to look at the attached patch, and to comment?

asrob’s picture

asrob
he/him
Primary language Hungarian
Location Hungary 🇭🇺 🇪🇺
commented

I planned to review this patch but I had not a lot of free time. I'll review this patch tonight and write a comment. I'll write you a message on IRC when I'm ready.

asrob’s picture

asrob
he/him
Primary language Hungarian
Location Hungary 🇭🇺 🇪🇺
commented
Version: 7.x-2.0-alpha4 » 7.x-2.x-dev

Hi!

I created a test instance with Commerce kickstart profile(7.x-1.0-rc7), installed Commerce Feeds(7.x-1.2) module and its dependencies. Downloaded and applied this patch and I got similar error.

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

Just a note: You should fix this patch based on http://drupal.org/patch.

megachriz’s picture

megachriz
Primary language Dutch
commented
Status: Active » Closed (duplicate)

The database columns that the patch in #1 changes to blobs are already blobs by now. These changes were made in #712304: Batch import does not continue where it left off, instead starts from the beginning and #1715124: feeds_importer "config" column to short - change size to big, so closing this a duplicate.