We're updating the issue view to help you get more done. 

1.8 to 1.9 migration script can fail on Linux

Description

The migrate-1.8-to-1.9.sql script used to upgrade OpenVPMS from 1.8 to 1.9 can fail with the error message:

1 ERROR 1054 (42S22) at line 1414: Unknown column 'maxDiscount.value' in 'field list'

This is due to mixed-case being used in a statement that may cause the script to fail on platforms where MySQL table names are case-sensitive. This failure has been observed in one Linux installation. Other Linux installations have worked successfully.
Windows installations are unlikely to be affected, as MySQL on Windows treats table names as case-insensitive.

WARNING

If the script was run and failed with the above error, any subsequent migration must be done with a freshly restored database, or the migrated prices will be incorrect.

The incorrect statement is:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 # # Update max discounts. # UPDATE product_price_details d JOIN ( SELECT product_price_id, markup, round((markup / (100 + markup)) * 100, 2) newMaxDiscount, maxDiscount FROM ( SELECT p.product_price_id, cast(markup.value AS DECIMAL(18, 3)) markup, maxDiscount.value maxDiscount FROM product_prices p JOIN product_price_details markup ON p.product_price_id = markup.product_price_id AND markup.name = 'markup' JOIN product_price_details maxdiscount ON p.product_price_id = maxdiscount.product_price_id AND maxdiscount.name = 'maxDiscount ' JOIN product_tax_rates rates ON p.product_id = rates.product_id WHERE cast(markup.value AS DECIMAL(18, 3)) <> 0) markups) calcs ON calcs.product_price_id = d.product_price_id AND d.name = 'maxDiscount' AND maxDiscount > newMaxDiscount SET d.value = calcs.newMaxDiscount;

It should be:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 # # Update max discounts. # UPDATE product_price_details d JOIN ( SELECT product_price_id, markup, round((markup / (100 + markup)) * 100, 2) newMaxDiscount, maxDiscount FROM ( SELECT p.product_price_id, cast(markup.value AS DECIMAL(18, 3)) markup, maxDiscount.value maxDiscount FROM product_prices p JOIN product_price_details markup ON p.product_price_id = markup.product_price_id AND markup.name = 'markup' JOIN product_price_details maxDiscount ON p.product_price_id = maxDiscount.product_price_id AND maxDiscount.name = 'maxDiscount ' JOIN product_tax_rates rates ON p.product_id = rates.product_id WHERE cast(markup.value AS DECIMAL(18, 3)) <> 0) markups) calcs ON calcs.product_price_id = d.product_price_id AND d.name = 'maxDiscount' AND maxDiscount > newMaxDiscount SET d.value = calcs.newMaxDiscount;

Environment

Status

Assignee

Tim Anderson

Reporter

Tim Anderson

Components

Fix versions

Affects versions

1.9

Priority

Major