Author Topic: [1.4] db upgrade fails  (Read 6048 times)

ThorstenW

  • Freshman
  • *
  • Posts: 34
    • View Profile
[1.4] db upgrade fails
« on: May 19, 2009, 05:24:41 pm »
Running the upgrade script (1.3.1 -> 1.4) after uploading the new files produces the following error message:
    * Config file found and loaded.
    * File '/config/config.php' exists and is writable
    * Folder '/config' exists and is writable
    * Folder '/public/files' exists and is writable
    * Folder '/cache' exists and is writable
    * Folder '/tmp' exists and is writable
    * Folder '/upload' exists and is writable
    * Extension 'mysql' is loaded
    * Extension 'gd' is loaded
    * Extension 'simplexml' is loaded
    * Upgrade script has connected to the database.
    * Test query has been executed. Its safe to proceed with database migration.
    * Upgrade script has connected to the database.
    * Test query has been executed. Its safe to proceed with database migration.
    *
    * Failed to execute DB schema transformations. MySQL said: Duplicate column name 'type'
    * Error upgrading to version 1.4


Trying to access opengoo without a successful upgrade results in another error:

(DBQueryError)
Query failed with message 'Unknown column 'can_manage_reports' in 'field list''
Error params:
File:    ...//opengoo/environment/library/database/adapters/AbstractDBAdapter.class.php
Line:    386
Sql:    SELECT `id`, `company_id`, `username`, `email`, `token`, `salt`, `twister`, `display_name`, `title`, `avatar_file`, `timezone`, `created_on`, `created_by_id`, `updated_on`, `last_login`, `last_visit`, `last_activity`, `can_edit_company_data`, `can_manage_workspaces`, `can_manage_security`, `can_manage_configuration`, `auto_assign`, `personal_project_id`, `can_manage_contacts`, `can_manage_templates`, `can_manage_reports`, `default_billing_id` FROM `og_users` WHERE `id` = '1'
Error number:    1054
Error message:    Unknown column 'can_manage_reports' in 'field list'
Backtrace: [...]


Seems I am locked out from my installation so far. (new security feature? ;))

My assumption: This 1.3.1 installation was once a "wrong 1.3.1" (see http://forums.opengoo.org/index.php?topic=1219.msg5912#msg5912 for details) and might already have added some columns that are now added again (without testing for existing cols before altering).

Am I heading into the right direction or is there another reason for preventing the upgrade?

Greetings,
Thorsten
« Last Edit: May 20, 2009, 01:19:41 pm by ThorstenW »

alvarotm01

  • Administrator
  • Sr. Member
  • *****
  • Posts: 335
    • View Profile
    • Email
Re: [1.4] db upgrade failes
« Reply #1 on: May 19, 2009, 06:38:49 pm »
Yes, it seems that some columns has been added before.

To complete the upgrade i'm attaching a script that will continue from the position it hanged.
You must copy the file "1_4_nutria.php" to the folder "public/upgrade/templates/db_migration".
And you also have to modify the file "config/installed_version.php", change the line "return 1.4" for "return 1.3.1".

Then run again the upgrade.

hope this helps!
if you have any other problem with this installation, don't hesitate to ask

ThorstenW

  • Freshman
  • *
  • Posts: 34
    • View Profile
Re: [1.4] db upgrade fails
« Reply #2 on: May 20, 2009, 02:20:01 pm »
There was a second column preventing the upgrade.
If someone else should encounter the same problems, just comment out the following two lines from the original 1_4_nutria.php and run the upgrade.
-- ALTER TABLE `<?php echo $table_prefix ?>project_files` ADD COLUMN `type` int(1) NOT NULL DEFAULT 0;
-- ALTER TABLE `<?php echo $table_prefix ?>project_files` ADD COLUMN `url` varchar(255) NULL;



# Database schema transformations executed (total queries: 61)
# OpenGoo has been upgraded. You are now running OpenGoo 1.4 Enjoy!

Up and running :)

Thanks for your quick reply, alvarotm01!

Thorsten

alvarotm01

  • Administrator
  • Sr. Member
  • *****
  • Posts: 335
    • View Profile
    • Email
Re: [1.4] db upgrade fails
« Reply #3 on: May 20, 2009, 02:26:35 pm »

I'm glad you cuold run the update successfully !!

greetings

soomon

  • Jr. Member
  • **
  • Posts: 93
    • View Profile
    • Email
Re: [1.4] db upgrade fails
« Reply #4 on: May 22, 2009, 06:44:42 am »
hey there,
had the same problem here...

removing everything that was duplicate i ended up here:
# Database schema transformations executed (total queries: 44)

Code: [Select]
-- <?php echo $table_prefix ?> og_
-- <?php echo $default_charset ?> DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
-- <?php echo $default_collation ?> collate utf8_unicode_ci
-- <?php echo $engine ?> InnoDB



ALTER TABLE `<?php echo $table_prefix?>project_webpages` MODIFY COLUMN `description` text <?php echo $default_collation ?>;





ALTER TABLE `<?php echo $table_prefix ?>project_tasks` MODIFY COLUMN `from_template_id` int(10) NOT NULL default '0';

ALTER TABLE `<?php echo $table_prefix ?>project_milestones` MODIFY COLUMN `from_template_id` int(10) NOT NULL default '0';

ALTER TABLE `<?php echo $table_prefix ?>project_file_revisions` MODIFY COLUMN `type_string` varchar(255) <?php echo $default_collation ?> NOT NULL default '';

UPDATE `<?php echo $table_prefix?>project_events` `pe` SET
`start`=ADDDATE(`start`, INTERVAL (SELECT CONCAT('\'',-1*FLOOR(`timezone`),':',FLOOR(abs(`timezone`)%1)*60,'\'') FROM `<?php echo $table_prefix?>users` `u` WHERE `u`.`id` = `pe`.`created_by_id`) HOUR_MINUTE),
`duration`=ADDDATE(`duration`, INTERVAL (SELECT CONCAT('\'',-1*FLOOR(`timezone`),':',FLOOR(abs(`timezone`)%1)*60,'\'') FROM `<?php echo $table_prefix?>users` `u` WHERE `u`.`id` = `pe`.`created_by_id`) HOUR_MINUTE);

CREATE TABLE IF NOT EXISTS `<?php echo $table_prefix ?>reports` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) <?php echo $default_collation ?> NOT NULL,
  `description` varchar(255) <?php echo $default_collation ?> NOT NULL,
  `object_type` varchar(255) <?php echo $default_collation ?> NOT NULL,
  `order_by` varchar(255) <?php echo $default_collation ?> NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=<?php echo $engine ?> <?php echo $default_charset ?>;

CREATE TABLE IF NOT EXISTS `<?php echo $table_prefix ?>report_columns` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `report_id` int(10) NOT NULL,
  `custom_property_id` int(10) NOT NULL,
  `field_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=<?php echo $engine ?> <?php echo $default_charset ?>;

CREATE TABLE IF NOT EXISTS `<?php echo $table_prefix ?>report_conditions` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `report_id` int(10) NOT NULL,
  `custom_property_id` int(10) NOT NULL,
  `field_name` varchar(255) <?php echo $default_collation ?> NOT NULL,
  `condition` varchar(255) <?php echo $default_collation ?> NOT NULL,
  `value` varchar(255) <?php echo $default_collation ?> NOT NULL,
  `is_parametrizable` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=<?php echo $engine ?> <?php echo $default_charset ?>;

UPDATE `<?php echo $table_prefix ?>user_ws_config_options` SET `default_value` = '' WHERE `category_name` = 'general' AND `name` = 'localization';
UPDATE `<?php echo $table_prefix ?>user_ws_config_options` SET `default_value` = 1 WHERE `category_name` = 'general' AND `name` = 'rememberGUIState';







ALTER TABLE `<?php echo $table_prefix ?>custom_property_values` MODIFY COLUMN `value` text <?php echo $default_collation ?> NOT NULL;


-- larger contact fields
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `firstname` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `lastname` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `middlename` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `department` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `job_title` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `w_city` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `w_state` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `w_zipcode` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `w_country` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `w_phone_number` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `w_phone_number2` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `w_fax_number` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `w_assistant_number` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `w_callback_number` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `h_city` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `h_state` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `h_zipcode` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `h_country` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `h_phone_number` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `h_phone_number2` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `h_fax_number` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `h_mobile_number` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `h_pager_number` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `o_city` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `o_state` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `o_zipcode` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `o_country` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `o_phone_number` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `o_phone_number2` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>contacts` MODIFY COLUMN `o_fax_number` varchar(50) <?php echo $default_collation ?> default NULL;

ALTER TABLE `<?php echo $table_prefix ?>companies` MODIFY COLUMN `phone_number` varchar(50) <?php echo $default_collation ?> default NULL;
ALTER TABLE `<?php echo $table_prefix ?>companies` MODIFY COLUMN `fax_number` varchar(50) <?php echo $default_collation ?> default NULL;

it said it worked but i still dont get any content...
sad thing is i didnt do a database dump because the lastu update went that smoothly :S

I now only get the op lines:
Welcome back soomon (Logout) : Administration | Account | Help

and the bottom line:

© 2009 by soomon. All rights reserved
Powered by OpenGoo 1.4. 0.207s. 6.18MB

what to do now??

thanks & greets,
soomon
« Last Edit: May 22, 2009, 06:46:45 am by soomon »

alvarotm01

  • Administrator
  • Sr. Member
  • *****
  • Posts: 335
    • View Profile
    • Email
Re: [1.4] db upgrade fails
« Reply #5 on: May 22, 2009, 09:46:51 am »
Try cleaning the browser cache,
to ensure that it is not using any old file stored there.

soomon

  • Jr. Member
  • **
  • Posts: 93
    • View Profile
    • Email
Re: [1.4] db upgrade fails
« Reply #6 on: May 22, 2009, 11:06:17 am »
that was too easy.....

thanks a lot!!!!

Arthur Mullard

  • Jr. Member
  • **
  • Posts: 73
    • View Profile
Re: [1.4] db upgrade fails
« Reply #7 on: July 02, 2009, 08:46:55 am »
I've just performed an automatic upgrade form 1.4.1. to 14.2 and got the following error meesage in the upgrade summary:

# Failed to execute DB schema transformations. MySQL said: Duplicate column name 'type'
# Error upgrading to version 1.4.2

Can I utilise any of the advice already given on this thread to sort this out? Any other tips gratefully received, Thanks.