Why is created_at (customer_entity table) set to change on update?

Question

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

Answer 1

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 .

Answer 2

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.

Answer 3

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/

Answer 4

ce 1.9 have fix the bug in ce 1.8.1 Below is the diff: