"DB constraint violation" when upgrading from 4.1.3 to 4.2.beta1

[ken]

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:

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'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

[ken]

Here are query results run on the database after the upgrade. (See previous reply for explanation.)

Ken

[JoeMurray]

Many thanks, Ken, for this bug report. I'll have the developer get back to by tomorrow with an analysis.

[JoeMurray]

We've committed a patch that should make into 4.2.beta2.

[ken]

Joy!

[ken]

BTW, I removed the files I attached, just in case they contained sensitive data (which i doubt).

[ken]

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]

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 ...

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 ...

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 ...

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

[JoeMurray]

Thanks again for your help - this is excellent followup. We'll get back to you.

[ken]

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]

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!