Post-import cleanup help needed
After importing from my bbPress ver. 1.0.2 to the new bbPress 2.2.4 (I did not use the bbConverter, I just followed the instructions to use the Tools>Forums>Import Forum), I am left with twice as many topics as I should have…..each one is duplicated and the extra item has (No Forum) showing as the parent Forum.
I had encountered an error on the first import, but I checked the box that says “Purge all information from a previously attempted import” on the second attempt…..looks like it did not purge……
SO I am comfortable with using phpMyAdmin to fix things, but I don’t know how to write a SQL query to delete the duplicates…….I could use help with that.
ALSO, is it now safe to drop the old tables that previously held all the topics/posts/replies/users, etc? The ones that start with bb_ ? I really want to keep my database cleaned up……AND can I remove the subfolder from the old version – the one that held the bb_config.php and all those old files?
Before you cleanup your old bbPress stuff lets clean up your import…
The quickest and easiest way to get around the failed ‘Purge previous import’ without diving into phpMyAdmin custom SQL queries would be to perform a ‘Forum Reset‘ via http://example.com/wp-admin/tools.php?page=bbp-reset
What this will do is remove forums, topics, replies, topic tags etc (almost everything) from your install of bbPress. You will need to manually delete the users though /wp-admin/users.php as at this stage as we haven’t quite got this bit ironed out yet.
Grab bbPress 2.3 RC1 http://bbpress.org/blog/2013/03/bbpress-2-3-release-candidate-1/ and perform your input again.
This should give you a nice clean bbPress and then once you are happy and have backups of your site and database then you can start removing the old bbPress 1.x bits and pieces.
Thank you Stephen – I very much appreciate your help.
Needless to say, this sounds a bit scary but I’ll attempt this late tonight – my forum does get enough activity that I’d prefer to do this when it’s unlikely that any users will notice.
The only part that will be difficult is deleting users – I have around 650 users, a few dozen of whom are also authors on my blog, so I can’t just wipe out all the users, and deleting them individually or in groups will take some time……
BUT I will post back my results in a day or two at the most, and then we can move on to the next step.
When you do an import if the user already exists eg. @TrishaM the importer will create a new user named @Imported_TrishaM.
Once the import is finished when you go to delete the user @Imported_TrishaM you have the option to delete the users created content or attribute them to another user, in this case you would select @TrishaM and the end result is everything is attributed to the user @TrishaM.
Usding the method above by ‘not deleting’ for example your blog authors would work well for your situation in regard to these users.
Everything is a kind of trade off in the end of how much to do manually versus automated :/
Hi again – just wanted to post a quick update…..
I have not been able to follow the instructions to reset my forum yet…..I backed up my database (using phpMyAdmin), but as a precaution I wanted to import that backup into a new [temporary] DB just in case something goes terribly wrong…..
However, I have not been able to get my backup to import into a new DB, I’m fairly certain because it is too large….at 30MB it’s under the 50MB limit imposed by 1&1 but it keeps timing out (or something) every time I try to import it using either the Import Files tab or just going to SQL and choosing the file…I get a blank screen each time and the import fails. I’ve even tried importing a smaller zipped version of the file with no luck.
Without knowing for sure that I could use my backup to recover from a disaster if it happens while going through the “reset forum” function, I really can’t take the chance on it yet….
So I’m going to try backing up each table one at a time and importing them one at a time – this is frustrating because of the way the site is built I have a LOT of tables (beyond the standard wp_ tables) but I don’t see that I have much choice.
Since the *only* problem I have right now is the duplicated topics with no parent forum, it would be much easier if I could just do a SQL query to delete those posts. That would cut down the overall size of the backup substantially since much of the size is due to all the forum posts.
The bbconverter plugin definitely has issues with importing forums, topics, posts and tags multiple times. I’ve used it on two separate sites, tried wiping clean and retrying multiple times, it just keeps going over and over again until you’ve got tons of duplicates.
Apologies for the late reply but a severe case of the flu has kept me from thinking
Firstly @clicknathan the bbconverter plugin has not been updated in a long time and is what the actual included converter with bbPress 2.x is based upon and indeed if you keep importing on top of imports you are going to have issues.
@trisham I cannot come up with a specific SQL query for you to delete the duplicates, ideally what you want to do is open phpMyAdmin and find the post ID’s of your imports.
i.e. Find the ‘first’ and ‘last’ post ID from your first import and then find the ‘first’ and ‘last’ post ID from your second import.
`SELECT * FROM ‘wp_posts’ WHERE ‘post_type’ LIKE ‘forum’`
`SELECT * FROM ‘wp_posts’ WHERE ‘post_type’ LIKE ‘topic’`
`SELECT * FROM ‘wp_posts’ WHERE ‘post_type’ LIKE ‘reply’`
The above queries you should see you started to see the post ‘ID’ for the duplicates and I would expect to find them in a numerical order by post ‘ID’ and this starts to build you a range of post ‘ID’ that you will want to delete.
You may find that the range of post ID’s are eg. 2650 – 5299 that are the duplicates that you need to remove and this SQL query highlights those posts `SELECT * FROM ‘wp_posts’ WHERE ID BETWEEN 2650 AND 5299 ` and this query `SELECT * FROM ‘wp_postmeta’ WHERE post_id BETWEEN 2650 AND 5299 ` finds the same posts in `wp_postmeta`
Once you have confirmed the post ID’s for your duplicates with the queries above and you are confident you have the correct ID’s you grab those SQL statements eg. `SELECT * FROM ‘wp_posts’ WHERE ID BETWEEN 2650 AND 5299 ` and `SELECT * FROM ‘wp_postmeta’ WHERE post_id BETWEEN 2650 AND 5299 ` and change the `SELECT * ` to `DELETE` for both of those queries to remove the posts from `wp_posts` & `wp_postmeta` tables.
I hope this makes some kind of sense and if you have any questions please ask away.
OK I finally have the first part completed by following your excellent instructions (thank you) – I’ve removed all the forums, topics, and replies from my database…..but naturally they are still *there* in the (old) bb_ tables, just no longer in my wp_posts or wp_postmeta tables.
This shrunk the size of my database considerably – from 30MB down to 8MB……BUT I still can’t get my backup to import to a new clean database (as a safeguard), even if I zip it……I get the “mySQL server has gone away” message, which tells me it’s timing out. I sent a message to my hosting company’s tech support (1&1) to ask that they increase the mysql.connect_timeout setting, but haven’t heard back yet…..and they may not do it, but hopefully they will.
I’m still reluctant to try importing my forums/topics/replies again until I’m certain that I can restore from a backup in the event of a disaster, so now I’m trying to export and import tables one at a time into a new database…..when I get to the bbPress tables I’ll import those to yet another new (temporary) database and keep them away from my wp_ tables……
That may take me a couple of days since I have to work on this at night….but I WILL be back with my results soon!
OK I think I’m ready to move forward……
I now have a (temporary) database set up into which I’ve imported ONLY the bbPress tables:
AND I have another (temporary) database into which I imported ONLY the WordPress tables:
In theory I could change out the database called in my wp-config.php file to this second database and my site would be just the same as it is now, minus the Forum which isn’t working anyway.
I did NOT export the wp_bbp_converter_translator to anywhere since it sounds like that’s part of the problem….
SO what’s next? Do I try the Tools>Forums>Import Forum again? OR should I switch to the newer, no-bbPress database and then import the forum from the existing (or backup) database? Although it seems straightforward my prior experience has me cautious and I’d rather have some expert guidance before I try anything else yet….
Giving this a bump……I could still really use some guidance so that I don’t screw this up again….
Thanks for any help anyone can offer….
If it helps anyone this will help you see all your bbpress duplicate topics at a glance with the duplicate ID
** Note these are just selects so they will not delete any data **
select post_type , post_date , post_title , post_content , max(id) as dup_id, count(*) as dupe_count from wp_posts where post_type in ('forum', 'topic', 'reply') group by post_type , post_date , post_title , post_content having count(*) > 1 order by post_type, post_date, dup_id
We can then take a variation of that and have it give us the start and end duplicate ranges for each post_type with counts of rows we expect to be impacted.
select post_type, min(dup_id) as start_dup, max(dup_id) as last_dup, count(*) as total_recs from ( select post_type , post_date , post_title , post_content , max(id) as dup_id, count(*) as dupe_count from wp_posts where post_type in ('forum', 'topic', 'reply') group by post_type , post_date , post_title , post_content having count(*) > 1 ) i group by post_type
of course you need to verify but this should help get you on your way
The topic ‘Post-import cleanup help needed’ is closed to new replies.