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;
Labels |
Added:
No Code Attached Yet
|
Labels |
Added:
Information Required
|
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
Labels |
Removed:
Information Required
|
@richard67 thank you for your information.
Ok, I'll split it into multiple individual MODIFY statements.
@thanhnv37 I recommend to use MODIFY and not CHANGE when not renaming a column.
Status | New | ⇒ | Closed |
Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2021-11-23 05:33:07 |
Closed_By | ⇒ | richard67 |
Closing as expected behavior
Does your first statement work for you if you use it directly in phpmyadmin?