I've modified some of my table columns that are DATETIME
fields. The SQL command is -
ALTER TABLE `#__table_name` MODIFY `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
But the Maintenance: Database reporting a problem
Table
#__table_name
has the wrong type or attributes for column 'created' with type DATETIME. (From file my-update-sql-file.sql)
If I change the default value from CURRENT_TIMESTAMP
to 'CURRENT_TIMESTAMP'
ALTER TABLE `#__table_name` MODIFY `created` DATETIME NOT NULL DEFAULT 'CURRENT_TIMESTAMP';
-----------------------------------------------------------------------^notice the single quote here
Tha means put the CURRENT_TIMESTAMP
inside a single quote the Database Maintenance problem is gone. But the default value 'CURRENT_TIMESTAMP'
is not a valid default value.
Should not show a problem for
ALTER TABLE `#__table_name` MODIFY `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
Showing Database Maintenance problem.
Table #__table_name has the wrong type or attributes for column 'created' with type DATETIME. (From file my-update-sql-file.sql)
Joomla! Version -> 4.3.2
PHP Version -> 8.0.8
Server -> mysq
Version -> 5.7.34
Collation -> utf8mb4_unicode_ci
Labels |
Added:
No Code Attached Yet
|
Labels |
Added:
Information Required
|
DATETIME !== CURRENT_TIMESTAMP
most of the times in sql iirc
DATETIME !== CURRENT_TIMESTAMP
most of the times in sql iirc
@alikon It works using CURRENT_TIMESTAMP()
as default value for a datetime column in MySQL or MariaDB. It just was never supported by the database schema checker, which never supported all possible SQL but only a subset.
the issue reports CURRENT_TIMESTAMP
and not CURRENT_TIMESTAMP()
,
anyway,
i'm not sure that it will work even on 5.6 as per our Requirements for Joomla! 4.x
but probably my old memories...
Well on MySQL and MariaDB it needs to be a function CURRENT_TIMESTAMP()
and not a constant CURRENT_TIMESTAMP
like in PostgreSQL. Regarding since which MySQL version an expression with CURRENT_TIMESTAMP()
is supported I don't really know. It might be unsupported in MySQL 5.6 and so not work for J4, but then we could consider it for J5. I would consider it anyway as a new feature and not as a bug fix, since the database checker / fixer never claimed to support that.
I would consider it anyway as a new feature and not as a bug fix, since the database checker / fixer never claimed to support that.
I fully agree with you @richard67
`
Well on MySQL and MariaDB it needs to be a function
CURRENT_TIMESTAMP()
and not a constantCURRENT_TIMESTAMP
like in PostgreSQL.
@richard67 I believe the constant CURRENT_TIMESTAMP
and the function CURRENT_TIMESTAMP()
work synonymously in MySQL and MariaDB. At least those two are working in the same way for my MySQL 5.7.34
.
But the problem is in the Joomla Database fixer. It reports CURRENT_TIMESTAMP as invalid unless you put it inside a single quote.
@ahamed I have already explained that the database fixer does not support that. I know the code of the fixer in all details.
If you put it in single quotes it is a constant string and so will make the database fixer happy but not do what you want because it's a string literal.
As far as I can see, expressions for default values were not supported in MySQL 5.7 (and so also in 5.6).
In the MySQL 8.0 documentation https://dev.mysql.com/doc/refman/8.0/en/create-table.html :
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
...
In the MySQL 5.7 documentation https://dev.mysql.com/doc/refman/5.7/en/create-table.html :
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT default_value]
...
Joomla 4 still has to work with MySQL 5.6, and so should do any 3rd party extensions for Joomla 4.
Therefore the CMS core doesn't use expressions like CURRENT_TIMESTAMP()
as default value.
What it does for the created
columns is to use the NOT NULL
constraint but not define a default value, see e.g. here https://github.com/joomla/joomla-cms/blob/4.3-dev/installation/sql/mysql/extensions.sql#L36 :
`created` datetime NOT NULL,
That means you have to explicitly specify the created
value when doing inserts of new records, and that's what the CMS core does.
In Joomla 4 you have to live with that and do it like that.
In Joomla 5 we can change that. Joomla 5 is planned to be released in October this year, so not so long to wait.
Labels |
Added:
Feature
Removed: Information Required |
supporting this in 4.4 would make maintaining 5.x and 4.x at the same time easier for extension developer. Motion for mysql minimum support version 8.0.13 is in progress.
Status | New | ⇒ | Closed |
Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2023-06-19 09:32:02 |
Closed_By | ⇒ | ahamed |
Thanks to all. I've found my answer.
@ahamed
CURRENT_TIMESTAMP
is not valid either, at least not for MySQL or MariaDB. It should beCURRENT_TIMESTAMP()
because it is a function and not a constant. But this might also not help you with the database checker because that doesn't suppoort expressions like that as default value. See my comments in the issue here: joomla-framework/database#267 . I haven't tested it yet, maybe it will work withCURRENT_TIMESTAMP()
or withcrrrent_timestamp()
. Please test and report back. Thanks in advance.