CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Discussion (deprecated) »
  • Alpha and Beta Release Testing »
  • 4.2 Release Testing »
  • "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
Pages: [1]

Author Topic: "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1  (Read 5188 times)

ken

  • I live on this forum
  • *****
  • Posts: 916
  • Karma: 53
    • City Bible Forum
  • CiviCRM version: 4.6.3
  • CMS version: Drupal 7.36
  • MySQL version: 5.5.41
  • PHP version: 5.3.10
"DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
July 15, 2012, 03:42:15 pm
When upgrading from 4.1.3 to 4.2.beta1 I get "DB constraint violation" error on the screen. When I press the 'next' button, the upgrade completes and indicates Success.

The logs contain this error:

Quote
Jul 16 08:16:44  [info] $CRM_Queue_Page_AJAX_runNext_result = Array
(
    [is_error] => 1
    [message] => DB Error: constraint violation
    [last_task_title] => Upgrade DB to 4.2.alpha1: Contributions (10111 => 15110)
    [numberOfItems] => 13
    [is_continue] => 0
)

The message and title variables are what I saw on my screen. Can anyone suggest a query I might run on my data to understand this error, and perhaps I can shed some light on why this is happening.

Thanks!

Ken

ken

  • I live on this forum
  • *****
  • Posts: 916
  • Karma: 53
    • City Bible Forum
  • CiviCRM version: 4.6.3
  • CMS version: Drupal 7.36
  • MySQL version: 5.5.41
  • PHP version: 5.3.10
Re: "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
July 15, 2012, 08:42:22 pm
I've done a bit of research.

The error is generated by the function task_4_2_alpha1_convertContributions in the file CRM/Upgrade/Incremental/php/FourTwo.php

I don't use Memberships, so the first two queries (lines 277 and 296) return zero rows.

The third query (line 392) returns results. Since $StartId is 10111 and $EndId is 15110 (based on the arguments above) the query is ...

Code: [Select]
SELECT cp.id as participant_id, cp.fee_amount, cp.fee_level,ce.is_monetary, cpse.price_set_id, cpf.id as price_field_id, cpfv.id as price_field_value_id
FROM civicrm_participant cp
LEFT JOIN civicrm_line_item cli ON cli.entity_id=cp.id and cli.entity_table = 'civicrm_participant'
LEFT JOIN civicrm_event ce ON ce.id=cp.event_id
LEFT JOIN civicrm_price_set_entity cpse ON cp.event_id = cpse.entity_id and cpse.entity_table = 'civicrm_event'
LEFT JOIN civicrm_price_field cpf ON cpf.price_set_id = cpse.price_set_id
LEFT JOIN civicrm_price_field_value cpfv ON cpfv.price_field_id = cpf.id AND cpfv.label = cp.fee_level
WHERE (cp.id BETWEEN 10111 AND 15110)
AND cli.entity_id IS NULL
AND cp.fee_amount IS NOT NULL
I'll attach 2 files: one showing the query results from the database before the upgrade, and the other after. (I'll attach the 'before' file to this reply, and the 'after' to the next, due to file size restrictions).

While the data in these files doesn't seem to me to be sensitive, I'd appreciate that when a developer looks at them, that someone could remove the files, or could let me know so i could do that? Thanks!

Ken
« Last Edit: July 19, 2012, 06:27:26 am by ken »

ken

  • I live on this forum
  • *****
  • Posts: 916
  • Karma: 53
    • City Bible Forum
  • CiviCRM version: 4.6.3
  • CMS version: Drupal 7.36
  • MySQL version: 5.5.41
  • PHP version: 5.3.10
Re: "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
July 15, 2012, 08:43:57 pm
Here are query results run on the database after the upgrade. (See previous reply for explanation.)

Ken
« Last Edit: July 19, 2012, 06:26:41 am by ken »

JoeMurray

  • Administrator
  • Ask me questions
  • *****
  • Posts: 578
  • Karma: 24
    • JMA Consulting
  • CiviCRM version: 4.4 and 4.5 (as of Nov 2014)
  • CMS version: Drupal, WordPress, Joomla
  • MySQL version: MySQL 5.5, 5.6, MariaDB 10.0 (as of Nov 2014)
Re: "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
July 16, 2012, 11:22:12 am
Many thanks, Ken, for this bug report. I'll have the developer get back to by tomorrow with an analysis.
Co-author of Using CiviCRM https://www.packtpub.com/using-civicrm/book

JoeMurray

  • Administrator
  • Ask me questions
  • *****
  • Posts: 578
  • Karma: 24
    • JMA Consulting
  • CiviCRM version: 4.4 and 4.5 (as of Nov 2014)
  • CMS version: Drupal, WordPress, Joomla
  • MySQL version: MySQL 5.5, 5.6, MariaDB 10.0 (as of Nov 2014)
Re: "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
July 18, 2012, 12:55:11 pm
We've committed a patch that should make into 4.2.beta2.
Co-author of Using CiviCRM https://www.packtpub.com/using-civicrm/book

ken

  • I live on this forum
  • *****
  • Posts: 916
  • Karma: 53
    • City Bible Forum
  • CiviCRM version: 4.6.3
  • CMS version: Drupal 7.36
  • MySQL version: 5.5.41
  • PHP version: 5.3.10
Re: "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
July 19, 2012, 06:26:15 am
Joy!

ken

  • I live on this forum
  • *****
  • Posts: 916
  • Karma: 53
    • City Bible Forum
  • CiviCRM version: 4.6.3
  • CMS version: Drupal 7.36
  • MySQL version: 5.5.41
  • PHP version: 5.3.10
Re: "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
July 19, 2012, 06:28:28 am
BTW, I removed the files I attached, just in case they contained sensitive data (which i doubt).

ken

  • I live on this forum
  • *****
  • Posts: 916
  • Karma: 53
    • City Bible Forum
  • CiviCRM version: 4.6.3
  • CMS version: Drupal 7.36
  • MySQL version: 5.5.41
  • PHP version: 5.3.10
Re: "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
July 21, 2012, 02:48:21 am
I get this error again with 4.2.beta2. Looking at the code there's no apparent change, so I suspect I'll need to wait until the next release.  :'(

ken

  • I live on this forum
  • *****
  • Posts: 916
  • Karma: 53
    • City Bible Forum
  • CiviCRM version: 4.6.3
  • CMS version: Drupal 7.36
  • MySQL version: 5.5.41
  • PHP version: 5.3.10
Re: "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
July 21, 2012, 04:58:05 am
GOOD NEWS!

When I apply the patch for r41629, the upgrade to 4.2.beta2 *works*

BUT there is still a possible issue.

When I run the query ...

Quote
SELECT cp.id as participant_id, cp.fee_amount, cp.fee_level, ce.is_monetary, cpse.price_set_id, cpf.id as price_field_id, cpfv.id as price_field_value_id
FROM civicrm_participant cp
LEFT JOIN civicrm_line_item cli ON cli.entity_id=cp.id and cli.entity_table = 'civicrm_participant'
LEFT JOIN civicrm_event ce ON ce.id=cp.event_id LEFT JOIN civicrm_price_set_entity cpse ON cp.event_id = cpse.entity_id and cpse.entity_table = 'civicrm_event'
LEFT JOIN civicrm_price_field cpf ON cpf.price_set_id = cpse.price_set_id
LEFT JOIN civicrm_price_field_value cpfv ON cpfv.price_field_id = cpf.id AND cpfv.label = cp.fee_level
WHERE cli.entity_id IS NULL AND cp.fee_amount IS NOT NULL

... there are still some rows returned after the upgrade. That's a bit odd as I understand the purpose of this step in the upgrade is to add a civicrm_line_item for every civicrm_participant that doesn't have one.

After running the upgrade, the query returns the following results ...

Quote
20112   200.00   Trip deposit   1   81   155   179
20149   250.00   Trip deposit   1   81   155   179
20308   250.00   Trip deposit   1   81   155   179
20408   250.00   Trip deposit   1   81   155   179

Prior to the upgrade, that query returned many more rows, but returned the following results for those 4 rows ...

Quote
20112   200.00   Trip deposit   1   NULL   NULL   NULL
20149   250.00   Trip deposit   1   NULL   NULL   NULL
20308   250.00   Trip deposit   1   NULL   NULL   NULL
20408   250.00   Trip deposit   1   NULL   NULL   NULL
« Last Edit: July 21, 2012, 05:07:46 am by ken »

JoeMurray

  • Administrator
  • Ask me questions
  • *****
  • Posts: 578
  • Karma: 24
    • JMA Consulting
  • CiviCRM version: 4.4 and 4.5 (as of Nov 2014)
  • CMS version: Drupal, WordPress, Joomla
  • MySQL version: MySQL 5.5, 5.6, MariaDB 10.0 (as of Nov 2014)
Re: "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
July 21, 2012, 10:01:18 am
Thanks again for your help - this is excellent followup. We'll get back to you.
Co-author of Using CiviCRM https://www.packtpub.com/using-civicrm/book

ken

  • I live on this forum
  • *****
  • Posts: 916
  • Karma: 53
    • City Bible Forum
  • CiviCRM version: 4.6.3
  • CMS version: Drupal 7.36
  • MySQL version: 5.5.41
  • PHP version: 5.3.10
Re: "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
July 22, 2012, 12:25:43 am
That's easy!  If you need more information about the data, please send me any queries you might want me to run, and I'll post the results.

ken

  • I live on this forum
  • *****
  • Posts: 916
  • Karma: 53
    • City Bible Forum
  • CiviCRM version: 4.6.3
  • CMS version: Drupal 7.36
  • MySQL version: 5.5.41
  • PHP version: 5.3.10
Re: "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1
August 09, 2012, 07:49:02 am
On July 21, I advised that 4 participants did not appear to be completely processed. After upgrading from 4.1.3 to 4.2.beta5, I can report that no participants are returned by the query I ran at that time. Good work!
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Discussion (deprecated) »
  • Alpha and Beta Release Testing »
  • 4.2 Release Testing »
  • "DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1

This forum was archived on 2017-11-26.