Ubuntu server upgrade 16.04 to 18.04 (20.04 pending)

Virtualize, document, and test. The surest way to upgrade success.

For years my server has been running my personal websites and other services without a hitch. It was Ubuntu 16.04. More than four years old at this point. Only a year left on the 16.04 support schedule. Plus 20.04 is out. Time to move to the latest platform without rushing rather than make the transition with support ended or time running out.

With the above in mind I decided to upgrade my 16.04.6 server to 20.04 and get another five years of support on deck. I’m half way there, at 18.04.4, and hovering for the next little while before the bump up to 20.04. The pause is because of a behavior of do-release-upgrade that I learned about while planning and testing the upgrade.

It turns out that do-release-upgrade won’t actually run the upgrade until a version’s first point release is out. A switch, -d, must be used to override that. Right now 20.04 is just that, 20.04. Once it’s 20.04.1 the upgrade will run without the switch. Per “How to upgrade from Ubuntu 18.04 LTS to 20.04 LTS today” the switch, which is intended to enable upgrading to a development release, does the upgrade to 20.04 because it is released.

I’m interested to try out the VPN that is in 20.04, WireGuard, so may try the -d before 20.04.1 gets here. In the meantime let me tell you about the fun I had with the upgrade.

First, as you should always see in any story about upgrade, backup! I did, several different ways. Mostly as experiments to see if I want to change how I’m doing it, rsync. An optional feature of 20.04 that looks to make backup simpler and more comprehensive is ZFS. It’s newly integrated into Ubuntu and I want to try it for backups.

I got my backups then took the server offline to get a system image with Clonezilla. Then I used VBoxManage convertfromraw to turn the Clonezilla disk image into a VDI file. That gave me a clone of the server in VirtualBox to practice upgrading and work out any kinks.

The server runs several websites, a MySQL server for the websites and other things, an SSH server for remote access, NFS, phpmyadmin, DNS, and more. They are either accessed remotely or from a LAN client. Testing those functions required connecting a client to the server. VirtualBox made that a simple trick.

In the end my lab setup was two virtual machines, my cloned server and a client, on a virtual network. DHCP for the client was provided by the VirtualBox Internal Network, the server had a fixed ip on the same subnet as the VirtualBox Internal Network and the server provided DNS for the network.

I ran the 16.04 to 18.04 upgrade on the server numerous times taking snapshots to roll back as I made tweaks to the process to confirm each feature worked. Once I had a final process I did the upgrade on the virtual machine three times to see if I could find anything I might have missed or some clarification to make to the document. Success x3 with no changes to the document!

Finally I ran the upgrade on the production hardware. Went exactly as per the document which of course is a good thing. Uneventful but slower than doing it on the virtual machine, which was expected. The virtual machine host is at least five years newer than the server hardware and has an SSD too.

I’ll continue running on 18.04 for a while and monitor logs for things I might have missed. Once I’m convinced everything is good then I’ll either use -d to get to 20.04 or wait until 20.04.1 is out and do it then.

MySQL backup and restore

Dig in and do it, and repeat. Get the desired result faster by combining research and testing.

Maintenance is important. A car needs oil changes or eventually the engine will be damaged by regular operation. A server needs software updates to fix bugs and protect against threats. Even when those things are done failures can happen that create problems returning to normal operation.

For a car there needs to be a spare ready to go in case of a flat. If there’s not a spare ready for use it will take longer to get the car back in operation when a flat happens. For a computer, programs and data need to be backed up. If a disk drive crashes the information stored there may be lost or too expensive to recover, so just as good as lost.

This website has not been well protected for too long and I knew that needed to change. There’s a server operating system, a web server, WordPress software, and a MySQL database that all operate interdependently to make it work. As the amount of content slowly continues to grow my manual system to back everything up has become too cumbersome and is not done frequently enough to ensure minimal to no loss of data.

That needed to change.

Step one – automate the MySQL backups. Documentation states the “logical” backup method is slow and not recommend for large databases. The alternative “physical” backup entails stopping the database server and copying the files. The licensed MySQL Enterprise Backup performs physical backups and from what I’m able to tell runs clone databases so one can be stopped and the files backed up while the clone continues to run and is available for use.

This is a hobby operation and has limited resources so purchasing a license for Enterprise Backup is out of the question. Taking the whole thing offline to backup probably doesn’t bother anyone except me. Still, I did want to be able to continue to run the server while the databases are being backed up. Enter logical backup.

It didn’t take long to find the command, mysqldump. Confirming that it would backup everything including user names and passwords so all the accounts got restored with all the data took longer.

Despite my best search-fu I was unable to find any documentation that explicitly says “do this” to back up user accounts in addition to system databases and other databases. Let me fill that gap by saying “do this to back up user accounts, system databases, and other databases”. mysqldump -u root -p -h server.yourdomain.org --all-databases -r backup_file.sql. I did find the preceding command as the backup command. Nothing I could find said this backs up user accounts and system databases. I tested it. It does.

With the backup done, the next step is restore. And confirming the restore works as expected. Another case of things that otherwise seem obvious not being declared in the documentation.

Restore from the command line looks like this: mysql -u root -p database < backup_file.sql. But wait, I want to restore all databases. Search-fu failed again to find any explicit instruction how to restore all databases and what database to name on the command line.

Try the command without naming a database to see if all are restored. No, that fails. Then a flash of insight. Create an empty database, name that on the command line, and try the restore again. It works!

$ mysql -u root -p
> create database scratch;
> exit
$ mysql -u root -p scratch < backup_file.sql

Did this a few times and then restored the tables. As far as I’ve been able to determine the restore is an exact replica of the backed up data.

It seems odd that important use cases, complete backup of database server and complete restore of database server aren’t clearly documented. The information is there but important nuggets are left out. The only way to be sure you’ll get what you need is to experiment until you’re able to produce the results you need.

So yes, do the research but also just do the work and inspect the results. When research doesn’t clearly answer the questions backup it up with experimentation. Do both and get a result faster.

Help people get the job done

IT’s job is supposed to be making things easier for users.

Users have been using a single URL for access to all their web applications and now the backend for just one is moved to another server to avoid end of life? If you’re where I am now users are sent a new URL and told to use it if that application is needed.

It is accessed via Citrix and I don’t understand Citrix architecture well I have to say. However the users of this app apparently don’t use any other app via Citrix.

In the meeting about the change I wondered out loud whether users could just be redirected? No need to learn a new URL, no need to know when or if to use it. Just send the apps’ users to the new URL when they attempt to use the app.

The response was, “no, can’t do that”, “don’t have wild card certificates”, “can’t install existing certificates on other servers”, “can’t change DNS”, “can’t send people from the old site to the new site”, and so on…

My reasoning was to simplify the user experience. Why make people learn something new if there’s a way to get them to the new webapp without learning a new URL? As a technologist I feel VERY strongly my job and the job of others like me is to enable people to do their work and not force them to understand or learn technology that is not relevant to that.

Back to the objections. A DNS name can have its network address updated periodically. This very website has a dynamic address and can still be found by name even after an address change. The server is running a job to monitor the public address and update DNS when it changes. Automatic. Hands off.

No certificate changes required. If siteA and siteB are continuing to operate as siteA and siteB and each has their own valid certificate then no change in certificate needed. When someone browses to the site the browser requests a secure connection. The trustworthiness of the connection is determined by information the site provides and certificate authorities the browser trusts. No need to move certificates anywhere. Even if there were that can be done without renewing certificates.

Sending people from one site to another, in its simplest (as far as I know) form only requires a Redirect. For wesiteA and websiteB, if visitors to websiteA should actually be going to websiteB tell websiteA’s webserver to redirect browsers to websiteB. When somebody browses to websiteA the webserver sends a message back to the user’s web browser which says you need to ask for websiteB instead. Then the browser does just that and ends up at websiteB even if it’s on a different server in a different country.

I actually set up Redirect on this server to test my understanding and be certain it would work the way I thought. It did. Visiting one of my webhosts on this server automatically directed me to workAppA and visiting another webhost went automatically to workAppB.

In doing the reading to get Redirect set up I learned it could be as granular as by user or program on an Apache server. I suppose it’s possible Citrix doesn’t have a way to support that. But I don’t believe it. I know Citrix apps can be secured by login so userA and userB don’t see all the same apps. I’ve written powershell to report what security groups are associated with which published apps on a Citrix server.

In this case telling end users YOU HAVE TO LEARN SOMETHING NEW to keep doing your job the same way strikes me as IT not doing its job!

Technical support

Technical support. Not quite “Vanilla Sky” level stuff but still thought provoking.

I provide some limited technical support on Apache OpenOffice and LibreOffice forums.

Why not Microsoft Office? Because that’s what I do at work. At home I use, and have used for many years, LibreOffice and OpenOffice. Why? Because they save me money, the support forums are generally more congenial and providing support lets me give back a bit for the value I get.

One of the things I really enjoy about providing support is seeing all the ways people are using software to do things they need to do. Things I would never have conceived of. It is a real eye opener to get a handle on someone else’s requirement. And then very gratifying to help someone meet their need.

One of the recent support requests was for a bowling league score sheet kept in Apache OpenOffice. The requestor wished to have the latest match value always be shown in a particular cell. The league has 32 weeks in its season and for each week the bowler’s score is entered for each of three games. The game total and average are calculated and displayed for each week.

The latest week’s match value always needs to show in a particular cell. The method being used was to update the cell manually each time a new week’s scores were entered.

I came up with a solution, posted it. Then, as I often do, rethought the whole idea. What I realized was the way I designed the solution it would always show the value for the 36th week, the last week of the season, rather than the most recently entered week! Not good.

After some deeper inspection I recognized this happened because the formula to show the score for the latest week actually just checked to see if there was anything in the referenced cell. And it counted a formula in the cell as something, even if the formula displayed nothing.

This needed to be fixed! I couldn’t provide a solution that didn’t work.

After some thinking I realized a formula to show the latest value needed to recognize whether the formula on each row displaying the value was showing a numeric value, a blank “”, or a label “DNB”. DNB, Did Not Bowl, was a label indicating the bowler hadn’t bowled that week.

Again, the season is 36 weeks. Scores are entered week by week. This meant the rows showing score and average were always followed by rows showing nothing. Even though cells in the rows contained formulas, the formula result was “” for each week after the latest one entered.

My original formula was detecting a formula in a cell as something. I needed to come up with a formula that could identify the row before the first row with formula result “”. Finally an idea struck. I could use a function that counted the rows where the formula result is “”. A blank. These are always the weeks of the season that are not yet played. The season is 36 weeks. Subtract the number of blank rows from 36 and that’s the last row with a score. Problem solved!

That formula is…

=INDEX($Sheet1.G6:G41,36-COUNTBLANK($G$6:$G$41),1)

With that part of the problem solved I saw that some formulas I hadn’t touched were returning #VALUE! errors. These were formulas that calculated total pins week by week. These errors were happening because of changes I’d made to the formulas to sum the pins for each week and to produce the pin average for each week.

My fixes created the problem so I was determined to resolve it.

I created what I call a “dynamic formula”. A formula that changes based on where it is in the sheet or what it reads from a value elsewhere in the sheet. It didn’t work. It has been some time since I created such a formula in OpenOffice or LibreOffice.

There’s quite a bit of compatible functionality between Microsoft Office, LibreOffice and OpenOffice. For the most part spreadsheets created in one work in the other without modification. For the most part.

As it turned out, I was creating the “dynamic formula” as if writing it in Excel. Dynamic formulas are one of the things that are a bit different between the Microsoft and OpenOffice/LibreOffice spreadsheets. Once I recognized that, I was on the way to developing a solution.

The below dynamic formula, “the solution”, totals values in a column beginning at a specific row and continuing to the row the formula is in.

=IF(ISNUMBER(I6),DSUM($G$5:INDIRECT(CONCATENATE("$G",ROW())),1,$BB$5:$BB$6),"")

The formula needs to calculate a sum from a fixed starting row to whatever row the formula happens to be in. And in the case of the bowling league it needs to do that for thirty-six rows. If the formula couldn’t tell which row it was in and sum from the first row to the formula’s row then thirty-six different formulas would need to be entered. One for each row.

Entering the same formula in thirty-six rows is much easier in my opinion. And easier to maintain and easier to modify.

By tackling this person’s question I:

  • Helped solve a problem
  • Familiarized (again) with the difference between Calc and Excel dynamic formulas
  • Learned about a process, a “functional requirement”, I wasn’t familiar with and provided a way to support it

For me, this was a win all around. What could be better than the warm glow of finding the solution to a previously unknown use case?

Remote Support

Using RealVNC and AeroAdmin to help people at their computer

I provide remote computer support to people. Mostly to family but also occasionally to friends and acquaintances.

For family I’ve been using RealVNC. For other folks, AeroAdmin.

RealVNC has several advantages from my perspective. Two important ones are there’s a native Linux version. Family doesn’t use Linux but I do and I want to be able to run something directly on my system rather than running it through layers. Secondly and most importantly for family, it’s running all the time on their system. If support is needed we get on the phone, I start the connection, a message pops up on their screen and they click OK to allow the connection. Shazam! remote support has started. There’s nothing for them to install, no program for them to start. Just ask for support then click OK.

Other pluses are it runs on Linux so I have remote access to my own system when I’m away from it and I can register up to five systems in my RealVNC account with no subscription license required.

For the person I’m tutoring now and others I’ve helped in the past AeroAdmin has been the way to go. There’s a free license that allows occasional usage without charge. When I say “occasional” I don’t mean to imply crazy limited. From my perspective the free license is very generous with the time and connections allowed. I have certainly never bumped into the usage limits.

Of course there’s drawbacks to both. With RealVNC the issue is running it on my system. RealVNC occasionally freezes and restarts. When it does I either have to wait out the freeze before I can begin using my pc again (same experience whether I’m connected remotely or at the keyboard) or I restart my system if I’m at the keyboard. An issue I choose to live with because it gives me remote access to my system and I know how to deal with it. When I’m at the keyboard I sometimes just shut down RealVNC so there’s no chance it interrupts me. I really should file a bug report but haven’t bothered yet. And since it’s me that’s affected, not family I provide remote support to, I know what’s going on and corrective actions I can take.


EDIT 3/2020: I found the issue back in January. A wall paper changer set to change wall paper every five minutes. Increased the interval to 15 minutes and the problem became less frequent. Switched to a different wallpaper changer and the problem has gone away


AeroAdmin is a different story. The biggest challenge is always getting the person who needs to use it to get it on their system, get it started, provide me the information I need to connect, and finally make the correct choices when the connection starts so I can actually help. That sounds hard but it’s not. But it is for many people.

I’ve made documentation and sent it to people. I’ve recently made this video, AeroAdmin, letting someone help you. But it is still a challenge. Getting the connection started so people can get help always seems to be the biggest issue.

Another AeroAdmin drawback, for me not the person supported, is that I need to run Wine to run AeroAdmin. I’ve never been a fan of Wine because whenever I tried it my system was changed in ways that I couldn’t easily undo. Plus, depending on the install package, it added things to my system that I didn’t want. Getting stuck with things I didn’t want was one of the reasons for getting away from Windows. However AeroAdmin does run well under Wine so, when I need it, I run it in an isolated Linux vm with Wine installed so Wine isn’t impacting my actual running system.