VaultPress Is Awesome, But Not Realtime For Plugins Like WooCommerce

vaultpress

VaultPress Is Awesome

A few months ago, I was researching WordPress backup services for a client of mine. VaultPress came up in many searches. I really liked the fact that they offered real-time backup; not daily, not hourly, but real-time. Their system is notified of any change that occurs in the database and is instantly backed up.

Pretty bomb if you ask me. Even cooler is that you can drill down to a specific hours backup and restore an entire site, a specific file or folder or even a specific database table. Talk about robust and feature rich with great usability.

So fast forward to this week. My clients wanted to test and document what it would take to do a restore from a catastrophic server failure. Another forward thinking feature of VaultPress is the ability to restore to an alternate site. This proved to be invaluable to test out their service. I simply created a test restore site on my clients VPS and well, as you might have guessed, restored the site.

The restore went very smooth… until I verified the data.

The Issue

My clients site is a WooCommerce site with tens of thousands of orders and users. And the first thing I checked was that all the orders (and other mission critical data) were safe and sound. And yes all orders aside from that days orders were fine. Roughly ten orders had no order items. I had the orders but no information about what those users purchased. I compared with the production site and sure enough those orders had order items.

Red flags were going off. What’s going on here?

I had to verify these findings. So I ran the restore two more times and each time the most recent orders had no order items. I needed to get to the bottom of this.

After some backup and forth with VaultPress’ support staff the issue lies in how often they back up non-WordPress core tables, such as custom plugin tables. Simply put, orders are backed up in real-time, but the actual orders items are only backed up once a day. From the horses mouth:

“Please note, though, that plugin tables are only indexed (and backed up) once per day, so you might have to wait 24 hours to see the order items appear in them.”

To get geeky and for those interested, WooCommerce stores master order records in wp_posts, while order items are stored in wp_woocommerce_order_items. Since the later table is backed up only once a day, using VaultPress you’re guaranteed to have data loss.

To reiterate this isn’t limited to WooCommerce. Any plugin using custom tables will suffer from this issue.

High Horse (Sorry)

The nature of this issue is bothersome as it’s an incomplete picture of your database. It’s not only data loss (in the event of a catastrophic failure), but it’s a mix match of data from different moments in time. So my question is: can VaultPress really claim real-time? If you’re talking about WordPress core tables, then yes it’s real-time. But it’s really not because custom plugin tables are so prevalent. I suggested VaultPress to my clients in good faith only to find we were at risk the whole time.

Recourse

Every situation is different. If you’re not using any plugins with custom tables, then you don’t have to worry. If you are and a full backup of your site every 24 hours is sufficient then keep on chugging. Otherwise it might be good to research another solution or service. It’s just that VaultPress is such a well built service that I don’t want to go anywhere else. At least they are aware of the issue and are working to solve it:

“However, as you mentioned, the restore inconsistencies arised because the WooCommerce plugin tables are only indexed daily. Our developers are working directly with WooCommerce (also a part of Automattic) to bring live sync to the WooCommerce tables. There is no definitive timeline related to this yet, but it is in the works!”

End transmission.

Posted in: Development, News  |  Tagged with: , , , ,  |  Leave a comment

SQL Script To Get All WooCommerce Orders Including Metadata

A client of mine has a rather large WooCommerce database. We’ve been trying to run reports using the WooCommerce Customer/Order CSV Export plugin but have been running into timeout issues—the glorious WordPress white screen of death.

I got fed up and wrote the following script to export the needed information. Customize as you see fit. Enjoy.

select 
	p.ID as order_id,
	p.post_date,
	max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
	max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
	max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
	max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
	max( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
	max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
	max( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
	max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
	max( CASE WHEN pm.meta_key = '_shipping_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
	max( CASE WHEN pm.meta_key = '_shipping_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
	max( CASE WHEN pm.meta_key = '_shipping_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
	max( CASE WHEN pm.meta_key = '_shipping_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
	max( CASE WHEN pm.meta_key = '_shipping_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
	max( CASE WHEN pm.meta_key = '_shipping_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
	max( CASE WHEN pm.meta_key = '_shipping_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
	max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
	max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
	max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date
from
	wp_posts as p,
	wp_postmeta as pm
where
	post_type = 'shop_order' and 
	p.ID = pm.post_id and
	post_date BETWEEN '2015-01-01' AND '2015-07-08' 
	and post_status = 'wc-completed'
group by
	p.ID
Posted in: Code Samples, Development  |  Tagged with: , , ,  |  Leave a comment

Well Don’t You Just Have Some Nice Manners (Music)

Nice Manners Music Logo

Ok here’s the deal, kids. There’s this pretty rad recording studio based out of LA with the most badass of names: they’re called Nice Manners. Not only do they have a badass name they also do badass work. And not only that, they’re run by one of the most badass people (Joanna Katcher) I’ve had the pleasure to work with over phone and electronic mail. I’m not downplaying the working friendship we’ve formed, in fact, I’m saying just the opposite. It speaks mountains to me that we’ve never met in person and have formed such a solid rapport.

Most of the projects I land are via word of mouth. And this project was no different. The power of word of mouth cuts through all the bullshit; there is an innate sense of trust already in place. I’m not a very good sales man so this strategy of letting whatever comes my way works well for me. And everyday I get to work with amazing people, who have amazing visions and together we make amazing stuff. Blah blah blah… insert more emotional hype.

Meet Nice Manners Music

Joanna came to me with a solid vision of a genre-based music search engine and player. Her design was simple, clean and elegant. This project was perfect for me.

The site is built on WordPress using Underscores, which is a very barebones starter theme. To support the main features of song playback and autocomplete genre and song title search we carefully chose, jPlayer and select2, respectively. Joanna actually suggested select2, which gives you a lot more power over multi-select, autocomplete dropdown boxes. jPlayer is a ridiculously easy to use HTML5 audio and video player and is highly customizable. Feast your eyes on these screen grabs.

Results, Player, Playing

Genre selection, search results and playback.

Autocomplete in action.

Autocomplete in action.

And of course fully responsive.

And of course fully responsive.

The Demo

I’m pretty excited to share this project. Part of Joanna’s vision was to allow access to only her clients, so with her permission I’ve created a demo site to showcase our work, right over here.

As a hobby I do a fair amount of nature field recordings so for your listening pleasure I have uploaded some of my favorites to populate the demo. Enjoi.

Posted in: Portfolio  |  Tagged with:  |  Leave a comment

ProtonMail Turning One, Doubles Storage to 1GB

ProtonMail: End-to-end Encrypted Email

Me yes me; I am a ProtonMail fan. They provide end-to-end encrypted email and are based in Switzerland. I know there are those that feel privacy is dead or quickly default to the “I have nothing to hide” excuse, but I am a firm believer in privacy as an inalienable right. Ya know, that whole search and seizure passage in the constitution protecting us from unlawful intrusion. Yeah, it’s important. And I don’t stand up nearly enough, which is a whole other story.

That’s why any person or company that dares to innovate and challenge head-on the mass surveillance ideologies that are covering the globe I am in full support of. This isn’t earth shattering, but it’s a continued step in the right direction. ProtonMail has turned one and they are doubling storage of early adopters to 1GB. Read more about it, right over here.

The announcement for which I’m really waiting is hosting my own domain at ProtonMail. This is due later this year, which I heard directly from the horse’s mouth.

Posted in: News, Technology  |  Tagged with: ,  |  Leave a comment

Most recent work

  • Nice Manners Music Nice Manners is an LA based recording studio offering their clients all sorts of digital audio services. This is a demo of an internal genre based music search & player I built for them. I am ecstatic about launching this project—check it out.
  • Valhalla DSP A one man Seattle based company, writing pretty sick professional audio plugins. Check ’em out!

Need hosting?

  • Media Temple My go to hosting provider. You get rockstar hosting and I make a little cash. No pressure, just clean honest fun.

Past work

Blogroll