Joomla 4.x Maintenance: Database showing an inconsistent problem.

[ahamed]

Steps to reproduce the issue

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.

Expected result

Should not show a problem for

ALTER TABLE `#__table_name` MODIFY `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

Actual result

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)

System information (as much as possible)

Joomla! Version -> 4.3.2
PHP Version -> 8.0.8
Server -> mysq
Version -> 5.7.34
Collation -> utf8mb4_unicode_ci

Additional comments

[richard67]

@ahamed CURRENT_TIMESTAMP is not valid either, at least not for MySQL or MariaDB. It should be CURRENT_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 with CURRENT_TIMESTAMP() or with crrrent_timestamp(). Please test and report back. Thanks in advance.

[alikon]

DATETIME !== CURRENT_TIMESTAMP most of the times in sql iirc

[richard67]

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.

[alikon]

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

[richard67]

Well on MySQL and MariaDB it needs to be a function CURRENT_TIMESTAMP() and not a constant CURRENT_TIMESTAMPlike 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.

[alikon]

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

`

[ahamed]

Well on MySQL and MariaDB it needs to be a function CURRENT_TIMESTAMP() and not a constant CURRENT_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.

[richard67]

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

[HLeithner]

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.

[ahamed]

Thanks to all. I've found my answer.