19

When looking at the structure of the customer_entity table, I noticed the created_at field has this attribute: on update CURRENT_TIMESTAMP. So every time the row is updated, the created_at timestamp changes.

It seems like this attribute should exist on the updated_at field, not the created_at field. I know it's rare that this table is directly modified due to the EAV structure, but it still seems wrong to ever modify the created_at field.

Is there a reason for this table structure, or is it just a bug?

Edit: I found a confirmed bug report from Magento for this. Issue #27944. Unfortunately, you must log in to view it. http://www.magentocommerce.com/bug-tracking/issue?issue=13882

  • 2
    Good question. I might add that these tables are in the same situation:cron_schedule, api_user, admin_user, customer_entity_address, downloadable_link_purchased, downloadable_link_purchased_item, index_event, eav_entity log_customer, sales_flat_quote_address, sales_flat_quote, sales_flat_quote_address_item, sales_flat_quote_payment, sales_flat_quote_shipping_rate, sales_recurring_profile. There might be others also. I kind of lost interest at one point, while searching for them. – Marius Apr 24 '13 at 15:48
  • I noticed sales_flat_quote first, then checked customer_entity. We just noticed it because some of our reports weren't making any sense. Can this really be a bug? – Ryre Apr 24 '13 at 16:57
  • I believe it is just a bug. – Dmytro Zavalkin Apr 24 '13 at 17:08
  • I there any way we can work around that? Sorry I am a newbie and am facing the same issue since i upgraded from 1.7.0.2 to 1.8.1 I am almost afraid to try editing the field in the database. Hope you can help!! Thanks Jinal – Jinal Jan 7 '14 at 11:37
  • @Jinal, your best option is to make the changes via mysql. Check Marius's answer for more details, and make sure to backup your database first! – Ryre Jan 7 '14 at 18:34
22

Here is what I found. The problem appears only on Magento CE 1.6+ (and matching EE versions). It's because of the new install/upgrade scripts using DDL in combination with mysql.
In versions before 1.6 this is how the created_at and updated_at columns looked like:

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

In 1.6+ the ddl looks like this:

    ->addColumn('created_at', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
        'nullable'  => false,
        ), 'Created At')
    ->addColumn('updated_at', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
        'nullable'  => false,
        ), 'Updated At')

and generates:

`created_at` timestamp NOT NULL COMMENT 'Created At',
`updated_at` timestamp NOT NULL COMMENT 'Updated At',

The difference is that the default value is missing.
And, as described here,

With neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

And since MySQL allows only one timestamp column with CURRENT_TIMESTAMP as default or for on update, the created_at column ends up with like this.

This is definitely a Magento bug .

| improve this answer | |
  • 1
    has there been any update from magento on this? it seems the bug is still in the new state. – Laura Apr 8 '14 at 17:38
  • @Laura, the bug tracking link in the answer still shows as open (almost 2 years now!). – Ryre Apr 10 '14 at 16:58
  • 2
    In Magento 1.9, the created_at column says: created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Created At'. And in release notes, it's mentioned that "The "customer since" date is correct." – MagePsycho Jun 11 '14 at 14:01
  • For EE it's affecting versions BEFORE 1.6, I have EE 1.13 and it looks like this: `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created At' – doc_id Sep 8 '15 at 17:10
4

First of all, read Marius' answer to see what's happening in the database.

I just wanted to mention that most developers won't run into this problem if their model properly extends Mage_Core_Model_Abstract. The stack looks like this:

  1. Your_Model::save calls
  2. Mage_Core_Model_Abstract::save calls
  3. Mage_Eav_Model_Entity_Abstract::save calls
  4. Mage_Eav_Model_Entity_Abstract::_beforeSave calls
  5. Mage_Eav_Model_Entity_Abstract::walkAttributes calls
  6. Mage_Eav_Model_Entity_Attribute_Backend_Time_Created::beforeSave

This does the following:

$attributeCode = $this->getAttribute()->getAttributeCode();
$date = $object->getData($attributeCode);
if (is_null($date)) {
    if ($object->isObjectNew()) {
        $object->setData($attributeCode, Varien_Date::now());
    }
}

Just note that this can have problems for some locales in both CE >= 1.8.x and EE >= 1.13.x.

| improve this answer | |
2

We, too, found this bug, and think that it's based on the difference between US and European date encoding.

In the United States, dates are written MM-DD-YYYY. (02-10-2015 = Feb 10, 2015). But in Europe and many other places, dates are written DD-MM-YYYY.  (02-10-2015 = 2nd October 2015, or Oct 2, 2015).

While Magento is based in the US, much of the development was done by programmers in the Ukraine. 

We have fixed this bug with a free Magento extension (so that you don't have to change any Magento Core Code). We have put it up on our site as a free download: http://www.CustomerParadigm.com/download/Magento-Date-Switch-Fix-Extension.zip

I've covered this in more detail on our blog here: http://www.customerparadigm.com/magento-bug-magento-customer-create-date-juxtaposition/

| improve this answer | |
-1

ce 1.9 have fix the bug in ce 1.8.1 Below is the diff: enter image description here

| improve this answer | |
  • 1
    The new code here isn't a fix for this issue. It just validates a "DDDD-DD-DD DD:DD:DD" format, or returns null. That null will still hit the database and become whatever the columns default value is. – Tyler V. Jul 31 '14 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.