Bug on created_at in customer_entity table on update

[sdecalom]

Preconditions (*)

1.OpenMage 19.4.23
2.Mysql 8.0

Steps to reproduce (*)

Update a customer created on 2024-07-01 at 15:00.

Expected result (*)

The date of created_at on customer_entity should be 2024.07.01 15:00

Actual result (*)

The created_at date on customer_entity is 2024.07.01 17:00

[ADDISON74]

You have a very old version, unmaintained.

I confirm with the cloned version of the repository from today I cannot reproduce it. I created a customer account in the Backend and the created_at column shows correct information. I updated an existing customer then the column changes with the correct timestamp.

Please try reproducing this issue in a test environment just with the latest version without any extension, modules, or custom work.

[sdecalom]

OK thank you for your support, I will try with the latest version.

[ADDISON74]

Did you manage to solve this issue?

In my tests I used MariaDB and Percona, but I will try to MySQL 8 in order to reproduce this issue.

[sdecalom]

Hello, I manage this issue with this pull request: #4070

…

________________________________ De : ADDISON ***@***.***> Envoyé : mercredi 3 juillet 2024 17:25 À : OpenMage/magento-lts ***@***.***> Cc : Sébastien DECALOM ***@***.***>; State change ***@***.***> Objet : Re: [OpenMage/magento-lts] Bug on created_at in customer_entity table on update (Issue #4069) Did you manage to solve this issue? In my tests I used MariaDB and Percona, but I will try to MySQL 8 in order to reproduce this issue. — Reply to this email directly, view it on GitHub<#4069 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AAY7U4IUCTJ2TEVPCBV3NIDZKQJYDAVCNFSM6AAAAABKHSOIXCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEMBWGU2DIMRUG4>. You are receiving this because you modified the open/close state.

[ma4nn]

Why is the created_at column on the customer_entity table set to on update current_timestamp() at all?
I noticed this coincidentally after doing a manual UPDATE on the table and now all customer created dates are overwritten. This must be a bug, right?

[sreichel]

@ma4nn its not only customer_entity ... its for all entity tables. Seems not correct to me. updated_at should be updated.

[ma4nn]

@sreichel ah yes you're right, even worse 👍 any idea why this has been done? I also don't get why nobody complained about that yet? It's a massive bug and the data cannot be recovered at all (or at least it's an immense effort depending on the backups) if you didn't notice immediately.

Sure it's only relevant if you do direct sql updates, but this should not be so uncommon I think..

[sreichel]

I also don't get why nobody complained about that yet?

Using models work and the most used, product and category are correct?

Should be fixed ....

Suggestion for all?

    created_at        timestamp       default current_timestamp()   not null comment 'Created At',
    updated_at        timestamp       default '0000-00-00 00:00:00' not null on update current_timestamp() comment 'Updated At',

[justinbeaty]

I guess everyone is using the sample data. 😅 Without it:

  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created At',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Updated At',

[sreichel]

Removing current timestamp for created_at could break import-scripts expected behavoir. (?)

[justinbeaty]

@sreichel What I'm saying is that without installing the sample data, that is how the columns are defined.

[justinbeaty]

However, I'm sure it would be better to be:

  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created At',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated At',

Then entirely remove the two files at app/code/core/Mage/Eav/Model/Entity/Attribute/Backend/Time/, and just convert to store timezone in Mage_Eav_Model_Entity_Abstract::_afterLoad (which is where the Mage_Eav_Model_Entity_Attribute_Backend_Time_Created::afterLoad method was called).

[sreichel]

@sreichel What I'm saying is that without installing the sample data, that is how the columns are defined.

Ahhhh. Have seen it now. 🤣

So "only" sample data needs to be updated for #4069 (comment)? After years it needs also some other updates ...

[justinbeaty]

So "only" sample data needs to be updated for #4069 (comment)? After years it needs also some other updates ...

Normally sample data shouldn't need to be updated for schema changes, because upgrade scripts would take care of that. So how did it happen? Did the author of the sample data just change the schema manually!?

This original issue and #4070 still seem valid, but my idea in the last comment could solve it.

[ma4nn]

@justinbeaty nice find 👍 I had a look at three different customer installations and all had the same table structure so I assumed it was created by default, but just verified with a clean installation that this is not the case. So all these instances must have had installed the sample data at some point 🙈

I agree with your solution in your comment above.

[justinbeaty]

@ma4nn Yeah it's definitely odd because as far back as Magento 1.6.0.0-alpha1 it never had the ON UPDATE statement.

Also, Varien has some constants to help up with this:

    /**
     * Default values for timestampses - fill with current timestamp on inserting record, on changing and both cases
     */
    public const TIMESTAMP_INIT_UPDATE = 'TIMESTAMP_INIT_UPDATE';
    public const TIMESTAMP_INIT        = 'TIMESTAMP_INIT';
    public const TIMESTAMP_UPDATE      = 'TIMESTAMP_UPDATE';