Problem creating new system setting items

[masakia]

Summary

For some reason, I can not create a new system settings items. . I was able to create them in the past, but it was before I upgraded my site from version 2 to version 3. Did something change?.

Observed behavior

After hitting ‘Create’ button in the systems setting page and fill in the boxes and hit save, The dialog box simply says it could not create a setting.
I looked into error log and saw this.

[2024-12-13 15:47:17] (ERROR @ /home/public_html/core/vendor/xpdo/xpdo/src/xPDO/Om/xPDOObject.php : 1447) Error 22007 executing statement:
INSERT INTO `modx_system_settings` (`key`, `value`, `xtype`, `namespace`, `area`) VALUES ('tboard.description', '', 'textfield', 'renderTboard', 'general')
Array
(
    [0] => 22007
    [1] => 1978
    [2] => Incorrect default value '0000-00-00 00:00:00' for column 'editedon'
)

So it looks like this system generated mysql query is malformed.

Environment

MODX version 3.05 , Apache. 10.5.26-MariaDB-cll-lve .PHP 8.2

Does anyone have any idea what’s going on? I have no problem editing existing system settings items. Just cant create new ones.
.

[bobray]

I’m able to create new System Settings in MODX 3.

Can you look in the DB at the modx_system_settings table on the structure tab. See if it looks like this for the editedon field:

editedon	timestamp			Yes	NULL		ON UPDATE CURRENT_TIMESTAMP()	

Also, on the “Browse” tab, do the settings that have not been edited have NULL in the editedon field?

Did you update the site from MODX 2 to MODX 3? If so how did you do the update?

Did you skip over MODX 3.0?

Did you run setup as part of the update?

[halftrainedharry]

This issue looks very similar to the following one concerning lexicon entries:

MySQL8 - edit lexicon entry fails

---

@masakia

I was able to create them in the past, but it was before I upgraded my site from version 2 to version 3.

Did you also change the database version?
Do you use MySQL8?

[masakia]

Thanks for quick response. To make a long story short, you pointed me to the right direction. The editedon column was set to not null and default was set as “0000-00-00 00:00:00”. By setting this column to allow null and default as null, the error disappeared. I can now create systems setting as before.

Looking at the Systems’ settings, Untouched system properties’ editedon columns are all set to “0000-00-00 00:00:00” in the db. These initial settings are now showing “-0001-11-30, 12:00pm” in modx manager’s system settings page’s ‘Last Modified’ field.

I am not sure what has caused it. I have been using a2hosting’s shared linux hosting service for about 8 years and I am not sure if anything has changed on db setting although I suspect they kept server version up-to-date. Current system setting says it is mariaDB 10.5.

I should also mention that I upgraded PHP from 7.2 to 8.2 early this year. I observed increased amount of warning messages (which was discussed elsewhere in this forum by other people) but did not see any obvious errors otherwise so I thought all was good.

As for upgrade path, my tool of choice is your extension. I would like to say I upgraded to 3.0 from 2.8 before upgrading to newer versions as I usually take conservative approach, but I can not recall it for sure.

Anyway, Thanks for your advise!

[masakia]

Thanks,

Yes, it does look similar. As I replied to Bob’s post, I was able to resolve it by changing db’s column setting to allow null.

However, I have also confirmed changing the default value to other than ‘0000-00-00 00:00:00’ such as ‘1970-01-02 00:00:00’ or set it simply to current_timestamp() also fix the problem. Modx has no problem adding the system settings with these settings.

At first look it appeared MariaDB’s default setting is not allowing zero date to be entered.

However, If I set the default value back to ‘0000-00-00 00:00:00’, I can use phpmyadmin console to execute the below query and it works, but not from modx manager page!

INSERT INTO `modx_system_settings` (`key`, `value`, `xtype`, `namespace`, `area`) VALUES ('tboard.description', '', 'textfield', 'renderTboard', 'general');

So Its still a mystery why there is a problem executing this from PHP script.

Thanks!

Post Edit: I checked my two other modx installs and in their database, these editedon columns default were set to null. At least one of them were upgraded from version 2.x so I dont know what causeed this difference. But it appears that correct setting for ‘editedon’ columns (for lexicon as well) should be default to null.

[bobray]

Interesting. I’m glad you got it sorted. Thanks for reporting back.