Upgrade error: Database Error Code: Duplicate key name 'index_medium_id', 1061

[c3]

I've been dealing with a few issues when attempting to upgrade civicrm to the latest stable version, 3.3.2 and 3.3.3 (released today) from 3.0.3. Followed the official documentation for upgrading civicrm and ran upgrade script, no problems doing that. However, after doing so I received the first of many upgrade errors. Following advice found on this forum, I rebuilt the DB schema, ran the upgrade script again (after restoring original civicrm DB and files), and received another error- "Sorry. A non-recoverable error has occurred.
DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
Database Error Code: Error on rename of './dev3_civicrm/civicrm_event' to './dev3_civicrm/#sql2-"
Rebuilding the schema did not solve the problem.

I continued to search for a fix and found civicrmDB Tools 1.2 and begin to check the DB's integrity of both original DB and rebuilt DB. The results were not what I expected- original DB reported 356 problems, rebuilt DB 338. I ran the DB tools repair utility on the rebuilt DB (and original DB), deleted all the offending data, and ran the DB integrity check again- 8 problems found, most of which having to do with import jobs in the database but not in the schema. I ran the repair utility again- this time there was nothing to repair, so the problems found must not be an issue.  After restoring the original civicrm files, using the repaired DB, with civicrm functioning fine, I tried to upgrade again and received a new error-
"DB Error: already exists
Database Error Code: Duplicate key name 'index_medium_id', 1061"
Note: When I repair the original DB with civicrmDB tools and use it for the upgrade, the end result is exactly the same.

I did find the post here on the forum (http://forum.civicrm.org/index.php?topic=15148.0) regarding this exact error, but the solution suggested by "Tall David" did not solve the problem. Did as he suggested an dropped the indexes and attempted to upgrade again, received a new error-
"Sorry. A non-recoverable error has occurred.
One of parameters (value: 6,800.00) is not of the type Float"
That's it, no further info regarding this error.

I really need to successfully upgrade civicrm ASAP. Any help would be much appreciated.
Thanks

[c3]

Well, since nobody was able to help me, after months of hard work, here's what I did to solve my issue.

After more than two months of trying to repair the DB, I was unable to successfully upgrade via civicrm's upgrade script, from 3.0.3 to 3.3.2 (at the time). I was able to upgrade to 3.1 stable, and 3.1alpha1, every version after that produced more DB related errors that I was unable to resolve. I tried every stable release and alpha/beta's in between.

I finally decided to take on performing the upgrade manually. Started with a fresh install of the latest stable version of civicrm, v3.3.3, manually imported data from the 3.0.3 DB, modifying the DB as needed- adding and/or deleting fields in each table. Then, I proceeded to run the incremental.sql (located in civicrm/CRM/Upgrade/Incremental) one query at a time, for every version after 3.0.3- civicrm_option_group, civicrm_option_value tables, mssg_tpl templates, and many more. I also had a fresh install of civicrm 3.3.3 with sample data, to compare to during this process.

After a practice run in my dev environment, I was able to successfully perform the manual upgrade in production in two days. There were a few things I missed and corrected as issues presented themselves, nothing major though- mostly option_group, and option_value related issues, a views issue related to civicrm_custom_group's table, and a few others.  I will create a new topic for the few remaining issues, and will post the links in this thread when I do. I have every issue and solution fully documented. If anybody has or is planning to upgrade civicrm manually, just post your issue and I will do my best to help.

[repostor]

Hi,
I had similare problems.
I tried a "beta" or mybee an alpha version, which was upgraded succesfully, and I though everything was allright.

Everytime I try to upgrade CiviCRM, I receives errors that some tables allready exists, or some columns allready exists etc..
I tried to follow your instruction with "incremental" upgrade; but was unsure how to apply this SQL.
Should i substitute (remove) all {localization} ?
Or how exactly did you perform the incremental upgrade?

And what more steps do I need to perform?
Looking forward for how you did a successful manual upgrade.

Regards Tomas

[c3]

I ran the incremental SQL statements without smarty tags, via PHPMyAdmin- You can do this in your current DB before migrating data to the DB of the fresh install of your new civicrm version, versus modifying the new DB for the old data (both ways work). It's tedious work, but it will get you up and running with the current version of civicrm.

 I can confirm that I have not had one issue upgrading to every stable release since 3.3.3. In my post, one member actually said he developed a script for running the incremental SQL from SSH/command line, which automatically strips the smarty tags out. Unfortunately, this script was never provided. 

One new development to this manual upgrade that is not covered in the civicrm forums, is an issue with views and civicrm, where the civicrm created views are producing missing/broken handlers errors- Solution for this is here: drupal.org/node/1074508

Hope this helps.

Jake

[minbori]

Just came across your post and realized that I think I'm dealing with exactly the same upgrade issues you were a few months ago.  I'm attempting an upgrade from 3.0.0 to the latest stable version 3.4.4 (my post here http://forum.civicrm.org/index.php/topic,20918.0.html )

Trembling in my shoes thinking I'll need to go through this process manually...did you ever post a topic going through your step-by-step?

[c3]

Yep, sounds like the same issue, same error, I'm running a drupal install though. I found the reason for this issue in the first place was due to a failed upgrade attempt to v3.1- before I was called in to clean up the mess. Unfortunately, this was way after the fact, so restoring the site from a backup was out of the question.

What you see here is all I have time to post, but everything I did is basically there, minus the 1, 2, 3 (baby-steps) of exporting/importing every one of 160+ DB tables data, and copy/pasting the many SQL statements along the way.

 I can tell you that while I was performing the manual upgrade, we were able to leave civicrm up and running for the approximately two days it took to do the DB work- we simply turned off all new memberships, event registrations (put up a off-line registration/payment page), editing and adding content, and just to be safe we temporarily removed all user roles/permissions. Everything was basically set to read access only. I did a couple of dry runs first in my dev environment before working in production. I am a one man team, if I can pull it off, so can you! And if you can't for some reason, you can always hire me

If you have any more questions, I will do my best to help.

[minbori]

Thanks so much for your encouragement Jake!  Luckily enough the CiviCRM on our live site isn't in active use at the moment, but I need to get the upgrade done before the next big event registration starts at the end of this month.  As far as I can tell this system has never been upgraded since 3.0.0 was installed...I was called in to sysadmin this Joomla install and have done a full sweep of upgrades for a load of outdated components.  So I don't think there was a failed upgrade attempt anywhere...

Actually the process has been pretty instructive, I know much more about the CiviCRM schema than I ever expected to know I'm hoping I can still pull this off, but will definitely keep your services in mind if my deadline looms and I get no closer to success.

At this point I've solved one of the issues and managed to get the test system upgraded to 3.2.5. We'll see how I get on...but again, thanks for your support! :-)