Author Topic: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file (Read 4765 times)

majovdh · May 08, 2013, 01:22:41 pm

helendb,

nice that you're problems is solved.

Any change to solve also the original thread? Eileen?

Would be nice

Majo

helenbn · May 08, 2013, 01:28:29 pm

Your problem looks similar to my problem. (Didn't mean to bogart your thread.)

Is your mysql the right version?

majovdh · May 08, 2013, 01:32:02 pm

No prob,

my version of mySQL is 5.5.23

helenbn · May 08, 2013, 01:48:52 pm

My Exception Trace was the same as yours, so the only diff is in the table.

Do you have backups? Restore the database, then try running this:

ALTER TABLE civicrm_membership_type DROP FOREIGN KEY FK_civicrm_membership_autorenewal_msg_id

If that works, then try your upgrade.

Eileen · May 08, 2013, 02:06:36 pm

Well done!

majovdh · May 08, 2013, 02:12:46 pm

Hey,

I get the same message :

#1025 - Error on rename of './chautaar_chautaardr7_crm/civicrm_membership_type' to './chautaar_chautaardr7_crm/#sql2-3003-2bca11f' (errno: 152)

Majo

helenbn · May 08, 2013, 02:38:19 pm

Do this and post results:

SHOW CREATE TABLE civicrm_membership_type;

majovdh · May 08, 2013, 02:53:41 pm

Sorry, found how to show complete text

Code: [Select]

CREATE TABLE `civicrm_membership_type` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Membership Id',
 `domain_id` int(10) unsigned NOT NULL COMMENT 'Which Domain is this match entry for',
 `member_of_contact_id` int(10) unsigned NOT NULL COMMENT 'Owner organization for this membership type. FK to Contact ID',
 `contribution_type_id` int(10) unsigned NOT NULL COMMENT 'If membership is paid by a contribution - what contribution type should be used. FK to Contribution Type ID',
 `minimum_fee` decimal(20,2) DEFAULT '0.00' COMMENT 'Minimum fee for this membership (0 for free/complimentary memberships).',
 `duration_unit` enum('day','month','year','lifetime') COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Unit in which membership period is expressed.',
 `duration_interval` int(11) DEFAULT NULL COMMENT 'Number of duration units in membership period (e.g. 1 year, 12 months).',
 `period_type` enum('rolling','fixed') COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Rolling membership period starts on signup date. Fixed membership periods start on fixed_period_start_day.',
 `fixed_period_start_day` int(11) DEFAULT NULL COMMENT 'For fixed period memberships, month and day (mmdd) on which subscription/membership will start. Period start is back-dated unless after rollover day.',
 `fixed_period_rollover_day` int(11) DEFAULT NULL COMMENT 'For fixed period memberships, signups after this day (mmdd) rollover to next period.',
 `relationship_type_id` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
 `relationship_direction` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
 `visibility` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
 `weight` int(11) DEFAULT NULL,
 `renewal_msg_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_msg_template.id',
 `renewal_reminder_day` int(11) DEFAULT NULL COMMENT 'Number of days prior to expiration to send renewal reminder',
 `receipt_text_signup` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Receipt Text for membership signup',
 `receipt_text_renewal` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Receipt Text for membership renewal',
 `is_active` tinyint(4) DEFAULT '1' COMMENT 'Is this membership_type enabled',
 `auto_renew` tinyint(4) DEFAULT '0',
 `autorenewal_msg_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_msg_template.id',
 `name_fr_FR` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
 `description_fr_FR` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
 `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `FK_civicrm_membership_type_domain_id` (`domain_id`),
 KEY `FK_civicrm_membership_type_member_of_contact_id` (`member_of_contact_id`),
 KEY `FK_civicrm_membership_type_contribution_type_id` (`contribution_type_id`),
 KEY `FK_civicrm_membership_type_renewal_msg_id` (`renewal_msg_id`),
 KEY `index_relationship_type_id` (`relationship_type_id`),
 KEY `FK_civicrm_membership_autorenewal_msg_id` (`autorenewal_msg_id`),
 CONSTRAINT `FK_civicrm_membership_type_contribution_type_id` FOREIGN KEY (`contribution_type_id`) REFERENCES `civicrm_contribution_type` (`id`),
 CONSTRAINT `FK_civicrm_membership_type_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`),
 CONSTRAINT `FK_civicrm_membership_type_member_of_contact_id` FOREIGN KEY (`member_of_contact_id`) REFERENCES `civicrm_contact` (`id`),
 CONSTRAINT `FK_civicrm_membership_type_renewal_msg_id` FOREIGN KEY (`renewal_msg_id`) REFERENCES `civicrm_msg_template` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

helenbn · May 09, 2013, 03:50:35 am

I would try this first, from the wiki http://wiki.civicrm.org/confluence/display/CRMDOC42/Ensuring+Schema+Integrity+on+Upgrades:

A note on possible Foreign Key Constraint Violation errors during import (and what to do about them): You may receive Foreign Key constraint violation errors on import because of the order in which the tables are exported and then imported, which is alphabetical.

For example, data for the civicrm_activity_assignment table would be imported before data for the civicrm_contact table, violating the Foreign Key constraint defined on the civicrm_activity_assignment.assignee_contact_id field. You can avoid this by instructing MySQL to disable Foreign Key checks before the import and to re-enable it when the import is complete.

Before importing the datafile.sql file, open it in a text editor and add the following MySQL code to the first line of the file:
SET foreign_key_checks = 0;

And append the following code to the last line of the file:

SET foreign_key_checks = 1;

If that doesn't work, try this:

ALTER TABLE civicrm_membership_type DROP KEY `FK_civicrm_membership_autorenewal_msg_id`;

if that works, then do this:

ALTER TABLE civicrm_membership_type DROP FOREIGN KEY FK_civicrm_membership_autorenewal_msg_id;

let us know.

majovdh · May 11, 2013, 05:52:46 am

damned, it looks like civicrm stops here for me.

I tried both option but ik keep getting error message

Is tried the procedure on following link, but when i want to import the custom field file i get following error message (same like before)

Code: [Select]

Error
SQL query:

--
-- Beperkingen voor gedumpte tabellen
--
--
-- Beperkingen voor tabel `civicrm_custom_field`
--
ALTER TABLE  `civicrm_custom_field` ADD CONSTRAINT  `FK_civicrm_custom_field_custom_group_id` FOREIGN KEY (  `custom_group_id` ) REFERENCES  `civicrm_custom_group` (  `id` ) ON DELETE CASCADE ;


MySQL said: 

#1005 - Can't create table 'chautaar_chautaaradr7_crm2.#sql-6c74_177623' (errno: 121) (Details...)

Then when i want to import the datafile i get following fault

Code: [Select]

Error
SQL query:

--
-- Gegevens worden uitgevoerd voor tabel `civicrm_contact_type`
--
INSERT INTO  `civicrm_contact_type` (  `id` ,  `name` ,  `image_URL` ,  `parent_id` ,  `is_active` ,  `is_reserved` ,  `label_fr_FR` ,  `description_fr_FR` ,  `label` ,  `description` ) 
VALUES ( 1,  'Individual', NULL , NULL , 1, 1,  'Individual', NULL ,  'Individual', NULL ) , ( 2,  'Household', NULL , NULL , 1, 1,  'Household', NULL ,  'Household', NULL ) , ( 3,  'Organization', NULL , NULL , 1, 1,  'Organization', NULL ,  'Organization', NULL ) , ( 5,  'School', NULL , 3, 1, NULL ,  'School', NULL ,  'School', NULL ) ;


MySQL said: 

#1054 - Unknown column 'label_fr_FR' in 'field list'

helenbn · May 11, 2013, 06:06:20 am

Can you restore your 4.2.7 and try to upgrade to 4.2.8, then 4.2.9, going one hop at a time, backing up after each successful upgrade? Theoretically, that should get you up to 4.3.0.

Also, before you start, make sure you don't have this problem

:

http://civicrm.org/blogs/totten/advisory-openflashchart-attacks

majovdh · May 11, 2013, 06:16:14 am

Helen,

First of all thanks for all the help.

I did the upgrade per version, so hopping from one to another. It's only when i upgrade to 4.3.x i'm getting the error messages.

I trying to follow the procedure from this link : http://wiki.civicrm.org/confluence/display/CRMDOC42/Ensuring+Schema+Integrity+on+Upgrades

The problem is when i'm importing the datafile.sql i'm getting errors because i have a lot of tables with this kind of column (title_fr_FR). When importing the data from the old table in the new it crashes on this kind of tables beacuse this are not present in the normal civicrm/sql/civicrm.mysql table.

I wonder if deleting all this columns would solve the problem and won't be messing up my database.

I have no idea how this columns got in to my database.

Greetings Majo

helenbn · May 11, 2013, 07:30:28 am

(Who's on first?

)

What is your steps to upgrade? When are you importing the database??

(I may not be the best at figuring so if any moderator wants to jump in here, feel free.)

majovdh · May 11, 2013, 08:02:13 am

Civicrm stops at 4.2.9 for me

i tried everything here :

followed this procedure http://wiki.civicrm.org/confluence/display/CRMDOC42/Ensuring+Schema+Integrity+on+Upgrades all the way but still getting this message.

A shame it was good for years for me

thanks for all the help Helen

Code: [Select]

CiviCRM Upgrade Tasks
[Error: Upgrade DB to 4.3.alpha1: SQL]
Error Field	Error Value
Type	DB_Error
Code	-3
Message	DB Error: constraint violation
Mode	16
UserInfo	INSERT INTO `civicrm_dashboard_contact` (dashboard_id, contact_id, column_no, is_active) SELECT (SELECT MAX(id) FROM `civicrm_dashboard`), contact_id, 1, IF (SUM(is_active) > 0, 0, 1) FROM `civicrm_dashboard_contact` WHERE 1 GROUP BY contact_id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`chautaar_chautaaradr7_crm2`.`civicrm_dashboard_contact`, CONSTRAINT `FK_civicrm_dashboard_contact_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
DebugInfo	INSERT INTO `civicrm_dashboard_contact` (dashboard_id, contact_id, column_no, is_active) SELECT (SELECT MAX(id) FROM `civicrm_dashboard`), contact_id, 1, IF (SUM(is_active) > 0, 0, 1) FROM `civicrm_dashboard_contact` WHERE 1 GROUP BY contact_id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`chautaar_chautaaradr7_crm2`.`civicrm_dashboard_contact`, CONSTRAINT `FK_civicrm_dashboard_contact_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
PEAR_Exception: DB Error: constraint violation in unknown on line unknown
- DB_Error: DB Error: constraint violation in unknown on line unknown
Exception trace
#	Function	Location
0	CRM_Core_Error::exceptionHandler(Object(DB_Error))	unknown:unknown
1	call_user_func(Array, Object(DB_Error))	/home/chautaar/public_html/sites/all/modules/civicrm/packages/PEAR.php:931
2	PEAR_Error->PEAR_Error('DB Error: constr…', -3, 16, Array, 'INSERT INTO `civ…')	/home/chautaar/public_html/sites/all/modules/civicrm/packages/DB.php:969
3	DB_Error->DB_Error(-3, 16, Array, 'INSERT INTO `civ…')	/home/chautaar/public_html/sites/all/modules/civicrm/packages/PEAR.php:564
4	PEAR->raiseError(null, -3, null, null, 'INSERT INTO `civ…', 'DB_Error', true)	/home/chautaar/public_html/sites/all/modules/civicrm/packages/DB/common.php:1905
5	DB_common->raiseError(-3, null, null, null, '1452 ** Cannot a…')	/home/chautaar/public_html/sites/all/modules/civicrm/packages/DB/mysql.php:898
6	DB_mysql->mysqlRaiseError()	/home/chautaar/public_html/sites/all/modules/civicrm/packages/DB/mysql.php:327
7	DB_mysql->simpleQuery('INSERT INTO `civ…')	/home/chautaar/public_html/sites/all/modules/civicrm/packages/DB/common.php:1216
8	DB_common->query('INSERT INTO `civ…')	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Utils/File.php:284
9	CRM_Utils_File::sourceSQLFile('mysql://chautaar…', ' SELECT @wo…', null, true)	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Upgrade/Form.php:159
10	CRM_Upgrade_Form->source(' SELECT @wo…', true)	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Upgrade/Form.php:299
11	CRM_Upgrade_Form->processLocales('/home/chautaar/p…', '4.3.alpha1')	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Upgrade/Form.php:323
12	CRM_Upgrade_Form->processSQL('4.3.alpha1')	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Upgrade/Incremental/php/FourThree.php:945
13	CRM_Upgrade_Incremental_php_FourThree::task_4_3_x_runSql(Object(CRM_Queue_TaskContext), '4.3.alpha1')	unknown:unknown
14	call_user_func_array(Array, Array)	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Queue/Task.php:79
15	CRM_Queue_Task->run(Object(CRM_Queue_TaskContext))	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Queue/Runner.php:186
16	CRM_Queue_Runner->runNext(true)	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:44
17	{closure}()	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Queue/ErrorPolicy.php:80
18	CRM_Queue_ErrorPolicy->call(Object(Closure))	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:47
19	CRM_Queue_Page_AJAX::runNext(Array)	unknown:unknown
20	call_user_func(Array, Array)	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:258
21	CRM_Core_Invoke::runItem(Array)	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:70
22	CRM_Core_Invoke::_invoke(Array)	/home/chautaar/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:52
23	CRM_Core_Invoke::invoke(Array)	/home/chautaar/public_html/sites/all/modules/civicrm/drupal/civicrm.module:436
24	civicrm_invoke('upgrade', 'queue', 'ajax', 'runNext')	unknown:unknown
25	call_user_func_array('civicrm_invoke', Array)	/home/chautaar/public_html/includes/menu.inc:517
26	menu_execute_active_handler()	/home/chautaar/public_html/index.php:21

helenbn · May 11, 2013, 12:32:05 pm

Have you a way of installing a fresh Civi into another test site and export the database, then open both databases in a text editor with some diff capabilities to see what's what? (Are you Mac or Windows?)

CiviCRM Community Forums (archive)

News:

Author Topic: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file (Read 4765 times)

majovdh

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

helenbn

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

majovdh

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

helenbn

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

Eileen

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

majovdh

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

helenbn

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

majovdh

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

helenbn

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

majovdh

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

helenbn

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

majovdh

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

helenbn

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

majovdh

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file

helenbn

Re: upgrade 4.2.7 to 4.3.2 fails on 4.3.alpha1 due error on rename file