What steps do I need to take to remove borked entry from database (channel_titles: yes, channel_data: no)

Question

Before I troubleshoot what caused the problem in the first place (entry made using safecracker form), I'd like to know how to remove the bad data.

EE Version v2.5.2 - Build Date: 20120606*
Development Environment: MAMP

(Yes, yes, I know. I was soon going to update it to 2.5.5; Now troubleshooting this issue is at top of list.)

Channel Entry #62, and how it manifests:

In the database:
entry_id 62 does exist in the exp_channel_titles table of the database.
entry_id 62 does not exist in exp_channel_data

In exp_channel_data it goes from 61 to 63. Oh my.

In the control panel:
The entry ID, title, author, date, status all appear in table of entries. Upon clicking the title link to edit entry, this message results:

"You have tried to access a channel that does not exist."

From the template:

• On an index page template that lists multiple entries in this channel, template calls for {title} (and url_title in link to individual page) {author} And a custom field for a short description: {showcase_txt_short_description}
  The rendered index page displays title, author name, but displays {showcase_txt_short_description} as is.

• Template for individual entry page has more custom fields in addition to title and author. Similar to rendered index page, the rendered individual entry page displays the title, but passes through {unrendered_custom_fields_enclosed_with_curly_braces}

• When I first loaded up that individual page, I got a number of errors. Alas (?) I cannot describe them for you because they magically disappeared when I refreshed the page just now while preparing to compose this question.
  Errors were:

  • 3 php warnings
  • 1 error 1064 with a SQL error and details of SQL query, that ended up referencing ft.matrix (yes, there's a matrix field in this particular channel).

Changes I have made since discovering this error

Between the time I first encountered this error and now, I updated addons for the site. Among the addons, I updated to the latest version of Matrix, and also pt fieldpack (source:github)

Another thing I did, in the DB: made minor manual correction to table for entry_id 62 in exp_channel_titles. Where versioning_enabled, it was set to n, (whereas channel preferences for this channel is y), so I changed it to y. (no, that didn't correct error)

So, the php warnings and SQL errors have gone away, but I have something bad in my database.

Now that you know a ridiculous amount of detail, my question: What is the best way to get rid of the bad entry #62. Should I just remove the row that contains entry_id 62 from the exp_channel_titles table, or are there other places I should go in order to prune this bad entry out and start again to see what caused it?

Answer 1

Your "More Info" comment shows you are on the right track, in looking for other data fragments in the database. exp_channel_data contains your custom fields. Several third-party field types have their own table to store information in (like Matrix/exp_matrix_data).

You should be safe to remove the row from exp_channel_titles. The other tables contain data that is tied to that main entry table, but they are rarely referenced separately. The exact addons/code will determine whether an orphaned entry elsewhere in the database is a problem or not (like some of the geolocation addons), but I am inclined to think it won't be an issue.

By the way, have you looked for other problematic entries? A query like this could be useful:

SELECT ct.entry_id
FROM exp_channel_titles ct
LEFT JOIN exp_channel_data cd ON (cd.entry_id = ct.entry_id)
WHERE cd.entry_id IS NULL

Answer 2

Here is the process I would follow to isolate the problem a bit more. Looks like that entry 62 is indeed problematic

1. Backup your database (a big dump is enough to allow you to rollback quickly if anything goes horribly wrong)
2. Delete that 62 entry from exp_channel_titles
3. If problem persists and you still get a message tied to matrix, try disabling it.

You can always restore everything if needed.