In the last four months there was a lot of discussion about some decisions made by the developers of the famous shopping cart plugin WP e-Commerce. I think that discussion would not have exist if there were no users affected by the decisions that originated it. It is to be noted that version 3.8.14, the most recent release of WP e-Commerce, is supposed to definitely fix the issue, but I’m sure there will be a lot of people not putting the plugins on their sites up to date, and there can appear some possible issues with backward compatibility that need to be solved. That’s what this article is meant to.

You can read the first page of the thread if your interested in the technical implications, but basically, since version 3.8.13, WP e-Commerce saves the session data of anonymous customers into the wp_users and wp_usermeta tables, instead of doing it into transients, as was formerly done and other shopping cart solutions, such as WooCommerce, still do. This means that every time a non-registered visitor enters your site a new user will be created and some meta data for that user will be stored. So, if your site gets a lot of visitors, your database will be populated with lots of dummy users in a considerably short time. There is a scheduled task meant to remove this users hourly, but it doesn’t work quite well in some server configurations, so there will be sites where the tables won’t ever be automatically cleaned, with databases growing uncontrollably larger and larger in just a matter of days. That’s not something that any capable developer or site owner wants for his website.

So let me stop here for a minute. I’m not in the train of detractors of this practice. I don’t think it is ideal, but I do think there is a really good reason to do things this way and not another. I’m not that into discussing things that were already done as I am for trying to fix what’s wrong with them. There has been a lot of hate comments, ranting and whining in the thread I linked at the beginning, and I’m pretty sure that’s not the way to solve things, and don’t have me start on the kind of motivation that a programmer finds to get something fixed when he or his product is being attacked. So please, if you need some software to be fixed, be nice and friendly to your programmer. Otherwise, either he won’t do it well or will just walk away from you.

Now let’s get into the problem. This is not exactly a tutorial, so if you’re looking for a copy & paste kind of solution, you’re not gonna find it here. If you have this problem, the solution will depend on your specific server configuration, so you may need to do some modifications to the examples of code I’m gonna provide here.

What’s Exactly The Problem?

First of all, take a look at the _wpsc_clear_customer_meta function, located in wpsc-includes/cron.php (at least by version 3.8.13).

That’s the function that will take charge of deleting your dummy users. It gets executed once an hour through the WP-Cron API. For most production environments it will work just fine, but there will be cases when a lot of users will be generated in a short time (let’s say a website with a lot of visits but a very cheap hosting, or under a DDOS/DOS attack, or a limited PHP/MySQL configuration), and the site may return an error due to a timeout in MySQL while trying to execute the function.

Now take a look at the line that reads wp_delete_user( $id );. The problem lies there. It gets triggered because the function tries to delete one user at a time, instead of doing a bulk removal. You can fix this behavior by creating a new MU plugin with the following code:

That example unregisters the execution of _wpsc_clear_customer_meta() and replaces it with fix_wpsc_clear_customer_meta(), which stores the IDs of the dummy users in a string that will be passed to a single MySQL query, instead of doing one query for every user. This saves a lot of resources for MySQL, since it only performs the search for users once in a larger query, instead of lots of searches with short queries that demand more time and memory usage, sometimes causing timeouts and crashes.

However, that may not solve your issue completely. If you take a look at the MySQL query that performs the initial search for users to be removed, you will see the UNIX_TIMESTAMP() MySQL function. This function, which is supposed to return the current time in UNIX format, doesn’t work in all MySQL configurations, since some servers don’t have it enabled by default, and you need to find a replacement. The PHP strtotime() function is supposed to return the same time format, so you could use that by modifying the query like this:

$sql = "
    SELECT user_id
    FROM {$wpdb->usermeta}
    WHERE
    meta_key = "_wpsc_last_active";
    AND meta_value < " . strtotime( "now" ) . " - " . WPSC_CUSTOMER_DATA_EXPIRATION . "
    LIMIT {$purge_count}
";

If that, for any reason that be, doesn’t work, you still have the chance to remove the time criteria from the MySQL query. However, that will remove the session data for all your anonymous visitors, even the ones that could be browsing the site while the task gets executed, so you have to consider that before modifying the query like this:

$sql = "
    SELECT user_id
    FROM {$wpdb->usermeta}
    WHERE
    meta_key = "_wpsc_last_active";
    LIMIT {$purge_count}
";

Little tip: Take a look at the WPSC_CUSTOMER_DATA_EXPIRATION constant. That defines the minimum amount of lifetime (in seconds) that a dummy user has before being removed. By default it’s set to 48 hours, two entire days (see definition here), but you can change it to anything you want by defining it in your wp-config.php file like this:

define( "WPSC_CUSTOMER_DATA_EXPIRATION", 60 * 30 ); // Set lifetime to 30 minutes.

The Approach That’s Working in 3.8.14

As for the new version, 3.8.14, user profiles are not used anymore to store session data for anonymous customers (you can take a look at all the new changes here), but you still needed to remove those dummy users from your tables if you are upgrading from 3.8.13. Also, you may not want to upgrade to 3.8.14 for any reason that be, and that’s OK if it your call, but you’ll surely want your database clean from dummy users, and there exists an alternative option that might be more effective. So, in 3.8.14, the function that performs the removal has change to this one (see in context here):

This little snippet searches and removes the dummy users in a more efficient way, doing it through AJAX by running in the background of an already displayed page. If you already upgraded to 3.8.14, you’re done here, but if you’re still on 3.8.13, you need to put the former piece of code in a MU plugin, but replacing this line:

add_action( "wpsc_migrate_anonymous_user_cron", "_wpsc_meta_migrate_anonymous_user_cron"; );

With this code:

// Modify action hook for WP e-Commerce automated task.
function fix_reset_wpsc_cron() {
    remove_action( "wpsc_hourly_cron_task", "_wpsc_clear_customer_meta"; );
    add_action( "wpsc_hourly_cron_task", "_wpsc_meta_migrate_anonymous_user_cron"; );
}

// Do reset.
add_action( "wpsc_init", "fix_reset_wpsc_cron"; );

Of course, with that initial query you still have the caveat of all session data being removed, so you may need to modify the query to something similar to one of the former examples.

If all fails, you can still go berserk and execute the cleaning functions by hooking fix_wpsc_clear_customer_meta() or _wpsc_meta_migrate_anonymous_user_cron() to the plugins_loaded action instead of the cron task, depending on the approach you use. I do not really recommend doing this, but I know it can be useful in desperate situations.

That should be all. Once you have applied one of these methods, you should have your WP e-Commerce plugin up and running without major issues.