Tainted data, glitches in admin pages and public templates, immense confusion!

[canadianbrass]

Hello,

Something has completely tainted all of my events calendar data not only on my live site, but on my dev site and local copy of that same site running the same plugin versions, my dev and local copies each containing various snapshots of the live data. This is really, really bizarre…

Basically, it seems like my data on all three sites (specifically, the postmeta data) has completely corrupted itself. On one WPMU site it is showing all events as reoccurring on the Events index page when they aren’t, on each of these the Start Date sorting does not work. I can’t make any sense of what is going on in the backend GUI. On the front end the mini calendar (http://canadianbrass.com) is showing different dates for this month than the full events page (http://canadianbrass.com/events). I’ve also gotten infinite loops, and a ton of duplicate events appearing on the same day, although I managed to do something to make this problem stop. Given that this makes zero sense to me and no doubt to you either, I decided to trace this further by looking at the actual post and postmeta data…

Are you prepared for more confusion?

This event was created in April 2012. From my wp_posts table:

*************************** 1. row ***************************
ID: 7979
post_author: 1
post_date: 2012-04-25 13:18:26
post_date_gmt: 2012-04-25 17:18:26
post_content: Canadian Brass Masterclass/Lecture in Regina, SK – TBD
post_title: Canadian Brass Masterclass/Lecture in Regina, SK
post_excerpt:
post_status: publish
comment_status: closed
ping_status: closed
post_password:
post_name: canadian-brass-masterclasslecture-in-regina-sk
to_ping:
pinged:
post_modified: 2012-04-25 13:18:26
post_modified_gmt: 2012-04-25 17:18:26
post_content_filtered:
post_parent: 0
guid: http://www.canadianbrass.com/?post_type=tribe_events&p=4979
menu_order: 0
post_type: tribe_events
post_mime_type:
comment_count: 0

It is showing up as Jan 11, 2013 on both mini calendar and full calendar listing. The postmeta data:

mysql> SELECT * FROM `wp_3_postmeta` WHERE meta_key like ‘%EventStart%’ and post_id = ‘7979’\G
*************************** 1. row ***************************
meta_id: 1885
post_id: 7979
meta_key: _EventStartDate
meta_value: 2013-01-11 14:00:00

So, the postmeta data appears to be what is corrupt here. I have no idea what set the meta_value to this incorrect date. I mentioned that the mini calendar and full calendar listing were showing different results, so:

mysql> SELECT * FROM `wp_3_postmeta` WHERE meta_key like ‘%EventStart%’ and meta_value like ‘2013-01-%’\G
*************************** 1. row ***************************
meta_id: 1870
post_id: 7976
meta_key: _EventStartDate
meta_value: 2013-01-09 20:00:00
*************************** 2. row ***************************
meta_id: 1885
post_id: 7979
meta_key: _EventStartDate
meta_value: 2013-01-11 14:00:00
*************************** 3. row ***************************
meta_id: 1903
post_id: 7980
meta_key: _EventStartDate
meta_value: 2013-01-12 20:00:00
*************************** 4. row ***************************
meta_id: 1912
post_id: 7983
meta_key: _EventStartDate
meta_value: 2013-01-22 20:00:00
*************************** 5. row ***************************
meta_id: 1926
post_id: 7987
meta_key: _EventStartDate
meta_value: 2013-01-13 19:30:00
*************************** 6. row ***************************
meta_id: 1941
post_id: 7990
meta_key: _EventStartDate
meta_value: 2013-01-26 20:00:00
*************************** 7. row ***************************
meta_id: 2099
post_id: 7994
meta_key: _EventStartDate
meta_value: 2013-01-27 20:00:00
*************************** 8. row ***************************
meta_id: 3284
post_id: 8394
meta_key: _EventStartDate
meta_value: 2013-01-25 19:30:00
*************************** 9. row ***************************
meta_id: 3303
post_id: 8396
meta_key: _EventStartDate
meta_value: 2013-01-24 00:00:00
*************************** 10. row ***************************
meta_id: 4063
post_id: 8483
meta_key: _EventStartDate
meta_value: 2013-01-15 00:00:00
10 rows in set (0.00 sec)

So, while I have no idea what set all of those bogus meta_value dates, what is displaying in the mini calendar is “right”, at least according to the data I have. Yet, the full calendar is only showing two of these events.

In short, I have absolutely no idea what could have corrupt all of this postmeta data, and why the current mini and full calendar views disagree with each other. I also have no idea why the admin pages are completely messed up on all three of these sites.

It seems like there are a number of possible causes here (e.g. bad SQL query in the plugin, template glitches) but if this was the case I’m sure this problem would be pretty widespread. Again, this has occurred on 3 versions of this site containing different snapshots of data, and everything seemed fine until earlier this month when everything just sort of exploded.

Please help!

[Jonah]

Hey canadianbrass,

That’s a doozy of an issue and I’m not quite sure what to about it myself so I’m going to assign this as a ticket to a developer and have them take a look as soon as they can. Stay tuned.

Thanks,
Jonah

[canadianbrass]

Thanks Jonah,

An update here…

Apparently all of the above data is actually fine, the problem isn’t with tainted data, I apologize for not helping my own cause by confusing this (it wasn’t me that entered the data, so this has been a challenge to work through conceptualizing).

The main problem is that the main calendar view doesn’t agree with the main calendar view, and the funkiness that is appearing in both (duplicates, etc.). The problem with the administrative views is also outstanding, but I haven’t spent much time tracing this – I realize the info I’ve provided there is sketchy.

I will continue to trace this on my end so that I can provide more useful info (if not figure out the problem). Thanks very much for your help with this!

[Jonah]

Sounds good canadianbrass, I’ll close out the ticket I created and wait for any further reports from you before doing anything else.

Thanks,
Jonah

[canadianbrass]

Okay, here is one of the problems…

Tracing the query that is sent by the mini calendar down to WordPress’ get_posts function in wp-includes/query.php, it looks like the final query sent, in my case is as follows:

SELECT wp_2_posts.*, eventStart.meta_value as EventStartDate, IFNULL(DATE_ADD(CAST(eventStart.meta_value AS DATETIME), INTERVAL eventDuration.meta_value SECOND), eventEnd.meta_value) as EventEndDate FROM wp_2_posts LEFT JOIN wp_2_postmeta as eventStart ON( wp_2_posts.ID = eventStart.post_id AND eventStart.meta_key = ‘_EventStartDate’) LEFT JOIN wp_2_postmeta as eventDuration ON( wp_2_posts.ID = eventDuration.post_id AND eventDuration.meta_key = ‘_EventDuration’) LEFT JOIN wp_2_postmeta as eventEnd ON( wp_2_posts.ID = eventEnd.post_id AND eventEnd.meta_key = ‘_EventEndDate’) WHERE 1=1 AND wp_2_posts.post_type = ‘tribe_events’ AND (wp_2_posts.post_status = ‘publish’) AND ((eventStart.meta_value >= ‘2013-01-01 00:00:00’ AND eventStart.meta_value = ‘2013-01-01 00:00:00’ AND eventStart.meta_value <= '2013-01-31 23:59:59' ) OR (eventStart.meta_value = ‘2013-01-31 23:59:59’ )) ORDER BY DATE(eventStart.meta_value) ASC, TIME(eventStart.meta_value) ASC

This query is producing duplicate entries on my test site, such as this row turning up a ton of times:

*************************** 226. row ***************************
ID: 4994
post_author: 1
post_date: 2012-04-25 14:03:47
post_date_gmt: 2012-04-25 18:03:47
post_content: Canadian Brass Concert w/Orchestra in Harrisburg, PA
post_title: Canadian Brass Concert w/Orchestra in Harrisburg, PA
post_excerpt:
post_status: publish
comment_status: closed
ping_status: closed
post_password:
post_name: canadian-brass-concert-worchestra-in-harrisburg-pa-2
to_ping:
pinged:
post_modified: 2012-04-25 14:03:47
post_modified_gmt: 2012-04-25 18:03:47
post_content_filtered:
post_parent: 0
guid: http://dev1.canadianbrass.com/?post_type=tribe_events&p=4994
menu_order: 0
post_type: tribe_events
post_mime_type:
comment_count: 0
EventStartDate: 2013-01-27 20:00:00
EventEndDate: 2013-01-27 22:00:59

Something is definitely generating bogus queries. Does the query I posted above look normal?

[canadianbrass]

When I say that this row turns up a ton of times, to be crystal clear, I mean that ID# 4994 appears over and over and over again a ton of times within the same day. The same happens for all the other events in the month. I’m happy to provide a MySQL dump file of this data if that would be helpful. You can see the results of this in the mini calendar view here, although you’ll have to wait for a while and hope you don’t create a timeout due to this heavy query:

http://dev1.canadianbrass.com/

[Jonah]

Hi canadianbrass,

Have you tested this on a fresh install with no other plugins installed and/or testing it in the Twenty Eleven/Twelve theme to rule out any conflicts or data corruptions already in place in your install?

– Jonah

[canadianbrass]

I have, but don’t you think it would be a pretty remotely small probability that some other plugin is mucking around with database entries of post_type “tribe_events”? I haven’t messed around with this since my initial testing since I figured that what I found pointed to the problem being self-contained. I’ll try this again once I have a free moment though.

[Jonah]

Hi canadianbrass,

It just seems strange considering we do tons of testing with the plugin on multiple platforms both remotely and locally and have not noticed this behavior ourselves let alone no other customers reporting any issues with duplicate posts being automatically created. If there was a common problem like this, we would notice it and tons of people would be reporting it. Before spending any extensive time troubleshooting something like this we need to be sure it’s something worth taking a look at and not just an isolated issue that is due to someone’s install, a conflict with a theme or another plugin and so on.

So, we need to know that you are seeing this behavior in a completely vanilla environment, removed from anything else that could be causing the issue, and it would also be helpful if we could see a screencast or something like that documenting how the problem is occurring, i.e. go here, do that and poof, the issue occurs.

Does that make sense?

– Jonah

[canadianbrass]

Absolutely! I didn’t mean to suggest any unwillingness on my part, I would also be surprised if this problem was at all widespread. I’ll re-test a vanilla install, and keep on try to figure out how to reproduce this. One thing I can provide that might be useful is a MySQL dump file with my calendar data, would that be useful to you guys? This might be more useful that a screencast if it proves that the problem can be reproduced that way, as this problem doesn’t seem GUI related in nature, although I could be wrong about that.

Would you be able to confirm what a typical MySQL query should look like of calendar data within wp-includes/query.php get_posts()? It would be really helpful to confirm what I should be expecting as far as a normal query. I’ll check this out with a vanilla install too.

I also trust that this plugin is officially supported in multisite installs?

[canadianbrass]

deactivating the plugin and using the default theme has unfortunately had no effect on the problem I’ve described above where I’m getting the events appearing over and over again due to the query that is being generated. Your confirming that the query I’m getting above is normal would be most appreciated!

[Jonah]

Hey canadianbrass,

It looks normal to me but I really don’t have the technical expertise to know for sure. I don’t know a whole lot about the queries going on behind the scenes… Before I can have a dev take a look at this for you I need to know that you’ve tried using the plugin under a clean install.

Let me know how that goes.

Thanks,
Jonah

[canadianbrass]

Please do. I’m having this problem with the TwentyTen theme and no plugins enabled other than the latest version of Events Calendar and Events Calendar Pro. I’d love to see what a dev thinks of the queries that are being generated!

[Jonah]

Alright, I’ll get another dev to take a look ASAP. Stay tuned.

[canadianbrass]

Thanks Jonah!

Some additional info…

Just to re-iterate, there are multiple symptoms here. They might be of the same basic problem, but I’ve discovered some stuff that might be revealing…

My primary concern here is the discrepancy between mini and full calendar views, and what might have caused the duplicates in the first place. This is evident on the mini calendar of one of my test sites: http://dev1.canadianbrass.com. As you can see on the dev1 subdomain the full calendar shows two events with no duplicates, and the mini calendar shows all of them with the duplicates.

I’ve provided SQL dumps of all wp_post data of post_type “tribe_events”, and wp_postmeta data where meta_key like ‘_Event%’, you can access them here:

http://netmusician.org/~joe/cbdata

There appears to be a lot of duplicate postmeta rows since there are over 4000 entries here. I’m not sure what caused all of these. I may have struggled with the WordPress Exporter plugin at some point in syncing data with the live site, it has always been finicky for me, but in my experience it is pretty good about not duplicating data, and even if it were to I’d expect it to provide a few extra duplicates, not as many as we are seeing here.

If you feel that the WordPress Exporter plugin was the culprit here, so be it, I can write a script to cleanup the duplicates, but this still doesn’t explain the discrepancy between the mini and full calendar views. Still, I will work on a script to search for and cleanup the duplicate and/or orphaned postmeta data to see if this helps.

Theory:

Could the full calendar view be seeing SQL limits on postmeta requests, expecting one set of corresponding postmeta data per event, and therefore we are not getting all the results because of this duplicate data?

Just to clarify something I wrote above, because I realize this thread is incredibly disorienting, I confirmed that the data is not “tainted”. What I meant by this is that I confirmed with my client that the events that are supposed to be appearing in January really should be, despite being created several months ago. Obviously, if there is duplicate postmeta data this could be considered a form of data taint as well. So, I take back what I said about taint not being the problem. I haven’t combed through the 4000 postmeta rows yet to see if there are indeed exact duplicates, but for now I will assume there are.

It could be that your developer(s) simply need to assess whether my case is super fringy and not provide error checking for this, or else tweak the code a little bit. I would think that at all costs the mini and full calendar views should show the exact same data, even if the underlying data is messed up.

[Author]

Posts