joomla/Component-Builder
joomla/Component-Builder
39
23
Fork 11
Code Issues 83 Pull Requests Projects 4 Wiki Activity

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

New Issue
Open
opened 2024-11-12 09:53:11 +00:00 by Daniel Duvald · 5 comments
Daniel Duvald commented 2024-11-12 09:53:11 +00:00
Copy Link

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
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

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

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

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

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
92 KiB
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels   
Beta

Issues related to the a beta release

  
Bug

   
Documentation

   
enhancement

   
help wanted

   
invalid

   
joomla

Joomla related changes

  
question

   
URGENT

   
wontfix
No Label
Beta
Bug
Documentation
enhancement
help wanted
invalid
joomla
question
URGENT
wontfix
Milestone
Clear milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
No Assignees
2 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: joomla/Component-Builder#1188
No description provided.
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?