1. Forum
  3. Joomla
  5. Issue Tracker
  7. Update to 1.6.3 fails at db adjustment

Update to 1.6.3 fails at db adjustment

9 years 8 months ago #1 by Ray
Update to 1.6.3 fails at db adjustment was created by Ray
Hi Geoff,
Running a local XAMPP install of Joomla 2.5.24 and moving from IT 1.6.0 to 1.6.3, the latest update to Issue Tracker returns the following error within the Joomla Update page:

JInstaller: :Install: Error SQL DB function failed with error number 1091
Can't DROP 'ltimp_it_attachment_issue_id_fk'; check that column/key exists SQL=ALTER TABLE `ltimp_it_attachment` DROP FOREIGN KEY `ltimp_it_attachment_issue_id_fk`;
SQL =
ALTER TABLE `#__it_attachment`
 DROP FOREIGN KEY `#__it_attachment_issue_id_fk`;
Component Update: SQL error file DB function failed with error number 1091
Can't DROP 'ltimp_it_attachment_issue_id_fk'; check that column/key exists SQL=ALTER TABLE `ltimp_it_attachment` DROP FOREIGN KEY `ltimp_it_attachment_issue_id_fk`;
SQL =
ALTER TABLE `#__it_attachment`
 DROP FOREIGN KEY `#__it_attachment_issue_id_fk`;


I've checked the it_attachments table and found it empty (no rows), but it does have structure and two triggers named it_attachment_bl and it_attachment_bu, respectively. In case it's important, no attachments have ever been added/included with an issue on this system.

On the back end, I am unable to Save any existing projects, even without any modifications, although the listing seems complete. However, attempting to open the Custom Fields and Custom Field Groups pages produce the following error:
Table 'lti25b.ltimp_it_custom_field' doesn't exist SQL=SELECT a.*,uc.name AS editor,cfgroup.name AS group_name,cfgroup.id AS group_id,ag.title AS access_level FROM `ltimp_it_custom_field` AS a LEFT JOIN ltimp_users AS uc ON uc.id=a.checked_out LEFT JOIN ltimp_it_custom_field_group AS cfgroup ON cfgroup.id = a.`group` LEFT JOIN ltimp_viewlevels AS ag ON ag.id = a.access WHERE (a.state IN (0, 1)) ORDER BY a.ordering asc LIMIT 0, 100

This is not a production server, so I have no problem if the only fix is to uninstall, clean-up, re-install, but I would prefer to learn what went wrong in order to know better how to handle such situations in the future. Any thoughts you could share with me would be highly appreciated.

Please Log in or Create an account to join the conversation.

9 years 8 months ago #2 by chrisc
Replied by chrisc on topic Update to 1.6.3 fails at db adjustment
I cannot think at the moment as to why some of the database objects have not been created.

The foreign key on the attachments table was modified in release 1.6.0 to the already existing table, and having checked the code I cannot see anything amiss with the code. The fact that it is running the 'DROP' statement leads me to think that it is running the 1.6.0 update script, which implies that for some reason it didn't think it was release 1.6.0 installed, otherwise why is it trying to run the update script? Either way the foreign key should have been present since it should have been created when the table itself was created as it is part of the standard SQL syntax.

The custom code table(s) were new in release 1.6.0 so should have been created when it was installed. Again looking at the code I can't see anything immediately wrong.

I suspect that there may have been a SQL problem when release 1.6.0 was installed although why it wasn't picked up by the installer is a bit of a mystery. Possibly there may be a problem or some form of conflict with the Joomla installer running on XAMPP, but I am not specifically aware of one, but am aware of other similar problems reported on the Joomla web forums and other third party products. The Issue Tracker component itself relying upon the Joomla installer to perform its installation steps.

With SQL errors such as these, they can often be resolved by manually creating the objects directly in the database using myPHPadmin. Since you are not using the attachments feature (no entries) it would be safe to just create the foreign key manually. Likewise one could just create the the custom fields table. It wuld be advisable at the same time to also check that the other 1.6.0 objects are present such as the custom fields group table and the progress table, before retrying the 1.6.3. update again.

The 'update SQL script' is located in the zip file in admin/sql/updates and is named 1.6.0.sql. If you open the file you will see a sequence of SQL statements, each of which can be run individually. The first line is the file is the 'DROP' of the foreign key and the second is the 're-creation' of the key. Since your installation doesn't have the Foreifn key it would be safe to just run the second statement in MyPHPAdmin, modifying the table name (and any index and key entries) to reflect your database prefix. i.e. Change the '#__' references to 'ltimp_' where 'ltimp' is your specific database table prefix.

Similarly in that self same file is the code to create the custom field table. One could just extract the relevent line and after modifying them slightly as above, run them directly in MyPHPAdmin.

What would be interesting is to see if the it_custom_field_group and it_progress tables exist in the database. These were also new in release 1.6.0 so I would expect them to be present. You mention that you can't save projects which may imply that the custom field group table may not be present. It could well explain the failure to save, since there is a foreign key reference to the custom field group table.

I am not curently aware of any specific problems such as you describe, although I do recall one former problem on an XAMPP installation where a Foreign key was not present on the projects table. Never discovered why it was missing, as again it was present in the installation script and should have been created along with the table itself. The solution then was just to add the forign key manually. It resolved the problem but never explained why it was a problem and not created in the first place.

Hope this helps and possibly explains what may have gone wrong. We ourselves do not tend to use an XAMPP installation, but have seen a few similar reported problems with other third party components that have also seen 'strange' errors occur, which lead us to think that there may be some underlying Joomla conflict somewhere between the Joomla installer and XAMPP. Whether this is a Joomla or an XAMPP problem I would not like to say. Your experiences certainly lead us currently to such a belief. Possibly related just to 'foreign keys' perhaps?
Regards

If you are using our extensions please leave a review at the JED: IP Mapping | Issue Tracker | JAudit | Password Control

Please Log in or Create an account to join the conversation.

9 years 8 months ago #3 by Ray
Replied by Ray on topic Update to 1.6.3 fails at db adjustment
Well, it seems you nailed some of the issue, but I made the mistake of confusing one installation with another. The one is question was not at 1.6.0 when I attempted to update it, but rather at 1.5.2. It is still showing as 1.5.2.

I took your advice and tried manually running the 1.6.0 sql commands. All of them appeared to 'take' without issue, but a subsequent attempt to update the result to 1.6.3 produced the following:
JInstaller: :Install: Error SQL DB function failed with error number 1091 
Can't DROP 'ltimp_it_attachment_issue_id_fk'; check that column/key exists SQL=ALTER TABLE `ltimp_it_attachment` DROP FOREIGN KEY `ltimp_it_attachment_issue_id_fk`;
SQL =
ALTER TABLE `#__it_attachment`
 DROP FOREIGN KEY `#__it_attachment_issue_id_fk`;
Component Update: SQL error file DB function failed with error number 1091 
Can't DROP 'ltimp_it_attachment_issue_id_fk'; check that column/key exists SQL=ALTER TABLE `ltimp_it_attachment` DROP FOREIGN KEY `ltimp_it_attachment_issue_id_fk`;
SQL =
ALTER TABLE `#__it_attachment`
 DROP FOREIGN KEY `#__it_attachment_issue_id_fk`;

It is still attempting to drop a foreign key (it_attachment_issue_id_fk) that isn't present in my database. I tried to create a command that would add it, but not having much MySQL experience, that didn't work out so well.

I'm assuming that if you could toss me the command needed to add that key, it may be enough to allow the 1.6.3 update to run completely. Then again, we all know what they say about assumptions. ;)

Please Log in or Create an account to join the conversation.

9 years 8 months ago #4 by chrisc
Replied by chrisc on topic Update to 1.6.3 fails at db adjustment
Ahh that explains why it was trying to run the 1.6.0 update script.

I am still puzzled why the Foreign key isn't there though. It was (should have been) originaly created when the table was created in 1.5.x and in 1.6.0 it was modified to add a 'delete cascade' clause.

Anyway what we can try is to just create it anew and then when the upgrade script is rerun it should fine it. At least that is the theory. So I suggest they we run in MyPHPAdmin the following command. I have modified the prefix for your sustem.
ALTER TABLE `ltimp_it_attachment`
 ADD CONSTRAINT `ltimp_it_attachment_issue_id_fk` FOREIGN KEY (issue_id) REFERENCES `ltimp_it_issues` (alias) ON UPDATE RESTRICT ON DELETE CASCADE;

If that creates it, and it should, the update should find it!

Depending upon whether you ran all of the commands in the 1.6.0.sql update to create the tables etc, there may be an error later when a couple of columns are added to the project and issue table, but we can address that IF it occurs. Basically on an update the various update scripts are run in the admin/sql/updates folder to bring the database schema up to data, so it a case of running throught those. i.e Going from 1.5.2 to 1.6.3, we would run (if they exist) the scripts 1.6.0.sql, 1.6.1.sql, 1.6.2.sql and 1.6.3.sql. If they do not exists it means that there are no database changes at those update levels. So we didn't make any database changes between 1.6.1 and 1.6.3 versions since 1.6.2.sql and 1.6.3.sql do not exist in the zip file.
Regards

If you are using our extensions please leave a review at the JED: IP Mapping | Issue Tracker | JAudit | Password Control

Please Log in or Create an account to join the conversation.

  1. Forum
  3. Joomla
  5. Issue Tracker
  7. Update to 1.6.3 fails at db adjustment
Time to create page: 0.143 seconds
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries