SQL Error when updating custom component in Joomla 3.9

Question

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`;

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