CiviCRM Community Forums (archive)

Same error with 4.0.6 and 4.0 7 attempts to upgrade. Attaching where it "breaks".

I believe this is a problem with the change from one Pledge Summary Report (that was using a Pledge Detail template) to two separate reports - a Detail and new Summary report. Suggestions ? Is there a way I could alter the report structures separately and then run a full upgrade?

....

CREATE TABLE IF NOT EXISTS civicrm_action_log (
     id                   int UNSIGNED NOT NULL AUTO_INCREMENT,
     contact_id           int UNSIGNED NULL DEFAULT NULL COMMENT 'FK to Contact ID',
     entity_id            int UNSIGNED NOT NULL COMMENT 'FK to id of the entity that the action was performed on. Pseudo - FK.',
     entity_table         varchar(255) COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'name of the entity table for the above id, e.g. civicrm_activity, civicrm_participant',
     action_schedule_id   int UNSIGNED NOT NULL COMMENT 'FK to the action schedule that this action originated from.',
     action_date_time     DATETIME NULL DEFAULT NULL COMMENT 'date time that the action was performed on.',
     is_error             TINYINT( 4 ) NULL DEFAULT '0' COMMENT 'Was there any error sending the reminder?',
     message              TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT 'Description / text in case there was an error encountered.',
     repetition_number    INT( 10 ) UNSIGNED NULL COMMENT 'Keeps track of the sequence number of this repetition.',
     PRIMARY KEY ( id ),
     CONSTRAINT FK_civicrm_action_log_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE,
     CONSTRAINT FK_civicrm_action_log_action_schedule_id FOREIGN KEY (action_schedule_id) REFERENCES civicrm_action_schedule(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;



ALTER TABLE `civicrm_action_log` CHANGE `repetition_number` `repetition_number` INT( 10 ) UNSIGNED NULL COMMENT 'Keeps track of the sequence number of this repetition.';



UPDATE civicrm_mailing SET domain_id = 1 WHERE domain_id IS NULL;



DELETE et2.* from civicrm_entity_tag et1
INNER JOIN civicrm_entity_tag et2 ON et1.entity_table = et2.entity_table AND et1.entity_id = et2.entity_id AND et1.tag_id = et2.tag_id
WHERE et1.id < et2.id;

ALTER TABLE civicrm_entity_tag
DROP INDEX index_entity;

ALTER TABLE civicrm_entity_tag
ADD UNIQUE INDEX UI_entity_id_entity_table_tag_id( entity_table, entity_id, tag_id );




SELECT @report_template_gid := MAX(id) FROM civicrm_option_group WHERE name = 'report_template';

      UPDATE civicrm_option_value SET label = 'Pledge Report (Detail)', description = 'Pledge Report' WHERE option_group_id = @report_template_gid AND value = 'pledge/summary';

UPDATE civicrm_option_value SET name = 'CRM_Report_Form_Pledge_Detail', value = 'pledge/detail' WHERE option_group_id = @report_template_gid AND value = 'pledge/summary';

UPDATE civicrm_report_instance SET report_id = 'pledge/detail' WHERE report_id = 'pledge/summary';

SELECT @weight              := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @report_template_gid;
SELECT @pledgeCompId        := MAX(id)     FROM civicrm_component where name = 'CiviPledge';
INSERT INTO civicrm_option_value
  (option_group_id, label, value, name, weight, description, is_active, component_id) VALUES
  (@report_template_gid, 'Pledge Summary Report', 'pledge/summary', 'CRM_Report_Form_Pledge_Summary', @weight := @weight + 1, 'Pledge Summary Report.', 1, @pledgeCompId);



UPDATE civicrm_payment_processor
SET `url_site` = 'https://sec.paymentexpress.com/pxpay/pxpay.aspx'
WHERE `url_site` = 'https://www.paymentexpress.com/pxpay/pxpay.aspx'
OR url_site = 'https://sec2.paymentexpress.com/pxpay/pxpay.aspx';

UPDATE civicrm_payment_processor
SET `url_site` = 'https://sec.paymentexpress.com/pxpay/pxaccess.aspx'
WHERE `url_site` = 'https://www.paymentexpress.com/pxpay/pxaccess.aspx'
OR url_site = 'https://sec2.paymentexpress.com/pxpay/pxpay/pxaccess.aspx';

UPDATE civicrm_payment_processor_type
SET url_site_default = 'https://sec.paymentexpress.com/pxpay/pxaccess.aspx',
    url_site_test_default = 'https://sec.paymentexpress.com/pxpay/pxaccess.aspx'
WHERE name = 'Payment_Express';




SELECT @option_group_id_languages := MAX(id) FROM civicrm_option_group WHERE name = 'languages';
SELECT @languages_max_weight := MAX(weight) FROM civicrm_option_value WHERE option_group_id = @option_group_id_languages;

     UPDATE civicrm_option_value SET label = 'Persian (Iran)' WHERE value = 'fa' AND option_group_id = @option_group_id_languages;

INSERT INTO civicrm_option_value
  (option_group_id, is_default, is_active, name, value, label, weight)
VALUES
(@option_group_id_languages, 0, 1, 'de_CH', 'de', 'German (Swiss)', @weight := @languages_max_weight + 1),
(@option_group_id_languages, 0, 1, 'es_PR', 'es', 'Spanish; Castilian (Puerto Rico)', @weight := @languages_max_weight + 2);



    ALTER TABLE `civicrm_dashboard` DROP `content`, DROP `created_date`;
    ALTER TABLE `civicrm_dashboard_contact`  ADD `content` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL AFTER `weight`,  ADD `created_date` DATETIME NULL DEFAULT NULL AFTER `content`; [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @content := msg_html FROM civicrm_msg_template WHERE workflow_id = @workf' at line 2]"]
)


Oct 04 20:46:45  [info] $backTrace = C:\wamp\www\sites\all\modules\civicrm\CRM\Core\Error.php, backtrace, 205
, handle,
C:\wamp\www\sites\all\modules\civicrm\packages\PEAR.php, call_user_func, 931
C:\wamp\www\sites\all\modules\civicrm\packages\DB.php, PEAR_Error, 968
C:\wamp\www\sites\all\modules\civicrm\packages\PEAR.php, DB_Error, 564
C:\wamp\www\sites\all\modules\civicrm\packages\DB\common.php, raiseError, 1903
C:\wamp\www\sites\all\modules\civicrm\packages\DB\mysql.php, raiseError, 898
C:\wamp\www\sites\all\modules\civicrm\packages\DB\mysql.php, mysqlRaiseError, 327
C:\wamp\www\sites\all\modules\civicrm\packages\DB\common.php, simpleQuery, 1216
C:\wamp\www\sites\all\modules\civicrm\CRM\Utils\File.php, query, 260
C:\wamp\www\sites\all\modules\civicrm\CRM\Upgrade\Form.php, sourceSQLFile, 154
C:\wamp\www\sites\all\modules\civicrm\CRM\Upgrade\Form.php, source, 286
C:\wamp\www\sites\all\modules\civicrm\CRM\Upgrade\Form.php, processLocales, 302
C:\wamp\www\sites\all\modules\civicrm\CRM\Upgrade\Incremental\php\ThreeFour.php, processSQL, 235
C:\wamp\www\sites\all\modules\civicrm\CRM\Upgrade\Page\Upgrade.php, upgrade_3_4_5, 246
C:\wamp\www\sites\all\modules\civicrm\CRM\Core\Invoke.php, run, 224
C:\wamp\www\sites\all\modules\civicrm\drupal\civicrm.module, invoke, 443
, civicrm_invoke,
C:\wamp\www\includes\menu.inc, call_user_func_array, 503
C:\wamp\www\index.php, menu_execute_active_handler, 21

Zip your txt file and then upload.

Or contact me offline and email it to me.

I don't see how this can be a data error when the problem is a syntax error during the attempt to rebuild the dashboard       

"... DEFAULT NULL AFTER `content`; [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @content := msg_html FROM civicrm_msg_template WHERE workflow_id = @workf' at line 2]"                                     

 as seen in the following excerpt from the end of the script results. It looks like the instruction is truncated? Not sure what to do with this. Same thing happened when I tried 4.0.4 to 4.0.5 and I just gave up that time. There's something incomplete somewhere, but how do I figure out where ?

    ALTER TABLE `civicrm_dashboard` DROP `content`, DROP `created_date`;
    ALTER TABLE `civicrm_dashboard_contact` ADD `content` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL AFTER `weight`, ADD `created_date` DATETIME NULL DEFAULT NULL AFTER `content`; [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @content := msg_html FROM civicrm_msg_template WHERE workflow_id = @workf' at line 2]"]
)
> ______________________________________________________________________________________________
> After running the script, the systems still works, except (of course), the dashboard hangs when dashlets are selected.
> ______________________________________________________________________________________________

Sending PART THREE of the txt file I had to split up for size

This appears to be a bug in CiviCRM. I filed an issue here: http://issues.civicrm.org/jira/browse/CRM-8993

I'm getting the same issue as Barbara, so I don't knopw if there's a solution found. Running Drupal 7 on an IIS server.