Author Topic: Upgrading Non-Standard Database (Read 768 times)

Hershel · March 09, 2011, 11:47:27 am

I have an install which was migrated from a host that did not support InnoDB. I'm not sure how they managed, but they had version 3.2.3 running (under Drupal). They were getting errors, however, so I migrated the site and changed all tables to InnoDB. Now it works fine.

Running the upgrade to 3.3.5 however, gives me this error:

Code: [Select]

ALTER TABLE civicrm_contact DROP  FOREIGN KEY FK_civicrm_contact_mail_to_household_id 
[nativecode=1025 ** Error on rename of'./civicrm/civicrm_contact'  to'./civicrm/#sql2-1241-4f29fb'  (errno: 152)

This code runs:

Code: [Select]

ALTER TABLE `civicrm_contact` DROP INDEX `FK_civicrm_contact_mail_to_household_id`
So it would appear me to that this FK was created as an Index instead of FK on the old host. I presume there may be more.

I found the above SQL in CRM/Upgrade/Incremental/sql/3.3.alpha1.mysql.tpl so I'm wondering if just editing that one line might work. I will try, but I also wonder if there may be more of these. If so, shall I just keep editing these .tpl files?

Seems in theory this should work, but I would like a second opinion.

Donald Lobo · March 09, 2011, 12:06:06 pm

you might want to do a:

mysql> show create table civicrm_contact;

and see all the constraints / indexes that exist

once u have it upgraded, i would reload a clean schema (which will add back FK support) and export/import the data using mysqldump

if i had to guess, you will have a fair number of errors due to invalid constraints and FK checks

I'm curious to hear how long this process takes (definitely dependent on size of db and how long they've been running it under MyISAM)

lobo

Hershel · March 09, 2011, 12:10:09 pm

> reload a clean schema

Now why didn't I think of that? Actually seems I should just do that with the existing 3.2.3 and that should (or could, in theory, at least) fix all the problems. I will try that first and either way, report back here.

Thanks.

Hershel · March 09, 2011, 02:53:51 pm

This idea I tried on the 3.3.2 but failed on an "INSERT INTO `civicrm_contact` ..." with this error:

Code: [Select]

MySQL said: Documentation
#1048 - Column 'is_opt_out' cannot be null

Seems that the old DB allowed null there.

So I edited that mysql.tpl file, and then a few more like it, and then the upgrade finished.

I could have adjusted the 'is_opt_out' values, but I had a feeling the other method would be easier for now.

I suppose I will have to revisit this for the next upgrade.

Thanks.

CiviCRM Community Forums (archive)

News:

Author Topic: Upgrading Non-Standard Database (Read 768 times)

Hershel

Upgrading Non-Standard Database

Donald Lobo

Re: Upgrading Non-Standard Database

Hershel

Re: Upgrading Non-Standard Database

Hershel

Re: Upgrading Non-Standard Database