SQL Version Upgrade Error

Hi @Brady @stephenwaite
Let’s start with a screenshot

Then I took a crayon and drew lines.

They don’t seem to match up. The sequence is out of order. Can you tell me what this line should be?

hi @juggernautsei, think it’s a fluke, would start over with this upgrade by deleting the database on the test server, creating a blank db, re-importing the prod db and then re-running sql_upgrade.

@juggernautsei, If you have moments to spare go to the code and find how @seq (mysql variable) is set. If it somehow @seq is set to null, @seq + 1 will be null and cause this error.

1 Like

@mdsupport, I took the time to lookup @seq means that the thinking in the writing of this script was to supply the value from

 SET @seq = (SELECT MAX(seq) FROM layout_options WHERE group_id = IFNULL(@group_id,@backup_group_id) AND form_id='DEM');

to here (truncated display)

   IFNULL(@group_id,@backup_group_id), 'Care Team (Facility)', @seq+1, 44, 1, 0,

But the query is coming back with a null value.

@stephenwaite, this is the second time I have run this upgrade and this is the second time that on day 4 the error appears. It is not a fluke. This is the 1.5GB database that I have been working with.

Generally, I just comment it the line out and move on. But I thought this time I would report it here.
But if I follow the logic back up the chain, the query tells me that the care_team_provider field_id does not exist in my database.

Once I comment out that line the upgrade script completes.

thanks @juggernautsei, the care_team_provider is created just above this if care_team exists which was added in the 4_2_1 upgrade.

@stephenwaite, good information. I went back and looked at the install and the install is 4.2.2 no patches. I checked the layout_options table and the care team provider is not there.

So, I checked searched all the scripts and it does not exist before 5.0.2 in any script.

use this:

 #IfRow2D layout_options form_id DEM field_id care_team_facility

Not this:

 #IfNotRow2D layout_options form_id DEM field_id care_team_facility

Would this logic work where the script checks to see if it does exist. The current logic checks to see if the field is missing. In my case, it is missing. But since groups are not enabled. It won’t be there. Then the code will try to install what is not there throwing an error.

What do you think?

don’t think so, the field should be there regardless of groups enabled, and it’s care_team that should have been in an install of 4.2.2