Author Topic: Error on DB Upgrade with 2.1 Alpha Release (Read 15759 times)

toddchris · August 06, 2008, 07:45:39 pm

Trying to upgrade to the alpha version of civi 2.1 and getting the following message running upgrade.php

DB Error: unknown error

Database Error Code: Error on rename of './mynladat_civicrm/civicrm_acl' to './mynladat_civicrm/#sql2-344f-53983' (errno: 152), 1025

Return to home page.

Code: [Select]

Error Details:


Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_acl` DROP FOREIGN KEY `FK_civicrm_acl_domain_id` [nativecode=1025 ** Error on rename of './mynladat_civicrm/civicrm_acl' to './mynladat_civicrm/#sql2-344f-53983' (errno: 152)]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_acl` DROP FOREIGN KEY `FK_civicrm_acl_domain_id` [nativecode=1025 ** Error on rename of './mynladat_civicrm/civicrm_acl' to './mynladat_civicrm/#sql2-344f-53983' (errno: 152)]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_acl` DROP FOREIGN KEY `FK_civicrm_acl_domain_id` [nativecode=1025 ** Error on rename of './mynladat_civicrm/civicrm_acl' to './mynladat_civicrm/#sql2-344f-53983' (errno: 152)]"]
)

Please let me know if more info is required.[/code]

Deepak Srivastava · August 07, 2008, 12:02:53 am

Thanks for the info. Few more ques -
- Looks like error is due to missing foreign key or wrong constraint in civicrm_acl table. Can you confirm and let us know.
- Is your 2.0 setup upgraded one or fresh one ?
- Are you comfortable playing /w db. If yes could you manually fix it and again run the script to check if there are any other errors.

Donald Lobo · August 07, 2008, 12:05:13 am

Did you run the upgrade previously and did it fail? seems like the db was partially upgraded and hence got into that state. If so, you might want to start from a clean 2.0 db

lobo

toddchris · August 07, 2008, 12:02:27 pm

Thanks for the responses. Let me answer your questions.

I had to go back and reload the database and recreate the error because I did run upgrade.php a couple of times last night, so I couldn't ensure that I had got the same error both times. However, I reran the upgrade script against a fresh set of data and got the same error message that I reported here.

My 2.0 database is what I currently have on our production site. We're only using it for a few things right now, so there's not a lot of data. I know when installed it was a version of 2.0, but I don't remember which one exactly. I believe after that I upgraded to 2.0.3 and recently to 2.0.5. Were there even any database changes between those?

I'm comfortable playing with the database. I might need some help interpreting the error as to know exactly what to change.

Thanks,
TC

Deepak Srivastava · August 07, 2008, 10:31:35 pm

Can you check the value of "version" column of civicrm_domain table for
1. Fresh 2.0 db.
2. Same db after having encountered the upgrade error.

toddchris · August 08, 2008, 04:30:20 am

The version number is the same for both. 2.0

Deepak Srivastava · August 08, 2008, 05:23:12 am

Actually i am a bit surprised as to how can the table not have foreign key to domain-id.

Do you have domain-id column present in tables like civicrm_acl, civicrm_acl_entity_role, civicrm_contact, civicrm_contribution .. etc ? If yes probably only civicrm_acl table doesn't have the foreign key constraint. You can run the following query to add it and try the upgrade again to check if there are any other errors you might encounter -

Code: [Select]

ALTER TABLE `civicrm_acl`
  ADD CONSTRAINT `FK_civicrm_acl_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`);

toddchris · August 08, 2008, 06:05:26 pm

I ran the database update that you sent, reran the upgrade and still got the same error. Then I started looking at the fresh version of the table before running the upgrade and it has the fk you were talking about.

I attached a screen shot of it, but here's something interesting. I tried to drop that same index and got pretty much the same error that I did on the upgrade.

Code: [Select]

Error
SQL query: 

ALTER TABLE `civicrm_acl` DROP INDEX `FK_civicrm_acl_domain_id` 

MySQL said:  

#1025 - Error on rename of './mynladat_civicrm/#sql-344f_57a8f' to './mynladat_civicrm/civicrm_acl' (errno: 150)

So it seems the error is not in the index being there, but that it won't let it be dropped. Is my database just screwed up or something?

tc

Donald Lobo · August 08, 2008, 07:52:46 pm

seems like there is some permissioning error with the user and db privileges. you might want to go a google/mysql search, figure and fix the issue before u try the upgrade

lobo

Cedeira · August 26, 2008, 02:48:01 pm

Does anyone know how this error can be fixed? I also got the error below when trying to upgrade a Joomla 1.5.6 site with CiviCRM from 2.0 to 2.1. The DB does not have any strange or customized permissions. I did a search for a similar error in Google, as suggested by Lobo for the DB perms and such, and I got a few hits about CiviCRM and errors with left over sessions from the previous version. Can someone let me know how to clear the sessions? Also, there seems to be some documentation missing on the upgrade, as if we don't delete the civiCRM directories under the [joomlaroot]/administrator/components and [joomlaroot]/components we get the errors about a module already existing in the directory. Shouldn't the upgrade instructions include how to uninstall a previous civicrm before doing the upgrade? Is it sufficient to remove the directories without going through the Joomla uninstall process?

Before messing the upgrade again, I am going to recover the previous installation, then I will try again another upgrade, but it would be nice to have a good set of instructions on how to best accomplish it. This document is a good start, http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Joomla+Sites+to+2.1#UpgradeJoomlaSitesto2.1-4.RuntheInstaller
but it seems like it is missing some pointers.

Thanks for your help in advance.

Code: [Select]

     Sorry. A non-recoverable error has occurred.

    DB Error: unknown error

    Database Error Code: Error on rename of './db242218876/civicrm_acl' to './db242218876/#sql2-3e7a-50d927d' (errno: 152), 1025

    Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_acl` DROP FOREIGN KEY `FK_civicrm_acl_domain_id` [nativecode=1025 ** Error on rename of './db242218876/civicrm_acl' to './db242218876/#sql2-3e7a-50d927d' (errno: 152)]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_acl` DROP FOREIGN KEY `FK_civicrm_acl_domain_id` [nativecode=1025 ** Error on rename of './db242218876/civicrm_acl' to './db242218876/#sql2-3e7a-50d927d' (errno: 152)]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_acl` DROP FOREIGN KEY `FK_civicrm_acl_domain_id` [nativecode=1025 ** Error on rename of './db242218876/civicrm_acl' to './db242218876/#sql2-3e7a-50d927d' (errno: 152)]"]
)

[/code]

Cedeira · August 26, 2008, 03:26:19 pm

I did a more specific search on the MySQL site and found these comments on one of the pages:

Quote

likewise...

ERROR 1025 (HY000): Error on rename of './MyDB/MyTable' to './MyDB/#sql2-e4a-ca3e' (errno: 152)

To avoid getting this error while trying to drop a foreign key, use the constraint name rather than the column name of the foreign key.

Summary:

99.999% of the time errors like this have nothing to do with the ability to create a temporary file and much to do with foreign key issues.

the thread can be found here, at the bottom of the page: http://dev.mysql.com/doc/refman/5.1/en/innodb-error-codes.html

I hope this helps to figure out where the problem lies. Where is the SQL file being use to upgrade? So that I can review it and see if I can troubleshoot a bit more of the problem.

thanks
Jose

Donald Lobo · August 26, 2008, 10:22:43 pm

1. Did u try to do the upgrade a couple of times, and got this error on the second time etc? the db might be in an inconsistent state if the first attempt of the upgrade failed

2. did u also migrate the civicrm component when upgrading from joomla 1.0.x -> 1.5.x? does it retain all the directories etc? if so you should uninstall it first. Can you do so and then fix the documentation (the wiki is community editable)

thanx

lobo

Cedeira · August 27, 2008, 09:37:47 am

Thanks for the comments Lobo, I don't mind changing the Wiki and or help with any documentation or answering forums, etc, but I would wait until I get it to work to make the necessary updates

.

I started the upgrade on a 1.5 installation of Joomla with CiviCRM 2.0.2. These were the steps I took:

1. I followed the upgrade instructions as detailed on the wiki page mentioned in my previous post. I read them a couple of times and also visited the forum to see the issues folks were reporting on the 2.1 upgrades.

2. When I went to install the component, I received the "directory is already being used" error. Subsequently I checked the forum posts and I decided to delete the directories as some folks suggested. The component was installed successfully after that and I checked the settings file and it had all the settings from the previous installation.

3. When I went to run the upgrade.php file I got the 152 error. And although I had some database problems with my installation prior to me trying to upgrade CiviCRM, they were not related to CiviCRM, but to other components, CiviCRM was working fine up to the point of upgrading.

To troubleshoot the issue I checked this sql file: domain_ids.mysql under civicrm/CRM/Upgrade/TwoOne/sql/ and it seems to be the one which throws the error. So, I tested the first to statements manually and I have some comments/questions:
The "Alter" statement with the "drop Foreign Key" option seems to be the cause, and my question is how does MySQL store the "Foreign Keys"?
I don't see any Foreign Key definitions on the creation of the tables?
I do see Index statements and an Index with the same name, is this the way MySQL keeps the Foreign Key relationships?
I am able to alter the table by dropping the Index with the name of the Foreign Key and then dropping the column without any problems. Is this basically what the SQL script is supposed to be doing?
I did try changing the script to drop the indexes and the columns with the Domain ID, but there seems to be another file with the same script or maybe it is cached somewhere as I tried the upgrade.php again and it failed with the same error even though I have the two first lines commented out.

Sorry for the lengthy post. Any other tips, ideas, suggestions, etc are welcomed.

thanks,
Jose

Cedeira · August 28, 2008, 07:29:06 pm

Just want to apologize for jumping the gun on the last few sentences of my last post. I went back to the SQL file that creates the tables for CiviCRM 2.0 and the constraints are part of the SQL, at least for the table I am getting the error on, civicrm_acl, which I think it might be one of the first tables the upgrade encounters. In my defense I offer the issue with MySQL, as I mentioned in the previous post, and which I found a new thread which explains the problem better: http://bugs.mysql.com/bug.php?id=10333 basically the error is supposed to say that the constraint name does not exist. So, I am a little confused as to how the table was altered prior to me running the upgrade.php for it not to find the constraint.

Well, I am going to restore back to 2.0 and redo the upgrade again, now that I now what is happening I think it might be possible to upgrade.

Regarding Joomla component upgrades: It is my understanding that component upgrades fall in two categories:
1. The first category is the one using the delete and overwrite method of the directories structures used by the component under the Joomla root structure. So, first you have to overwrite the old directories for the component, obviously making a backup first is crucial, and second running any SQL scripts to upgrade the DB schema, if needed.

2. The second category is where the administrative features of the component calls for it to be "uninstalled" through the component management administration interface in Joomla, before reinstalling the upgrade. The upgrade, in this instance, would be:(backup first) uninstall old component, install new component, using the admin interface in Joomla. Obviously this is the preferred method for everyone.

Is it safe to say that CiviCRM's upgrade falls in the first category?

If so, I wanted to let everyone know that the instructions here: http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Joomla+2.0+Sites+to+2.0.2 to upgrade from CiviCRM 2.0 to 2.0.2 on Joomla sites contain several steps that might be useful, and dare I say, better for upgrades than the ones here: http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Joomla+Sites+to+2.1 which are specific to 2.1 upgrades on Joomla sites.

I will report back with my findings on the upgrade and will update the wiki, if I find any changes are needed to the Joomla CiviCRM upgrade documentation.

Jose

emilyf · September 22, 2008, 07:14:26 am

I am having the same error. Upgrading 2.0.6 to 2.1 beta5.

Starting out, civicrm_acl does appear to have a foreign key/constraint. I have attached a phpmyadmin screenshot of this table BEFORE doing any kind of upgrade (screenshot 1).

My civicrm_domain table 'version' column has 2.0 before and after trying to run upgrade.php.

So I get the error. Then I go back to my clean 2.0 database and run the query Deepak has provided above. This seems to slightly change the table and I have attached my phpmyadmin sceenshot 2 for how this table looks after running this query.

I then try upgrade again, same error. It does drop the domain_id column completely from civicrm_acl table before churning out the same error again. I have attached screenshot 3 which is how my civicrm_acl table looks after running upgrade and getting the error again.

Again, I am always starting with my clean 2.0.6 database, not just running the upgrade over and over again.

Just for reference again, here is the error I get no matter what:

Code: [Select]

/var/www/html/sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 138
, handle, 
/var/www/html/sites/all/modules/civicrm/packages/PEAR.php, call_user_func, 912
/var/www/html/sites/all/modules/civicrm/packages/DB.php, PEAR_Error, 966
/var/www/html/sites/all/modules/civicrm/packages/PEAR.php, DB_Error, 574
/var/www/html/sites/all/modules/civicrm/packages/DB/common.php, raiseError, 1903
/var/www/html/sites/all/modules/civicrm/packages/DB/mysql.php, raiseError, 898
/var/www/html/sites/all/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/var/www/html/sites/all/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
/var/www/html/sites/all/modules/civicrm/CRM/Utils/File.php, query, 225
/var/www/html/sites/all/modules/civicrm/CRM/Upgrade/Form.php, sourceSQLFile, 75
/var/www/html/sites/all/modules/civicrm/CRM/Upgrade/TwoOne/Form/Step1.php, source, 261
/var/www/html/sites/all/modules/civicrm/CRM/Upgrade/TwoOne/Page/Upgrade.php, upgrade, 65
/var/www/html/sites/all/modules/civicrm/CRM/Upgrade/TwoOne/Page/Upgrade.php, runForm, 41
/var/www/html/sites/all/modules/civicrm/install/upgrade.php, run, 48
/var/www/html/sites/all/modules/civicrm/install/upgrade.php, run, 51

unrecoverable error
    Sorry. A non-recoverable error has occurred.

    DB Error: unknown error

    Database Error Code: Error on rename of './cctvcivicrm_v2/civicrm_acl_entity_role' to './cctvcivicrm_v2/#sql2-6cb-c7e3' (errno: 152), 1025

    Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_acl_entity_role` DROP FOREIGN KEY `FK_civicrm_acl_entity_role_domain_id` [nativecode=1025 ** Error on rename of './cctvcivicrm_v2/civicrm_acl_entity_role' to './cctvcivicrm_v2/#sql2-6cb-c7e3' (errno: 152)]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_acl_entity_role` DROP FOREIGN KEY `FK_civicrm_acl_entity_role_domain_id` [nativecode=1025 ** Error on rename of './cctvcivicrm_v2/civicrm_acl_entity_role' to './cctvcivicrm_v2/#sql2-6cb-c7e3' (errno: 152)]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_acl_entity_role` DROP FOREIGN KEY `FK_civicrm_acl_entity_role_domain_id` [nativecode=1025 ** Error on rename of './cctvcivicrm_v2/civicrm_acl_entity_role' to './cctvcivicrm_v2/#sql2-6cb-c7e3' (errno: 152)]"]
)

[/code]

CiviCRM Community Forums (archive)

News:

Author Topic: Error on DB Upgrade with 2.1 Alpha Release (Read 15759 times)

toddchris

Error on DB Upgrade with 2.1 Alpha Release

Deepak Srivastava

Re: Error on DB Upgrade with 2.1 Alpha Release

Donald Lobo

Re: Error on DB Upgrade with 2.1 Alpha Release

toddchris

Re: Error on DB Upgrade with 2.1 Alpha Release

Deepak Srivastava

Re: Error on DB Upgrade with 2.1 Alpha Release

toddchris

Re: Error on DB Upgrade with 2.1 Alpha Release

Deepak Srivastava

Re: Error on DB Upgrade with 2.1 Alpha Release

toddchris

Re: Error on DB Upgrade with 2.1 Alpha Release

Donald Lobo

Re: Error on DB Upgrade with 2.1 Alpha Release

Cedeira

Re: Error on DB Upgrade with 2.1 Alpha Release

Cedeira

Re: Error on DB Upgrade with 2.1 Alpha Release

Donald Lobo

Re: Error on DB Upgrade with 2.1 Alpha Release

Cedeira

Re: Error on DB Upgrade with 2.1 Alpha Release

Cedeira

Re: Error on DB Upgrade with 2.1 Alpha Release

emilyf

Re: Error on DB Upgrade with 2.1 Alpha Release