The most problematic part when updating components in Joomla! CMS is the update of the database tables and their columns.
Extensions (components) do not install themselves in Joomla! CMS. They are installed by Joomla! extension manager which uses SQL update files to update table columns in database.
It sometimes happens that the update is not performed correctly, because the SQL file tries to add a column to the database table that already exists there. Unfortunately, there is no workable method to determine if a column already exists inside static SQL file and the installation ends with an error.
Commands like: ALTER TABLE ... ADD COLUMN ... end with error in case that the required column already exists in the database. This is valid for MySQL database, not for MariaDB database. In MariaDB database, you can use ALTER TABLE ... ADD COLUMN IF NOT EXISTS ... This command would solve all problems. But the problem is, there is no difference between MySQL and MariaDB driver in Joomla! CMS. So we cannot use this MariaDB command because it will throw an error in MySQL database.
In short: all problems with updating columns would be solved by the "IF NOT EXISTS" command part, which does not exist for one database, and where it exists, it cannot be used because it would cause an error in another database.
So, if you get following screen when installing component, mostly the update SQL file needs to be found and the SQL command needs to be commented or removed manually.
This action should be done mostly by advanced users who have the knowledge to search in files and edit them:
1) SQL update files can be found in ZIP installation package, for example in Phoca Cart installation package, you can find them in following folder:
- ZIP: admin/update/sql/mysql
2) Unzip the installation ZIP package, go to the above mentioned folder and search in files for the column name which was displayed in error message.
3) Open the file where this column name occurs and comment the whole SQL command (starting with ALTER TABLE ...) or delete it (Comments in SQL files are set per double dashes (--))
For example FROM:
ALTER TABLE `#__phocacart_product_stock` ADD COLUMN `sku` varchar(255) NOT NULL DEFAULT '';
ALTER TABLE `#__phocacart_product_stock` ADD COLUMN `ean` varchar(15) NOT NULL DEFAULT '';
TO:
-- ALTER TABLE `#__phocacart_product_stock` ADD COLUMN `sku` varchar(255) NOT NULL DEFAULT '';
-- ALTER TABLE `#__phocacart_product_stock` ADD COLUMN `ean` varchar(15) NOT NULL DEFAULT '';
4) ZIP the installation package back (including newly edited files) and try to install the component again.
Phoca Cart 3.5.4
If you get the problem when updating Phoca Cart to version 3.5.4 and the problematic column is "sku" or "ean", you can try to install prepared package where the SQL commands were commented. There is no need to do manual changes described above. The package is ready to install (it does not include the SQL queries which are problematic).
Download alternative Phoca Cart version 3.5.4 and install it in Joomla! extension manager.