SQL Woes

For the most part, managing my web server is pretty straightforward, especially because I don’t really get a ton of traffic. Its mostly just keeping things up to date through standard channels.

Occasionally I have a bit of a brain fart moment. I recently was doing regular Linux updates on the server. I noticed a message I had seen before about some packages being held back. Occasionally I will go through and update these, because I am not real sure why they are being held back, but don’t really see any reason they should be.

Then MySQL broke.

So I went digging in some logs and searching for solutions, and decided I needed to roll back the version. Following a guide I found, I discovered… I had done this before, which I now vaguely remebered. Because the old .deb file was still there from last time I broke it.

Anyway, this didn’t fix it, MySQL still was not launching.

I decided that maybe it was time to just switch to MariaDB, which I believe is the spiritual successor to MySQL. And the process was simple enough, I would not even need to dump my Databases. So I uninstalled MySQL, installed MariaDB and… It worked!

Then it stopped working.

I restarted the SQL service and it worked!

Then it…. Stopped working… Again…

So I checked logs again and corrected some issues there and again it worked, then a half hour or so later it stopped working.

One thing I had come across in troubleshooting the original MySQL issue was that there was a command, mysql_upgrade that needed to be run to change how some tables are configured. I couldn’t do that before because I couldn’t even get MySQL to run. But I could get MariaDB to run at least for a bit, and had successfully gotten this upgrade command to run.

So I decided to, once again, try MySQL again, so I uninstalled MariaDB, and purged everything out, rebooting a few times to be sure. And MySQL would not even install anymore, so more purging, this time, including the Databases.

One thing I was glad I had decided to do, “Just in Case” when MariaDB was “working” was dump the databases out with backups. I was glad I did at this point. So with absolutely everything purged, MySQL installed and was working.

I set about recreating the databases from the dumps, and while I was at it updated all the passwords, since I had to recreate the user accounts used by WordPress anyway.

And now everything is working smoothly again.

A couple of links that were actually helpful in solving my problem.



Leaving Google, Part 2

I discussed briefly last post, about what’s come up with my GSuite Legacy account. I wanted to mention a few other things that came up, and some additional steps I made. A lot of the other issues were pretty easy to correct. One extremely useful tool in all of this I found, was the Google Dashboard. This lists all of the services used with some generic metrics of how much might be there to look into.

For example, I found I had a few “Saved locations” in Google maps. They ended up being some inconsequential hotels from a trip years ago, but it was good to know so if they were something meaningful, I could resave them to my regular old Google Account.

There is also some useless and even slightly misleading information here. For example Google Cloud Print no longer exists, but it’s still listed. There doesn’t seem to be a way to purge out that data. Youtube lists “112 Purchases”, which isn’t true either, it’s “112 titles synced through my connected Vudu/Ultraviolet/MoviesAnywhere” access.” It’s not something I need to care about.

One, I had forgotten about was Contacts. I could have done a straight import/export, but I opted to take the long road and manually transfer everything to Outlook. This way I could also clean things up. This also meant I had to do some settings changes and shuffling on my phone, so it would use my Outlook Contacts instead of my Google contacts. I was, thankfully, already using Outlook as my email client anyway.

I also remembered that I used my domain account for my Google Analytics and Search Console credentials (Why aren’t these just one service?). Fortunately, these were fairly easily transferred to my standard Google Account. I found 4 services where I was able to do this, so I’ll roughly cover them all together.

Google Search Console, Google Analytics, Blogger, and Youtube, could all be directly transferred to a standard Gmail/Google account. The process is similar but slightly different on each.

Search Console allows you to delegate access to a secondary account. This account can then be made the primary owner, though to do this they need to be re-verified using DNS records.

Google Analytics is similar to Search Console, without the need to re-verify. I simply added my Gmail account as a user, promoted it to Admin, then deleted my domain account’s access.

Blogger worked the same way. I don’t really use Blogger, but I did have an empty blog with a name I’d like to hold on to, so I transferred it over.

Youtube is a bit more complex. Channels have to first be converted to “Brand Accounts”. Then you can delegate secondary users. Also, there is a waiting period of 7 days before a delegated user can be elevated to be the “Primary Owner”. After that period, I made my Gmail log in the Primary, and deleted access from my domain account. I had initially started simply consolidated playlists between accounts, but this was a pain in the ass. Going with the Brand account method, I was able to transfer everything. I BELEIVE, but am not positive, that there may be a way to convert the channels back to personal accounts, but I’m not positive on that one.

I’m am nearly ready to see what steps I need to take to cancel out the Gsuite part of the domain account, which HOPEFULLY will convert it to a Web ID. The only thing left is my Google Play apps. It’s not cleat at all what will happen here. If it converts to a Web ID, the Play Purchases should just continue to be used and licensed to that account. I have documented everything I’ve every bought, free or not, in case I need to “re-purchase” anything I use or care about (free or not) on my Gmail Account. I still plan to use the Gmail account going forward. I do also need to figure out how to transfer my Pokemon Home subscription to my Gmail account.

The whole process has gone surprisingly smoothly, it’s just been time consuming. It helped a lot that frankly, I wasn’t really using many Google Services anymore to start with. The reality is, that consolidating all my email to one (Microsoft) account has been great, because I can better use Rules to manage it before it’s shuffled off to the archive ball on my NAS.

Leaving Google

I’m not going to go into a ton of detail, because I am sure I have rambled about it before, but I have had a pretty downward relationship with Google. There was a time when I was an enormous Google fanboy, and that slowly started to decline as they started just, removing services and features, and the core turn happened when they dumped Google Reader. Over the last ten years or so I’ve slowly migrated things away from Google, though I never quite outright left Google. In fact, technically i still and not quite “leaving” Google.

This is the problem with a company like Google, you can never quite “leave”. Most of what I use though wasn’t super due to choice. I use YouTube, because there isn’t really an alternative, for example. I also continues to use Gmail and Calendar, because I had a free Legacy G-suite account, that allowed me to use my domain for my email. I do also have a Legacy Gmail account, from when things were Invite Only as well. I don’t really use Docs or drive, except for a few annoying services that don’t offer alternatives. I sort of used Photos, but more as a backup to my backup to my backup, never as a primary, and I stopped when they discontinued the Unlimited Storage.

I have a variety of reasons I dislike Google, but I’m not going to get into all of that here and now.

However, it recently came to my attention that Google is discontinuing it’s free tier Legacy G-suite accounts. Previously these accounts were grandfathered, but now they are going away. This is an enormous problem for a lot of people who use this as their Primary account, central to their work flow. Thankfully, in my case, I have mostly stopped using Google for my workflow, but this is definitely giving people a bad time. The only option is essentially to pay to upgrade to a regular account, at $6/month on the lowest tier.

Part of my annoyance stems from having to hear about this from a Podcast, talking about Microsoft offering a discount to people who switch. I didn’t get the email, as the email went to my admin account, that I never check and forgot existed. Granted that is on me, but I wonder how many others are going to get screwed by this.

My only worry at the moment is what will happen to my Play Store purchases. It appears that my account will be able to downgrade to an Identity Account, which will allow me to keep my Play Store purchases. It also seems to keep YoutTube data as well. but to be safe I’m shuffling that around as well (more in a bit).

So what do I, personally, need to do about this?

The main issue is email. I use my domain email on Google a lot. It’s one of two Primary Email addresses, and it’s the top one of those two. Fortunately, I already run an email server for some secondary domains. I was planning to get rid of that email server, but it could wait for a bit while I wind down my Primary Email as well.

Fortunately, I didn’t need to go this route. I pay for Microsoft 365. I suppose it’s worth mentioning, my problem with what is happening with Google, isn’t the paying. The $6/month tier at Google is just not worth it for me. I dislike Google as a privacy nightmare for starters. The search results have been shot for years, they push a lot of weird lock ins. Docs/Sheets sucks next to MS Office as well, and you get a LOT more space with One Drive than Google Drive. The lowest tier of Google’s offerings is $72/year, and I pay $99/year for Microsoft 365, for SIX accounts, which I use across my family.

Anyway, Microsoft 365 offers the ability to use a custom domain email. The set up is designed to be simple, and uses GoDaddy, which I don’t use, but there is an easy work around to use any domain registrar. It’s all just DNS records. If you are capable enough to set up DNS records, you are capable of following this helpful guide on Reddit. You may need to massage things a bit, for some reason the guide has a 0 leading the @ entry which I didn’t need. Though I did need to set the Priority to “0” (Zero) before it took, over on Cloudflare’s DNS. I followed the guide, and around 24 hours later (probably would have been sooner but I had Priority 5), the email was coming in to my Outlook account instead of my Gmail account.

The biggest hurdle was out of the way.

It’s worth mentioning, that Gmail will still contain the older emails. It may be EASIEST, to clean out those emails BEFORE transferring the domain. You can probably still connect the old account to something like Outlook or Thunderbird, but to be sure, either make sure you already set up the Gmail account in a client, before the transfer, if you go this route. This suggestion is honestly, for any 3rd part email provider. You can do a Take Out request, but it’s much easier to just use a client, then drag and drop emails.

In my case, I already had MOST of my emails cleaned out. I keep a local email server on my Synology NAS, with a sorted archive of all my emails. If you really want to be thorough, once you have copied the emails off, go back to the Gmail web interface, select “All Mail”, then use the “Select all” button at the top of the list to delete everything.

Next step was calendars. This was fairly simple. I opened Outlook’s calendar and Google Calendar in tabs side by side. I copied the URL for any web calendars I was subscribed to (use the ical URL) over to Outlook. I then took a moment to sort out the events on my remaining user created Google Calendars, before exporting and importing them to Outlook. Lastly, Google Calendar lets you create tasks and reminders, I manually copied those all into Microsoft ToDo, (which I also, already used). The remaining task, my wife has a Google Calendar from her Gmail account that we share. I’ll transfer my half of that from my Gsuite account to my Gmail account and be done there. I’d convince her to convert it to Microsoft but frankly convincing her to do the shared calendar int he first place was enough of a hassle.

After the Calendar we have Docs, well, Drive, and mostly Sheets. I honestly could never quite get Google’s naming for it’s office suite. I don’t keep much in my Google Drive, some PDFs I had clipped and a few shared docs I had saved off. I simply downloaded everything and sorted the files out to other places in my archive structure.

Photos is tedious, but not hard either. I already have better back up systems in place for my photos, using some USB drives in a safe and One Drive in the cloud through my NAS. I did have thousands of photos uploaded to Google Photos however, from around 2005 through 2019. Mostly I am just going through periodically and deleting them all out, downloading a few here and there just to make sure that I have them saved locally, which I probably do. These “here are there” things are more like, scanned photos, some random work photos, some screen shots from my phone. Things that may have been direct uploads basically, instead of pushed up by the old Photo Uploader from my archive.

The last real potential issue is YouTube. I have several channels on YouTube, though I don’t use them a lot for posting, I do use them for different things. 3 of the 4 channels are “Brand Accounts”. I had previously converted them to Brand Accounts to consolidate ownership under this Gsuite account. This also makes it easier to shuffle ownership back to my Gmail account. I have a 4th channel that is just a regular YouTube account, that I will likely convert to a Brand account and shuffle over to the Gmail account as well. I may also just copy it’s playlists over and call it good.

The last worry, I mentioned before, is my Play Store purchases, mostly Apps, I don’t care about books and movies or music as everything I have gotten there is free. I suspect I will continue to be able to use this account as an ID account, connected to those app licenses. Ideally, I would love to transfer ownership of everything to my Gmail account, but I don’t think that’s going to be offered as an option.

Purging WordPress .ico Malware… (Hopefully)

So, this is a hopefully, because it’s been a bit since I have done this, and things seem to be clean. So, there is a reasonably common bit of Malware out there that seems to affect WordPress sites, I say reasonably common, because in my time looking for a solution, I have come across a fair number of others with the issue, but no solutions. And I have tries several solutions. As of now, I have been a few months clean, and without hacky work-arounds. I’m going to attempt to run through what I did that held it at bay, and what seems to have finally managed to purge it, in hopes of helping others.

The Malware itself basically would occasionally redirect the blog domain to a spam website. I say occasionally, because it’s not all the time, and with enough anti-advertising stuff in your browser, you may never see it happen. I have personally, never once seen it happen, on any of the sites I run on this web space. I first found it was infected because occasionally, my wife would mention that someone she had linked her blog to, was getting sent to a spam website. Initially I thought maybe someone was mistyping the domain along the line. My wife also said it would happen occasionally. In my work combating this malware, it seems like the actual redirect occurs slowly over time, as the infections spreads.

It also will spread across sites hosted on the same server. Which made it extra tricky to fight because I had to juggle several sites at once.

Part 1 – Keeping It at Bay and How it Spreads

I have no idea how the infection was initially started, which is rough, because that would be key to KNOWING it’s gone. As near as I can tell, the initial source of the infection is int he uploads directory of a blog. It eventually starts to add “gibberish code” to files like wp-config.php and settings.php. I say “Gibberish Code” because it’s actual PHP, but it’s very messy in it’s design and encoding to make it hard to read to locate files. The gibberish code would generally show up at the top of the files, but could be elsewhere.

Eventually, random folders would start showing up in the root WordPress directory, sometimes with gibberish names, sometimes with specific spammy sounding names, sometimes with names that appear to be part of the blog (like ‘site’ or ‘blog’).

The first step in holding this at bay was so dump all write permissions for several critical WordPress files that kept being infected. This seemed to only sort of help, the problem was more that the owner, www-data, still could write to the files.

The next step was to convert all of the web files to an alternative user account as the owner, then set the files so www-data could only read. This created a new problem, it meant I could not easily update anything or upload images for blog posts easily. Since www-data had no permissions to write anywhere. If I was making a new post, I would have to SSH into the server, temporarily change the permissions, then change things back.

Pain in the ass.

My temporary fix there, was to keep the current year as writable, and run a script that would probe for malware files and delete them. There were two scripts, one for the hidden .ico files that would crop up and one for any .php files that were in the Uploads folders. Both run with a cron job.

/usr/bin/find /var/www/html -name ".*.ico" -exec rm {} +

This is, admittedly, an EXTREMELY Hack way to correct this problem. Hack less in the sense of “computer hacker”, hack in the sense of “jankey or shoddy”. But it worked, while I figured out the root issues.

Part 2 – Fixing the Issue

Eventually I sat down and just sort of rebooted everything, all at once. I started with everything set up as an alternative user permissions and locked down. I then scrubbed out all infected php and ico files from the upload folders. I then thoroughly scrubbed out the wp-config files. Basically, any files I would need to moved to a fresh WordPress install, which was the uploaded images, and the configurations, were completely sanitized.

Next, I downloaded a fresh copy of WordPress, expanded it out and made copies for each site folder with websitename_new. After that, I copied the uploads folders and wp-config files to the new copies. Then i renamed each current folder as websitename_old, and renamed the new ones as simply websitename. (I actually did this and the subsequent steps once at a time for each site). This made the new, fresh installed copies live.

Except they have no plug ins and no themes. I did not transfer any old theme files or plug in files, for worry of infection. Instead I went into the old folders to get a list, then redownloaded each theme and plug in to the fresh copies. This meant doing some reconfiguring but it was worth it for clean copies. I also left out anything that wasn’t absolutely essential to the basic look and operation.

Site note, when a fresh install copy is made live, it may not load until you go into /wp-admin, and change the theme to literally anything else (Generally, the current year WordPress Default works). The config files will still be looking for the non-existent old theme.

With everything fresh and ready to go, I deleted the old potentially infected copies, to ensure the infection was now completely purged. After that, I created a backup folder, and copied all of the current fresh versions of the site folders to the back ups. This way, in the event of a reinfection, I could simply, slap a fresh back up in place easily. It might be missing a few recent images, but it would be way less work.

Still worried, I then reverted the permissions for the sites back to www-data, but I did them one at a time, roughly a week apart. Carefuly checking for reinfection with each change.

So far so good, I have not had any problems. Here is hoping it stays.

Fixing Cron Not Executing

Recently I encountered an issue I hadn’t run into before. Specifically, my Cron Jobs were not running. Everything seemed correct and I could manually run the commands at the CLI. I’ve had some issue before with getting things to run because I wasn’t using the complete path for programs but this seemed to be something different.

The problem I found was that the root password needed to be changed. Running the following:

sudo  grep CRON /var/log/syslog

Would output a long list of the same issue repeating over and over.

May 27 10:30:01 Webserver CRON[12943]: Authentication token is no longer valid; new one required
May 27 10:39:01 Webserver CRON[12978]: Authentication token is no longer valid; new one required
May 27 10:39:01 Webserver CRON[12977]: Authentication token is no longer valid; new one required
May 27 10:40:01 Webserver CRON[13049]: Authentication token is no longer valid; new one required

When running the following command:

 sudo chage -l root

Would output something like:

Password expires               : never
Password inactive              : never
Account expires                : never

Which suggests the root password has never changed. So I ran the following command:

sudo passwd root

And set a new root password (which was the same as the old root password) and suddenly everything started working again. It felt like a really odd issue, especially considering I didn’t actually change the password, and as far as I could tell I had a root password. Plus the password wasn’t set to expire at all.

Anyway, I wrapped it off by doing an (optional) truncation of the system log. Since the file had become unwieldingly huge with the following.

sudo truncate -s 0 /var/log/syslog