Base_external_referential : ID problem with export-import CSV

[YannickB (YOLO consulting)]

Hi everyone,

Today I needed to tick a checkbox field on 2000+ product in OpenERP with magentoerpconnect enabled. To do it, I used the easiest method : a CSV export of all the products with column "Database ID", "Name", and the field checkbox.

I modified the checkbox field to True in my OpenOffice Calc, and then I re-imported the CSV in OpenERP.

But I had a very ugly message :

Error trying to import this record :
 track_production: True

Error message :
Line 1 : "extref.society_name.product.product_8" contains too many dots. XML ids should not contain dots ! These are used to refer to other modules data, as in module.reference_id

I should precise that I didn't try to import the column "Id". But after this I did a CSV export with the column "Id" and I effectively get the value "extref.society_name.product.product_8".

I don't know why I had a bug with a field that was not imported, probably because it was the field "Id" that is relatively important for OpenERP.

Anyway, my devs men say to me that this absolutely not normal that we have an Id with several dots, and so there is several very bad consequences on the CSV system. For example here, Impossible to modify a product by update CSV.

I see in Administration / Customization / Database Structure / External Referential / Data two interesting fields : module and XML identifier. For example with the example product, I have
Module : product.product_8
XML identifier : extref.society_name

This quite obvious that the id is generated by base_external_referential with the concatenation "Module"."XML identifier". But this is bad because with this approach we have an id with several dots. I am already surprised that only the CSV system was compromised.

What do you think?

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

question:

are you sure that you cannot reproduce the bug without the base_external_referentials module?
I think you would have the bug anyway (so not magentoerpconnect specific).

suggestion: in any case you could replace the ext_id dots buy some other char, no?
I'm pretty sure that OpenERP has this limitation at its core (because model.data) separates modules and ids by dots. May be only the first dot could be taken into account as a global fix in OpenERP, not sure.

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi,

I confirm this bug and it is a sad one, cause using the base external referential would avoid user to use the import/export feature !

Try this simple example :

1. Export product Database_ID, code

2. Change nothing

3. Import the same .csv file

Because of the way you put the External id, OpenERP breaks :(

See attached print screen.

[YannickB (YOLO consulting) (yannick-buron)]

Hi Raphael, thanks for the answer.

I assure you that I can't reproduce the bug without base_external_referential. For example, with OpenERP demo product (so define by XML), they have an ID like "product.product_product_pc3". Only one dot, between module name and xml identifier, no problem.

Also I did some complementary test. I consult for example an ir.view : sale.view_order_form.

Here also, only one dot so no problem with CSV export-import. In Administration / Customization / Database Structure / External Referential / Data, it has :
Module : sale
XML Identifier : view_order_form

It seems that OpenERP will do the concatenation "Module"."XML Identifier". So only one dot, no problem. But we saw that ID generated by base_external_referential like "extref.society_name.product.product_8" use some dots in fields "Module" and "XML Identifier", here is the problem according to me. The only dot should be the dot in "Module"."XML Identifier", that implies no dot in "Module" and "XML Identifier".

About your suggestion, we tried before submit the report to change . in _, with no success (we can't modify the ID by CSV). Of course for our customer we'll find an answer, but we don't want that future product/sale order etc... will still be generated with several dots.

PS : It seems Administration / Customization / Database Structure / External Referential / Data, really useful for my test, is no longer accessible on new base with recent version of magentoerpconnect and base_external_referential. I did test on customer's OpenERP, which use magentoerpconnect for more than six month. But the problem occurred also on new OpenERP base.

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

Hum...

Luckily we still have TerminatOOOR for import/export.
Any idea what we can do? May be use a custom separator where we are using
dots?
Disclaimer: I didn't investigate yet where the issue comes from.

Many thanks for reporting

On Thu, Nov 11, 2010 at 10:17 AM, YannickB <email address hidden>wrote:

> Hi Raphael, thanks for the answer.
>
> I assure you that I can't reproduce the bug without
> base_external_referential. For example, with OpenERP demo product (so
> define by XML), they have an ID like "product.product_product_pc3". Only
> one dot, between module name and xml identifier, no problem.
>
> Also I did some complementary test. I consult for example an ir.view :
> sale.view_order_form.
>
> Here also, only one dot so no problem with CSV export-import. In
> Administration / Customization / Database Structure / External Referential /
> Data, it has :
> Module : sale
> XML Identifier : view_order_form
>
> It seems that OpenERP will do the concatenation "Module"."XML
> Identifier". So only one dot, no problem. But we saw that ID generated
> by base_external_referential like
> "extref.society_name.product.product_8" use some dots in fields "Module"
> and "XML Identifier", here is the problem according to me. The only dot
> should be the dot in "Module"."XML Identifier", that implies no dot in
> "Module" and "XML Identifier".
>
> About your suggestion, we tried before submit the report to change . in
> _, with no success (we can't modify the ID by CSV). Of course for our
> customer we'll find an answer, but we don't want that future
> product/sale order etc... will still be generated with several dots.
>
> PS : It seems Administration / Customization / Database Structure /
> External Referential / Data, really useful for my test, is no longer
> accessible on new base with recent version of magentoerpconnect and
> base_external_referential. I did test on customer's OpenERP, which use
> magentoerpconnect for more than six month. But the problem occurred also
> on new OpenERP base.
>
> --
> Base_external_referential : ID problem with export-import CSV
> https://bugs.launchpad.net/bugs/673614
> You received this bug notification because you are subscribed to Magento
> Open ERP Connector.
>
> Status in Magento Open ERP Connector: New
>
> Bug description:
> Hi everyone,
>
> Today I needed to tick a checkbox field on 2000+ product in OpenERP with
> magentoerpconnect enabled. To do it, I used the easiest method : a CSV
> export of all the products with column "Database ID", "Name", and the field
> checkbox.
>
> I modified the checkbox field to True in my OpenOffice Calc, and then I
> re-imported the CSV in OpenERP.
>
> But I had a very ugly message :
>
> Error trying to import this record :
> track_production: True
>
> Error message :
> Line 1 : "extref.society_name.product.product_8" contains too many dots.
> XML ids should not contain dots ! These are used to refer to other modules
> data, as in module.reference_id
>
> I should precise that I didn't try to import the column "Id". But after
> this I did a CSV export with the column "Id" and I effectively get the value
> "extref.society_name.product.product_8".
>
> I don't know...

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi Raphaël,

It comes from here (ir_model.py) :

def _update(self,cr, uid, model, module, values, xml_id=False, store=True, noupdate=False, mode='init', res_id=False, context=None):
        warning = True
        model_obj = self.pool.get(model)
        if not context:
            context = {}
        if xml_id and ('.' in xml_id):
            assert len(xml_id.split('.'))==2, _('"%s" contains too many dots. XML ids should not contain dots ! These are used to refer to other modules data, as in module.reference_id') % (xml_id)
            warning = False
            module, xml_id = xml_id.split('.')
        if (not xml_id) and (not self.doinit):
            return False
        action_id = False

And it's because they consider only one dot in xml_ids... :( !

I think we must choose another separator (like two underscore : '__') for us in base_external referential. I suggest also that once this change happen, we should have a was to convert the old one to new one in the appropriate __init__ function. So it'll be transparent for the end user.

This is quite critical, cause the import/export on product are widely used !

What do you think ?

Regards,

Joël

[Kyle Waid (midwest)]

Raphael, as I understand we are funding this fix monday as well as a few other things? Contact me via skype when you are ready. Thanks

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

fixed on 6.0 branch in rev #5262 to #5265

Be careful! in order to avoid messing up your production data, you absolutely need to pass that group of commit using --update=all after updating to rev #5265 or later!!!

Enjoy!

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

BTW: in case this wasn't clear: the fix has been released in the extra-addons 6.0 branch.

[Kyle Waid (midwest)]

I did update=all couldnt update

 File "/opt/openerp/server/bin/netsvc.py", line 489, in dispatch
    result = ExportService.getService(service_name).dispatch(method, auth, params)
  File "/opt/openerp/server/bin/service/web_services.py", line 596, in dispatch
    security.check(db,uid,passwd)
  File "/opt/openerp/server/bin/service/security.py", line 43, in check
    pool = pooler.get_pool(db)
  File "/opt/openerp/server/bin/pooler.py", line 79, in get_pool
    pool = get_db_and_pool(db_name, force_demo, status, update_module)[1]
  File "/opt/openerp/server/bin/pooler.py", line 39, in get_db_and_pool
    addons.load_modules(db, force_demo, status, update_module)
  File "/opt/openerp/server/bin/addons/__init__.py", line 883, in load_modules
    processed_modules.extend(load_module_graph(cr, graph, status, report=report, skip_modules=processed_modules))
  File "/opt/openerp/server/bin/addons/__init__.py", line 719, in load_module_graph
    init_module_objects(cr, package.name, modules)
  File "/opt/openerp/server/bin/addons/__init__.py", line 420, in init_module_objects
    obj.init(cr)
  File "/opt/openerp/addons/base_external_referentials/base_external_referentials.py", line 257, in init
    cr.execute("update ir_model_data set name = regexp_replace(name, '_([1-9])', E'/\\1') where module ilike 'extref%';")
  File "/opt/openerp/server/bin/sql_db.py", line 78, in wrapper
    return f(self, *args, **kwargs)
  File "/opt/openerp/server/bin/sql_db.py", line 131, in execute
    res = self._obj.execute(query, params)
IntegrityError: duplicate key value violates unique constraint "ir_model_data_module_name_uniq"

[Kyle Waid (midwest)]

It appears to be caught in an infinite loop

2011-03-16 13:52:17,980][midwestsupplies] DEBUG:web-services:netrpc: rpc-dispatching exception
Traceback (most recent call last):
  File "/opt/openerp/server/bin/service/netrpc_server.py", line 70, in run
    result = self.dispatch(msg[0], msg[1], msg[2:])
  File "/opt/openerp/server/bin/netsvc.py", line 499, in dispatch
    raise OpenERPDispatcherException(e, tb_s)
OpenERPDispatcherException
[2011-03-16 13:52:18,049][midwestsupplies] DEBUG:init:loading 1 packages..
[2011-03-16 13:52:18,050][midwestsupplies] INFO:init:module base: loading objects
[2011-03-16 13:52:18,161][midwestsupplies] INFO:init:module base: creating or updating database tables
[2011-03-16 13:52:18,226][midwestsupplies] DEBUG:orm.schema:Table 'ir_model_fields': dropped constraint 'ir_model_fields_size_gt_zero'. Reason: its definition changed from 'check (size > 0)' to 'CHECK (size>0)'
[2011-03-16 13:52:18,231][midwestsupplies] DEBUG:orm.schema:Table 'ir_model_fields': added constraint 'ir_model_fields_size_gt_zero' with definition=CHECK (size>0)
[2011-03-16 13:52:19,306][midwestsupplies] DEBUG:orm.schema:Table 'ir_model_data': dropped constraint 'ir_model_data_module_name_uniq'. Reason: its definition changed from 'unique (name, module)' to 'unique(name, module)'
[2011-03-16 13:52:22,232][midwestsupplies] DEBUG:orm.schema:Table 'ir_model_data': added constraint 'ir_model_data_module_name_uniq' with definition=unique(name, module)
[2011-03-16 13:52:22,371][midwestsupplies] DEBUG:orm.schema:Table 'ir_ui_view_sc': dropped constraint 'ir_ui_view_sc_shortcut_unique'. Reason: its definition changed from 'unique (res_id, resource, user_id)' to 'unique(res_id, resource, user_id)'
[2011-03-16 13:52:22,388][midwestsupplies] DEBUG:orm.schema:Table 'ir_ui_view_sc': added constraint 'ir_ui_view_sc_shortcut_unique' with definition=unique(res_id, resource, user_id)
[2011-03-16 13:52:22,628][midwestsupplies] DEBUG:orm.schema:Table 'ir_model_fields': dropped constraint 'ir_model_fields_size_gt_zero'. Reason: its definition changed from 'check (size > 0)' to 'CHECK (size>0)'
[2011-03-16 13:52:22,637][midwestsupplies] DEBUG:orm.schema:Table 'ir_model_fields': added constraint 'ir_model_fields_size_gt_zero' with definition=CHECK (size>0)
[2011-03-16 13:52:23,113][midwestsupplies] DEBUG:orm.schema:Table 'ir_rule': dropped constraint 'ir_rule_no_access_rights'. Reason: its definition changed from 'check (perm_read <> false or perm_write <> false or perm_create <> false or perm_unlink <> false)' to 'CHECK (perm_read!=False or perm_write!=False or perm_create!=False or perm_unlink!=False)'
[2011-03-16 13:52:23,116][midwestsupplies] DEBUG:orm.schema:Table 'ir_rule': added constraint 'ir_rule_no_access_rights' with definition=CHECK (perm_read!=False or perm_write!=False or perm_create!=False or perm_unlink!=False)
[2011-03-16 13:52:24,028][midwestsupplies] DEBUG:orm.schema:Table 'publisher_warranty_contract': dropped constraint 'publisher_warranty_contract_uniq_name'. Reason: its definition changed from 'unique (name)' to 'unique(name)'
[2011-03-16 13:52:24,056][midwestsupplies] DEBUG:orm.schema:Table 'publisher_warranty_contract': added constraint 'publ...