Support

June 21, 2016 at 3:44 am

Thanks for the information Josh. We’ve updated the plugin and the site is still going very slow, and we’ve had a look at the database, and noticed one of the tables has gotten exceedingly large.

Table name: wp_esp_line_item
Rows: ~633,257
Size: 104.7 MiB

In my local environment this never went above 10k lines and 2MB in size. I’ve noticed on some other threads you’ve recommended this bot plugin, do you still recommend this, and are you able to provide and SQL commands that will help prune this table back down to a reasonable size?

Many thanks

June 21, 2016 at 9:06 am

I’ll had to add, we don’t have any “live” events currently, and we haven’t hosted an event in a while. We only do a few a year. We’ve tracked the spam back from the point of origin which not long after the updates went live.

This means we don’t have any live data from registrants, it’s all spam.

June 22, 2016 at 2:18 am

Which updates? How long ago was this?

We done an update from EE3 to EE4 which went live last week, very early Thursday morning around 2AM. When I was looking through the database download on my local machine the failed transactions started happening just a bit after it went live.

The cleanup function will only remove transactions that are over 1 week old, we can provide a filter to make this shorter if your spam registrations are fairly new?

This may be why it hasn’t started cleaning up the failed transactions in the database then as it’s not been over a week yet.

Added to your themes functions.php file will change the cleanup to remove transactions over 1 day old.

I’ll get this added to my local version with a fresh install of the database from production and see how this works, if this does the trick I’ll use this instead of manipulating the database directly.

The SQL snippets provided were run locally and seemed to drastically clean up the database records to a point they were almost identical to my local development version of the site.

June 22, 2016 at 2:36 am

I’ve found where about’s you’re calling that filter in the plugin, do we need to remove the transactions that are TFL first as the line items are dependant on them?

June 22, 2016 at 3:38 am

I’ve added the filter into my local copy of the codebase, ran the cron job manually with WP Crontrol and waited for it to tick over itself manually.

The tables in the database didn’t reduce in size or remove any records.
I updated this function within the plugin itself to see if it would make any difference instead of relying on a filter:

public function delete_line_items_with_no_transaction(){
		/** @type WPDB $wpdb */
		global $wpdb;
		$time_to_leave_alone = apply_filters(
			'FHEE__EEM_Line_Item__delete_line_items_with_no_transaction__time_to_leave_alone', DAY_IN_SECONDS
		);
		$query = $wpdb->prepare(
				'DELETE li
				FROM ' . $this->table() . ' li
				LEFT JOIN ' . EEM_Transaction::instance()->table(). ' t ON li.TXN_ID = t.TXN_ID
				WHERE t.TXN_ID IS NULL AND li.LIN_timestamp < %s',
				// use GMT time because that's what TXN_timestamps are in
				gmdate(  'Y-m-d H:i:s', time() - $time_to_leave_alone )
				);
		return $wpdb->query( $query );
	}

Which didn’t make any difference to the database either. (Plugin as been reverted back to normal with no changes in place.)

I’ve just noticed another small development in regards to the transactions table. to give some insight, we disabled EE4 yesterday morning around 10AM. I’ve just looked at the very end of the database table wp_esp_transaction and the extra transactions stopped until we re-enabled the plugin. but once we re-enabled it. it wasn’t as consistent as it was before we disabled it. Two images below for you to look at

Before disabling

After disabling but in maintenance mode.

Not sure if this helps help diagnose the issue a bit more. We have one live event but it’s not publicised and the booking page isn’t public either so no one can sign up. Any idea what might be causing the new transactions to appear in the database?

Apologise for the difficulty of this one, and we appreciate all the help so far. Hopefully we’ll get to the bottom of this soon.

Many thanks

June 22, 2016 at 6:43 am

Just checked the production database and the transaction table has the primary keys set etc. so that’s a non-issue now.

June 22, 2016 at 7:00 am

We’re going to investigate that this issue is potentially being caused by the caching system in place on the site.
W3TC is the plugin being use currently as a reference. Thank you for the help so far. We’ll report back with anything we find when we find it.

June 22, 2016 at 7:08 am

Looking at that page now, I’ve setup the excludes on the cache system, including the ticket selector pages.

Thank you for the help, will confirm if this is the issue soon.

June 24, 2016 at 2:18 am

We’re going to look over our logs now, will report any strange behaviour.
Can we remove your credentials to the server now?

June 27, 2016 at 5:28 am

Another thing we have noticed.

If you land on the ticket selection page and instantly click submit, this creates a new entry (Ajax submission), then click submit again, it creates a new entry with a qty of 1… I think the book now form is what is currently being spammed and in turn causing the mass of TFL entries.

I just manually went to the page, clicked register now 10 times before the JS had time to set the QTY to default option of 1 and I was able to get it to create 10 new entries by doing this.

June 27, 2016 at 1:18 pm

Are your team currently on the site?

June 27, 2016 at 1:45 pm

An update… There is a piece of JS in there to remove the 0 qty option as the ticket minimum wasn’t setting the QTY to 1 on page load. So we removed the 0 option once the page was ready; unfortunately, this allows time for a submission to happen creating the first entry, then clicking again once set, creates another with QTY of 1.

Due to the nature of the site, there is no simple way of fire-walling x amount of traffic which are unknown, but the database gets inundated with data and this is what we are trying to resolve at the moment. Have you found anything else at this stage as all we can find is that the ticket selector seems to get spammed?

Below is the simple method that injects the JS for the ticket selector and can be found in “themes/Site/inc/event_espresso.php”.

function ee_default_ticket_selector_one_all_events() {
  ?>
  <script type="text/javascript">
    jQuery(document).ready(function () {
      jQuery('[id^=ticket-selector-tbl-qty-slct-]').find("option[value='0']").remove();
    });
  </script>
  <?php
}
add_action( 'wp_footer', 'ee_default_ticket_selector_one_all_events' );

As you can see this merely removes the 0 qty option so that the field defaults to 1.

We really need to get to the bottom of this as these were due to go-live yesterday.

Is there anything else you can suggest at this point?

June 28, 2016 at 10:33 am

Hi Josh, here is a response from the hosting company.

—
We don’t run ConfigServer, but we do run fail2ban monitoring failed logins and doing some checks for bots and banning them if found or logins fail.

With regards to something like they’re suggesting, its very unlikely to help the situation (although I’ve not looked through the logs myself) most bots these days do not send multiple concurrent requests from the same IP address but cycle through thousands of these addresses, if one was to be banned they would switch to another. I would need to check on the compatibility between CSF and Plesk, but can investigate this if you want.

Whilst I do understand where they’re coming from I’m not convinced adding something like this will actually stop the problem from recurring.
—

Thank you.

June 29, 2016 at 5:02 am

Hi Josh,

How would you like these sent over?

Thanks.

June 30, 2016 at 2:42 am

Email sent with a link to a dropbox folder.

Thanks.

June 30, 2016 at 1:38 pm

Josh, we have banned this ip in WordPress (not on a server level).

We have disabled the cron on the live instance and not as part of our version control for the moment. Is there anything else to do next? Or should we now try taking EE out of maintenance mode?

With this disabled there are regular tasks that are not going to run going forward, so I assume this is a temporary solution?

Many thanks.

July 4, 2016 at 9:31 am

Hey guys, just wanted to check whether there was any update on this at the moment?