|
Jonathan Free
|
June 11, 2015 at 4:10 pm
We’re experiencing the exact same issue as the support topic I’ve duplicated below. wp_esp_line_item is growing at an alarming rate. Sadly, no solution was publicly posted. Is there a solution?
https://eventespresso.com/topic/database-crash-from-event-espresso-table/
Hello- My website was crashing until I deactivated event espresso. here is what hours of my host and developer found:
I am very sorry to hear that you continue to experience problems with your WordPress website at http://www.thecosmicpath.com. I’ve been looking into this to see what we can do to help.
What I have found is that the table ‘wp_esp_line_item’ in the database ‘thecosn5_cosmicpath_wp_live’ contains 915,802 rows. This is way, way more than there should be, and is likely what is causing all of these issues you’ve been encountering on your site.
I was able to find the cause of the problem, which is Event Espresso, and pinpoint it to an exact table. This should be enough information for the developers of that plugin to get the problem fixed for you. It is a paid product, so they should be able to figure out why their code is creating a table so large, and get it fixed for you.
Thanks,
Gary S.
Technical Support
Hostnine
FROM MY DEVELOPER:
It might be related to expresso trying to ‘upgrade’ or ‘update’ the database.
[13-Apr-2015 04:53:28 UTC] WordPress database error Incorrect key file for table ‘./thecosn5_cosmicpath_wp_live/wp_options.MYI’; try to repair it for query INSERT INTO wp_options (option_name, option_value, autoload) VALUES (‘espresso_db_update’, ‘a:0:{}’, ‘no’) ON DUPLICATE KEY UPDATE option_name = VALUES(option_name), option_value = VALUES(option_value), autoload = VALUES(autoload) made by require(‘wp-blog-header.php’), require_once(‘wp-load.php’), require_once(‘wp-config.php’), require_once(‘wp-settings.php’), do_action(‘plugins_loaded’), call_user_func_array, EE_System->detect_activations_or_upgrades, EE_System->detect_if_activation_or_upgrade, EE_System->fix_espresso_db_upgrade_option, add_option
I have events coming up this weekend and need this tended to imediately. I have purchased a support token to expedite the matter.
Kim, webmaster for thecosmicpath.com
|
|
Lorenzo Orlando Caum
|
June 11, 2015 at 5:35 pm
Hi Jonathan, here is that information:
1) Login to the cpanel / hosting panel or directly to PHPMyAdmin
2) Towards the top, there is a database section. Click on PHPMyAdmin:
http://cl.ly/image/1F3k01322a0Z
3) This opens a new browser tab and you can click on the database for your site.
4) Once you are viewing the live site, you’ll see a SQL tab along the top. Click on that and you’ll see a text box.
Copy and paste this entry and then run it:
delete t from wp_esp_transaction t LEFT JOIN wp_esp_registration r ON t.TXN_ID=r.TXN_ID WHERE r.REG_ID IS NULL
Allow it to complete.
5) Then click on the SQL tab again and you’ll see the text box again.
delete li from wp_esp_line_item li LEFT JOIN wp_esp_transaction t ON t.TXN_ID=li.TXN_ID WHERE t.TXN_ID IS NULL
That should clear the entries.
Thanks!
—
Lorenzo
|
|
Jonathan Free
|
June 15, 2015 at 1:12 pm
Thank you.
Those queries were helpful for removing the excess records. Is there a way to prevent the records from being created in the first place? Our line items table is accumulating around 250k records per day. Is a cronjob the ‘fix’?
|
|
Josh
|
June 15, 2015 at 2:22 pm
Hi Jonathan,
You might try installing a few plugins like Bad Block Queries and Bad Behavior, the ticket selector on your site appears to be getting hit by some spam bots with that many records.
|
|
Michael Nelson
|
June 15, 2015 at 3:26 pm
ya jonathan a cron task isn’t the best solution ever, but it will help until we get the ideal solution in place (which is to prevent these spam registrations in the first place).
The plan right now is that the cron will delete failed transactions which are over a week old, but we will have a filter so you can change that. You’ll probably need to set it to delete failed (spam) transactions after an hour or so
|
|
Jonathan Free
|
June 15, 2015 at 4:27 pm
For the record, when we first encountered this issue, we added an index to the LIN_parent field on wp_esp_line_item. This ‘saved’ the database/database for the time being.
With an index, the queries on these hundreds of thousand records completed reasonable quickly. Having an index on the foreign key field is a good practice, and should probably be folded into the core regardless of this bug.
|
|
Josh
|
June 15, 2015 at 5:58 pm
Hi Jonathan,
This idea has merit and will be discussed. Thanks for the feedback.
|
|
Josh
|
June 19, 2015 at 10:04 am
Hi Jonathan,
A little update on a few things:
1) Event Espresso 4.7.2 adds an automatic cron for deleting spam line items.
2) We’re working on adding indexing for an upcoming release.
|
|
Lorenzo Orlando Caum
|
September 12, 2015 at 4:00 pm
Hello again, I wanted to share some updated information on this issue as it could be helpful to members that have not yet upgraded to a current version of Event Espresso.
In Event Espresso 4.7.x we introduced a clean up feature that runs on its own to remove these junk transactions. In Event Espresso 4.8.x, we introduced a bot trap which should minimize registration attempts from bots.
If you are using an older version of Event Espresso (e.g. Event Espresso 4.6.x) than one mentioned above, then you won’t have the combined protection of the features above. The recommended solution is to manually remove the junk transactions as described here:
https://eventespresso.com/topic/slow-data-migration-to-4-7-0/#post-172280
…and then backup your WordPress site upgrade to the current version of Event Espresso:
https://eventespresso.com/wiki/ee4-changelog/
Thanks
—
Lorenzo
|
