Error establishing connection to database – WordPress solution

solutions for wordpress database errors

A crashed database is a problem I’ve encountered across multiple WordPress websites. When trying to load the site you’re faced with a dreaded “Error establishing a database connection” message. Restarting the DB service usually clears things up. But, sometimes it won’t restart at all – which is why I started automating nightly data dumps to an S3 bucket.

Recently, one particular site kept going down unusually often. I assumed it was happening due to low computing resources on the EC2 t3.micro instance. I decide to spin up a a new box with more RAM (t3.small) and migrate the entire WordPress setup.

Since I couldn’t be sure of what was causing the issue, I needed a way to monitor the health of my WordPress websites. I decided to write code that would periodically ping the site, and if it is down send an email alert and attempt to restart the database.

warning message when a website can't connect to the database

The first challenge was determining the status of the database. Even if it crashed, my site would still return a 200 OK response. I figured I could use cURL to get the homepage content, and then strip out any HTML tags to check the text output. If the text did match the error message, I could take further action.

Next, I needed to programmatically restart MySql. This is the command I run to do it manually: sudo service mariadb restart 

After doing some research, I found that I could use shell_exec() to run it from my PHP code. Unfortunately, Apache wouldn’t let the (non-password using) web server user execute that without special authorization. I moved that command to its own restart-db.sh file, and allowed my code to run it by adding this to the visudo file: apache ALL=NOPASSWD: /var/www/html/restart-db.sh

I also needed to make the file executable by adjusting permissions: sudo chmod +x /var/www/html/restart-db.sh

Once those pieces were configured, my code would work:

<?php

$url = "http://www.antpace.com/blog";
$curl_connection = curl_init();

curl_setopt($curl_connection, CURLOPT_URL, $url);

curl_setopt($curl_connection, CURLOPT_RETURNTRANSFER, true);
$curl_response = curl_exec($curl_connection);
$plain_text = strip_tags($curl_response);

if(strpos($plain_text, "Error establishing a database connection") !== false){
	echo "The DB is down.";
        
        //restart the database
        shell_exec('sudo /var/www/html/restart-db.sh');
        
        //send notification email
        import 'send-email.php';
        send_email();
}else{
	echo "The DB is healthy.";
}

?>

You can read more about how to send a notification email in another post that I wrote on this blog.

Create the cron job

A cron job is a scheduled task in Linux that runs at set times. For my PHP code to effectively monitor the health of the database, it needs to run often. I decided to execute it every five minutes. Below are three shell commands to create a cron job.

The first creates the cron file for the root user:

sudo touch /var/spool/cron/root

The next appends my cron command to that file:

echo "*/5 * * * * sudo wget -q 127.0.0.1/check-db-health.php" | sudo tee -a /var/spool/cron/root

And, the last sets the cron software to listen for that file:

sudo crontab /var/spool/cron/root

Alternatively, you can create, edit, and set the cron file directly by running sudo crontab -e . The contents of the cron file can be confirmed by running sudo crontab -l .

 

Migrate a WordPress site to AWS

WordPress migration

In a previous article I discussed launching a website on AWS. The project was framed as transferring a static site from another hosting provider. This post will extend that to migrating a dynamic WordPress site with existing content.

Install WordPress

After following the steps to launch your website to a new AWS EC2 instance, you’ll be able to connect via sFTP. I use FileZilla as my client. You’ll need the hostname (public DNS), username (ec2-user in this example), and key file for access. The latest version of WordPress can be downloaded from wordpress.org. Once connected to the server, I copy those files to the root web directory for my setup: /var/www/html

Make sure the wp-config.php file has the correct details (username, password) for your database. You should use the same database name from the previous hosting environment.

Data backup and import

It is crucial to be sure we don’t lose any data. I make a MySql dump of the current database and copy the entire wp-content folder to my local machine. I’m careful to not delete or cancel the old server until I am sure the new one is working identically.

After configuring my EC2 instance, I install phpMyAdmin so that I can easily import the sql file.

sudo yum install php-mbstring -y
sudo systemctl restart httpd
sudo systemctl restart php-fpm
cd /var/www/html
wget https://www.phpmyadmin.net/downloads/phpMyAdmin-latest-all-languages.tar.gz
mkdir phpMyAdmin && tar -xvzf phpMyAdmin-latest-all-languages.tar.gz -C phpMyAdmin --strip-components 1
rm phpMyAdmin-latest-all-languages.tar.gz
sudo systemctl start mariadb

The above Linux commands installs the database management software on the root directory of the new web server. It is accessible from a browser via yourdomainname.com/phpMyAdmin. This tool is used to upload the data to the new environment.

phpMyAdmin import screen

Create the database and make sure the name matches what’s in wp-config.php from the last step. Now you’ll be able to upload your .sql file.

Next, I take the wp-content folder that I stored on my computer, and copy it over to the new remote. At this point, the site homepage will load correctly. You might notice other pages won’t resolve, and will produce a 404 “not found” response. That error has to do with certain Apache settings, and can be fixed by tweaking some options.

Server settings

With my setup, I encountered the above issue with page permalinks . WordPress relies on the .htaccess file to route pages/posts with their correct URL slugs. By default, this Apache setup does not allow its settings to be overridden by .htaccess directives. To fix this issue, the httpd.conf file needs to be edited. Mine was located in this directory: /etc/httpd/conf

You’ll need to find (or create) a section that corresponds to the default document root: <Directory “/var/www/html”></Directory>. In that block, they’ll be a AllowOverride command that is set to “None”. That needs to be changed to “All” for our configuration file to work.

apache config settings found in the HTTPD conf file

Final steps

After all the data and content has been transferred, do some smoke-testing. Try out as many pages and features as you can to make sure the new site is working as it should. Make sure you keep a back-up of everything some place secure (I use an S3 bucket). Once satisfied, you can switch your domain’s A records to point at the new box. Since the old and new servers will appear identical, I add a console.log(“new server”) to the header file. That allows me tell when the DNS update has finally resolved. Afterwards, I can safely cancel/decommission the old web hosting package.

Don’t forget to make sure SSL is setup!

Update

AWS offers an entire suite of services to help businesses migrate. AWS Application Migration Service is a great choice to “simplify and expedite your migration while reducing costs”.

WordPress homepage cards with lazy load images

wordpress homepage design

I write online a lot. Adding articles to this blog serves to build a catalog of technical solutions for future reference. I also publish to the SplitWit blog, writing about A/B testing. Updating the homepages of these sites has improved user experience and SEO for each. The new design displays the most recent articles as clickable cards, rather than listing the entire text of each one. The changes for this were added to index.php file, in the child-theme folder. The theme’s original code already used a While() loop to iterate through the post records. My modification removed the article content, and only kept the title and image:

<div class="doc-item-wrap">
	<?php
	while ( have_posts() ) {
		the_post();
		echo "<div class='doc-item'><a href='". get_the_permalink() ."'><img class='lazy' data-src='".get_the_post_thumbnail_url()."'><h2>" . get_the_title() . "</h2></a></div>";
	} ?>
</div> <!-- doc-item-wrap -->

I used custom CSS, leveraging Flexbox, to style and position the cards:

.doc-item-wrap{
    display: flex;
    flex-wrap: wrap;
    justify-content: center;
}
.doc-item{
    width: 30%;
    padding: 20px;
    border: 3px solid #f0503a;
    margin: 15px;
    background: black;
    flex-grow: 1;
    text-align: center;
}
.doc-item:hover{
    background-color: #34495e;
}
.doc-item p{
    margin: 0px;
    line-height: 40px;
    color: white;
}
.doc-item img{
    display: block;
    margin: 0 auto;
}
.doc-item h2{
    font-size: 22px;
    color: white;

}
@media(max-width: 1000px){
	.doc-item{
		width: 45%
	}
}
@media(max-width: 700px){
	.doc-item{
		width: 100%
	}
}

The media queries adjust the size of the cards (and how many are in a row), based on screen size.

Lazy loaded images

Image content can often be the biggest drag to site speed. Lazy loading media defers rendering until it is needed. Since this blog’s homepage has an image for each post, this was essential.

While iterating through post records the image URL is assigned to a custom data-src attribute on the image tag, leaving the normal src blank. This assures the image is not immediately retrieved nor loaded. I wrote a JavaScript function to lazy load the images, relying on the IntersectionObserver API. The card’s image does not load until a user scrolls it into view. This improves the speed of the page, which has a positive effect on SEO and UX.

The code creates a IntersectionObserver object.  It observes each of the image elements, checking to see if they are within the browser viewport. Once the image elements come into view, it takes the image URL from the data-src attribute, and assigns it to the tag’s src – causing the image to load.

document.addEventListener("DOMContentLoaded", function() {
  var lazyImages = [].slice.call(document.querySelectorAll("img.lazy"));
  if ("IntersectionObserver" in window) {
    let lazyImageObserver = new IntersectionObserver(function(entries, observer) {
      entries.forEach(function(entry) {
        if (entry.isIntersecting) {
          let lazyImage = entry.target;
          lazyImage.src = lazyImage.dataset.src;
          // lazyImage.srcset = lazyImage.dataset.srcset;
          lazyImage.classList.remove("lazy");
          lazyImageObserver.unobserve(lazyImage);
        }
      });
    });

    lazyImages.forEach(function(lazyImage) {
      lazyImageObserver.observe(lazyImage);
    });
  } 
});

 

Original JS code referenced from this article: Lazy-loading images 

You can also use this same method for lazy loading videos and backgrounds.

Look and feel of the design

Card layout design is the best way to arrange blog content. It gives visitors a visual overview of what’s available. It also stops the homepage from duplicating content that’s already available on the individual post pages.

You can see this pattern throughout the digital world. Card layout translates well across screen sizes and devices. Since I put much effort into writing, making it organized was a priority. This implementation can be extended to add additional content (such as date, description, etc.) and features (share links, animations, expandability). And, it fits nicely with what WordPress already provides.

How I built my career in tech as a programmer

Anthony Pace's resume and portfolio

Building a fulfilling career can seem daunting. Technology and programming is a great option in today’s world. Resources and opportunities are abundant. You can work from anywhere and help build the future. When I started out, I faced challenges, doubt, and struggle. The ride has been worth it, and I’m excited to keep moving forward.

Starting out

About half way through college, I decided to dropout. I was majoring in Philosophy at a small school in New York.  My main source of income was delivering pizza in the Bronx.

A decade earlier, I found computer programming. I spent my nights coding desktop applications, learning HTML, and exploring the web. Those early days of technology laid the foundation for what would be my career.

When I left school in 2007, I wasn’t sure what to do next. I started earning money in tech that same year by starting a business. It focused on creating blogs and producing content. Ads and affiliate programs served to generate revenue.

It wasn’t as lucrative as I hoped. The real value came from the web development skills I honed. The software and technologies I used then, I still rely on today.

WordPress, Linux, and PHP. Writing, SEO, and digital marketing. These were the bricks I used to form the ground floor of my career in tech.

Service worker

While my early stint at entrepreneurship didn’t make me wealthy, it proved valuable. I managed to grow a freelance business leveraging this experience.

Networking and word-of-mouth were my primary means of growth. After printing business cards, I would give them to everyone I met. While delivering pizzas, I would hand them out to any small businesses or shops I passed.

I found my first paying customer in 2008. Since then, my client list has grown to triple digits.

The services I’ve offered range beyond web development. I’ve designed logos and written copy. I’ve managed infrastructure: web hosting, domain names, email, and more.

I have designed and managed both print and digital marketing campaigns. I’ve given strategy advice to young startups. Truly full stack: business, technology, and design. This has been a theme that has rung true my entire career.

The lessons learned during this period were ones of hard-work and getting the job done. The most valuable skills translate across industries. Finding clients fuels the engine of any business. The art of pitching and selling is a career-long study. Being able to manage business needs has proven to be foundational.

Office life

By 2011 I landed my first in-house gig, working at a marketing company. It felt like a turning point. I was the only developer, and got to deal directly with clients. I worked there for less than a year.

In 2012 I connected with a recruiter for the first time. They set me up on many interviews. I clicked with a small medical education company based in Manhattan. Hired as a web developer, I graduated to senior engineer and marketing specialist.

Team work

There, I was the head of all things digital. That meant building websites, coding native apps, and managing infrastructure. After a promotion to head of marketing my responsibilities expanded. Managing analytics took time. Copywriting promotional materials required patience. My horizons expanded while coordinating live events, and traveling internationally to exhibition shows.

Educational grants funded our projects. They included apps, websites, live events, and digital newsletters. Having a coordinated team was imperative to making things work. The project management and leadership was world-class and invaluable.

A single project was multifarious. I would design responsive layouts, build registration websites, deploy apps, and more. Once a product would launch, I would travel to live events to handle promotion and logistics. While I fulfilled many roles, I was lucky to work with a talented group.

Software Engineer

After four years, I made the difficult decision to leave the job that helped shape my career. A better opportunity presented itself in 2016. I was hired as a software engineer. This is when I came into my own as a programmer. I was able to collaborate with a brilliant team. The technologies I became familiar with continued to grow.

I got to work with early-stage startups and brands backed by venture capital. I learned the intricacies of building digital products and growing direct-to-consumer brands. My colleagues included entrepreneurs, CEOs, and product experts. The office was exciting and full of talent.

At the time of writing this (2020), we are stuck in quarantine due to the COVID-19 pandemic. We’re working remotely, but continuing to grow. Uncertain times prompt us to evaluate our circumstances and take inventory of what we value. What is the future of my career? How does it play into my life overall?

What’s next?

I love what I do for a living. I enjoy programming; I love problem solving; I’m an artist at heart. I plan on continuing to build software products. Chances are, I’ll be doing it somewhere other than New York City – especially since remote work seems to be the future of business.

If you’re thinking about starting a career in technology as a programmer, my advice is to jump right in. Start building, keep learning, and put yourself out there. If anyone reading this wants to chat about careers, technology, programming, or anything else, feel free to email me!

Create a WordPress plugin – How to

Distributing software to app and plugin markets is a great way to gain organic traffic. Last year I submitted BJJ Tracker to the Google Play store as a Progressive Web App. Since then, I get signups every few days – with zero marketing effort.

I created a WordPress plugin for SplitWit, to grow its reach in a similar way. SplitWit helps run A/B experiments on the web. A JavaScript snippet needs to  be added to your code for it to work. This plugin injects the code snippet automatically.

Here is the process I took to develop and submit it to the WordPress plugin directory.

Plugin code

Since this is such a simple plugin, all I needed was one PHP file, and a readme.txt file. “At its simplest, a WordPress plugin is a PHP file with a WordPress plugin header comment.

The header comment defines meta-data:

/*

Plugin Name: SplitWit
Plugin URI: https://www.splitwit.com/
Description: This plugin automatically adds the SplitWit code snippet to your WordPress site. SplitWit lets you create a variation of your web page using our visual editor. It splits traffic between the original version and your variation. You can track key metrics, and let SplitWit determine a winner. No code needed. Go to SplitWit to register for free.
Author: SplitWit
Version: 1.0
License: GPLv2+
License URI: http://www.gnu.org/licenses/gpl-2.0.txt

*/

My PHP code defines six functions, uses four action hooks, and one filter hook.

The first function injects the SplitWit snippet code into the WordPress website’s header:

function splitwit_header_code(){
	//inject SplitWit code snippet
	 
	$splitwit_project_id = get_option('splitwit_project_id');
	 
	if($splitwit_project_id){
		wp_enqueue_script( 'splitwit-snippet', 'https://www.splitwit.com/snippet/'.$splitwit_project_id.'.js' );
	}
}
add_action( 'wp_head', 'splitwit_header_code', 1 );

Another defines the WordPress plugin’s menu page:

function splitwit_plugin_menu_page() { ?>

	<div>
		<h1>SplitWit Experiments</h1>
		<p>This plugin automatically adds the <a href="https://www.splitwit.com" target="_blank">SplitWit</a> code snippet to your WordPress site. <a href="https://www.splitwit.com" target="_blank">SplitWit</a> lets you create a variation of your web page using our visual editor. It splits traffic between the original version and your variation. You can track key metrics, and let SplitWit determine a winner. No code needed.
		</p>
		<p>You'll need to create an account at SplitWit.com - it's free. After signing up, you can create a new SplitWit project for your website. Find that project's ID code, and copy/paste it into this page.</p>
		
		<form method="post" action="options.php">
			<?php settings_fields( 'splitwit_settings_group' ); ?>
			<input style="width: 340px;display: block; margin-bottom: 10px;" type="text" name="splitwit_project_id" value="<?php echo get_option('splitwit_project_id'); ?>" />
			<input type="submit" class="button-primary" value="Save" />
		</form>
	</div>

<?php }

I add that menu page to the dashboard:

function splitwit_plugin_menu() {
	add_options_page('SplitWit Experiments', 'SplitWit Experiments', 'publish_posts', 'splitwit_settings', 'splitwit_plugin_menu_page');
}
add_action( 'admin_menu', 'splitwit_plugin_menu' );

And link to it in the Settings section of the dashboard:

function splitwit_link( $links ) {
     $links[] ='<a href="' . admin_url( 'options-general.php?page=splitwit_settings' ) .'">Settings</a>';
    return $links;
}
add_filter('plugin_action_links_'.plugin_basename(__FILE__), 'splitwit_link');

When the SplitWit code snippet is injected into the website’s header, it needs to reference a project ID. I register that value from the menu page:

function splitwit_settings(){
	register_setting('splitwit_settings_group','splitwit_project_id','string');
}
add_action( 'admin_init', 'splitwit_settings' );

If the project ID value has not been defined, I show a warning message at the top of the dashboard:

function splitwit_warning(){
  if (!is_admin()){
     return;
  }

  $splitwit_project_id = get_option("splitwit_project_id");
  if (!$splitwit_project_id || $splitwit_project_id < 1){
    echo "<div class='notice notice-error'><p><strong>SplitWit is missing a project ID code.</strong> You need to enter <a href='options-general.php?page=splitwit_settings'>a SplitWit project ID code</a> for the plugin to work.</p></div>";
  }
}
add_action( 'admin_notices','splitwit_warning');

The readme.txt defines additional meta-data. Each section corresponds to parts of the WordPress plugin directory page. The header section is required, and includes some basic fields that are parsed to the plugin page UI.

=== SplitWit ===
Contributors: SplitWit
Plugin Name: SplitWit
Plugin URI: https://www.splitwit.com
Tags: split test, split testing, ab testing, conversions
Requires at least: 2.8
Tested up to: 5.3.2
Stable tag: 1.0

Optimize your website for maximum convertibility. This plugin lets you use SplitWit to run experiments on your WordPress website.

I also added sections for a long description and installation instructions. Later, I included a screenshots section (see Subversion repo).

Submit for review

Plugin zip files can be uploaded to WordPress.org.  Plugins can also be distributed to WordPress users without this step – but having it listed in the WordPress directory lends credibility and visibility. After my initial submission, I received an email indicating issues with my code and requesting changes. The changes were simple: “use wp_enqueue commands” and “document use of an external service”.

Originally, my “splitwit_header_code()” function include the SplitWit JS snippet directly as plain text. I changed it to use the built-in function “wp_enqueue_script()”.

//wrong:
echo '<script type="text/javascript" async src="https://www.splitwit.com/snippet/'.$splitwit_project_id.'.js"> </script>';

//correct:
wp_enqueue_script( 'splitwit-snippet', 'https://www.splitwit.com/snippet/'.$splitwit_project_id.'.js' );

Next, they wanted me to disclose the use of SplitWit, the service that powers the plugin. I added this to my readme.txt:

This plugin relies on SplitWit, a third-party service. The SplitWit service adds code to your website to run A/B experiments. It also collects data about how users interact with your site, based on the metrics you configure.

After making these changes, I replied back with an updated .zip. A few days later I received approval. But, that wasn’t the end  – I still needed to upload my code to a WordPress.org hosted SVN repository.

Subversion Repo

I’ve used Git for versioning my entire career. I had heard of SVN, but never used it. What a great opportunity to learn!

The approval email provided me with a SVN URL. On my local machine, I created a new folder, “svn-wp-splitwit”. From a terminal, I navigated to this directory and checked out the pre-built repo:

svn co https://plugins.svn.wordpress.org/splitwit

I added my plugin files (readme.txt and splitwit.php) to the “trunk” folder. This is where the most up-to-date, ready-to-distribute, version of code belongs.

In the “tags” folder, I created a new directory called “1.0” and put a copy of my files there too – for the sake of version control. This step is completely optional and is how SVN handles revisions.

In the assets folder I included my banner, icon, and screenshot files. The filenames follow as prescribed by WordPress.org. I made sure to reference the screenshot files in my readme.txt file, under a new “Screenshots” section.

Finally, I pushed my code back up to the remote:

 svn ci -m "Initial commit of my plugin."

You can now find my plugin in the WordPress.org plugin directory. SplitWit is available for a free trial. Give it a try, and let me know what you think.

 


 

Pro-tip: Some WordPress setups won’t let you to install plugins from the dashboard with out providing FTP credentials, including a password. If you use a key file, instead of a password, this is a roadblock.

Install WordPress plugin roadblock
Not everyone uses a password to connect to their server.

You can remedy this by defining the file system connection method in your functions.php file:

define( 'FS_METHOD', 'direct' );

 

Writing, engineering, and creativity

writing resources

It was 2006 and I had just installed WordPress on a web server. I would draft blog posts nightly, before getting ready for bed. At the time I was a philosophy major and wrote prose more than code. That was my first venture into web development and digital marketing. It started with writing.

Writing blog posts and publishing software have a lot in common. For both, “perfect” is the opposite of ready. It’s easy to keep editing your own work. It’s even easier to keep adding half-done features and clutter. That’s why having a plan before you start helps so much. When I write, my first draft tends to be bullet points and a vague outline. The same goes for software. If I’m building something complex, I write comments explaining its functionality before any code. It’s my way of “thinking out loud”, and making sure that what I plan on doing even makes sense.

It’s been over a decade since I’ve maintained a blog. Creative tasks require hard work, lest they bear no fruit. (“Writer’s block is for amateurs”). Problem solving, in its many shapes, is the highest form of creativity. It’s how we build our reality. Modern technology gives us creative leverage through tools, knowledge, and community. We’re being given opportunities to build and create things, to grow and be better, at an unprecedented scale. It’s the best time in history to be CEO of your own life; creative director of your destiny. This also sets the bar higher to stand out.

My plan here is to write regularly, and discuss what I’ve been working on and learning, as well as what’s next. This gives me a chance to explore my thoughts, and prune the branches from which they stem. Hopefully, working at this will help to make me a better storyteller too.