[#39979] - Database changes skipped on install/update extension
- New
- Medium
- Build: 4.2.8
- # 39979
Steps to reproduce the issue
Inside an extension directory "administrator/components/com_xxx/sql/updates/mysql" create a newly file, for example 0.3.sql with 3 queries, for example :
ALTER TABLE #__adminloc_clients ADD FOREIGN KEY (created_by) REFERENCES #__users(id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE #__adminloc_clients ADD email2 VARCHAR(255) NOT NULL AFTER email;
CREATE TABLE #__adminloc_clients2 (id INT NOT NULL AUTO_INCREMENT , email VARCHAR(255) NOT NULL , PRIMARY KEY (id));
Then, go to System > Database > check extension com_xxx > Update structure
Expected result
Message "All database table structures are up to date."
Creation of a foreign key and a new field in table #__adminloc_clients
Creation of a new table #__adminloc_clients2
Actual result
Message "All database table structures are up to date."
New table #__adminloc_clients2 is created, but no modification on table #__adminloc_clients.
The new field "email2" isn't created, nor the foreign key
System information (as much as possible)
Joomla 4.2.8 without any extension except the one testing.
Votes
| Labels |
Removed:
?
|
||
joomla-cms-bot
- | Labels |
Added:
No Code Attached Yet
|
||
| Labels |
Added:
bug
|
||
@tristan-bellosta P.S.: The title of this issue is not really true. It is only the database schema checker / fixer which does not accept all kinds of statements and all kinds of possible and valid syntax. The installer will run the update SQL when the extension is updated if the version in the name of the update SQL script is greater than the schema version saved for this extension in database (which is the case here). You can try this by building a new version for your extension which includes the new SQL script. You will see that all statements are run, also the one to create the foreign key. So when you update an extension, nothing is skipped. It is only not handled by the database schema checker / fixer, which you normally never will have to use if your extension installs and updates right.
@richard67 OK thank you for the keyword "COLUMN" tip.
I confirm that is work well when I add it.
With your explaination I agree about the thread title.
I would expect the additional SQL updates to be executed when the version in our schema table and the provided update scripts don't match. Why are we handling these manually instead of behaving identical to the normal extension update?
I would expect the additional SQL updates to be executed when the version in our schema table and the provided update scripts don't match. Why are we handling these manually instead of behaving identical to the normal extension update?
@Hackwar Sure the scripts are run on update automatically. But the database checker / fixer has to be able to handle them, too, and the procedure provided in the description is just for testing that.
@Hackwar The database checker has never supported foreign keys, see e.g. https://github.com/joomla/joomla-cms/blob/4.2-dev/libraries/src/Schema/ChangeItem/MysqlChangeItem.php#L42 . Are you sure this issue should be labelled as bug? Shouldn't it be better labelled as new feature?
@tristan-bellosta The database checker does not and never has supported all possible valid SQL statements. So it needs to adapt the update SQL scripts to that. The reason why the new field "email2" is not added is because the database checker expects the (optional) keyword "COLUMN" to be used, see here: https://github.com/joomla/joomla-cms/blob/4.2-dev/libraries/src/Schema/ChangeItem/MysqlChangeItem.php#L90 . So the statement for adding that column should be: