Home › Forums › implementation › Implementation discussion ›

Migration error - max.Discount.Value 1.8 > 1.9

Submitted by mckelvey on Tue, 29/11/2016 - 06:59
in

Greetings All,

I have run into the following error while attempting to install 1.9-release on my test server.  I just brought over a current copy of openvpms.sql from our production server, which is running 1.8, but encounter the following mysql error when doing the db migration to 1.9:

ERROR 1054: (42S22) at line 1414: Unknown column 'maxDiscountValue' in 'field list'  Line 1414 starts 'UPDATE product_price_details d'

A little history may help:

1) I successfully installed a BETA version of 1.9 on this same test server about six weeks ago(?) using a current (at the time) 1.8 database and had no issues at that time migrating from 1.8 to 1.9.    

2) To do a '1.8 to 1.9 dry run' on my test server with a current 1.8 database, I totally rolled back to 1.8 by dropping and recreating the openvpms db using the current 1.8 sql and replacing the tomcat webapp with 1.8.  In that config, I get a good 1.8 (6778) with the current 1.8 database.

3) So from that starting point, I followed the 1.9 upgrade instructions, but fail at the db migration as above.  

4) Not sure if this is relevant: In my notes (and I admit I am not sure whether they were for a 1.7 or 1.8 install) I see that in act.customerEstimation, the following had their non-negative assertions removed:  Fixed Price, Low Unit Price, Low Total, High Unit Price, High Total.  I can't find the original info source for the change, but the changes were related to problems with estimates w/ discounts that I think have now been resolved.

5) Not sure if this is relevant: Archetypes product.service and product.medication had their descriptor.assertion propercasing removed.  That was just to ease data entry I believe.

Anyway... I'm stuck here.  Has the migration script changed recently from the BETA or has something nasty crept into my 1.8 db?

Thanks,

Sam

Kamloops, BC 

 

‹ Invoice Items -PT .jrxml How to get yesterday for history function ›

Comment viewing options

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

Re: Migration error - maxDiscountValue 1.8 > 1.9

Submitted by mckelvey on Tue, 29/11/2016 - 07:19.

Found my old OpenVPMS-1.9-BETA download and tried to migrate with that.  No go, same error.  And a diff only shows a non-related change between the BETA and release migration scripts.  Now I really am stuck...

Sam 

Re: Migration error - maxDiscountValue 1.8 > 1.9

Submitted by tanderson on Tue, 29/11/2016 - 08:30.

Can you verify you have the following SQL in the migrate-1.8-to-1.9.sql

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

Re: Migration error - maxDiscountValue 1.8 > 1.9

Submitted by mckelvey on Tue, 29/11/2016 - 11:37.

I'll have to check when I get back to work tomorrow, but I just ran it again using last night's 1.8 production server that clones to my house.  I used the migration script that came from a fresh download of openvpms-release-1.9.zip.  Identical results.    

I did a visual check of the UPDATE product_price_details stanza that you provided above, and didn't see any differences with what I have, but not trusting a visual check, pulled both your version and mine into their own text files and did another diff.  

diff -y --suppress-common-lines myUPDATE.txt timsUPDATE.txt
SET d.value = calcs.newMaxDiscount;                  /    SET d.value = calcs.newMaxDiscount;

I would have thought that I would just get a prompt back, but got the above instead.  Couldn't see anything there, but cleaned up the last lines and ran diff again, no hits this time.

I put a clean version of the stanza into migrate-1.8-to-1.9.sql and ran it again... failed as before.

Thanks for looking at this...  Frustrating in that I had such an easy migration the first time around.  But now using nothing different but a more current 1.8 db and the release version of 1.9... nada.  Same error on different copies of the database, different machines, different copies of the migration script from different sources... downloads and my recompiled release.zip...  Even ran the BETA version of the migration script.  Same error.

Thanks,

Sam

Re: Migration error - maxDiscountValue 1.8 > 1.9

Submitted by tanderson on Tue, 29/11/2016 - 11:57.

The migration script should work with MySQL 5.1 -> 5.5. It has not be tested with any other version.

Re: Migration error - maxDiscountValue 1.8 > 1.9

Submitted by mckelvey on Tue, 29/11/2016 - 12:59.

I'm running 5.5.53 everywhere.  I backed off doing the upgrade to Ubuntu Server 16.04 as 5.7 was the default and you pointed out that there may be some issues with that. Just running 14.04.5 on all machines. None of these databases have been exposed to 5.7.  Now it is true that a 5.5 update came through not so long ago... That has been something that has occurred since the BETA release.

I'll install an older version of 5.5 somewhere and see if these issues go away. Maybe just a default 14.04 without doing a system-wide update would be the way to go.

But I'm beginning to think that of all these different attempts... I'm the common denominator!

Sam

Re: Migration error - maxDiscountValue 1.8 > 1.9

Submitted by tanderson on Tue, 29/11/2016 - 14:13.

If you are still having issues, can you run the migration using:

mysql -v -v -v -u openvpms -p openvpms < migrate-1.8-to-1.9.sql >| migrate.txt

and send me the output at tanderson at openvpms.org?

 

Re: Migration error - max.Discount.Value 1.8 > 1.9

Submitted by tanderson on Wed, 30/11/2016 - 14:15.

I've verified I can run the migration script against  MySQL 5.5.53 on windows without issue.

The only thing I can think is that the migration script you are running has been modified. In the release distribution, there is no incidence of maxDiscountValue anywhere in the script. The closest match is maxDiscount.value

Re: Migration error - max.Discount.Value 1.8 > 1.9

Submitted by mckelvey on Wed, 30/11/2016 - 18:09.

You are correct.  The error message reads:

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

The only line in the migration script that refers to maxDiscount.value is in the stanza that you sent above, and that matches the scripts I have been using as confirmed with diff.  Even migration scripts from fresh downloads of 1.9-release fail on both older and current versions of the 1.8 database.

I did get a good migration with 1.9 BETA but that was several weeks ago now.  I of course will have to do a new migration on the current version of the 1.8 database as a pre-requisite to deploying 1.9 to the production servers. I have tried to migrate using a variety of pre- and post-BETA versions of Ubuntu Server, MySQL, and 1.8 databases, as well as BETA and release migration scripts.  All fail as above.

I will try the migration tomorrow on a Windows 7 machine I have access to as I can think of nothing else to try. If that works, I will be good to go alhough left totally flummoxed as to what is going here. 

If that fails, is there a check I can use on the db for structure or for bad data?  A recent database corruption doesn't explain my previous success but current failure on older databases, but the migration log was not too illuminating as to where specifically the problem may be.

Thanks,

Sam

Re: Migration error - max.Discount.Value 1.8 > 1.9

Submitted by tanderson on Wed, 30/11/2016 - 22:02.

What does the Create Table column of:

show create table product_price_details;

display? It should be:

CREATE TABLE `product_price_details` (
  `product_price_id` bigint(20) NOT NULL,
  `type` varchar(255) NOT NULL,
  `value` varchar(5000) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`product_price_id`,`name`),
  KEY `FKF9A9C1FC521C9574` (`product_price_id`),
  CONSTRAINT `FKF9A9C1FC521C9574` FOREIGN KEY (`product_price_id`) REFERENCES `product_prices` (`product_price_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

If the ENGINE is not InnoDB, it may be a case-sensitivity issue, as the migration script uses both maxDiscount:

SELECT
  p.product_price_id,
  cast(markup.value AS DECIMAL(18, 3)) markup,
  maxDiscount.value                    maxDiscount

and maxdiscount:

JOIN product_price_details maxdiscount
  ON p.product_price_id = maxdiscount.product_price_id
     AND maxdiscount.name = 'maxDiscount '

It should use one or the other.

The ENGINE should be InnoDB for all OpenVPMS tables; MyISAM shouldn't be used.

If ENGINE is InnoDB, then its probably still a case-sensitivity issue,  and the script should be fixed.

Re: Migration error - max.Discount.Value 1.8 > 1.9

Submitted by mckelvey on Thu, 01/12/2016 - 01:43.

Tim... you're a genius!

The output from the show table was identical to yours, and I have been careful to check that I'm using the InnoDB engine from the start.  But I have to admit I did not catch the case change in the stanza.  I changed all occurrences of maxdiscount to maxDiscount and ran the migration script again.  It ran to completion.

Below, is a snip from the output after running the altered migration script...

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

Query OK, 1029 rows affected (0.06 sec)
Rows matched: 1029  Changed: 1029  Warnings: 0

I'm not sure why it passed before and not now.... but on the surface, it seems to be working and that's a good thing!

Do you have a preference maxdiscount or maxDiscount... or perhaps you could forward or post an updated migrate script.  Just wondering if a mismatch here with the next script might cause similar problems later.  I'll go with your suggestion.

Thank you so much for all your help.  As an old Linux guy I would think that I would be very sensitive by now to case-change issues and at least have noticed and mentioned it.  Aargh.

Thank you again,

Sam

Re: Migration error - max.Discount.Value 1.8 > 1.9

Submitted by tanderson on Thu, 01/12/2016 - 11:24.

Thanks Sam. I've created https://openvpms.atlassian.net/browse/OVPMS-1831 for this. The corrected script is at https://openvpms.atlassian.net/secure/attachment/15100/migrate-1.8-to-1....

I've included a link to this in the Database migration instructions, until 1.9.1 is out.

Note that because your migration failed early, running it multiple times prevented this statement from executing:

#
# Insert the showPricesTaxInclusive flag. This prevents subseqent migration of prices if the script is run multiple
# times.
#
INSERT INTO entity_details (entity_id, name, type, value)
  SELECT
    p.entity_id,
    'showPricesTaxInclusive',
    'boolean',
    'true'
  FROM entities p
  WHERE p.arch_short_name = 'party.organisationPractice'
        AND p.active = 1 AND
        NOT exists(SELECT *
                   FROM entity_details d
                   WHERE d.entity_id = p.entity_id AND d.name = 'showPricesTaxInclus

As a result, your product prices that attract tax will be incorrect. You will need to restore your backup and run again.

 

Re: Migration error - max.Discount.Value 1.8 > 1.9

Submitted by mckelvey on Thu, 01/12/2016 - 13:18.

OK, thanks Tim...

That all looks good.  I'll start from scratch again.  Would be interesting to track down the source of the difference in the Linux deployments, but with Linux, one should probably always assume that everything is case sensitive. 

Good catch!

Sam

 

Syndicate content