Importing products skip if catalog_product_entity = sku crisscross

[Kyle Waid]

When we were importing our orders about 500 of our products would not import. We couldnt figure out why. We downloaded what we could and dumped it to a log file

We discovered that when magento was returning the products OpenERP would not add the product to the database if the catalog_entity matched the sku. We are not sure why it happens

Is there any way to fix this?

[Kyle Waid (midwest)]

Update: if the external_id (or magento entity_id) matched the sku, the product is skipped.

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Kyle, not sure I understand the issue, could you detail please?

On Thu, Dec 9, 2010 at 6:20 PM, Kyle Waid <email address hidden> wrote:

> Update: if the external_id (or magento entity_id) matched the sku, the
> product is skipped.
>
> --
> You received this bug notification because you are subscribed to Magento
> Open ERP Connector.
> https://bugs.launchpad.net/bugs/688225
>
> Title:
> Importing products skip if entity_id = sku
>
> Status in Magento Open ERP Connector:
> New
>
> Bug description:
> When we were importing our orders about 500 of our products would not
> import. We couldnt figure out why. We downloaded what we could and dumped it
> to a log file
>
> We discovered that when magento was returning the products OpenERP would
> not add the product to the database if the catalog_entity matched the sku.
> We are not sure why it happens
>
> Is there any way to fix this?
>
>
>

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

I don't get it, what do you call "entity_id"? it's that a Magento field? what is it meant for? BTW, because Magento allows only unique sku's de do the same in OpenERP I believe.

[Kyle Waid (midwest)]

Hi, Sorry the title, I mean

catalog_product_entity matches sku. This is an exact copy from one of our logs
[2010-12-07 17:42:53,128][gamma4] INFO:Magento Connection:Calling Method:catalog_product.info,Arguments:['101']
[2010-12-07 17:42:53,440][gamma4] INFO:Magento Connection:Query Returned:{'set': '63', 'weight': '9.0000', 'type_id': 'simple', 'featured_description': None, 'inventory_unit': None, 'options_container': 'container2', 'small_image_label': '', 'featured_product': None, 'bitterness_level': None, 'cost': None, 'thumbnail_label': '', 'minimal_price': None, 'beer_kit_color': None, 'aitfiles_title': None, 'url_key': 'all-ale-kit-of-the-month-club-w-dry-yeast', 'sku': '101', 'yeast_instructions': '<div class="yeast-instructions"><a href="/media/pdf-printouts/Dry.pdf" target="_blank">Dry Yeast | <a href="/media/pdf-printouts/Propagator.pdf" target="_blank">Wyeast Propagator</a> | <a href="/media/pdf-printouts/Activator.pdf" target="_blank">Wyeast Activator</a> | <a href="/media/pdf-printouts/WhiteLabs.pdf" target="_blank">White Labs</a></div>', 'inventory_qty_per_unit': None, 'custom_layout_update': '', 'old_id': None, 'page_layout': None, 'tax_class_id': '0', 'category_ids': [], 'meta_keyword': '', 'gift_message_available': '2', 'categories': [], 'custom_design_to': None, 'tier_price': [], 'manufacturer': None, 'type': 'simple', 'beer_kit_style': None, 'status': '1', 'meta_description': '', 'meta_title': '',

Now you see the argument is the same as the sku. When these two areas match meaning that the catalog_product_entity and the product sku are the same it wont add the product to the ERP database. This is the result

[2010-12-07 18:19:21,589][gamma4] INFO:ext synchro:Updated in OpenERP product.product from External Ref with external_id 100 and OpenERP id 458 successfully
[2010-12-07 18:19:21,826][gamma4] INFO:ext synchro:Updated in OpenERP product.product from External Ref with external_id 1746 and OpenERP id

You see it downloads in order. When it reaches that product with external Reference it doesnt say 101 it says 1746 and as a result that product isnt added to the database.

[Kyle Waid (midwest)]

For instance I noticed argument 101 is actually product 1746, It returns everything correct except the sku which it returns as 101. For argument 1746 the sku is 101. Both arguments contain sku 101. There are over 400 instances that his happens.

[Kyle Waid (midwest)]

So for argument 101 it creates the correct sku of 1746, even though as you see above it reported a sku of '101' but when it gets to argument 1746 which actually has the sku of 101 it never creates the product. The problem is quite confusing to say the least.

So to fix the problem in a terrible way we are going to have to temporarily change all of the product skus that conflict in that manner. Is there any way to resolve this problem? Its quite ridiculous.

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Hello Kyle,

I'm digging into the same issue on OpenERP 6 + Magento. I'll fix that.
May be it's not that it's taking a wrong id by mistake, but may be there is some issue (regression then) that makes the product on the fly import to fail silently and what you see later is an update of an existing product.
I'm not sure about that yet, I'm investigating. Thanks for reporting.

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Hello Kyle,

All right, at least this is very clear to me now and well, once again, I Magento deserve it's trophy in the hall of horrors.
Frankly did those guys think for a sec before they made their stupid API??? I mean, I'm very serious.

Here, when we see that we don't have the product in OpenERP, we try to get it from Magento using the Magento product API and the product_id.
Now take a look here: http://www.magentocommerce.com/wiki/doc/webservices-api/api/catalog_product#catalog_product.info

WTF ?!!
Arguments: mixed product - product ID or Sku
I'll repeat it: "product ID or Sku" Ha Ha Ha
Ha Ha Ha
Ha Ha Ha

So before in earlier Magento version perhaps, or just if you had some luck, well, when we asked for the Magento product with the product_id from the order_line, well Magento would have answered the right product info so we could create it on the fly in OpenERP.
But, because those guys are absolutely stupids, now, if by lack of luck, your product_id actually matches some Magento product SKU, then Magento returns you the product info of the wrong product!!!!!!

I'm still not sure how we can fix that. The most obvious way would be instead to take the product sku in the order line and request the product from Magento using the SKU directly.
Now it sucks as it breaks a lot of our nice abstraction to deal with external entities without specific code. I think I'l take that way, but I'm really disappointed by Magento once again. People with no professional skills should NEVER design API's.

Fix coming hopefully.

[Kyle Waid (midwest)]

Hi, Thanks for working on it! I sort of knew it was a magento problem as in its not your fault magento is returning the wrong information, But I thought there could be some relationship so I reported it anyway. I did report it as a bug to magento also but they might not fix it. I think its absolutely pathetic that it cant return correct information. Anyone using the magento api in this way with that call would have this exact problem. Its a wonder it has gone so long without being known!

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

I got a 3 lines patches, it's coming.

Meanwhile, if you want to detect if you are impacted, it's pretty simple, all order lines with no product_id could potentially have corrupted a product which SKU was the same as the product_id of the line (you can check it with Magento API).
If all order lines have a product_id in OpenERP, then you are not impacted. If you imported orders right after the catalog import, chances are you are not impacted. Looks like we are not impacted on the dump with our 11 000 orders import here for instance, only later imports attempts made corruption.

[Kyle Waid (midwest)]

Here is a link to the magento bug if it helps at all

http://www.magentocommerce.com/bug-tracking/issue?issue=10468

[Kyle Waid (midwest)]

Hi,

We do not have a final database yet so we could just start over importing the products and then the sales orders. Would definitely like to try anything you have that could work. What we were going to do is change all of the effected products skus temporarily, import the products, then change them back and import orders. We tested that a little and it seems to work but that would be several hours worth of work.

Please let me know if you would share any fixes. Thanks again for your time.

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Hello Kyle,

Actually, I'll test again before committing, but so far it works, here is
what I did:
patch your base_external_referentials/external_osv.py,
in method "oeid_to_extid" around line 46,
just before the line:
                    result = self.get_external_data(cr, uid,
self.external_connection(cr, uid,
self.pool.get('external.referential').browse(cr, uid,
external_referential_id)), external_referential_id, {}, {'id':id})
add:
                    if context.get('alternative_key', False):
                        id = context.get('alternative_key', False)
                    result = self.get_external_data(cr, uid,
self.external_connection(cr, uid,
self.pool.get('external.referential').browse(cr, uid,
external_referential_id)), external_referential_id, {}, {'id':id})

Admittedly, this is a bit hugly to patch an abstract module just because of
Magento, but it's really the simpler, so so far...
Then we also need to change the product mapping in order to tel we want to
use that 'alternative_key'.
So in OpenERP client, edit the instance 'sale.order.line' mapping, then edit
the product_id field mapping and add the following right at the beginning of
the mapping:
context['alternative_key'] = data['sku']

Try to import again, this will do the job and force OpenERP to lookup
Magento products by sku when importing order. Luckily enough, after import,
we properly continue to read the product_id field in the Magento answer and
we continue to use that product_id key as the cross reference in OpenERP.

Can you confirm it works for you too? BTW, Olivier Dony from OpenERP SA
pointed me to the following link on Twitter
http://www.fontis.com.au/blog/magento/api-product-ids-vs-skus
it seems it's a know issue. I can't believe they did that. I implemented
myself a polyvalent database id / absolute string id lookup on the OOOR
OpenERP Ruby connector, but at least I test the nature of the field before
doing anything wrong silently. They are really kidding.

Thank you to provide feedback, I'll probably commit Sunday or Monday.

On Sat, Dec 11, 2010 at 10:24 PM, Kyle Waid <email address hidden>wrote:

> Hi,
>
> We do not have a final database yet so we could just start over
> importing the products and then the sales orders. Would definitely like
> to try anything you have that could work. What we were going to do is
> change all of the effected products skus temporarily, import the
> products, then change them back and import orders. We tested that a
> little and it seems to work but that would be several hours worth of
> work.
>
> Please let me know if you would share any fixes. Thanks again for your
> time.
>
> --
> You received this bug notification because you are subscribed to Magento
> Open ERP Connector.
> https://bugs.launchpad.net/bugs/688225
>
> Title:
> Importing products skip if catalog_product_entity = sku crisscross
>
> Status in Magento Open ERP Connector:
> Confirmed
>
> Bug description:
> When we were importing our orders about 500 of our products would not
> import. We couldnt figure out why. We downloaded what we could and dumped it
> to a log file
>
> We discovered that when magento was returning the products OpenERP would
> not ...

[Kyle Waid (midwest)]

Im not sure why, But I keep getting this error

ProgrammingError: column "categ_id" is of type integer but expression is of type boolean
LINE 1: ...product_categ_rel (product_id,categ_id) values (2267, false)

Im assuming this means there is a null value in category_ids.

At first when I looked at the database there was a null in category_ids so I put a category in there. It got to the next one and did the same thing even though both products were assigned to the same category. I changed nothing and ran it again then it got to the next product. Same thing. Also there were several, mabe even 100 products before that one that also did not have a category assigned but it didnt give me a problem like this. Its quite strange.

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

May be something like your Magento product belongs to a category that you
have deleted on Magento? Could you check?
Magento enforces nearly 0 data integrity control, in Magento you can delete
a category a product belongs to or delete a product that belongs to some
orders and we cannot do much about it (just import the record and flag it
properly for manual correction later), as when we ask Magento for the linked
resource it simply says it doesn't exist (we cannot put guards everywhere).
Could you ensure it's not such a case (an easy way to check is to do XML/RPC
request in a Python console using xmlrpclib).

On Sun, Dec 12, 2010 at 7:00 AM, Kyle Waid <email address hidden>wrote:

> Im not sure why, But I keep getting this error
>
> ProgrammingError: column "categ_id" is of type integer but expression is of
> type boolean
> LINE 1: ...product_categ_rel (product_id,categ_id) values (2267, false)
>
> Im assuming this means there is a null value in category_ids.
>
> At first when I looked at the database there was a null in category_ids
> so I put a category in there. It got to the next one and did the same
> thing even though both products were assigned to the same category. I
> changed nothing and ran it again then it got to the next product. Same
> thing. Also there were several, mabe even 100 products before that one
> that also did not have a category assigned but it didnt give me a
> problem like this. Its quite strange.
>
> --
> You received this bug notification because you are subscribed to Magento
> Open ERP Connector.
> https://bugs.launchpad.net/bugs/688225
>
> Title:
> Importing products skip if catalog_product_entity = sku crisscross
>
> Status in Magento Open ERP Connector:
> Confirmed
>
> Bug description:
> When we were importing our orders about 500 of our products would not
> import. We couldnt figure out why. We downloaded what we could and dumped it
> to a log file
>
> We discovered that when magento was returning the products OpenERP would
> not add the product to the database if the catalog_entity matched the sku.
> We are not sure why it happens
>
> Is there any way to fix this?
>
>
>

[Kyle Waid (midwest)]

Hi, in 5.0 it didnt appear to add new products but what I did was first import products to make sure I wouldnt get that dreaded error, then make the change and update the module via admin panel. Then I tried to import products again. Mabe I should have imported products after modification first.

I will test with the 6.0 module in a few hours and again with 5.0 with no imported products also. I havent looked at the 6.0 module yet but I did notice in the 5.0 module the line you speak of is not located in method oeid_to_extid but in the method directly after

def ext_id to_oeid

I hope I get it working. Thanks for your support

[Kyle Waid (midwest)]

Im terribly sorry to keep bothering you but I have one question, This fix doesnt actually produce more products upon import of products but rather adds the products to the database as the orders are imported. Because alternative_key isnt defined anywhere except in the sale.order.line am I correct?

[Kyle Waid (midwest)]

Ive spent a few hours with this and all I get when importing orders is,

'NoneType' object has no attribute 'get'

now when downloading sales orders it wont download any products and gives that error. I patched the file external_osv.py at like 96. Added the two lines, copy pasted exactly as shown. Then I went into the client

Administration>customization>database Structure>External Referentials clicked on my site
Down at object mapping clicked on sale order line
product_id then added your line copy pasted it
so its that line the result =
and the rest.

Also the problem I was having earlier occurs with the modification, if I dont have it then the products import just fine but without the extra missing products. Im not sure why it would cause that error I pasted above. But I know for sure because ive run an import several times and have some data to support that.
Would really like to get this to work. :(

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Kyle,

the fix has been committed in commit #375 in magentorpconnect. You should also update you base_external_referentials module to rev #5187 at least and also reload you instance mapping "reload mappings" button.
This force to lookup order line products by SKU rather than product_id as the SKU seems to have the priority over the product_id anyway. If no SKU is found and you order get linked to some product having the same product_id as the unfound sku, well you are very bad lucky, but in any case your Magento order would have a problem (as it would reference SKU that don't exist in Magento), I think its reasonable to assume it will be rare enough so we do nothing about it.

[Erik Dannenberg (bloopark systems) (edannenberg)]

We also noticed this stupid bug of magento last year, we opted to fix it in magento instead as that is where the bug really is. The fix is pretty small, i can look it up in our repo if anyone is interested.

[Kyle Waid (midwest)]

The fix released did not change anything, we were still not able to import any of the missing products. The only thing that has worked for us was by changing the skus of the products where their entiy_id matched the sku of a missing prduct.

[Kyle Waid (midwest)]

Would love to see your fix Erik!

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Kyle,

are you sure you reloaded your instance mappings and that you see the
"alternative_key' being passed in the product_id order line mapping?
If this is not the case, the code would behave just like before.
This is really strange as it fixes the issue here on our side.

On Fri, Dec 17, 2010 at 5:18 PM, Kyle Waid <email address hidden>wrote:

> Would love to see your fix Erik!
>
> --
> You received this bug notification because you are subscribed to Magento
> Open ERP Connector.
> https://bugs.launchpad.net/bugs/688225
>
> Title:
> Importing products skip if catalog_product_entity = sku crisscross
>
> Status in Magento Open ERP Connector:
> Fix Released
>
> Bug description:
> When we were importing our orders about 500 of our products would not
> import. We couldnt figure out why. We downloaded what we could and dumped it
> to a log file
>
> We discovered that when magento was returning the products OpenERP would
> not add the product to the database if the catalog_entity matched the sku.
> We are not sure why it happens
>
> Is there any way to fix this?
>
>
>

[Kyle Waid (midwest)]

Hi, I completely removed all openerp components, and restored a fresh backup of our magento database on a dev server. I downloaded the latest sources after you released the voucher payment fix and yes it still does not import those products. I see your mapping in the mapping template, but I dont see any difference when it is downloading the orders. I tried adding the products to the cart, anything, Just cant get those missing products.

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Kyle, do you see at least the SKU passed when requesting the products during
the order imports?
Second, this change will make no difference when downloading ALL the product
from Magento to OpenERP in the instance form. Some products will probably
not be downloaded at that moment if their SKU can match some product id. But
I think this is not something that important as downloading the products
happens on the fly while importing the orders anyway.
Anything information you can tell to help us hunt this issue Kyle?
Alternatively, if in trouble, you could hack the Magento PHP to fix the
issue on their side, but I should say I don't understand why you keep having
this issue.

On Fri, Dec 17, 2010 at 5:41 PM, Kyle Waid <email address hidden>wrote:

> Hi, I completely removed all openerp components, and restored a fresh
> backup of our magento database on a dev server. I downloaded the latest
> sources after you released the voucher payment fix and yes it still does
> not import those products. I see your mapping in the mapping template,
> but I dont see any difference when it is downloading the orders. I tried
> adding the products to the cart, anything, Just cant get those missing
> products.
>
> --
> You received this bug notification because you are subscribed to Magento
> Open ERP Connector.
> https://bugs.launchpad.net/bugs/688225
>
> Title:
> Importing products skip if catalog_product_entity = sku crisscross
>
> Status in Magento Open ERP Connector:
> Fix Released
>
> Bug description:
> When we were importing our orders about 500 of our products would not
> import. We couldnt figure out why. We downloaded what we could and dumped it
> to a log file
>
> We discovered that when magento was returning the products OpenERP would
> not add the product to the database if the catalog_entity matched the sku.
> We are not sure why it happens
>
> Is there any way to fix this?
>
>
>

[Kyle Waid (midwest)]

Hi, When you say do you see the sku passed, is this displayed in the openerp server log? Here is what happens when I click import orders. I understand that they wont be imported while products import but only during sale import. I know the mapping is set I see it there. Any information you have or if you tell me what to do I can try it. Im not sure to see if it is requesting the sku or not.

[2010-12-17 16:03:29,328][midev1] INFO:ext synchro:Created in OpenERP res.partner.address from External Ref with external_id mag_order2018154 and OpenERP id 15499 successfully
[2010-12-17 16:03:29,368][midev1] INFO:ext synchro:Created in OpenERP res.partner.address from External Ref with external_id mag_order2018155 and OpenERP id 15500 successfully
[2010-12-17 16:03:31,103][midev1] INFO:ext synchro:Updated in OpenERP product.product from External Ref with external_id 4370 and OpenERP id 3996 successfully
[2010-12-17 16:03:31,265][midev1] INFO:ext synchro:Created in OpenERP sale.order from External Ref with external_id 1109619 and OpenERP id 9502 successfully
[2010-12-17 16:03:32,097][midev1] INFO:ext synchro:Created in OpenERP res.partner.address from External Ref with external_id mag_order2018160 and OpenERP id 15501 successfully
[2010-12-17 16:03:32,128][midev1] INFO:ext synchro:Created in OpenERP res.partner.address from External Ref with external_id mag_order2018161 and OpenERP id 15502 successfully
[2010-12-17 16:03:32,758][midev1] INFO:ext synchro:Created in OpenERP sale.order from External Ref with external_id 1109620 and OpenERP id 9503 successfully

[Kyle Waid (midwest)]

This line here
[2010-12-17 16:03:31,103][midev1] INFO:ext synchro:Updated in OpenERP product.product from External Ref with external_id 4370 and OpenERP id 3996 successfully

4370 is not a sku or an entity id in magento but it says this is a product?

The products in order 1109619 as shown in the log is only one product
Starter Cheese Making Kit
SKU: CK01
Entity ID 5369

They had deleted products previously, so there is no entity_id 4370

[Kyle Waid (midwest)]

It happens on all the orders with missing products, when it imports the order, OpenERP knows that the product in that order is not in the database, but it does not add it, and the external reference is not an entity or a sku related to the product in the cart. Other than that, I have no more information unless there is some way you think I can obtain it.

[Kyle Waid (midwest)]

The reason the product mentioned above wont import is because that products entity_id is the same sku as product
Chrome Faucet with Brass Lever
SKU: 5369
Entity ID 4370

Thats the only relationship we have. If we changed this products sku to anything else, then the cheese making product will import. I do not know how to tell if it is looking up in sku, but the above part about it saying an external ID that isnt equal to anything is weird to say the least.

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Kyle,

this is very strange: I imported 12 000 orders over the weekend: I had the same sku/product_id issue before, but now it worked like a charm: it downloaded all the missing products perfectly; I have no idea what is wrong in your case.

[Kyle Waid (midwest)]

Hi, Thank you for continued support. I eventually found another way that worked. It could be perhaps we were using a different magento version than you? We are using 1.3.2.4.

What I did was compare the table in OpenERP to the Magento Entity_id table. I first downloaded what products I could. Then on a dummy database I ran a special query and nulled all of the skus where they conflict using the list I got. I then downloaded all of our orders and the products imported that way. I noticed that if I click import products, it will overwrite the original ones, erasing the skus so when I imported orders it detected the missing products but didnt overwrite the ones already there. When I get all of the products, I drop that database and sync with the live site. Hey it works for me. I checked also and it appears to correctly map the products because it looks like the import orders maps the product based on the name field.