MySQL8 - edit lexicon entry fails

[rutkas]

Bug report

Summary

Can not edit lexicon entry after migrating to MySQL 8

Step to reproduce

• Upgrade DB to mySQL 8
• Try to change new lexicon entry (that was not edited before and has no changed date in table) in manager backend

Editing green record (that was edited before update to mysql8) - works fine.

Observed behavior

Record is not changed and error appears in the log:

[2020-02-26 00:12:14] (ERROR @ /var/www/htdocs/core/xpdo/om/xpdoobject.class.php : 1452) Error 22007 executing statement: INSERT INTO lexicon_entries (name, value, topic, namespace, language, createdon) VALUES ('shopping_cart.total_price', 'Сумма', 'default', 'shopping_cart', 'ru', '2020-02-26 00:12:14') Array ( [0] => 22007 [1] => 1292 [2] => Incorrect datetime value: '0000-00-00 00:00:00' for column 'editedon' at row 1 )

Environment

Modx 2.7.2, PHP Version 7.3.14, MySQL 8.0.19

[rutkas]

As workaround It can be removed NO_ZERO_IN_DATE,NO_ZERO_DATE from sql_mod in mySQL config. But I wondered why I had these options in 5.7 and ModX lexicon worked fine

[JoshuaLuckers]

That's odd. It's default value is NULL. What happens if you execute the query manually?

[rutkas]

I think the problem is in db schema and editedon field:

editedon | timestamp |   | on update CURRENT_TIMESTAMP | No | 0000-00-00 00:00:00 Field is 0000-00-00 00:00:00 by default, maybe it is better to set allow NULL and change default to NULL ?

as it is already done for createdon field: createdon | datetime |   |   | Yes | NULL

[JoshuaLuckers]

In my local installation the column's default is set to NULL. Is this a clean installation or upgrade?

[rutkas]

This is upgraded MODX

[rutkas]

I have checked 2.7.2 test web site that was installed from scratch without updates from previous releases. Date fields in lexicon_entries are ok there - they both can be null and are null by default.. so it looks that the problem is with modx update script

[JoshuaLuckers]

Thanks for testing and sharing the results!