No Code Attached Yet
avatar thanhnv37
thanhnv37
22 Nov 2021

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;
avatar thanhnv37 thanhnv37 - open - 22 Nov 2021
avatar joomla-cms-bot joomla-cms-bot - change - 22 Nov 2021
Labels Added: No Code Attached Yet
avatar joomla-cms-bot joomla-cms-bot - labeled - 22 Nov 2021
avatar thanhnv37 thanhnv37 - change - 22 Nov 2021
The description was changed
avatar thanhnv37 thanhnv37 - edited - 22 Nov 2021
avatar thanhnv37 thanhnv37 - change - 22 Nov 2021
The description was changed
avatar thanhnv37 thanhnv37 - edited - 22 Nov 2021
avatar chmst
chmst - comment - 22 Nov 2021

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

avatar chmst chmst - change - 22 Nov 2021
Labels Added: Information Required
avatar chmst chmst - labeled - 22 Nov 2021
avatar richard67
richard67 - comment - 22 Nov 2021

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

avatar richard67 richard67 - change - 22 Nov 2021
Labels Removed: Information Required
avatar richard67 richard67 - unlabeled - 22 Nov 2021
avatar richard67
richard67 - comment - 22 Nov 2021

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

avatar thanhnv37
thanhnv37 - comment - 23 Nov 2021

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

avatar richard67
richard67 - comment - 23 Nov 2021

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

avatar richard67 richard67 - change - 23 Nov 2021
Status New Closed
Closed_Date 0000-00-00 00:00:00 2021-11-23 05:33:07
Closed_By richard67
avatar richard67 richard67 - close - 23 Nov 2021
avatar richard67
richard67 - comment - 23 Nov 2021

Closing as expected behavior

Add a Comment

Login with GitHub to post a comment