Best way to clean duplicates and database?

[sagemg]

We had a calendar with 15,000 to 20,000 events that shot up to 60,000 events when duplicates were created by the plugin…

• What is the best way to remove the duplicates – when we try selecting more than 60, the system hangs deleting? When we do an ‘Edit All’ occurrences it takes overnight and hangs – we have to resume in the morning.
• After clean-up, what is the recommended way to clean-up and/or optimize our database? Plugin we should use?
• Once we get all the dups deleted, what is the best/proven method for adding new recurring events that don’t create extra dups?
• Do the automatically deleted past items get put in ‘Trash’ or permanently deleted? (Ask since trash takes so long to delete since we can only select 60 events at a time – is there a better way to flush ‘Trash’ that is stable?

We can’t stage the site since the original database was close to 400mb in size, from all the events, in order to try alternative routes.

…we’ve cranked up our server memory and request time-out as much as possible. We host at a very good WordPress-only host, Flywheel.

Please advise as this applies to some of our past purchased licenses too?

Regards,
Jeremy

[Brook]

Howdy Jeremy,

I would love to help you with this.

When were those duplicated created? Were they from the bug in version 4.2.2 of Pro that existed briefly a few weeks back? Or from before that?

Depending on how they were created I might have a solid method for cleaning these up. Do you have access to your MySQL server where you could run a query, perhaps through WP Admin? If so could you run this query?

https://gist.github.com/elimn/a998d7b67f6166dab5e17ec071eb35ee

It will return a list of events IDs and how many recurrences each one has. Ideally we will see a small number of events, maybe even 1-2, that has an insane amount of recurrences. Can you share the output of that query with me so I can consider some possible options for you?

Cheers!

– Brook

[sagemg]

Brook,

I’m not sure when the duplicates started showing – the original recurring events were created months ago. Did the upgrade to the 4.2.2 generate duplicates when it was upgraded (later on then the events were created.) In-other-words, these recurring events were not created in 4.2.2 – they were created in 4.0.5 or a version shortly after that…

I can’t run the SQL commands successfully, I get the error Attached. (Error in querry (1146): Table ‘db3929526080.wp_posts’ doesn’t exist)

We host at Flywheel in NGINX and there is no phpMyAdmin, just Flywheels database tool.

I’m currently deleting events in batches of 50-60 since I get a time out request beyond that – Flywheel has already increased the memory and time out limit on the space.

When I delete these events manually (one-by-one) do they end up in Trash? I ask because I then have to spend a long time deleting again from trash? When I select ‘Edit All’ on several of the recurring events it only edits that one event where as the first series of events I edited all and deleted removed whole series and gave the yellow status bar that took a while, but deleted everything?

I’m running versions 4.2.6 and 4.2.5 of the two plugins (4.2.5 is pro plugin).

If I end up deleting all the duplicates manually, what is the best way to guarantee recurring events don’t get duplicated – what are the best practices? I currently have 35,000 events (down from original 60k when I started dup clean-up).

Regards,
Jeremy

[Brook]

Howdy Jeremy,

If I end up deleting all the duplicates manually, what is the best way to guarantee recurring events don’t get duplicated – what are the best practices? I currently have 35,000 events (down from original 60k when I started dup clean-up).

Man, you have made some considerable progress but ouch that’s a lot of manual deletions!

I am not actually sure why you are seeing duplicates. There was a bug in Events Calendar Pro 4.2.2, that was fixed a few hours later in 4.2.2.1, that could have caused some duplicates. If you updated to that version that is a very likely source of what you are seeing. There is nothing you will need to avoid to prevent that from happening again, it was a one-of sort of problem.

Would you mind grabbing your system information and pasting it here? Make sure to use the ‘Set as private reply’ checkbox to protect your private information from the public. You can find the system info by going to WP Admin > Events > Settings, clicking on the “Help” tab, and scrolling down to the ‘System Information’ box. (Or by going to [yoursite]/wp-admin/edit.php?post_type=tribe_events&page=tribe-events-calendar&tab=help) Then I could tell if you were on that version.

When I delete these events manually (one-by-one) do they end up in Trash? I ask because I then have to spend a long time deleting again from trash? When I select ‘Edit All’ on several of the recurring events it only edits that one event where as the first series of events I edited all and deleted removed whole series and gave the yellow status bar that took a while, but deleted everything?

If you are deleting them from WP Admin then yes they will first go to the trash. It would probably be best to be emptying the trash periodically as you go along. WordPress does include a convenient “Empty Trash” button. But deletions in WordPress take a decent chunk of server time while it thinks (as you’re now very familiar with) . Hitting Empty Trash on that many events will probably cause a timeout. Which is not the end of the world, but might need to refresh the page and hit the button again a few times to empty everything.

I can’t run the SQL commands successfully, I get the error Attached. (Error in querry (1146): Table ‘db3929526080.wp_posts’ doesn’t exist)

I am happy to hear you can run SQL commands. It does not need to be through PHPMyAdmin, just being able to run them at all will be helpful.

The error you are seeing is undoubtedly because your site is not using the default wp_ prefix for its database. Some hosts will change this prefix. In order to proceed you will need to determine what “WP database table prefix” you are using. If you don’t know what it is, you could check with your host. From there modify that query I shared with you slightly. You see where it says this:

FROM 
 wp_posts

change the wp_ to your databases prefix. If you prefix is example123_ then the line will become:

FROM 
 example123_posts

Now run the modified query. Can you share its output with me? With all of this info it might make it readily apparent what caused the duplicates, and knowing that I can advise you how best to clean them up.

Cheers!

– Brook

[sagemg]

Brook:

We started beginning of the year with Events Calendar Pro v4.0.5 and have updated a few times since then. I don’t see the site ever running 4.2.2 or 4.2.2.1 The latest updates took place after the duplicates issue since we weren’t aware of it. Site is currently running v4.2.5

There is more at the bottom of this post, but here is the Sys Info from the Dashboard:

—

Home URL
http://fortcollinsclub.net
Site URL
http://fortcollinsclub.net
Site Language
English
Character Set
UTF-8
WP Permalinks
/%postname%/
Name
The Club
Email
[email protected]
Install keys

events-calendar-pro = 2ada################################b6aa

WordPress version
4.6.1
PHP version
5.6.25-1+deb.sury.org~precise+1
PHP

max_execution_time = 360
memory_limit = 1024M
upload_max_filesize = 300M
post_max_size = 300M
display_errors =
log_errors = 1

Server
Flywheel
SAPI
fpm-fcgi
Plugins

Gravity Forms version 2.0.6 by rocketgenius(http://www.rocketgenius.com)
LayerSlider WP version 5.6.3 by Kreatura Media(http://kreaturamedia.com/)
Ninja Kick: Sliding Panel version 3.0.0 by Looks Awesome(http://looks-awesome.com/)
Advanced Post Manager version 4.2.2 by Modern Tribe, Inc.(http://m.tri.be/4n)
Envato WordPress Toolkit version 1.7.3 by Envato(http://envato.com)
The Events Calendar PRO version 4.2.5 by Modern Tribe, Inc.(http://m.tri.be/20)
WPBakery Visual Composer (Artbees Modified Version) version 4.12.1 by Michael M – WPBakery.com(http://wpbakery.com)
Shortcoder version 3.4.1 by Aakash Chakravarthy(http://www.aakashweb.com/)
The Events Calendar version 4.2.6 by Modern Tribe, Inc.(http://m.tri.be/1x)
Yoast SEO version 3.4.2 by Team Yoast(https://yoast.com/)
WP Google Map Plugin version 3.1.3 by flippercode(http://www.flippercode.com/)
WP Smush Pro version 2.4.5 by WPMU DEV(http://premium.wpmudev.org/)
WPMU DEV Dashboard version 4.1.2 by WPMU DEV(https://premium.wpmudev.org/)

Network Plugins
–
MU Plugins
–
Theme
Jupiter Child Theme
Multisite
–
Settings

schema-version = 4.2.6
recurring_events_are_hidden = exposed
previous_ecp_versions =

Array
(
[0] => 0
[1] => 4.0.5
[2] => 4.0.6
[3] => 4.0.7
[4] => 4.1.1.1
[5] => 4.1.3
[6] => 4.2.1
[7] => 4.2.3
[8] => 4.2.5
)

latest_ecp_version = 4.2.6
last-update-message = 4.0.5
disable_metabox_custom_fields = hide
pro-schema-version = 4.2.5
tribeEnableViews =

Array
(
[0] => month
[1] => week
[2] => day
)

earliest_date = 2016-02-01 08:00:00
latest_date = 2016-12-31 22:30:00
donate-link =
postsPerPage = 30
liveFiltersUpdate =
hideSubsequentRecurrencesDefault =
userToggleSubsequentRecurrences =
recurrenceMaxMonthsBefore = 1
recurrenceMaxMonthsAfter = 3
showComments =
showEventsInMainLoop =
eventsSlug = calendar
singleEventSlug = calendar
multiDayCutoff = 00:00
defaultCurrencySymbol = $
reverseCurrencyPosition =
embedGoogleMaps =
geoloc_default_geofence = 25
geoloc_default_unit = miles
embedGoogleMapsZoom = 10
debugEvents =
tribe_events_timezone_mode = site
tribe_events_timezones_show_zone = 1
stylesheetOption = tribe
tribeEventsTemplate = default
viewOption = month
tribeDisableTribeBar = 1
hideLocationSearch = 1
hideRelatedEvents = 1
week_view_hide_weekends =
monthEventAmount = 15
enable_month_view_cache = 1
dateWithYearFormat = F j, Y
dateWithoutYearFormat =
monthAndYearFormat = F Y
weekDayFormat = D
dateTimeSeparator = @
timeRangeSeparator = –
datepickerFormat = 1
tribeEventsBeforeHTML =
tribeEventsAfterHTML =
pue_install_key_events_calendar_pro = 2ada################################b6aa
custom-fields =

Array
(
)

custom-fields-max-index = 4
earliest_date_markers =

Array
(
[0] => 1051
[1] => 1236
)

latest_date_markers =

Array
(
[0] => 3958
[1] => 8905
)

WP Timezone
America/Denver
WP GMT Offset
-6
Server Timezone
UTC
WP Date Format
F j, Y
WP Time Format
g:i a
Week Starts On
1
Common Library Dir
/www/wp-content/plugins/the-events-calendar/common/src/Tribe
Common Library Version
4.2.6

—

I ran the SQL again with the correct, custom prefix and attached the first dozen or so results – looks like the first may be a problem? Event ID 37993 = 25997 Recurrences… please see attached.

Thoughts?

Thanks Brook!

Jeremy

[sagemg]

Continuing from last post…

The duplicated event is “Level 1” and has over 25,000 occurrences. When we search for “Level 1” in the dashboard we see all 25k+ listed. Ideas on the best way to clean quickly from the db?

This event can go away completely.

Some screenshots attached.

Please advise,
Jeremy

[Brook]

Alright now we are making some progress! Thank you for collecting all that info.

If you feel comfortable running another SQL query or two, then we can rapidly delete that event. This is the query:

https://gist.github.com/elimn/e5de5d2037678de0b926c307022571c5

As you can see the query includes step by step details on how to run it. <span style=”line-height: 1.5;”>Before starting on those steps do make sure you have a backup of your database. This is very important, and it sounds like you already have one. </span><span style=”line-height: 1.5;”>This is just a safety net in case anything accidentally happens. </span>

Also note that there are a number of tables in that query, each prefixed with the standard “wp_”. You will again need to change these for your prefix, whatever it is. Then run the query.

It looks like you have already obtained the event ID, based on your screenshot it is 37993. That will be the event ID you will want to target with this query. After running the query, in the matter of a few seconds, maybe minutes, all 25k instances should be deleted.

Did that do the trick?

Cheers!

– Brook

[Support Droid]

Hey there! This thread has been pretty quiet for the last three weeks, so we’re going to go ahead and close it to avoid confusion with other topics. If you’re still looking for help with this, please do open a new thread, reference this one and we’d be more than happy to continue the conversation over there.

Thanks so much!
The Events Calendar Support Team

[Author]

Posts