Uploaded image for project: 'VPMS Web Application'
  1. OVPMS-1831

1.8 to 1.9 migration script can fail on Linux

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.9
    • Fix Version/s: 1.9.1
    • Component/s: Release
    • Labels:
      None
    • Sprint:

      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:

      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:

      #
      # 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:

      #
      # 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;
      

        Attachments

          Activity

            People

            • Assignee:
              tanderson Tim Anderson
              Reporter:
              tanderson Tim Anderson
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0h
                0h
                Logged:
                Time Spent - 6h
                6h

                  Who's Looking?