Someone discarded a draft in eZ Publish (4.x, 5.x, or legacy) and it's missing! How do I find it?

eZ Publish seems to have a bug where content goes missing sometimes when a draft is discarded. How Do I find the content to restore?

Answer

This seems to happen when the content version status doesn't match the content object status. Here's a query you can use on the database to find contentobjects which are in this unusual state:

SELECT ecot.contentobject_is_published,
   eco.id,
   eco.name,
   eco.status,
   ecov.status,
   ecot.path_string,
   ecot.path_identification_string AS version_status,
   ecot.node_id
FROM   ezcontentobject eco
   JOIN ezcontentobject_tree ecot
     ON ( eco.id = ecot.contentobject_id )
   JOIN ezcontentobject_version ecov
     ON ( ecot.contentobject_id = ecov.contentobject_id
          AND eco.current_version = ecov.version )
WHERE  ecot.contentobject_is_published
   AND eco.status != ecov.status
   AND ecov.status = 3
LIMIT  100  

Note that there's a JIRA ticket tracking this issue, but as of this post it's still unresolved: https://jira.ez.no/browse/EZP-28681