Database Maintenance lists issues for DATETIME columns using CURRENT_TIMESTAMP

[skurvish]

Steps to reproduce the issue

In a component installation include an update sql file that alters/modifies a table column of type DATETIME setting the default value to CURRENT_TIMESTAMP

Expected result

Installation completes without error and Database Maintenance shows no issues.

Actual result

When viewing the Database Maintenance section of the administration the component is shown with errors/warnings for the table indicating that table has the wrong type or attributes for the column with type DATETIME

System information (as much as possible)

Running J! 4.2.0-rc1

Additional comments

The DatabaseModel fetchSchemaCache function (and whatever subfunctions are used) do not take into consideration that a function might be used for a default. In the case of a DATETIME or TIMESTAMP the default could be CURRENT_TIMESTAMP, now(), etc. CURRENT_TIMESTAMP is actually stored in the database as current_timestamp(). When the system attempts to validate the update it attempts to compare the default as defined in the alter/modify statement to the results of a SHOW for that column. The problem with this is that the value stored in the database may not be what is coded in the alter/modify statement such as for CURRENT_TIMESTAMP, therefore the SHOW returns no rows and the table is flagged as having errors.

[richard67]

@skurvish In MySQL (and MariaDB) CURRENT_TIMESTAMP() is a function, so you always have to use the brackets. See https://www.tutorialspoint.com/mysql/mysql_date_time_functions_current_timestamp.htm .CURRENT_TIMESTAMP is not right.

[richard67]

I just checked this issue again and see now that it is about the database checker. So it is a CMS issue with the code in the here https://github.com/joomla/joomla-cms/blob/4.3-dev/libraries/src/Schema/ChangeItem/PostgresqlChangeItem.php and possibly also here https://github.com/joomla/joomla-cms/blob/4.3-dev/libraries/src/Schema/ChangeItem/PostgresqlChangeItem.php if we have the same problem on PostgreSQL.

I will see if I can replicate it and if yes create an issue in the CMS repository.