Posts not showing content after migration

Question

I recently transferred a huge custom database of about 100,000 articles to wordpress. Everything seemed to work. But recently while checking the site. I found this issue where the post is displayed but when clicked, it doesn't open. Somehow the link doesn't contain the post title. Although when I edit the post and update it, it works. Now the problem is that it is very time consuming to bulk update 100 posts each time. Is there another way?

I have tried to update the permalink settings but no use.

Any help will be highly appreciated.

Answer 1

As suggested by Jos the reason for this was that the post_name column in the wp_posts table was empty. To correct the said problem, the following steps were taken:

1. Backup the database.
2. Check for duplicates in the database. select * from (select count(*) as c, post_title from wp_posts where post_name = '' and post_type = 'post') as counts where c > 1;
3. Update the table wp_posts set post_name = replace(post_title, ' ', '-');
4. Be extra careful that your post_name column doesn't contain accented characters or characters that are not allowed in the urls.
5. I then replaced most of the disallowed character in the post_name by running the following query (Absolute noob in sql):

UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Š','S');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'š','s');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ð','Dj');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ž','Z');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ž','z');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'À','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Á','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Â','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ã','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ä','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Å','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Æ','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ç','C');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'È','E');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'É','E');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ê','E');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ë','E');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ì','I');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Í','I');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Î','I');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ï','I');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ñ','N');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ò','O');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ó','O');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ô','O');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Õ','O');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ö','O');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ø','O');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ù','U');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ú','U');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Û','U');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ü','U');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ý','Y');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Þ','B');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ß','Ss');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'à','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'á','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'â','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ã','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ä','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'å','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'æ','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ç','c');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'è','e');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'é','e');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ê','e');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ë','e');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ì','i');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'í','i');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'î','i');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ï','i');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ð','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ñ','n');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ò','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ó','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ô','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'õ','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ö','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ø','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ù','u');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ú','u');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'û','u');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ý','y');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ý','y');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'þ','b');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ÿ','y');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ƒ','f');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'.','-');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'-','-');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'--','-');

UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ě','e');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ž','z');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'š','s');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'č','c');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ř','r');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ď','d');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ť','t');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ň','n');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ů','u');

UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ě','E');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ž','Z');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Š','S');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Č','C');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ř','R');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ď','D');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ť','T');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ň','N');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ů','U');
UPDATE `wp_posts` SET
    post_name = lower(name),
    post_name = replace(post_name, '.', '-'),
    post_name = replace(post_name, ',', '-'),
    post_name = replace(post_name, ';', '-'),
    post_name = replace(post_name, ':', '-'),
    post_name = replace(post_name, '?', '-'),
    post_name = replace(post_name, '%', '-'),
    post_name = replace(post_name, '&', '-'),
    post_name = replace(post_name, '#', '-'),
    post_name = replace(post_name, '*', '-'),
    post_name = replace(post_name, '!', '-'),
    post_name = replace(post_name, '_', '-'),
    post_name = replace(post_name, '@', '-'),
    post_name = replace(post_name, '+', '-'),
    post_name = replace(post_name, '(', '-'),
    post_name = replace(post_name, ')', '-'),
    post_name = replace(post_name, '[', '-'),
    post_name = replace(post_name, ']', '-'),
    post_name = replace(post_name, '/', '-'),
    post_name = replace(post_name, '-', '-'),
    post_name = replace(post_name, '\'', ''),
    post_name = trim(post_name),
    post_name = replace(post_name, '-', '-'),
    post_name = replace(post_name, '--', '-');

UPDATE `wp_posts` SET post_name = LOWER(post_name);

6. Seems everything works.
7. I also used the plugin 'Rebuild Permalinks' just to be sure.