ghost hunting … exorcism instructions

a normal person doesn’t think about it, but everybody should know, probably you should pay attention to what comes …

know what this is?

ml-slider 24
mgmlp_media_folder 13
wp_template 9
wp_template_part 7
bsf_custom_fonts 6
ris_gallery 4
wp_navigation 3
revision 2
wp_global_styles 2
sp_post_carousel 1
wp_block 1

it is a crap report from your wp_posts table, that are the ghosts you might be hunting, custom post types, without knowing, people say “phenomena”, they say “out of nothing”, “weired” or any other mystical words to explain their state of not knowing what is happening …

there are thousands of search engine results with wild suggestions and sensless solutions, AI goes into helpless mode and answers with crappy unlogic suggestions …

what is it then? firstable it is CRAP, real crap, crap created by others and let behind by others

it hit’s you when you’re getting funny wp_query results with scrambled post order ...

here is how you can make a 1 minute check if you’re site is affected:

SELECT post_date, COUNT(*) AS cnt
FROM WP_POSTS
WHERE post_status = ‘publish’
GROUP BY post_date
HAVING cnt > 1
ORDER BY cnt DESC, post_date DESC;

nothing? good, lucky guy, if not read on …

that is a double trap, planted by a fugging theme or by a fugging incompetent plugins, they create custom post_types and inject them into the posts table, what is not necessary bad creating them, but they don’t remove them when you deactivate or uninstall the plugin, or change the theme … that’s crap number 1

still worse is, that they insert them with the EXACT SAME DATE STAMP, aka as post_data and post_date_gmt …

that fuggs up your table indexes and comes out as a scrambled post order when you query your post table, EVEN if you query any other post type, when SQL finds two records with the exact same timestamp it delivers the first match and inserts deliberately the other or the others somewhere else in the query result!

it also really doesn’t matter if you query with post_data or ID order, the index matters, the data is correct

it might help ordering the query by two arguments, like

‘orderby’        => array(
                       ‘date’ => ‘DESC’,
                       ‘ID’   => ‘DESC’,
               )

but doesn’t solve the problem, it likely catches you later somewhere else or stays unnoticed ….

the problem is that the index doesn’t hold invalid data, means ANALYZE and OPTIMIZE TABLE doesn’t catch it

what should you do then?

first step is checking which plugin created the bad data, then you really should remove the records for good and also consider removing that crappy thing, if, for any reason, you need them still for something else than mocking you constantely, you can try this, just change the duplicated time stamp in the records, with this SQL QUERY

SET @offset := 0;
UPDATE WP_POSTS
JOIN (
SELECT ID, post_date,
@offset := IF(@prev = post_date, @offset + 1, 1) AS delta,
@prev := post_date
FROM wp_posts
WHERE post_type NOT IN (‘post’,’page’,’attachment’)
ORDER BY post_date, ID
) t ON p.ID = t.ID
SET p.post_date = DATE_ADD(p.post_date, INTERVAL t.delta SECOND);

yes, a second is enough to have distinguishable time stamps, after running it, check agaiin with the first query above, you should have no results and your data WP_POSTS table is clean now

now run this QUERY to exterminate the ghost’s leftovers

ALTER TABLE wp_posts DROP INDEX type_status_date;
ALTER TABLE wp_posts
ADD INDEX type_status_date (post_type, post_status, post_date, ID);
ANALYZE TABLE wp_posts;
OPTIMIZE TABLE wp_posts;


now go check your query order to see the ghost is gone …

exorcism was successful

thanks for reading this post …

obs.: the queries are good, just substitute wp_posts or WP_POSTS with your valid table name

credit: problem analyzed and solved by H.Mich