Beware InnoDB's auto_increment reset on reboot

Earlier this year I helped my friend Samuel bring his used cell phone resell business online using Drupal Commerce. The site is still in maintenance mode while we finish the self-service features, but his staff currently uses it logged in from their various locations as their point of sale system. Knowing the ins and outs of Commerce, I didn't have any problems tailor making an eCommerce application for his business, but I did have one hiccup during deployment that I'd never seen before.

We built Wikiwoo.com on Pantheon, a Drupal Platform as a Service, using a free developer site until it was ready for use in stores. Pantheon really helped us collaborate on the site build, with me doing the coding and configuring while he filled the product catalog. We did everything on the site's dev environment, including letting his partners take a look around to find things worth fixing, until we were ready to go live.

One of the last things I did to prepare for the launch was update the auto_increment value of the commerce_order table to account for the number of orders they processed in the previous year and a half. However, we weren't really migrating old eCommerce data, so I just expected the first order on the new site to start where we wanted and we'd watch them grow from there. A quick test showed it working as expected, so I deleted the dummy orders and sent him a link to upgrade the account to a paid plan to take it live.

Unfortunately, when I went back to the site the next day, I saw that orders were being created with IDs starting back at 1. I knew there was nothing in Commerce that would effect such a change, so I hit up Pantheon support and got a quick confirmation that nothing they do would intentionally reset auto_increment values either.

Sidebar: I really should emphasize quick. Any time I've ever contacted Pantheon support, they've responded right away. "Groovy," said Josh Koenig in this particular instance when we nailed down what was happening. "Groovy," I say to Pantheon's customer service. Cool

It turns out what I experienced was a result of InnoDB's treatment of auto_increment values. The auto_increment counter is only stored in memory, not on disk, and it is recalculated on server startup. InnoDB simply looks for the highest used ID and sets the counter to the next value, explaining why my order IDs shrunk back down to 1 after I cleared out all of our dummy orders.

In our case, it was the upgrade from a free account to a paid account that restarted the database server, triggering the reset of the counter. However, with cloud based Platforms as a Service, I imagine there are other scenarios where an expected alteration to an auto_increment value is apparently "lost" on migration between environments or builds. This is probably mostly an eCommerce issue with respect to Drupal sites, as merchants often want or need order IDs to account for historical sales, but perhaps the tip can save someone else a bit of head scratching.

To get around my issue, I simply reset the auto_increment to where I wanted it to be, created a cart order for myself, and waited for a real order to be created before deleting my dummy order.

Problem solved, it's been fun to watch the order count grow from there.

Photo credit: Max Barnes

Beyond Wombats

I accidentally started publishing open source software in 2006, the first integration of the QuickBooks Web Connector with anything. This was pre-Ubercart when I was just cutting my teeth on PHP / MySQL development at Prima Supply, and I thought it would be fun to claim the code was written by wombats while I just published it online. I decided to own the silliness and start blogging on bywombats.com using Drupal 4.7 at the time - and immediately picked up a freelance contract doing QuickBooks integration work. Tongue

Fast forward a few years, a couple of eCommerce projects, and a Drupal startup, and the moniker has apparently run its course. A lot of folks over the years assumed "By Wombats" was the name of a Drupal shop and tried to contact me for work. Little did they know my site's contact form hasn't been working for years. Shock

This past week I finally got around to fixing that by moving from Rackspace to Digital Ocean and configuring my server to send mail through Mandrill. I upgraded to Drupal 7 (about time?) and played around with Mike Crittenden's Meedjum theme until I liked the look. He's presently trying to bribe me to help maintain the theme, but I'm holding out for better beer.

Migrating was the fun part, as it was my first opportunity to make use of the Drupal-to-Drupal data migration module. I couldn't get moving files to work for some reason, but I figured out how to make options settings work in the configuration form (leave the select lists alone and put the value in the "Default value" text field) to make use of the preserve_files option. I copied the old files to the Drupal 7 files directory and ran the migration with that flag set for it to start managing the files that were already in the directory.

Migrating taxonomy terms was straightforward, but it took me a while to get blog posts right. I learned I had to select the taxonomy term migration in the "Source migration" select list to ensure blog posts got created in the Drupal 7 site referencing the correct term ID (since they changed during migration). I had to do the same for files, choosing the "Upload [upload]" source field and selecting the file migration I used as the source migration. I also had to set the optional MigrateFile class to "MigrateFileFid" to ensure the new blog's file field referenced the appropriate file IDs.

Perhaps the best thing about Migrate is its ability to rollback migrations and re-import them to debug the process. I did this several times for the various migrations. I also made use of its ability to import a representative subset of the awaiting content while I was debugging the term / file association process. Clever module - Mike Ryan rocks.

The future for this blog is a consolidation of various other things I'm doing online, including blogging about life in Greenville, SC (hint: it rocks) and Roguelikes / indie games (again with the Wordpress?). Once that's done, I'll carry on with the blogging about Drupal, startups, and a few other things, including my recent explorations with R and Commerce Guys's Platform.

I hope to hear from you soon. Smile

Creating a custom Add to Cart form with Drupal Commerce

I'm currently helping some friends rebuild the theological education website, BiblicalTraining.org, in Drupal 7 with Drupal Commerce. I built the Drupal 6 version some years ago to move the website from a bespoke PHP application into Drupal, using modules like Ubercart, Quiz, and Organic Groups to solve most of the requirements. However, the donation code was more or less a straight Drupal port of the PHP script handling donations via PayTrace at the time.

With the rebuild onto Drupal 7, we have the opportunity to unify the donation form with the course payment checkout form to begin using Commerce Reports and multiple payment methods, including the newly released Commerce PayPal 2.0 for Express Checkout payments. However, we still have to deal with actually creating an order to represent the donation payment on the checkout form.

On this site, we don't use product display nodes. I decided instead to directly instantiate the Add to Cart form at a custom URL and avoid the need to create a product display node type just for the one form.

I started by defining a donation product type so the site administrators could create a product to represent the various campaigns donors could give toward. Since I am building the form in a small page callback function, I can easily support as many donation products as get created without introducing the human process of making sure administrators both add the product and update a product display node to reference it.

I created a single menu item at /donate whose page callback is the following:

<?php
/**
 * Page callback: builds a donation Add to Cart form.
 */
function bt_donation_form_page() {
 
// Create the donation line item defaulted to the General fund.
 
$line_item = commerce_product_line_item_new(commerce_product_load(5), 1, 0, array('context' => array('display_path' => 'donate')), 'donation');
 
$wrapper = entity_metadata_wrapper('commerce_line_item', $line_item);

 
// Set the line item context to reference all of the donation products.
 
$query = new EntityFieldQuery();
 
$query
   
->entityCondition('entity_type', 'commerce_product')
    ->
entityCondition('bundle', 'donation')
    ->
propertyCondition('status', TRUE);

 
$result = $query->execute();

  if (!empty(
$result['commerce_product'])) {
   
$line_item->data['context']['product_ids'] = array_keys($result['commerce_product']);
  }

 
// Do not allow the Add to Cart form to combine line items.
 
$line_item->data['context']['add_to_cart_combine'] = FALSE;

  return
drupal_get_form('commerce_cart_add_to_cart_form', $line_item);
}
?>

To build an Add to Cart form, you have to pass in a line item object that contains all the information required to build the form. Our page callback starts by building a donation product line item, a custom line item type that allows for custom donation amounts as demonstrated in this tutorial video from Randy Fay. The product the line item references, "General fund", will be the default selection on the Add to Cart form, and the context array I pass to the line item creation function populates the line item's display_path field to link this line item to the donation page.

Next I use a simple EntityFieldQuery to find all the enabled donation products on the site. These product IDs are added to the line item's build context array, which the Add to Cart form builder function uses to know which products the form should represent. In a product display scenario, these product IDs would come from the value of the product reference field. Here I pass them in directly and get a simple Add to Cart form that is now ready to be themed to perfection:


Cameo: Select or Other powers the "Other" option here.

Additional improvements on the site involve changing the "Add to Cart" button to read "Donate Now" and using a custom message upon submission with a redirect to /checkout. In case the donor cancels checkout and ends up at /cart, I do two things to ensure they can't manipulate the quantity of the donation line items: I removed the quantity textfield field from the View, but in case we need to add it back in later for other line item types, I also use a form alter to convert any donation line item quantity textfield to the plain text quantity value:

<?php
/**
 * Implements hook_form_FORM_ID_alter().
 */
function bt_donation_form_views_form_commerce_cart_form_default_alter(&$form, &$form_state) {
 
// Loop over the quantity textfields on the form.
 
foreach (element_children($form['edit_quantity']) as $key) {
   
$line_item_id = $form['edit_quantity'][$key]['#line_item_id'];
   
$line_item = commerce_line_item_load($line_item_id);
   
   
// If it's for a donation line item...
   
if ($line_item->type == 'donation') { 
     
// Turn it into a simple text representation of the quantity.
     
$form['edit_quantity'][$key]['#type'] = 'value';
     
$form['edit_quantity'][$key]['#suffix'] = check_plain($form['edit_quantity'][$key]['#default_value']);
    }
  }
}
?>

This might actually make a handy contrib module...

If the donor leaves a donation line item in the cart and goes back to the donation form, you'll notice toward the end of my page callback that I also indicate in the context array that the form should not attempt to combine like items during the Add to Cart submission process. I actually realized the form builder function was missing some documentation for context keys, so I added those in straightaway.

All told, I spent a couple hours building a custom donation form and workflow that now perfectly integrates with the checkout process used by the rest of the site. This will make it easier to customize and maintain long term, and it allows us to use existing Drupal Commerce payment method modules to manage donations instead of having to write and maintain a custom payment module for the task.

Bypassing Drupal Commerce customer profile duplication

In Drupal Commerce, we deal in entities, fields, and field-based relationships between entities (i.e. references). The extent to which we implemented our data model on these systems from the earliest days of Drupal 7 is what grants Drupal Commerce developers a level of flexibility previously unavailable to eCommerce developers in general. Cool, right? Wink

Unfortunately, the Drupal entity trade can be dangerous when dealing with historical data that isn't supposed to change - when a reference should do more than merely "refer" but also express constraints.

Such is the case with customer profile references on orders.

Once customer information (e.g. a billing or shipping address) is entered for an order, we don't want the order to lose that information at a later date. This means we have to prevent not just the deletion of the referenced customer profiles but also changes to them - the latter because our references are to entitiies, not revisions of entities.

Side note: honestly, I'm fine with that - I can't imagine a reasonable UI or update strategy for entity revision references. It's hard enough to keep straight as is. Tongue

The most common place users encounter customer profile duplication is when they go to update a customer profile through the UI. If any field values are changed, the Order module will prevent the update if it detects the customer profile is referenced by a non-shopping cart order. It empties the profile IDs in a presave hook, forcing the save to create a new customer profile instead of updating the existing one.

This same process affects not just customer profiles being edited through the UI but also customer profiles being updated through code. However, if you combine the fact that customer profiles have revisions with the absence of the duplication related messages you see when performing the operation in the UI, it's easy to see how this functionality might appear to developers as a case of revisions gone awry.

There are a couple of functions developers can refer to to read comments describing the process and see the implementation itself:

  • commerce_order_commerce_customer_profile_presave()
  • commerce_order_commerce_customer_profile_can_delete()

It's worth noting that we've coded this functionality to be paranoid - if there's a slight chance something substantive may have changed in the field data, we force duplication instead of updating the original. Better to duplicate than to lose vital data.

Still, the keen observer will note that we actually do permit customer profiles to be updated through the UI on one condition. If an administrator edits a customer profile through the edit form of the sole order referencing the profile, we permit the update.

You may have a need in custom code to perform an update to a customer profile field that you know does not affect the historical record in a substantive way. Maybe it's simply a matter of changing some internal field that has no bearing on the fulfillment of orders. In such cases, to bypass customer profile duplication, you can imitate the process the order edit form uses to identify an order as safe to delete.

To do this, add a temporary "entity_context" property to the customer profile object. This is the property the Order module looks for in the presave hook to determine if the customer profile is being edited in the context of its sole referencing order. If you properly identify this order in the entity_context, the Order module will permit the update to occur without duplication:

<?php
$profile
= commerce_customer_profile_load(1);
$wrapper = entity_metadata_wrapper('commerce_customer_profile', $profile);
$wrapper->field_referral_source = 'MySpace';
$profile->entity_context = array(
 
'entity_type' => 'commerce_order',
 
'entity_id' => 1,
);
commerce_customer_profile_save($profile);
?>

Obviously, this is obtuse.

In Commerce 2.x, we'll do well to improve the developer experience here. It gets even worse if you want to make such an update on a site using Commerce Addressbook where you do want to update a customer profile referenced by multiple orders without enacting duplication. In fact, until I wrote this post, there would have been no way to achieve this short of a miraculous hook_query_alter().

This whole post came out of a quick e-mail exchange with Forest Mars, whom I'm looking forward to meeting at next week's Florida DrupalCamp (you going?). Since I've had a chance to think about the issue in a longer form post, I'm going to go ahead and add a query tag so it's at least possible to target the "can delete" query without committing developer sins.

And that is how e-mail and blogging improve open source projects, my friends! Cool

Pages