Earlier today, I noticed that the robservatory database was massive—over 1GB in size, which it shouldn’t be anywhere near (it’s usually around 100MB). This is over the database size limit at my host, so I couldn’t add any new content (nor could visitors comment, create accounts, etc.)
My host offers phpmyadmin acccess, so I connected to the database to try to figure out what was going on. Using phpmyadmin, you can browse tables, perform SQL commands, and export and import data—it’s a must-have tool for managing remote databases.
The first challenge was to figure out which table was causing the problem. To help with that, I wanted to see which WordPress tables were the largest, as that should be a good hint. A web search found lots of possible solutions, but I liked this one the best. Within that thread, I slightly modified one of the queries to do what I wanted:
I’ve been updating my A useless analysis of macOS (OS X) release dates post for nearly 13 years now (OMG). Over the years, the one thing that’s bugged me was that I couldn’t find a good way to have fixed column headings on that post’s incredibly long scrolling table.
Hooray for fixed headers! Read on if you’d like to use this trick yourself…
I actually intended to start posting again a few days ago, but I was running into a couple of issues with the site: It was incredibly slow (annoying, but survivable), and the editor box was missing when I added a new post (which made posting quite tricky). I tried the usual troubleshooting steps—made sure everything was updated, disabled plug-ins, even briefly changed the site’s theme to see if it was a theme issue. No luck with anything.
Then I enabled WP_DEBUG on the site, which is something you should never do on a live site, as it will fill every page with tons of mostly meaningless warning and error messages. But in my case, I had to see what was happening when I tried to load the new post page. What I saw was troubling…
WordPress database error: [INSERT command denied to user '#######'@'###.###.###.###' for table 'wp_options']
INSERT INTO wp_options (option_name, option_value, autoload) VALUES ('_transient_timeout_jetpack_sync_constants_await', '1502403204', 'no') ON DUPLICATE KEY UPDATE option_name = VALUES(option_name), option_value = VALUES(option_value), autoload = VALUES(autoload)
WordPress database error: [INSERT command denied to user '#######'@'###.###.###.###' for table 'wp_options']
INSERT INTO wp_options (option_name, option_value, autoload) VALUES ('_transient_jetpack_sync_constants_await', '1502399604.7325', 'no') ON DUPLICATE KEY UPDATE option_name = VALUES(option_name), option_value = VALUES(option_value), autoload = VALUES(autoload)
This went on and on, with screenfuls of such errors. Uh oh. Web searches found lots of possible causes with fixes, but none that worked for me. So I logged into 1and1 to check on the database…and I didn’t have to get any further than the summary screen to see the problem, as revealed in the image at right: The site’s database was using 150MB of the 100MB allocated to it—whoops!
100MB is not a lot of space, and it’s not what I get when I create a new database on 1and1—the limit is now 1GB. So why so puny? Basically because I’ve had robservatory.com since 2005, and never ever updated the database! I have no idea how long it’s been over the limit, but apparently it was finally over the limit enough that no more data could be written to the database.
The problem is that 1and1 can’t just bump the size of the database; you have to create a new one and migrate your data over to it. Thankfully, that’s not overly hard (read the rest if you’d like to know how I did it; documented for my own future sanity). After I moved to the new database, my edit window was back (hooray!), and as an added benefit, the site got much faster: The database is now hosted on an SSD, and the site’s not throwing 5,000 errors per second any more.
I recently needed a gradient background for a page I was making. My usual method of creating gradient backgrounds is to muck around in my image editor until I find some combination I like, then futz around in my text editor getting the syntax just right for CSS gradients across all the browsers.
But then I discovered the Ultimate CSS Gradient Generator from ColorZilla. This handy tool lets you create gradients directly in the browser, and it outputs all the required codes for full browser support. The UI is very much like any image editor’s gradient tool:
Drag the slider thumbs, click them to change the colors, click along the gradient to add color stops, etc. This tools works like a charm, and saves me a bit of time and aggravation whenever I need to make a CSS gradient.
I wanted to embed an animated GIF in my post about changing the iOS Settings screen. However, because the GIF was about 4MB in size, I didn’t want it to auto-load—and in general, I find auto-playing GIFs annoying. I wanted something that would stop and start on click, like this (wonderfully subtle) example GIF…
So I did what any WordPress user would do in such a situation: I went looking for a WordPress plug-in that offered control over GIFs.
I initially found WP GIF Player and GIF Animation Preview. Both did what I wanted, mostly, but they added a bunch of their own HTML and CSS, and/or relied on the WordPress media library (which I don’t use). After testing both, I just couldn’t get them to work with the GIF and the size/position that I wanted to use. Perhaps there are others that would work, but I got frustrated and gave up searching.
My blog is, and has been, hosted with 1and1 for many years. While I’ve had some minor issues during that time, I’ve been generally happy with their hosting. Recently I noticed that they now offer a free SSL certificate. The free certificate only covers one domain and no sub-domains, but that’s all I needed for robservatory. I must say that 1and1 has made this incredibly easy: Enabling the certificate only took a couple mouse clicks, and it was active a minute or two later.
Getting the site actually secured, though, was and is a different story. You should see the secure site indicator on the front page, at least, indicating that you’ve made a secure connection to the site…
The “secure site” indicator as seen in Chrome…Safari uses an ugly gray lock.
Once you navigate back into the older posts, however, you’ll probably lose the lock indicator. The connection is still secure, but the older posts have hardcoded image paths that start with http://, so they load non-securely.
I’m using that as an excuse to go through my old posts and update broken links, etc. This takes a while, but it’s a good thing to do every so often. And note to self, never hardcode the full URL—I have no idea why I did that!
And yes, I could just do a mass replace in the database, but the audit has already helped me fix a couple handfuls of posts with errors.
I was looking for an easy way to make a development copy of the Many Tricks blog, which (like Robservatory) is powered by WordPress. In the past, I’ve done this manually, but it’s a bit of a pain to get the required edits done correctly and make everything work at the new URL.
So this time, I went searching for a plug-in, and found Duplicator. Borrowing from the plug-in’s description of itself…
Duplicator gives WordPress administrators the ability to migrate, copy or clone a site from one location to another. The plugin also serves as a simple backup utility. Duplicator supports both serialized and base64 serialized string replacement. If you need to move WordPress or backup WordPress this plugin can help simplify the process.
Once installed, usage is pretty easy: You follow a simple three-step process that creates a new package. Move that package to the new location, expand it, and then run the installer.php file. (I had to rename the three files from the package to remove everything except the filenames; the plug-in adds a bunch of identifying text in front of each filename.)
The installer asks questions about the new site’s URLs and database connection info, then does its magic. I had a clone site up and running in minutes, saving what (for me) is usually an hour or so’s aggravation. Duplicator should work equally well for moving a WordPress installation to a new host, too, though I haven’t tested it in that situation (yet).
I recently tweaked the look here a bit, greatly simplifying the fonts and lightening the visual weight of the site quite a bit.
As part of that process, I wanted to find a larger lighter yet highly legible font. So I went back to Font Squirrel, the same site I used in my 2014 redesign.
They offer a huge assortment of fonts, all licensed for free commercial use, with a nice set of categories and search engine. And free…though the tradeoff is a fairly heavy advertising load. After much looking and testing, I’ve got the site running on three font families: Open Sans for most of the content and sidebar, Open Sans Condensed for headlines, and Ubuntu Mono for code snippets.
As part of the cleanup, I was able to remove 40+ font-family and font-size statements from the CSS, and the site should scale a bit better on small-screen devices. (I’m still not completely happy with things, so expect minor changes going forward.)
Font Squirrel not only has a great collection of fonts, but they offer a free web font generator. Using the generator, you can create fonts that are embedded in your page, so that they’re available even when users don’t have those fonts installed locally. Just upload a font you’re licensed to use, and Font Squirrel will create a web font, complete with CSS. Upload the converted web fonts to your server, copy and paste the CSS bit into your CSS master file, and you can use the fonts on your site.
There are 20 web fonts on the site now (two forms of 10 font faces across the three font families), and in total, they’re 200KB in size—or less than the typical “larger” image I often post here.
There are lots of sites that offer free-to-use fonts; I really like the assortment at Font Squirrel, and the web generator is an added bonus.
By default, though, the embedded tweets will be left-aligned. I wanted them center aligned, as above. And because I just wasted 15 minutes figuring this out, I’m documenting the solution here to save myself future aggravations…
One of the things that annoys me about WordPress’ admin side is that to get to draft versions of posts, you have to first select Posts > All Posts, wait for that page to load, then select Drafts. The majority of the time, when I’m headed to my posts, I’m headed to the drafts section to work on an upcoming post.
This little modification adds a Drafts entry to the Posts sidebar item, as seen in this before-and-after view:
The change is relatively trivial, requiring only a simple edit to your theme’s functions.php file. David Walsh explains it all in this thorough post. I’ve recreated the bit of code in the remainder of this post, just in case the linked site ever goes away. (It’s all under the MIT License, so there are no restrictions on copying.)
But really, just go read David’s post, he explains it very well. I’ve added this to the admin page on the three sites I run, because it’s just so convenient.