Problem upgrading to 4.5.0b - Specified key was too long

[seade]

So much for my quick upgrade this morning!

Just attempted an upgrade from 4.2.1c to 4.5.0b. All the pre-flight checks passed, but when the SQL update was processed the following was output:

updated - ./modules/UpgradeWizard/uw_files.php An error has occured while performing db query. See log file for details.
Query Failed:ALTER TABLE mos_core_acl_aro CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;::MySQL error 1071: Specified key was too long; max key length is 1024 bytes

Other vital stats:

• OS: RHEL4
• Webserver: Apache httpd 2.0.52
• PHP: 4.3.9
• MySQL:4.1.20

Okay, so now I need to restore everything. Before I do this, is there a manual fix I could apply to complete the upgrade?

I note that the referenced table, mos_core_acl_aro, is unrelated to SugarCRM - it is actually a table from an old Mambo instance that happened to share the same database as Sugar. Why is it that the upgrade script is updating tables that have nothing to do with Sugar!

Thanks,

Scott

[kbrill]

Thats a known problem, see this on page #7. I believe that the upgrade script just scrolls through the tables list as it does not take into consideration other programs using it's database (even tho that happens).

I'm going to assume that you didn't back up the database before upgrading. Not sure what the roll back procedure would be. Do you have other modules installed or is it just SugarCRM?

[seade]

Thats a known problem, see this on page #7. I believe that the upgrade script just scrolls through the tables list as it does not take into consideration other programs using it's database (even tho that happens).

I'm going to assume that you didn't back up the database before upgrading. Not sure what the roll back procedure would be. Do you have other modules installed or is it just SugarCRM?

Thanks for your quick reply.

Seems like a problem that would have been easy to avoid (why, oh why didn't the upgrade script stick to the known tables?)

While I did ignore the advice to perform the upgrade against a copy of the system (give that you can only do the upgrade via the wizard this seems like a real pain) but I certainly did not ignore the advice to take a backup.

That said, it seems that it was pretty close to completing the upgrade - if I can complete the upgrade without having to restore the application files (I just did a "cp -R" as root so there may be file permission fun) and the database (I'll have to look up how to do this) that would be great. OTOH, that is what backups are for :-)

Seriously though, I need to determine right now which is going to be the quickest path to getting this going. Note that the database concerned contains a no longer used set of Mambo tables, a small number of tables belonging to an unrelated application and SugarCRM OS with no additional modules installed.

Thanks,

Scott

[kbrill]

Thanks for your quick reply.

Seems like a problem that would have been easy to avoid (why, oh why didn't the upgrade script stick to the known tables?)

agree completly, I have had a few clients with similar problems, but just to cover my butt I must say that I have not even looked at the update scripts to see if I am right about how it works or not yet.

While I did ignore the advice to perform the upgrade against a copy of the system (give that you can only do the upgrade via the wizard this seems like a real pain) but I certainly did not ignore the advice to take a backup.

Good to hear, that puts you in a far too small group of people.

That said, it seems that it was pretty close to completing the upgrade - if I can complete the upgrade without having to restore the application files (I just did a "cp -R" as root so there may be file permission fun) and the database (I'll have to look up how to do this) that would be great. OTOH, that is what backups are for :-)

Seriously though, I need to determine right now which is going to be the quickest path to getting this going. Note that the database concerned contains a no longer used set of Mambo tables, a small number of tables belonging to an unrelated application and SugarCRM OS with no additional modules installed.

Thanks,

Scott

I would have to look at the update script too see if it could be rewritten. Otherwise you could just back up your database, then remove the other tables, run the update, then restore those other tables.

[seade]

I would have to look at the update script too see if it could be rewritten. Otherwise you could just back up your database, then remove the other tables, run the update, then restore those other tables.

Dropping the old Mambo tables is not a problem. But if I go to the UpgradeWizard page again it stops at the pre-flight check with:

Error: PCLZIP_ERR_MISSING_FILE (-4) : Missing archive file ''

Do you think that if I manually upload the upgrade zip file to the appropriate location (where would that be?), after i drop the old Mambo tables I should be able to rerun the upgrade wizard?

Performing the database restore looks easier than I first thought (the dump script seems to take care of everything) so I am not ruling that out, but it seems that I will also need to either hack my aborted upgrade to "see" the upgrade file or also restore the application itself.

Thanks again for your help.

Scott

[seade]

Okay, I have restored from my backups (this turned out to be very easy) and I now have 4.2.1c back up and running.

I was about to rerun the upgrade when I was notified that one of our other php based applications is not functioning correctly. It looks like mbstrings is breaking a couple of our applications. There is no point me upgrading to 4.5 if I am then going to have to back out mbstrings to get the other apps back up and running. Hopefully we can sort out the problem with the other apps soon and get back to the Sugar upgrade.

I have dropped the offending Mambo tables so hopefully the problem will not occur again in the future, though it would be great if the appropriate code was updated so as to impact only the Sugar tables.

Thanks one more time for your help.

Scott