joomla/Component-Builder
42
28 Fork 13
Code Issues 56 Pull Requests Projects Wiki Activity

[BUG]: datetime field NULL value is wrong in compiled install.mysql.utf8.sql #1188

New Issue
Closed
opened 2024-11-12 09:53:11 +00:00 by Daniel Duvald · 6 comments
Daniel Duvald commented 2024-11-12 09:53:11 +00:00
Copy link
Copy source

What Happened?

In my install.mysql.utf8.sql I see this invalid setting for a DATETIME field with default value set to None:
finish_timestamp DATETIME NULL DEFAULT '',
It should be:
finish_timestamp DATETIME NULL,

Steps to reproduce the Bug

Create a datetime field (calendar).
In the database tab, set datatype = datetime, data default = none, Null Switch = NULL

Which Joomla version are you compiling in?

v5.2.1

Which PHP version are you compiling in?

v8.1

Which Joomla versions are you targeting?

v5.2.1

Which PHP version are you targeting?

8.2

Which Web server is JCB running on?

Apache 2.4

Which Relational Database is JCB running on?

MariaDB 10.4.31

Which OS is JCB running on?

Ubuntu 20.0.4

Which JCB version are you using?

v5.0.4-beta1

Where in JCB did this issue occur?

Installation of a component

On which browsers did you encounter the issue?

Safari

Additional Comments

No response

### What Happened? In my install.mysql.utf8.sql I see this invalid setting for a DATETIME field with default value set to None: `finish_timestamp` DATETIME NULL DEFAULT '', It should be: `finish_timestamp` DATETIME NULL, ### Steps to reproduce the Bug Create a datetime field (calendar). In the database tab, set datatype = datetime, data default = none, Null Switch = NULL ### Which Joomla version are you compiling in? v5.2.1 ### Which PHP version are you compiling in? v8.1 ### Which Joomla versions are you targeting? v5.2.1 ### Which PHP version are you targeting? 8.2 ### Which Web server is JCB running on? Apache 2.4 ### Which Relational Database is JCB running on? MariaDB 10.4.31 ### Which OS is JCB running on? Ubuntu 20.0.4 ### Which JCB version are you using? v5.0.4-beta1 ### Where in JCB did this issue occur? Installation of a component ### On which browsers did you encounter the issue? Safari ### Additional Comments _No response_
Daniel Duvald added the Bug label 2024-11-12 09:53:11 +00:00
Daniel Duvald commented 2024-11-12 09:53:24 +00:00
Author
Copy link
Copy source
No description provided.
Skærmbillede 2024-11-12 kl. 10.52.45.png
255 KiB
Skærmbillede 2024-11-12 kl. 10.52.45.png
Daniel Duvald commented 2024-12-06 23:03:18 +00:00
Author
Copy link
Copy source

Just checking in on this issue?

Just checking in on this issue?
Llewellyn commented 2024-12-11 13:39:15 +00:00
Owner
Copy link
Copy source

This normally worked for me:

image

As for the finish_timestamp DATETIME NULL DEFAULT '', to change this to show NULL I will have to check... this might take a while :)

This normally worked for me: ![image](https://git.vdm.dev/attachments/adb1e10e-8706-4133-ad61-a367f88c08df) As for the `finish_timestamp DATETIME NULL DEFAULT '',` to change this to show `NULL` I will have to check... this might take a while :)
image.png
32 KiB
Daniel Duvald commented 2024-12-13 23:22:27 +00:00
Author
Copy link
Copy source

0000-00-00 00:00:00 would require to disable strict mode on MySQL 5.7+. I think it's considered bad practice to do that. In MariaDB from 10.2.4 onwards STRICT_TRANS_TABLES is enabled by default. This mode prevents the use of invalid date values like 0000-00-00 00:00:00.

0000-00-00 00:00:00 would require to disable strict mode on MySQL 5.7+. I think it's considered bad practice to do that. In MariaDB from 10.2.4 onwards STRICT_TRANS_TABLES is enabled by default. This mode prevents the use of invalid date values like 0000-00-00 00:00:00.
Llewellyn commented 2024-12-14 09:39:12 +00:00
Owner
Copy link
Copy source

Yes, I am aware of this, and we did add this change to the core dates

To achieve this you can do the following:

image

That will produce:

`created` DATETIME NULL,

This string EMPTY in the other value removes the DEFAULT '', we opt for that as the solution since we used the 'none' option to mean empty string... in most of our projects. So maybe we should rethink the whole design of that... for now just add EMPTY string to the other value and it should work.

This also works:

image

This will produce:

`created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP
Yes, I am aware of this, and we did add this [change to the core dates](https://git.vdm.dev/joomla/Component-Builder/src/commit/703e8e0133c2266f4a711d34bb0898029a6b7671/admin/sql/install.mysql.utf8.sql#L214) To achieve this you can do the following: ![image](https://git.vdm.dev/attachments/fb8e3a63-376c-4fff-861b-859c2f0d65a3) That will produce: ```sql `created` DATETIME NULL, ``` This string `EMPTY` in the other value removes the `DEFAULT ''`, we opt for that as the solution since we used the `'none'` option to mean empty string... in most of our projects. So maybe we should rethink the whole design of that... for now just add `EMPTY` string to the other value and it should work. This also works: ![image](https://git.vdm.dev/attachments/aae0b53e-8675-44ef-bd1a-a0ce746a2b5c) This will produce: ```sql `created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ```
image.png
106 KiB
image.png
91 KiB
Llewellyn commented 2025-06-30 07:55:26 +00:00
Owner
Copy link
Copy source

I hope this has been resolved :)

I hope this has been resolved :)
Sign in to join this conversation.
No Branch/Tag Specified
Labels
Clear labels
Beta
Issues related to the a beta release
 Bug
Documentation
URGENT
enhancement
help wanted
invalid
joomla
Joomla related changes
 question
wontfix
No labels Bug
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
aamigo Eunice Hannah Jeff Lemuel Llewellyn Malachi Roline vistamedia Martin (Martin Kopp) aB0t (Robot) ro-ot (root) TLWebdesign (Tom van der Laan)
No Assignees
2 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: joomla/Component-Builder#1188