1

I have a custom component and I want to add language filter to my extension. So in my sql update file I added the following two lines to Alter my tables.

ALTER TABLE `#__contract_contracts` ADD `language` CHAR(7) NOT NULL DEFAULT '*' AFTER `alias`;
ALTER TABLE `#__contract_templates` ADD `language` CHAR(7) NOT NULL DEFAULT '*' AFTER `alias`;

But I get the following error and cannot update.

Warning JInstaller: :Install: Error SQL Incorrect table name 'ALTER TABLE ' Extension Update: SQL error processing query: DB function failed with error number 1103 Incorrect table name 'ALTER TABLE ' SQL = ALTER TABLE `ALTER TABLE `

Error
Error installing component

I checked table and field names many many times and when I run the following queries in phpMyAdmin works.

ALTER TABLE `jdev_contract_contracts` ADD `language` CHAR(7) NOT NULL DEFAULT '*' AFTER `alias`;
ALTER TABLE `jdev_contract_templates` ADD `language` CHAR(7) NOT NULL DEFAULT '*' AFTER `alias`;
  • Are those two lines the only text in your ...../sql/updates/mysql/X.X.X.sql file? My suspicion is that prior text or even earlier loaded .sql files are flawed -- which will be ruining your posted queries. How many other .sql files are in the versions prior to this one? Have you had a look at them? Please tell us more. – mickmackusa Nov 14 '18 at 22:26
  • It seems to me that in a previously executed query (previous .sql version file), you have left a dangling/un-closed ALTER TABLE query and this new set of queries is being appended to it. If this "guess" turns out to be true, let me know and I'll post as an answer. – mickmackusa Nov 14 '18 at 22:37
  • Sorry no other sql update prior to this version. This is the first one 0.0.35.sql and the file does not contain any other sql statements, only those 2 lines. The truth is that the 1st time I run the update I had a typo in the 1st table name which was with single underscore instead of double, after the number sign (#_) So I changed it to (#__) and cleared the cache and the tmp folder. – mixahlos Nov 14 '18 at 23:35
1

Here is the problem as I understand it -- I am also simultaneous stroking a crystal ball as I break this down.

  1. You have an install.mysql.utf8.sql file which contains (at least) a CREATE query followed by (at least) one ALTER TABLE. It might look like this (additional tables creations are irrelevant to my explanation) ...
CREATE TABLE IF NOT EXISTS #__contract_contracts (...
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;
ALTER TABLE #_contract_contracts ADD id INT PRIMARY KEY AUTO_INCREMENT;
             ^^---whoops, typo @ prefix

So, you realize that there is a typo, you fix the syntax, clear the cache and tmp folder, but the flawed install actually completed with the typo in the install file, so it should be uninstalled. Alas, even after all those tactics in combination with any other unmentioned processes that you performed while un-installing then re-installing the component failed to erase the flawed version of your install file.

Why is a single missing underscore causing so much trouble? Joomla is very precisely hunting for the prefix placeholder #__ -- any deviation from that 3-character sequence will be ignored as a prefix replacement substring. I am also going to assume that there were no backticks around the table name after ALTER TABLE.

Since the prefix placeholder was not replaced by Joomla, MySQL will treat the # as it normally does -- as the start of a comment! This renders any characters after the # as "non-actionable". Now your query is read like this: (I'll replace the MySQL comment with PHP comment syntax)

   ALTER TABLE //_contract_contracts` ADD id INT PRIMARY KEY AUTO_INCREMENT;
   ^^^^^^^^^^^^-- only this part is read by MySQL

Then your lone update file will be added to the end of that...

ALTER TABLE ALTER TABLE `#__contract_contracts` ADD `language` CHAR(7) NOT NULL DEFAULT '*' AFTER `alias`;
ALTER TABLE `#__contract_templates` ADD `language` CHAR(7) NOT NULL DEFAULT '*' AFTER `alias`;

Finally, my advice is to uninstall your component (again). Double-check that you have actually saved your typo correction in the file being used. Reset the versioning so that Joomla knows to read the corrected install file. Feel free to clear any caching that you are concerned about. Then install the component as if for the first time.

  • 1
    Correct, the extension files updated to the new version. So because I didn't want to uninstall the component, I deleted the sql file from the administrator folder, changed the version to the xml, changed the version in the com_extensions table and I finally updated to the latest version. – mixahlos Nov 15 '18 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.