Fix Disqus Problem – Duplicate Comments after Export & Import

The Disqus commenting system is quite good in terms of offering your Blog visitors with a better and easier way to post comments but there are some drawbacks of the same too. One of the biggest one is lack of support from a company which is growing huge and not helping out the users who are struck with problems.

In our case we implemented the Disqus Commenting System, following our article on how to get more blog comments, and it was a very quick process. Following this we went ahead with the Exporting of our 20,300 comments which instantly made our server go down and make the blog unavailable for our visitors, which is a very bad deal. No issues within a few minutes we were back and the server load was back to normal.

Next there was no information on the plugin page if the Export was completed or not, nor no information if we should be re-export all our comments back to Disqus which is the reason after a few hours we went ahead to export all our blog comments after disabling most of our other WordPress plugins to make sure the blog resource usage is at its minimum and there is no issue in this one time setup because following this everything will be synced.

Once this was done at 100% we found that our comments were not the same 100% levels but were at around ~218% which means that our 20,300 comments became 44,361 comments which is quite a joke. All the blog posts had double the comments with the same stuff just repeating and we went ahead contacting the disqus support through twitter @disqus, @disqushelp and also emailing them comment page screenshot, api information, comments doubling problem at [email protected] which was replied after a days time by just asking what the problem is which was already mentioned in the email.

At the same time we were having huge number of visitors and we didn’t want them to feel that this is a fake blog with duplicated comments, following which we went ahead to the SQL > Phpmyadmin section and started digging into those confusing tables and rows to fix this huge problem. Thanks to Shodan Nayak from iVPS who helped with the right sql queries in order to fix this problem of duplicate comments in the WordPress Blog.

Once you login to the PhpmyAdmin, Under the WordPress database you will find several tables. Here in you will have to click on the ‘wp_comments’ table with Type – MyISAM & Collation – latin1_swedish_ci. Click on the Browser Icon as shown below.

Once you perform this, you will be inside the ‘wp_comments’ table, where all the different rows are listed. Most of these rows would be confusing but each of these serve a purpose like storing the comment_author name, comment_author_email for email of commenter & comment_content for the content of the posted comment etc When you Export your comments to Disqus all of these have a specific ‘comment_agent’ which is going to give you more information on the service using which the comment was posted. The Disqus based comments would have the information stored in the user-agent field which has to sorted and removed.

Disqus comment_agent Examples :
Disqus/1.1(2.48):76268969
Disqus/1.1(2.48):76268228

Once you find this and confirm, you can move to the next step of finding out how many of these comments are to be removed and how many of your blog comments are listed under the Disqus user-agent. Click on the SQL Query box and then run the following query which will give you more info.

SELECT COUNT(*) FROM wp_comments where comment_agent like 'Disqus%';

This SQL Query would give you a total number of the comments which are tagged with the user_agent.

The next step would be to to test a few of the comments which are found in the number above to make sure we are proceeding correctly. For this you will have to run the following SQL Query.

select * from wp_comments where comment_agent like 'Disqus%' limit 10

Once everything is perfect, you should first take a Backup of the Comments Table or the whole database. Next run the following query to wipe out of delete all the duplicate comments.

delete from wp_comments where comment_agent like 'Disqus%';

This will give you a result like > 25482 rows affected (2.46 sec), which means that all the comments are removed from your database and if you want to be on the safe side of not deleting all your comments, you can limit the delete query to the specific count which was found earlier.

delete from wp_comments where comment_agent like 'Disqus%' limit 25482;, following which only the number of comments which you found initially will only be deleted.

This solves the problem partly because there is one more table which is also effected with this plugin and this is the ‘wp_commentmeta’ table for which you have to run the following SQL Query to clean all the useless records.

SELECT COUNT(*) FROM wp_commentmeta where meta_key like 'dsq_%';

Now all the remains of disqus are also removed. Just run the WordPress Optimize plugin to clean up everything and enjoy your Blog with the default and the best commenting system.