1. Forum
  3. Joomla
  5. Issue Tracker
  7. Update from 1.5.2 to 1.6.0 produces database error

Update from 1.5.2 to 1.6.0 produces database error

10 years 2 weeks ago - 10 years 2 weeks ago #1 by Ray
Update from 1.5.2 to 1.6.0 produces database error was created by Ray
Hi Geoff,
I hope this finds you well, my friend.

This time, I have a database updating issue brought about when attempting to update from 1.5.2 to 1.6.0. Prior to the attempt, IT had been running/functioning without any apparent issues.

Website specifics:
Joomla 2.5.19
MySQL 5.0.95

While attempting to update 1.5.2 to the latest 1.6.0 version via Joomla's built-in "Extension Manager: Update" system, I receive the following error.

Please note that after running this, the 1.6.0 update is still showing as 'available' and the component itself is still reporting as version 1.5.2, so I'm assuming that no changes were retained.
        JInstaller: :Install: Error SQL DB function failed with error number 1025
        Error on rename of './ltimp/ltimp_it_attachment' to './ltimp/#sql2-4f0e-29e6f4' (errno: 152) 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 1025
        Error on rename of './ltimp/ltimp_it_attachment' to './ltimp/#sql2-4f0e-29e6f4' (errno: 152) 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 am not a database expert by any means, but I do understand some basics. Preliminary web searches imply that it's directly related to foreign key handling, while my limited experience with/understanding of database structure has me not quite sure what a foreign key is/does.

Any thoughts on what might correct this updating issue?

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

10 years 2 weeks ago #2 by geoffc
Replied by geoffc on topic Update from 1.5.2 to 1.6.0 produces database error
Could you please run the MySQL command from phpadmin (or similar):

SHOW CREATE TABLE ltimp_it_attachment;

OR

DESCRIBE ltimp_it_attachment;

I would like to see what it thinks the name should be.

Depending upon the MySQL version the error message may be misleading.
Regards
Geoff

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

10 years 2 weeks ago #3 by geoffc
Replied by geoffc on topic Update from 1.5.2 to 1.6.0 produces database error
My suspicion is that the error is caused by the version of MySQL that you are running. Version 5.0.96 long ago ceased to be supported. It was released back in 2010. The latest version is 5.6.xx.

That said sometimes it can take a while for systems to be upgraded and dropping a foreign key is hardly 'new' functionality.

The error is (I think) coming from the InnoDB engine within MySQL. It is possible in this specific case to get around it. The change is related to an 'enhancement' to make use of the 'DELETE CASCADE' option which will delete any attachments IF the related issue is itself deleted. It was intended as a catch all in case there is something missed in the PHP code. (Not that I am aware there is, but just in case.)

We can in this case, especially if you are not using the attachments feature, therefore not worry about the foreign key and 'add' it if/when the DB is updated. {You will have to note and remember to add/change the foreign key manually later when your/the MySQL version is upgraded, if you wish to ensure that the release version of Issue Tracker stays compleletly compatible with your version. This may sound 'bad' but is not really that big a problem. However to be complete I need to mention it so yoy are aware.]

The code is present in the following file: admin\sql\updates\1.6.0.sql

What you can do is either:
a) expand out the zip file PC, and edit the file to remove the first two commands in the file.

i.e. Remove the following lines.

ALTER TABLE `#__it_attachment`
DROP FOREIGN KEY `#__it_attachment_issue_id_fk`;
ALTER TABLE `#__it_attachment`
ADD CONSTRAINT `#__it_attachment_issue_id_fk` FOREIGN KEY (issue_id) REFERENCES `#__it_issues` (alias) ON UPDATE RESTRICT ON DELETE CASCADE;

Then save your file and zip it up again and then install it anew.

OR

b) Expand out the zip file in the tmp directory on the Joomla site and after making the same change to the file, install from the same tmp directory.

This should then install correctly.

I have specifically checked the previous Issue Tracker versions and the name of the foreign key is correct (and has never changed), and the syntax for dropping it is also correct.

After some research I have also found several other similar reported problems in MySQL 5.0.x hence my suspicions. One report I found even said that after they upgraded to a later version of MySQL an 'ALTER TABLE DROP FOREIGN KEY' command worked fine.

If you post the results of the 'show create table' statement I suspect that it will show the expected foreign key name as well, reinforcing my belief. I do not have access to such an old version of MySQL so cannot personally test it for myself, but think the above suggestions should work.

Please update this post with your results of the 're-installation' with the suggested changes.
Regards
Geoff

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

10 years 1 week ago - 10 years 1 week ago #4 by Ray
Replied by Ray on topic Update from 1.5.2 to 1.6.0 produces database error
Results of SHOW CREATE TABLE ltimp_it_attachment;

<code>
CREATE TABLE `ltimp_it_attachment` (
`id` int(11) unsigned NOT NULL auto_increment COMMENT 'The system generated unique identifier for the attachment.',
`issue_id` varchar(10) NOT NULL COMMENT 'Foreign key to associated issue',
`uid` int(11) default NULL COMMENT 'User id of the user attaching the file',
`title` varchar(255) NOT NULL COMMENT 'Title for attachment',
`description` mediumtext NOT NULL COMMENT 'Description of the file attachment',
`filepath` mediumtext NOT NULL COMMENT 'Path to the file in the system',
`filename` varchar(255) NOT NULL COMMENT 'Original name of the file attachment',
`hashname` text NOT NULL COMMENT 'Hash of file name and date string',
`filetype` varchar(255) NOT NULL default 'application/octet-stream' COMMENT 'Type of file attachment',
`size` int(10) NOT NULL COMMENT 'Size of file attachment',
`ordering` int(11) NOT NULL default '0' COMMENT 'Order in which issues are presented.',
`state` tinyint(4) NOT NULL default '0' COMMENT 'State of the specific record. i.e. Published, archived, trashed etc.',
`checked_out` int(11) NOT NULL default '0' COMMENT 'Checked out indicator. User id of user editing the record.',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'Time and date when the record was checked out.',
`created_on` datetime NOT NULL COMMENT 'Audit Column: Date the record was created.',
`created_by` varchar(255) NOT NULL COMMENT 'Audit Column: The user who created the record.',
`modified_on` datetime default NULL COMMENT 'Audit Column: Date the record was last modified.',
`modified_by` varchar(255) default NULL COMMENT 'Audit Column: The user who last modified the record.',
PRIMARY KEY (`id`),
KEY `lti25b_it_attachment_issue_id_fk` (`issue_id`),
CONSTRAINT `lti25b_it_attachment_issue_id_fk` FOREIGN KEY (`issue_id`) REFERENCES `ltimp_it_issues` (`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Attachments for raised issues.'
</code>


mysql 5.0.96 is what is supplied with the Centos 5.x OS running on this server. There appears to be an update to 5.5 available on a non-default repo, so I'm preparing to try that.


Edit:
Interestingly, it appears that the it_attachment_issue_id_fk KEY & CONSTRAINT entries are still referencing my local server lti25b_, rather than the production server's ltimp_ prefix. When I open up ltimp_it_attachment in phpmyadmin, "Browse" shows "MySQL returned an empty result set (i.e. zero rows)." But a look in the Structure tab shows 18 rows of data. I do not see where to adjust the incorrect lti25b_ database reference, however.

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

10 years 1 week ago #5 by Ray
Replied by Ray on topic Update from 1.5.2 to 1.6.0 produces database error
Ok, I may have uncovered where to make that adjustment, but I'd like some confirmation before I end up breaking something. lol

Under the Routines tab for the entire site, I see the following list (provided in its entirety). As you can probably visualize, each includes Edit, Execute, Export & Drop links. As I am still relatively n00b to database handling, my concern is that if I drop the ones connected to lti25b, it may break something. Please advise.

lti25b_add_it_sample_data
lti25b_create_sample_issues
lti25b_create_sample_people
lti25b_create_sample_projects
lti25b_remove_it_sample_data
ltimp_add_it_sample_data
ltimp_create_sample_issues
ltimp_create_sample_people
ltimp_create_sample_projects
ltimp_remove_it_sample_data

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

10 years 1 week ago - 10 years 1 week ago #6 by geoffc
Replied by geoffc on topic Update from 1.5.2 to 1.6.0 produces database error
I thought there was something amiss. As you have yourself spotted. At the moment I can't begin to think why the reference is across the different prefixes. One of the problems of having multiple 'schema' in one database perhaps.

When one installs or updates a Joomla component the prefix is obtained from the Joomla instance you are working in, so it shouldn't be possible for them to get 'crossed'. The install/update scripts only reference '#__' and they get the prefix automatically from the db. It obviously has somehow though. Not seen or had that particular situation reported before, I must admit, hence my puzzlement.

My past as an 'Oracle' database admin/architect has always led me to always keep the schemas in diferent databases. i.e. DEV, PROD, PROD1 etc. Hence when installing multiple Joomla instances in the same 'MySQL' install I always make a point of specifying a different database.

What I would probably do is drop the constraint and recreate it pointing to the correct object. This is something that would have to be done using phpmyadmin or similar. I can tell you the syntax if you are use or it will be onteh MySQL web site.

It may not be a MySQL version problem specifically but it is certainly a strange one, because the error message was obviously telling the truth.

Re the routines. Each of these routines (or procedures as MySQL prefers to call them) are related to custom data being installed or removed. You should be able to safely remove them all and all you would lose would be the ability to add and remove the sample data. They are invoked from the icons in the control panel and the code inside them should all refer to the same prefix tables. They are procedural code (similar in concept to PHP) but code that runs in the database. You should be able to see the code in phpmyadmin. You might just want to have a look before you drop them to be 100% sure, but I suspect even if they are 'wrong' its only sample data handling that is involved.

Any problems let me know.

Update: Logically from a MySQL viewpoint there is nothing 'wrong' with the SQL referencing tables with a different prefix, so this is not a database error. What is wrong is how they have crossed over the two different prefixes and the update has no way of knowing. I am wondering how I would do it IF I deliberately wanted to do it in an install or update script, since I would only 'know' the current Joomla instance suffix.

What I would recommend if you are unsure what dropping an object will do, is to ensure that you have a database backup before you start. That way even if you end up destroying the database you can get back to where you started from. You can perform a database 'export' (and an import) in phpmyadmin, or if you want to use 'belt and braces' use something like Akeeba backup (core would be fine) to back up your Joomla instance. (Note that you have to tell Akeeba to backup up triggeres/procedures/views in the configuration.)

IF this is an update of a 'restored database' then I think I might have an idea as to what may have happened. I suspect that what ever backup/restore product you used has not renamed the constraint. The backup has renamed the 'referenced' table in the constraint but not the constraint name itself. This is not a specific problem except when the update is doing something like changing a contraint. In our case adding a few more options to the constraint, such as 'CASCADE DELETE'.

I must warn you that the forthcoming 1.6.1 update does this with another constraint.
Regards
Geoff

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

  1. Forum
  3. Joomla
  5. Issue Tracker
  7. Update from 1.5.2 to 1.6.0 produces database error
Time to create page: 0.156 seconds
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries