Database migration error 1.7 to 1.8

Hello All, On a test system when attempting to upgrade from 1.7 to 1.8 running the script : migrate-1.7-to-1.8.sql I get this error: ERROR 1062 <23000> at line 3120: Duplicate entry '2941-lowQuantity' for key 'PRIMARY' The db update script continues on regardless but perhaps as a result of this error the "entity_link_details" table is empty. Any thought/advice would be appreciated.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Database migration error 1.7 to 1.8

I'm not yet sure why this is happening, but it means that your product templates haven't migrated correctly.

I'll run some tests tomorrow to see if I can reproduce the problem.

Re: Database migration error 1.7 to 1.8

Is this just the fact they have a duplicate entityrelationship in that table and its breaching the PK restraints when you try to import it into the entitylinks....

 

Regards
 
Ben 
OpenVPMS Installer and Helper 
Ph: +61423044823 
Email: info[at]charltonit.com[dot]au

Re: Database migration error 1.7 to 1.8

Yes. A product has been included more than once by a product template, and the script is unable to handle this. The workaround is to:

  1. locate the duplicates in the original 1.7 database using the script below
  2. remove the duplicates by editing the corresponding template product in the GUI
  3. back up the database
  4. run the migration

To detect the duplicates, run:

select template.entity_id, 
    template.name template,
    product.entity_id, 
    product.name product
from entity_relationships r
join entities template 
    on r.source_id = template.entity_id
join entities product 
    on r.target_id = product.entity_id
WHERE r.arch_short_name = "entityRelationship.productIncludes"
group by template.entity_id, product.entity_id
having count(product.entity_id) > 1;

The returned columns are:

  • product template id
  • product template name
  • included product id
  • included product name

 

Re: Database migration error 1.7 to 1.8

The migration script attached to OVPMS-1753 corrects the above problem.

You will need to start with a 1.7 database for it to work correctly, as the original version of the script deletes the old template relationships.

 

Re: Database migration error 1.7 to 1.8

Thank you Tim and Ben for the rapid response.

Syndicate content