I attempted to upgrade Octopus this morning from version 3.3.10 to 3.4.4. The installation seemed to run fine but the database upgrade failed. I have attached the log, but the failure seems to relate to a failed insert statement:
======================================
System.Data.SqlClient.SqlException: Cannot insert the value NULL into column ‘ChannelId’, table ‘OctopusDeploy.dbo.Deployment’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
I tried running the script refered to in a previous post. But got the same error when I ran it.
If anybody has experienced a similar problem or can shed light on my issue that would be great.
Thanks for getting in touch. I’m really sorry this upgrade has gone wrong for you. Would you be able to run the following three SQL queries on your Octopus database and send it through to me?
select Id, ProjectId, ReleaseId from dbo.deployment
select Id, ProjectId, ChannelId from dbo.Release
select Id, ProjectId, JSON from dbo.Channel
That will help me to see what is going on with the upgrade.
No problem these things happen. To be honest we know what the problem is and we know how to fix it. But it’s a manual process. The problem is, we have a project set up that is quite old, it has no Channel associated to it and interestingly the release history has been deleted.
Ideally, we’d like to delete the project but it needs to remain where it is. Removing the Null Channel fields in the release table should fix the issue. However, it would be nice if we can get round this using the Octopus upgrade approach.
The update should have been able to handle older projects, as default channels were created internally for old projects when channels were introduced. This update just moved some data around to clean up the schema a bit, but it couldn’t find those default channels on your data for some reason.
I’ve put an updated version of that part of our upgrade script here: https://gist.github.com/markryd/0ef66e2c3de8d33d6433de631cf125ec if you could give that a go please? I’ve tested it against the data you sent and it succeeds in my test environment. If it succeeds, the normal octopus upgrade should then work for you, since the missing data will be filled in. If this doesn’t succeed, can I get you to send me through the results of:
select Id from dbo.Project
I should have asked for that last time sorry. The only change I’ve made to the upgrade is to not link through the Project table, which wasn’t necessary (and I forget to ask you for that data), so I was a little surprised that fix worked with the test data.
I’ve had to restore the database pre running that script. See the attached error for details.
During a deployment we got an error, rather than risk this happening again I’ve reverted the database back to it’s original state. The error looks like it’s related to the script you sent.
Okay, I can see what is happening. The script I sent is a new version of one of the scripts included in the update, once it has run there is a column that can’t accept nulls, however if you haven’t completed the upgrade Octopus itself doesn’t know about the new column, and doesn’t know to write data to it, hence the error you are seeing. I’m sorry I didn’t consider this.
You should be able to re-run that script immediately before you run the update, then everything should be okay. Again, really sorry I didn’t pick up on that and warn you. Let me know how you get on with the upgrade when you get to it.
Quick update. I ran the script before the upgrade and everything worked fine. No errors. Thank you for your help.
I’ve documented the update script in case we ever need it again. One final question before I close the ticket. I’ve built up a UAT vanilla environment. Regarding the data, is it best practice to export data from Live and then import into UAT?
I’m guessing a SQL backup and restore may cause problems?
SQL backup and restore should work fine, the only thing you will run into is that by default the two servers will have different Master Keys (this is the key we use to encrypt things like Sensitive Variables). This may not matter depending on what you need your UAT environment for. However if you need to you can use Octopus.Server.exe configure --instance=UAT_INSTANCE_NAME --master-key=VALUE to set the UAT key to be the same as the Prod key. See http://docs.octopusdeploy.com/display/OD/Server+configuration+and+File+storage for some more detailson the configure command.
If you do stick to the inbuilt backup/restore one thing to keep in mind is that the restore will also restore the certificate that the server uses for tentacle communications (you will see the thumbprints in Configure->Certificates will match. This will allow your UAT server to start communicating with any Prod listening tentacles. This is okay for a few minutes while you are restoring but will definitely cause problems if left like this. To change this cert on UAT after the restore use Octopus.Server.exe --instance=UAT_INSTANCE_NAME regenerate-certificate --octopus-tentacle
I am on annual leave untill Tuesday 20th September. Please contact Web Ops (webops.team@bbc.co.uk) if you require assistance.
Cheers,
Ben
This e-mail (and any attachments) is confidential and may contain personal views which are not the views of the BBC unless specifically stated. If you have received it in error, please delete it from your system. Do not use, copy or disclose the information in any way nor act in reliance on it and notify the sender immediately.
Please note that the BBC monitors e-mails sent or received. Further communication will signify your consent to this.
Again thanks for your help. We’ve been using version 3.4.8 for a couple of weeks now and it’s performing fine. The developers are liking the new features :-).
I’ve been tied up with other things but intend to config the UAT environment over the next couple of days. I’ll use the standard in-built back up/restore method for the data transfer and then run the script prevent UAT communicating with Production tentacles.
One question, in this script: Octopus.Server.exe --instance=UAT_INSTANCE_NAME regenerate-certificate --octopus-tentacle.
Does the UAT_INSTANCE_NAME refer to IP or hostname of the server? I’m 99% sure it does but just checking
Sorry, I should have been a bit clearer there. Instance in this case is the Octopus instance name you set when you installed Octopus. You only need to set a name if you have more than one server running on a single machine, so you may not have set one. If that’s the case you can just leave that setting out of the command. There is a page in our docs that describes things a little more and shows you where you can see the instance name if you need it here: http://docs.octopusdeploy.com/display/OD/Managing+multiple+instances
Sorry to keep this thread running. I just thought I’d let you know that I’ve successfully set up our UAT environment using the database backup/restore method rather than the Import/Export method. After restoring I ran the script to update the master key.
Worked absolutely fine.
One question though, am I safe to delete projects in UAT without affecting live? Both environments use a different database server but I’m just concerned about the master key share.
No problem with keeping this thread running. It’s worth kicking off a new off a new one if you have anything urgent though.
Nothing you do in UAT will affect your Live installation with the following caveat: Octopus stores a few things on the drive of the server it is running on, such as packages in the internal package cache. By default they are in sub-folders of C:\Octopus and that path is stored in the DB. If UAT and Live are on the same machine (which I assume they aren’t) they would both point to the same folder and a retention policy on one would delete packages the other is using. Alternatively if you have overridden that location to use a network share they would point to the same folder.
The only other thing is to make sure you run that regenerate-certificate command after you restore as we discussed previously.
Polling tentacles won’t talk to the new server because of the different server IP address, the UAT server will reach out to the Prod tentacles to open comms but won’t suceed due to the different certificate.
If you want to prevent even that connection attempt you can disable all the tentacles in the UAT server. You can do that directly in the DB after you’ve done the restore by running update dbo.Machine set IsDisabled = 1. After running that you should see that all of the Machines in the Environment screen are disabled.
