CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • Error upgrading from 2.2.3 to 3.0.2
Pages: [1]

Author Topic: Error upgrading from 2.2.3 to 3.0.2  (Read 2650 times)

subkult

  • Guest
Error upgrading from 2.2.3 to 3.0.2
November 18, 2009, 12:52:35 am
Joomla: 1.5.15
PHP: 5.2.11
MySQL: 5.0.81-community

I uninstalled CiviCRM v.2.2.3 and installed v.3.0.2. When I ran the database upgrade script, I received the following error:
Code: [Select]
Sorry. A non-recoverable error has occurred.


--------------------------------------------------------------------------------

DB Error: unknown error

Database Error Code: Error on rename of './hf9421_jo151/civicrm_group_organization' to './hf9421_jo151/#sql2-6c0-2046' (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] => -- CRM-4610
    ALTER TABLE `civicrm_group_organization`
        DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
        DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id` [nativecode=1025 ** Error on rename of './hf9421_jo151/civicrm_group_organization' to './hf9421_jo151/#sql2-6c0-2046' (errno: 152)]
    [type] => DB_Error
    [user_info] => -- CRM-4610
    ALTER TABLE `civicrm_group_organization`
        DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
        DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id` [nativecode=1025 ** Error on rename of './hf9421_jo151/civicrm_group_organization' to './hf9421_jo151/#sql2-6c0-2046' (errno: 152)]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="-- CRM-4610
    ALTER TABLE `civicrm_group_organization`
        DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
        DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id` [nativecode=1025 ** Error on rename of './hf9421_jo151/civicrm_group_organization' to './hf9421_jo151/#sql2-6c0-2046' (errno: 152)]"]
)
I looked over the rebuild schema, but it confused me. I restored the database with my backup, uninstalled CiviCRM then installed it again. Upon running the database upgrade script again, I received the following error:
Code: [Select]
Sorry. A non-recoverable error has occurred.


--------------------------------------------------------------------------------

DB Error: already exists

Database Error Code: Table 'civicrm_report_instance' already exists, 1050

Return to home page.

Error Details:


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

    [code] => -5
    [message] => DB Error: already exists
    [mode] => 16
    [debug_info] => CREATE TABLE civicrm_report_instance (
    id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Report Instance ID',
    title varchar(255)    COMMENT 'Report Instance Title.',
    report_id varchar(64) NOT NULL   COMMENT 'FK to civicrm_option_value for the report template',
    description varchar(255)    COMMENT 'Report Instance description.',
    permission varchar(255)    COMMENT 'permission required to be able to run this instance',
    form_values text    COMMENT 'Submitted form values for this report',
    is_active tinyint    COMMENT 'Is this entry active?',
    email_subject varchar(255)    COMMENT 'Subject of email',
    email_to text    COMMENT 'comma-separated list of email addresses to send the report to',
    email_cc text    COMMENT 'comma-separated list of email addresses to send the report to',
    header text    COMMENT 'comma-separated list of email addresses to send the report to',
    footer text    COMMENT 'comma-separated list of email addresses to send the report to',
    PRIMARY KEY ( id )
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci [nativecode=1050 ** Table 'civicrm_report_instance' already exists]
    [type] => DB_Error
    [user_info] => CREATE TABLE civicrm_report_instance (
    id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Report Instance ID',
    title varchar(255)    COMMENT 'Report Instance Title.',
    report_id varchar(64) NOT NULL   COMMENT 'FK to civicrm_option_value for the report template',
    description varchar(255)    COMMENT 'Report Instance description.',
    permission varchar(255)    COMMENT 'permission required to be able to run this instance',
    form_values text    COMMENT 'Submitted form values for this report',
    is_active tinyint    COMMENT 'Is this entry active?',
    email_subject varchar(255)    COMMENT 'Subject of email',
    email_to text    COMMENT 'comma-separated list of email addresses to send the report to',
    email_cc text    COMMENT 'comma-separated list of email addresses to send the report to',
    header text    COMMENT 'comma-separated list of email addresses to send the report to',
    footer text    COMMENT 'comma-separated list of email addresses to send the report to',
    PRIMARY KEY ( id )
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci [nativecode=1050 ** Table 'civicrm_report_instance' already exists]
    [to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TABLE civicrm_report_instance (
    id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Report Instance ID',
    title varchar(255)    COMMENT 'Report Instance Title.',
    report_id varchar(64) NOT NULL   COMMENT 'FK to civicrm_option_value for the report template',
    description varchar(255)    COMMENT 'Report Instance description.',
    permission varchar(255)    COMMENT 'permission required to be able to run this instance',
    form_values text    COMMENT 'Submitted form values for this report',
    is_active tinyint    COMMENT 'Is this entry active?',
    email_subject varchar(255)    COMMENT 'Subject of email',
    email_to text    COMMENT 'comma-separated list of email addresses to send the report to',
    email_cc text    COMMENT 'comma-separated list of email addresses to send the report to',
    header text    COMMENT 'comma-separated list of email addresses to send the report to',
    footer text    COMMENT 'comma-separated list of email addresses to send the report to',
    PRIMARY KEY ( id )
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci [nativecode=1050 ** Table 'civicrm_report_instance' already exists]"]
I have previously ran this same upgrade on another site and had no problems. Please advise.[/code][/code]

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Error upgrading from 2.2.3 to 3.0.2
November 18, 2009, 06:18:14 am

civicrm_report_instance was introduced in 2.2.7 or so. Seems like you did not restore from the right backup

can you check?

also on a test copy of 2.2.3, can u run the following sql:

Code: [Select]
    ALTER TABLE `civicrm_group_organization`
        DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
        DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id`

and see if it succeeds

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

subkult

  • Guest
Re: Error upgrading from 2.2.3 to 3.0.2
November 18, 2009, 06:23:34 am
I never upgraded to 2.2.7. I went from 2.2.3 to 3.0.2. Do I need to upgrade to 2.2.7 first? I will try the code you gave. I'm assuming I should run the code then try to upgrade. Correct?

Thanks in advance.

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Error upgrading from 2.2.3 to 3.0.2
November 18, 2009, 06:39:29 am

1. no u dont need to uograde to 2.2.7 first. however your backup is not a 2.2.3 (based on the error message)

2. the code i gave was only test code (i.e. check it the sql works on that db). You'll need to restore the db from backup once u've verified if it works or does not work

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

subkult

  • Guest
Re: Error upgrading from 2.2.3 to 3.0.2
November 18, 2009, 07:09:22 am
When I check the table civicrm_domain it shows version 2.2.3. Isn't this where you check version? I only made one backup right before I tried to upgrade to 3.0.2 and never upgraded to 2.2.7.
SQL isn't my strong point, so pardon my ignorance. Can I test the code from PHPmyadmin on the databsae I restored, and what am I looking for that would tell me that it worked? Should I do a repair or optimize database first or just run the code?

Thank you for your help.

ijames

  • Guest
Re: Error upgrading from 2.2.3 to 3.0.2
November 18, 2009, 01:59:07 pm
Yes that test code of removing the FKs will kill the upgrade. 

Important point to note.  Try the test on a just restored database, and then if it works, restore it again, THEN do the upgrade.  Then hopefully it'll work.

subkult

  • Guest
Re: Error upgrading from 2.2.3 to 3.0.2
November 18, 2009, 04:04:27 pm
ok, I restored the database and ran the code and got the following:

Error
SQL query:

ALTER TABLE `civicrm_group_organization` DROP FOREIGN KEY `FK_civicrm_group_organization_group_id` ,
DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id`

MySQL said: 

#1025 - Error on rename of './hf9421_jo151/civicrm_group_organization' to './hf9421_jo151/#sql2-6c0-15232' (errno: 152)

What now? Also, I'm still showing a civicrm_report_instance yet civicrm_domain shows version 2.2.3. Is this something I should be concerned about? Should I delete that table?

Thanks in advance.

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Error upgrading from 2.2.3 to 3.0.2
November 18, 2009, 04:52:22 pm

seems like that is a mysql error and u might not have permissions to alter table / drop FK's. U might want to do a google search on it and figure out what/why

yes, not sure why the version says 2.2.3 but has a report instance in it. you might want to investigate and figure out why there is a version mismatch between your db and the tables

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

subkult

  • Guest
Re: Error upgrading from 2.2.3 to 3.0.2
November 18, 2009, 04:57:58 pm
is it possible that restoring my db is just checking for the tables and not replacing the entire db? Maybe this is why I have tables that shouldn't be in my old version since it's not actually deleting the structure that was upgraded before the error happened and replacing it with the old database structure, but only verifying that the backup db tables exist? Also, would it make a difference if my db was 21.3 MB when restoring. I tried restoring thru PHPmyadmin this time and got a timeout error.

Thanks in advance.

subkult

  • Guest
Re: Error upgrading from 2.2.3 to 3.0.2
November 22, 2009, 02:50:12 pm
Update: I d/l a copy of 2.2.8 and installed. I recieved an error message saying that civicrm_report_instance already existed but I got CiviCRM up and running. I do get the following webpage error:

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)
Timestamp: Sun, 22 Nov 2009 22:36:11 UTC


Message: Member not found.

Line: 20
Char: 48632
Code: 0
URI: http://www.lpo.org/administrator/components/com_civicrm/civicrm/packages/dojo/dojo/dojo.js

I am fearful of upgrading to 3.0.2 because I don't understand where my original error comes from. I looked up the error on bugs,mysql.com and it said something about using the constraint name.
My question is since PHPmyadmin says this table appears to be empty, if I delete the table then upgrade, will the upgrade create the table and all should be good?
Also, on civicrm_import_job_x should the type be InnoDB or MyISAM or does it make a difference?

Thanks in advance.

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Error upgrading from 2.2.3 to 3.0.2
November 22, 2009, 03:46:27 pm

u can drop all the civicrm_import_job_* tables. we'll fix this in a future upgrade script

if u r on 2.2.8 i would not delete that table

you should try upgrading to 3.0.2 and see if things work. keep a copy of the 2.2.8 db

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

ijames

  • Guest
Re: Error upgrading from 2.2.3 to 3.0.2
November 24, 2009, 04:07:03 pm
Success!!!!

There was a LOT of corruption in my database which I've cleaned out.

I had to go through the database by setting it all up except for the constraints

Then adding the constraints and hitting the errors one by one until I had killed them all. 

I created a tool to find the corrupt records.

It would be possible to write a query for every single constraint DELETE CASCADE or SET TO NULL to find all invalid records.  Those records could be dumped out and then deleted or set to NULL depending on the type of constraint.

Whoa that was tricky...
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • Error upgrading from 2.2.3 to 3.0.2

This forum was archived on 2017-11-26.