- Forums
- Support and Presales
- Developer Members Support
- datetime field DDL, Joomla 4 is now STRICT mode
datetime field DDL, Joomla 4 is now STRICT mode
- fdinkler
- OFFLINE
-
Elite Member
- Posts: 196
- Thanks: 25
- Karma: 0
1 year 3 months ago - 1 year 3 months ago #332535
by fdinkler
datetime field DDL, Joomla 4 is now STRICT mode was created by fdinkler
Hey guys -
I use CB to power multiple event registration sites. After each year's event, I need to reset certain user profile fields in prep for the next event.
After upgrading to Jooma4, I ran into an issue resetting datetime fields using native MySQL.
CB creates datetime fields like this: <column name="acceptedtermsconsent" type="sql:datetime" null="false" default="0000-00-00 00:00:00" />,
e.g. NOT NULL DEFAULT '0000-00-00 00:00:00'
Because Joomla 4 implements strict mode, the MySQL sql_mode variable contains "NO_ZEROE_DATE" which prevents me from resetting a datetime field; "0000-00-00 00:00:00" is not allowed, and the field can't be NULL. The work around was to temporarily modify the sql_mode variable inside my reset SQL transaction: SET sql_mode=(SELECT REPLACE(@@sql_mode,"NO_ZERO_DATE", ""));
All other date fields accept NULL.
Will you be addressing datetime fields as NULLable in an upcoming release?
I use CB to power multiple event registration sites. After each year's event, I need to reset certain user profile fields in prep for the next event.
After upgrading to Jooma4, I ran into an issue resetting datetime fields using native MySQL.
CB creates datetime fields like this: <column name="acceptedtermsconsent" type="sql:datetime" null="false" default="0000-00-00 00:00:00" />,
e.g. NOT NULL DEFAULT '0000-00-00 00:00:00'
Because Joomla 4 implements strict mode, the MySQL sql_mode variable contains "NO_ZEROE_DATE" which prevents me from resetting a datetime field; "0000-00-00 00:00:00" is not allowed, and the field can't be NULL. The work around was to temporarily modify the sql_mode variable inside my reset SQL transaction: SET sql_mode=(SELECT REPLACE(@@sql_mode,"NO_ZERO_DATE", ""));
All other date fields accept NULL.
Will you be addressing datetime fields as NULLable in an upcoming release?
Last edit: 1 year 3 months ago by fdinkler. Reason: spell check
Please Log in to join the conversation.
krileon
Team Member- OFFLINE
- Posts: 68521
- Thanks: 9091
- Karma: 1434
1 year 3 months ago #332541
by krileon
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Replied by krileon on topic datetime field DDL, Joomla 4 is now STRICT mode
Joomla 4 sets sql_mode when a connection is established. So this issue only affects queries made directly against the database outside of Joomla. All you need to do is what you're already doing and that's to set sql_mode and perform your query or an easier solution is to use CB Auto Actions and a Query action to run your query so it comes from within Joomla.
Yes, we'll eventually change the default behavior for datetime to NULL from 0000-00-00 00:00:00. I do not have a timeframe for this change. We've over 12 years of peoples databases using 0000-00-00 00:00:0 default. We've to be careful not to completely and utterly break peoples installs with this change. We'll probably wait for CB 3.x to do this as we shift profile field storage into a different storage behavior instead of in CB 2.x.
Yes, we'll eventually change the default behavior for datetime to NULL from 0000-00-00 00:00:00. I do not have a timeframe for this change. We've over 12 years of peoples databases using 0000-00-00 00:00:0 default. We've to be careful not to completely and utterly break peoples installs with this change. We'll probably wait for CB 3.x to do this as we shift profile field storage into a different storage behavior instead of in CB 2.x.
Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
Please Log in to join the conversation.
Moderators: beat, nant, krileon
- Forums
- Support and Presales
- Developer Members Support
- datetime field DDL, Joomla 4 is now STRICT mode
Time to create page: 0.206 seconds
-
You are here:
- Home
- Forums
- Support and Presales
- Developer Members Support
- datetime field DDL, Joomla 4 is now STRICT mode