Error check database update in Joomla 4

[thanhnv37]

In the sql update file, if I add multiple CHANGE COLUMN in one ALTER TABLE statement.
It will cause error in the page Check Database Updates (In Joomla 4 Backend > System > Database).

For example, if I add the statement below to the file:
sql/updates/4.8.3.sql

ALTER TABLE `#__geekelasticsearch_log_searches`
  CHANGE `first_search` `first_search` DATETIME NULL DEFAULT NULL,
  CHANGE `last_search` `last_search` DATETIME NULL DEFAULT NULL;

The class that build sql check update will generate invalid query as below (libraries/src/Schema/ChangeItem/MysqlChangeItem.php):

SHOW COLUMNS IN `jos_geekelasticsearch_log_searches` WHERE field = 'first_search' AND UPPER(type) = 'DATETIME' AND `default` = NULL, AND `null` = 'YES'

I have to split that sql statement into multiple ones as below to resolve this issue:

ALTER TABLE `#__geekelasticsearch_log_searches` CHANGE `first_search` `first_search` DATETIME NULL DEFAULT NULL;
ALTER TABLE `#__geekelasticsearch_log_searches` CHANGE `last_search` `last_search` DATETIME NULL DEFAULT NULL;

[chmst]

Does your first statement work for you if you use it directly in phpmyadmin?

[richard67]

This is expected behavior. The database checker doesn't understand alter table statements which do multiple changes. You have to make a separate statement for each change.

See e.g. here for an example: https://github.com/joomla/joomla-cms/blob/4.0-dev/administrator/components/com_admin/sql/updates/mysql/4.0.0-2018-07-29.sql

[richard67]

@chmst Your question is not really important here because it's a known behavior of the database checker not to allow everything which is valid SQL, so it needs to follow the best practice which can be found in existing update SQL scripts for the core.

[thanhnv37]

@richard67 thank you for your information.
Ok, I'll split it into multiple individual MODIFY statements.

[richard67]

@thanhnv37 I recommend to use MODIFY and not CHANGE when not renaming a column.

[richard67]

Closing as expected behavior