spam users in wp_users after wpsc upgrade

When your testing you might want to add a error_log( memory_get_usage( true ) ); into the loop. You might find that that every user you delete consumes a big chunk of memory. When the value logged approaches your memory limit the cleanup process stops abruptly.

The upcoming 3.8.14 point release seems to completely resolve this issue. It might be a better use of your time to alpha test that release and report any defects you find?

@pye, tx for the info. At the moment i am looking for a quick fix as the live website is getting slow. Some every users is “read” by the theme. Do you know the mysql query which i can use directly in phpmyadmin to delete the user and corresponding meta data???

hmm i added
error_log( memory_get_usage( true ),3, "php.log");

and i get this value back

4325376043253760

i don’t get it. I made a copy of my live wp_users and wp_usermeta locally and i have this in wp-config
define( ‘WPSC_CUSTOMER_DATA_EXPIRATION’, 3 * 3600 );

I change the query a bit with now():

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

From the 4000+ user i still have 395 user which should have been deleted ( last activity < 3 hours ago). Should i delete all the rows manual in phpmyadmin?

Are you using any caching on your site? If so a direct query would not be a good idea.

This is the function that is being tested in the new WPSC version’s upgrade routine:

function _wpsc_meta_migrate_anonymous_user_worker() {

		global $wpdb;

		$blog_prefix = is_multisite() ? $wpdb->get_blog_prefix() : '';
		$key_pattern = "{$blog_prefix}_wpsc_";

		wp_suspend_cache_addition( true );

		$sql = 'SELECT ID FROM '. $wpdb->users . ' WHERE user_login LIKE "\_%" AND user_email = "" AND user_login = user_nicename AND user_login = display_name LIMIT 100';
		$user_ids = $wpdb->get_col( $sql, 0 );

		foreach ( $user_ids as $user_id ) {

			$wpdb->query( 'INSERT INTO ' . $wpdb->wpsc_visitors . '(<code>id</code>) VALUES ( ' . $user_id . ' )' );

			wpsc_set_visitor_expiration( $user_id,  DAY_IN_SECONDS );

			$meta = get_user_meta( $user_id );
			foreach ( $meta as $key => $value ) {

				if ( strpos( $key, $key_pattern ) === FALSE )
					continue;

				$short_key = str_replace( $key_pattern, '', $key );
				if ( $short_key !== 'cart' ) {
					wpsc_add_visitor_meta( $user_id , $short_key, $value[0] );
				} else {
					$wpsc_user_cart = maybe_unserialize( base64_decode( $value[0] ) );

					if ( ! ($wpsc_user_cart instanceof wpsc_cart) ) {
						$wpsc_user_cart = new wpsc_cart();
					}

					wpsc_update_visitor_cart( $user_id , $wpsc_user_cart );
				}
			}

			$comment_count = $wpdb->get_var( 'SELECT COUNT(comment_ID) FROM ' . $wpdb->comments. ' WHERE user_id = ' . $user_id );
			if ( ! count_user_posts( $user_id ) && ! $comment_count ) {
				//wp_delete_user( $user_id );
				$wpdb->query( 'DELETE FROM ' . $wpdb->users . ' WHERE ID = ' . $user_id  );
				$wpdb->query( 'DELETE FROM ' . $wpdb->usermeta . ' WHERE user_id = ' . $user_id  );
			}
		}

		wp_suspend_cache_addition( false );
		exit( 0 );

	}

@pye, no caching at the moment.

I tried _wpsc_meta_migrate_anonymous_user_worker() function but calling it causes a blank page/blank WP (i am testing locally) I use WPSC 3.8.13.3.

Does DAY_IN_SECONDS exists in 3.8.13.3??

DAY_IN_SECONDS is a wordpress constant.

Are you calling it directly or using AJAX? Notice the exit at the bottom of the function

hmm, i called _wpsc_meta_migrate_anonymous_user_worker() directly in functions.php overlooked the exit but //exit(0); still gives me a blank page.

Calling the other function fix_wpsc_clear_customer_meta() reduces the queries but still not really as i expected. I might need to use this hacky solution as the website is sloooowing down ??

btw1: can the users and their meta data savely be deleted? it’s just for the temp cart products, if the user doesn’t make the purchase immediatley, right?

btw2: why does WordPress need to query the wp_users table?
i notice this simple code even queries all users?

define('WP_USE_THEMES', false);
require('../site/wp-load.php');
echo get_num_queries(); ?> queries in <?php timer_stop(1);

returns 8617 queries in 5,492

This slows down my website so i need the wp_users table to be reduced (in control).

If you don’t have any caching and you are not concerned about losing any session data you probably can directly delete the users and the meta created by WPEC without too many side effects. Without knowing everything in your setup I can’t say for sure.

@ddt: I have that same function running right now.

count_users() returns an array, that’s why I used count_users()['total_users'], which returns an integer, to update the user_count option. But, in any case, that piece of code just updates the count of users for your site, it does nothing to do with removing users, so it should not be the cause of the function not working.

I see you’re getting some users removed using NOW() instead of UNIX_TIMESTAMP. You may also want to try UNIX_TIMESTAMP(NOW()), and see if that works well. Perhaps you’re testing locally in a Windows system? UNIX_TIMESTAMP() may not work well there.

You can also try to run the function outside of the cron scope, so you can test it in a more comfortable way, by calling it with add_action( 'init', 'fix_wpsc_clear_customer_meta' ); and adding die(); at the end of the function.

Do you have WP_DEBUG set to true and your PHP configuration set to show errors?

Note that fix_wpsc_clear_customer_meta() is pretty much the same as _wpsc_clear_customer_meta(), but avoiding single removals in favor of a bulk one. The other function you’re testing, _wpsc_meta_migrate_anonymous_user_worker(), is doing single removals too, and though is using a little less resources than _wpsc_clear_customer_meta, it still performs two queries for each user that it needs to remove, and that can mean a lot of load to your server when it is running the function if you have many anonymous users. You may need to apply there a bulk removal by selecting the users to be removed with WHERE ID IN ($list_of_users) after the foreach. Check my fix_wpsc_clear_customer_meta() to see hoy I solved that part.

Ahh I use caching could this be causing my problem (about 10 posts up page)

Or is it better to just wait for latest release ?

Earlier WPEC contributors seem to of gone very quiet

if you can wait for a little longer I am expecting that there will be a call for beta testers of the point release. When I see the beta ready I can post back to this thread.

you`re a good man, cheers for the support

@andreas, really appreciate your explanation i think i understand. I have put debug logging on and this is the error message which i don’t understand

[20-Feb-2014 19:59:54] WordPress database error ..... query DELETE FROM wp_users WHERE ID IN () gemaakt door require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), include('/themes/mydomain/functions.php'), include('/themes/mydomain/custom/custom_functions.php'), fix_wpsc_clear_customer_meta
[20-Feb-2014 19:59:54] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 bij query DELETE FROM wp_usermeta WHERE user_id IN () .... require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), include('/themes/mydomain/functions.php'), include('/themes/mydomain/custom/custom_functions.php'), fix_wpsc_clear_customer_meta

so somehow my live production website doesn’t seem to get the

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

What can i do to test/fix this?

ps2: i am also trying to see what i get back with
SELECT user_id FROM wp_usermeta WHERE meta_key = '_wpsc_last_active' AND meta_value < UNIX_TIMESTAMP(now()) - 172800 LIMIT 20
[EDIT] i notice with meta_value < now() i get something back and i used it but still not sure if i use it correctly. i also use 24 vs 48 define( 'WPSC_CUSTOMER_DATA_EXPIRATION', 24 * 3600 );

btw: you where right about me using a windows locally just xampp

Well, it looks that UNIX_TIMESTAMP not working correctly in your local server might be part of the problem, and that’s why the query returns an empty set.

Is your production server a Windows system too? If it’s not, that query should work there.

I think you could try replacing UNIX_TIMESTAMP(NOW()) with PHP’s strtotime( 'now' ), 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}
	";

However, I didn’t try that on a XAMPP setup, so I’m not quite sure that will work.

@andres, thx man really appreciate it. Gonna check tomorrow with the hosting company what kind of server this website runs on. So
" . strtotime( 'now' ) . is preferrable over now()

FYI: on my live website i tried the select query with UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()) and NOW() directly with phpmyadmin and only NOW() gave some results

add_action( ‘init’, ‘fix_wpsc_clear_customer_meta’ ); and adding die(); at the end of the function

you mean at the end of function fix_wpsc_clear_customer_meta, right?

Yes, I meant exactly that ??