datatime should be NULL not '000-00-00 00:00:00'

[Grzegorz Grzelak (OpenGLOBE.pl)]

Ubuntu 12.04, Postgres 9.1, OpenERP Version 7.0-20130806-231058
Magento connector from c2c restricted area updated today. Installed according to instruction from magentoerpconnect. I didn't check instruction for Magento side. It was installed by someone else.

It is big db of Magento used before in pair of OpenERP 6.0-Magento 1.5
Now Magento db migrated to 1.7.
OpenERP db migrated by OpenERP to 7.0.

I try to import data from Magento 1.7.

I tried to import Customers Groups - Success.

Import of Customers - no success - I iwll make another bug report.

Import of Products categories - no success - I will make another bug report
but

When I tried to import products I see that 5 Product Categories were imported from Magento (now not 100% sure if it was partial success of Product Categories import or Categories were imported together wit Products.)

Import of Product - just 1 imported. For all the rest (few thousends) I see following bug report:

 Task openerp.addons.magentoerpconnect.unit.import_synchronizer.import_record('magento.product.product', 1, 526)

Traceback (most recent call last): File "/home/gg/addons-70-custom/connector/queue/worker.py", line 121, in run_job job.perform(session) File "/home/gg/addons-70-custom/connector/queue/job.py", line 460, in perform self.result = self.func(session, *self.args, **self.kwargs) File "/home/gg/addons-70-custom/magentoerpconnect/unit/import_synchronizer.py", line 311, in import_record importer.run(magento_id, force=force) File "/home/gg/addons-70-custom/magentoerpconnect/unit/import_synchronizer.py", line 168, in run binding_id = self._create(record) File "/home/gg/addons-70-custom/magentoerpconnect/product.py", line 331, in _create openerp_binding_id = super(ProductImport, self)._create(data) File "/home/gg/addons-70-custom/magentoerpconnect/unit/import_synchronizer.py", line 121, in _create binding_id = self.session.create(self.model._name, data) File "/home/gg/addons-70-custom/connector/session.py", line 173, in create return self.pool[model].create(self.cr, self.uid, values, context=self.context) File "/home/gg/addons-70-custom/connector/producer.py", line 42, in create record_id = create_original(self, cr, uid, vals, context=context) File "/home/gg/openerp-server-7.0/openerp/osv/orm.py", line 4434, in create cr.execute('insert into "'+self._table+'" (id'+upd0+") values ("+str(id_new)+upd1+')', tuple(upd2)) File "/home/gg/openerp-server-7.0/openerp/sql_db.py", line 161, in wrapper return f(self, *args, **kwargs) File "/home/gg/openerp-server-7.0/openerp/sql_db.py", line 226, in execute res = self._obj.execute(query, params) DataError: date/time field value out of range: "0000-00-00 00:00:00" LINE 1: ...te_date) values (443,6867,'2013-03-05 20:17:18',1,'0000-00-0...

[Grzegorz Grzelak (OpenGLOBE.pl) (grzegorz-og.pl)]

OpenERP log looks like:

2013-08-09 10:51:19,846 2965 DEBUG v70_ff_20130807_migrated_01 openerp.addons.connector.unit.mapper: converting record {'ff_vegan': None, 'msrp_enabled': None, 'type_id': 'simple', 'ff_wein_land': None, 'ff_laktosefrei': None, 'sku': '1002433a', 'ff_auf_startseite': None, 'ff_bio': None, 'custom_design_to': None, 'meta_title': 'NIVEA Shampoo for Men Strong Power', 'fett_gesaettigt': None, 'news_to_date': None, 'format': '331', 'tier_price': [], 'kalorien': None, 'categories': ['353'], 'ff_flugobst': None, 'name': 'NIVEA Shampoo for Men Strong Power', 'set': '9', 'options_container': 'container2', 'cost': '1.8300', 'ff_gp_einheit': 'ml', 'meta_description': 'NIVEA Shampoo for Men Strong Power, Schampoo', 'msrp_display_actual_price_type': None, 'is_imported': None, 'tax_class_id': '1', 'deposit_helper_isdeposit': None, 'ff_regional': None, 'rebsorte': None, 'enable_googlecheckout': '1', 'special_price': None, 'geschmack': None, 'manufacturer': None, 'delivery_time': None, 'required_options': '0', 'product_id': '897', 'region': None, 'ff_von_hier': None, 'generate_meta': '0', 'ff_glutenfrei': None, 'produktbezeichnung': None, 'small_image_label': 'NIVEA Shampoo for Men Strong Power 250ml', 'ff_naturland_neu': None, 'thumbnail_label': 'NIVEA Shampoo for Men Strong Power 250ml', 'url_key': 'nivea-shampoo-for-men-strong-power', 'group_price': [], 'page_layout': None, 'minimal_price': None, 'gift_message_available': None, 'type': 'simple', 'news_from_date': None, 'visibility': '4', 'ff_jahrgang': None, 'recurring_profile': None, 'is_recurring': None, 'eiweiss': None, 'custom_design_from': None, 'msrp': None, 'image_label': 'NIVEA Shampoo for Men Strong Power 250ml', 'updated_at': '2013-01-05 18:16:34', 'websites': ['1'], 'kohlenhydrate': None, 'fett': None, 'country_of_manufacture': None, 'ff_bioland': None, 'meta_keyword': 'NIVEA Shampoo for Men Strong Power, Schampoo', 'custom_layout_update': None, 'short_description': 'NIVEA - for Men Strong Power', 'ff_grundpreis': u'250 ml (100 ml = 1,32 \u20ac)', 'ff_demeter': None, 'status': '1', 'balaststoffe': None, 'description': u'Das Strong Power For Men Shampoo st\xe4rkt das Haar messbar, ist f\xfcr die t\xe4gliche Haarw\xe4sche geeignet.', 'old_id': None, 'price': '3.2900', 'deposit_helper_sku': None, 'zucker': None, 'ff_inhaltsstoffe': None, 'url_path': 'nivea-shampoo-for-men-strong-power.html', 'typ': None, 'created_at': '0000-00-00 00:00:00', 'ff_fairtrade': None, 'special_from_date': None, 'product_ingredients': None, 'special_to_date': None, 'custom_design': None, 'has_options': '0'} to model magento.product.product
2013-08-09 10:51:20,228 2965 ERROR v70_ff_20130807_migrated_01 openerp.sql_db: bad query: insert into "magento_product_product" (id,openerp_id,"updated_at","backend_id","created_at","backorders","manage_stock","product_type","magento_id",create_uid,create_date,write_uid,write_date) values (1106,7530,'2013-01-05 18:16:34',1,'0000-00-00 00:00:00','use_default','use_default','simple','897',1,(now() at time zone 'UTC'),1,(now() at time zone 'UTC'))
Traceback (most recent call last):
  File "/home/gg/openerp-server-7.0/openerp/sql_db.py", line 226, in execute
    re...

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

Hi, that means that the category in Magento contains the value "0000-00-00 00:00:00" in the created_at field.
This field cannot have such a value when created from the Magento backend (the category likely has been imported, or modified in database).

However, the fields are defined as following in Magento.

    `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
    `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',

What an idea to put such a horrible value for a date...

I already saw errors like that when importing customers having a "0000-00-00 00:00:00" birthday, but Magento itself was bugged by this invalid date. The customers had been imported in a bad way, they have been corrected in Magento.

It doesn't please much to me to correct all the corrupted data when we import them from Magento, because Magento does not have any constraints on the DB, it allows everything, so we would end up with too many safe-guards. But it sounds like for the 'created_at', 'updated_at', we should consider to replace the "0000-00-00 00:00:00" by NULL because that's likely to happen again.

Until a patch arrives, and if you are hurried, you have the option to correct the data on Magento or submit a patch (we would be glad to coach you in that purpose if needed).

For my own curiosity, do you know how the categories have been created?

[Grzegorz Grzelak (OpenGLOBE.pl) (grzegorz-og.pl)]

I heard that Magento db 1.5 was simply opened in 1.7. I am not sure what does it mean.

[Grzegorz Grzelak (OpenGLOBE.pl) (grzegorz-og.pl)]

This patch fixed the problem. But I am not familiar with module structure. So maybe it can be designed better. I mean it should fix all dates imported from Magento not only create_date and write_date in Product table.

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

Thanks for the patch!
I think that's the responsibility of the mappers and not of the synchronizer. Although, not all models have those fields.

In the mappers, these fields are actually in direct mappings:

    direct = [
            ('created_at', 'created_at'),
            ('updated_at', 'updated_at'),
    ]

The simple but a bit tedious solution is to transform all of them in method mappings:

    @mapping
    def created_at(self, record):
        return normalize_date(record['created_at'])

Where normalize_date would be a simple helper which clean the invalid dates:

         def normalize_date(value):
             if value == '0000-00-00 00:00:00':
                 return None
             return value

The solution I would prefer is to add the possibility (directly in the `connector` module) to give a transformation method on the direct mappings.
Either in this form:

    direct = [
            ('created_at', normalize_date('created_at')),
            ('updated_at', normalize_date('updated_at')),
    ]

    (using a closure like:
     def normalize_date(field):
         def transform(self, record):
             if record[field] == '0000-00-00 00:00:00':
                 return None
             return record[field]
        return transform
    )

Or in this form:

    direct = [
            ('created_at', 'created_at', normalize_date),
            ('updated_at', 'updated_at', normalize_date),
    ]

The latter form would allow to use builtin types like str but is maybe less readable.

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

Le 9 août 2013 17:35, "Guewen Baconnier @ Camptocamp" <
<email address hidden>> a écrit :
>
> Thanks for the patch!
> I think that's the responsibility of the mappers and not of the
synchronizer. Although, not all models have those fields.
>
> In the mappers, these fields are actually in direct mappings:
>
> direct = [
> ('created_at', 'created_at'),
> ('updated_at', 'updated_at'),
> ]
>
> The simple but a bit tedious solution is to transform all of them in
> method mappings:
>
> @mapping
> def created_at(self, record):
> return normalize_date(record['created_at'])
>
> Where normalize_date would be a simple helper which clean the invalid
> dates:
>
> def normalize_date(value):
> if value == '0000-00-00 00:00:00':
> return None
> return value
>
> The solution I would prefer is to add the possibility (directly in the
`connector` module) to give a transformation method on the direct mappings.
> Either in this form:
>
> direct = [
> ('created_at', normalize_date('created_at')),
> ('updated_at', normalize_date('updated_at')),
> ]
>
> (using a closure like:
> def normalize_date(field):
> def transform(self, record):
> if record[field] == '0000-00-00 00:00:00':
> return None
> return record[field]
> return transform
> )
>

With a bit more of thinking that's definitely my preferred form as we can
write a generic converter for types or have special arguments that the
3-items tuple below would not permit. Example

    direct = [
            ('created_at', normalize_date('created_at')),
            ('updated_at', normalize_date('updated_at')),
             ('myfield', convert('origin_field', float)),
    ]

> Or in this form:
>
> direct = [
> ('created_at', 'created_at', normalize_date),
> ('updated_at', 'updated_at', normalize_date),
> ]
>
> The latter form would allow to use builtin types like str but is maybe
> less readable.
>