• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Import Question: second import over old one

bubbl3

Active member
#1
My xenforo board is almost ready from primetime, 1 month ago i imported my vb3.8 datatabase and started work on the board style, installed addons, etc.
Now i obviously need to reimport the vb3.8 db before going live, what is the best way to go? What happens if i just reimport over the existing data? Suggestions are more than welcome :)
 

Jake Bunce

XenForo moderator
Staff member
#2
When you are ready for the final import you should run the import again from scratch. Use the test forum as a point of reference when doing your final import, as opposed to building onto your test forum and taking it live. If you have done any custom development on the test forum (e.g. custom styles and addons) then you need to apply those changes to the "from scratch" copy of xenForo when doing the final import.

It is better to do the final import from scratch. The test forum won't contain new content that was added to your live forum since you ran the initial import, and there is no option to pull in only the new content. So you would have to run the entire import again and deal with duplicate content from the initial import.
 

SchmitzIT

Well-known member
#3
I went the other way and reused my test installation. I wiped all the tables containing imported data, but that ended up being quite cumbersome in the long run. It's possible, but it requires you to not be afraid to write SQL queries, and of course, to make very frequent backups inbetween so you can easily roll back in case something goes wrong during a particular step.

Which reminds me, I should look into documenting what I did during the import.
 

Luke F

Well-known member
#4
I went the other way and reused my test installation. I wiped all the tables containing imported data, but that ended up being quite cumbersome in the long run. It's possible, but it requires you to not be afraid to write SQL queries, and of course, to make very frequent backups inbetween so you can easily roll back in case something goes wrong during a particular step.

Which reminds me, I should look into documenting what I did during the import.
Same here, just truncated a whole bunch of tables. Far easier than reinstalling from scratch and merging.
 

Brogan

XenForo moderator
Staff member
#5
This may be of some help: http://xenforo.com/help/importing/

  • Repeating the import process will result in duplicated content, including attachments and avatars. Users however will not be duplicated, due to the constraint related to user names.
  • If multiple imports are performed and URLs are redirected, only the last import will have working redirects unless the import log data is archived.
  • To completely remove any data related to an import, in order to do it again for example, the original XenForo database must be restored and all avatars and attachments must be deleted from the /data and /internal_data directories.
 

SchmitzIT

Well-known member
#7
Here's what I ended up using.

Code:
DELETE FROM xf_user_group WHERE user_group_id >= 5;
DELETE FROM xf_user WHERE user_id != 1;
DELETE FROM xf_admin WHERE user_id != 1;

ALTER TABLE xf_user_group
AUTO_INCREMENT = 5;

ALTER TABLE xf_user
AUTO_INCREMENT = 2;

TRUNCATE TABLE xf_thread;
TRUNCATE TABLE xf_post;

DELETE FROM xf_user_authenticate WHERE user_id != 1;
TRUNCATE TABLE xf_user_group_relation;
TRUNCATE TABLE xf_user_ban;
TRUNCATE TABLE xf_user_external_auth;
TRUNCATE TABLE xf_user_alert;
TRUNCATE TABLE xf_user_follow;
TRUNCATE TABLE xf_user_identity;
TRUNCATE TABLE xf_user_news_feed_cache;

DELETE FROM xf_user_option WHERE user_id > 1;
DELETE FROM xf_user_privacy WHERE user_id > 1;
DELETE FROM xf_user_profile WHERE user_id > 1;
TRUNCATE TABLE xf_user_status;
TRUNCATE TABLE xf_user_trophy;

TRUNCATE TABLE xf_conversation_master;
TRUNCATE TABLE xf_conversation_message;
TRUNCATE TABLE xf_conversation_recipient;
TRUNCATE TABLE xf_conversation_user;
TRUNCATE TABLE xf_error_log;
TRUNCATE TABLE xf_forum;
TRUNCATE TABLE xf_link_forum;
TRUNCATE TABLE xf_import_log;
TRUNCATE TABLE xf_ip;
TRUNCATE TABLE xf_liked_content;
TRUNCATE TABLE xf_moderation_queue;
TRUNCATE TABLE xf_moderator;
TRUNCATE TABLE xf_moderator_content;
TRUNCATE TABLE xf_node;
TRUNCATE TABLE xf_poll;
TRUNCATE TABLE xf_poll_response;
TRUNCATE TABLE xf_poll_vote;
TRUNCATE TABLE xf_profile_post;
TRUNCATE TABLE xf_search;
TRUNCATE TABLE xf_search_index;
TRUNCATE TABLE xf_session;
TRUNCATE TABLE xf_session_activity;
TRUNCATE TABLE xf_thread_user_post;
TRUNCATE TABLE xf_thread_watch;
TRUNCATE TABLE xf_trophy;
TRUNCATE TABLE xf_attachment;
TRUNCATE TABLE xf_attachment_data;
TRUNCATE TABLE xf_attachment_view;


ALTER TABLE xf_ip
AUTO_INCREMENT = 2;

ALTER TABLE xf_node
AUTO_INCREMENT = 1;

ALTER TABLE xf_forum
AUTO_INCREMENT = 1;


ALTER TABLE xf_thread
AUTO_INCREMENT = 1;

ALTER TABLE xf_post
AUTO_INCREMENT = 1;

ALTER TABLE xf_poll
AUTO_INCREMENT = 1;
 

SchmitzIT

Well-known member
#9
Thanks SchmitzIT, that was more than i hoped for, much appreciated :)
You're most welcome. I struggled with the whole process for two hours or so, mostly due to not resetting the auto incremenets, and basically restoring and backing up while solving individual steps. I figured then that I would save the statements to keep others from having to do the same :)
 
#10
thanks Peter, that helped a lot.
I've extended this a little, mainly to get it working with 1.1. with its' new tables. Maybe anyone else can use it as a starting point

Code:
DELETE FROM xf_user_group WHERE user_group_id >= 5;
DELETE FROM xf_user WHERE user_id != 1;
DELETE FROM xf_admin WHERE user_id != 1;
 
ALTER TABLE xf_user_group
AUTO_INCREMENT = 5;
 
ALTER TABLE xf_user
AUTO_INCREMENT = 2;
 
TRUNCATE TABLE xf_thread;
 
ALTER TABLE xf_thread
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_post;
 
ALTER TABLE xf_post
AUTO_INCREMENT = 1;
 
DELETE FROM xf_user_authenticate WHERE user_id != 1;
TRUNCATE TABLE xf_user_group_relation;
TRUNCATE TABLE xf_user_ban;
TRUNCATE TABLE xf_user_external_auth;
TRUNCATE TABLE xf_user_alert;
TRUNCATE TABLE xf_user_follow;
TRUNCATE TABLE xf_user_news_feed_cache;
 
TRUNCATE TABLE archived_import_log;
 
DELETE FROM xf_user_option WHERE user_id > 1;
DELETE FROM xf_user_privacy WHERE user_id > 1;
DELETE FROM xf_user_profile WHERE user_id > 1;
TRUNCATE TABLE xf_user_status;
TRUNCATE TABLE xf_user_trophy;
 
TRUNCATE TABLE xf_admin_log;
 
ALTER TABLE xf_admin_log
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_conversation_master;
 
ALTER TABLE xf_conversation_master
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_conversation_message;
 
ALTER TABLE xf_conversation_message
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_conversation_recipient;
TRUNCATE TABLE xf_conversation_user;
 
TRUNCATE TABLE xf_error_log;
TRUNCATE TABLE xf_forum;
 
ALTER TABLE xf_forum
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_forum_prefix;
TRUNCATE TABLE xf_link_forum;
TRUNCATE TABLE xf_import_log;
TRUNCATE TABLE xf_ip;
 
ALTER TABLE xf_ip
AUTO_INCREMENT = 2;
 
TRUNCATE TABLE xf_liked_content;
 
ALTER TABLE xf_liked_content
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_moderation_queue;
TRUNCATE TABLE xf_moderator;
TRUNCATE TABLE xf_moderator_content;
 
ALTER TABLE xf_moderator_content
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_node;
 
ALTER TABLE xf_node
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_poll;
 
ALTER TABLE xf_poll
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_poll_response;
 
ALTER TABLE xf_poll_response
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_poll_vote;
TRUNCATE TABLE xf_profile_post;
 
ALTER TABLE xf_profile_post
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_search;
TRUNCATE TABLE xf_search_index;
TRUNCATE TABLE xf_session;
TRUNCATE TABLE xf_session_activity;
TRUNCATE TABLE xf_thread_user_post;
TRUNCATE TABLE xf_thread_watch;
TRUNCATE TABLE xf_thread_read;
TRUNCATE TABLE xf_permission_cache_content_type;
TRUNCATE TABLE xf_permission_cache_global_group;
TRUNCATE TABLE xf_permission_cache_content;
 
 
TRUNCATE TABLE xf_warning;
 
ALTER TABLE xf_warning
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_moderator_log;
 
ALTER TABLE xf_moderator_log
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_forum_watch;
TRUNCATE TABLE xf_forum_read;
 
TRUNCATE TABLE xf_report;
TRUNCATE TABLE xf_report_comment;
 
ALTER TABLE xf_report
AUTO_INCREMENT = 1;
 
ALTER TABLE xf_report_comment
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_stats_daily;
TRUNCATE TABLE xf_user_confirmation;
 
ALTER TABLE xf_thread_read
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_attachment;
TRUNCATE TABLE xf_attachment_data;
TRUNCATE TABLE xf_attachment_view;
 
ALTER TABLE xf_attachment
AUTO_INCREMENT = 1;
 
ALTER TABLE xf_attachment_data
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_user_field_value;
TRUNCATE TABLE xf_user_field;
TRUNCATE TABLE xf_thread_prefix;
 
ALTER TABLE xf_thread_prefix
AUTO_INCREMENT = 1;
 
TRUNCATE TABLE xf_thread_prefix_group;
 
ALTER TABLE xf_thread_prefix_group
AUTO_INCREMENT = 1;
 

Coop1979

Well-known member
#13
Hi SchmitzIT and others - I'm going to finally perform my big migration this weekend from vB 3.8 to XF. I've been testing and tweaking my installation for a couple months now and want to be sure that I have the steps right to re-do my import.
This probably sounds like a dumb question, but can I do this migration using my testsite url and then change the urls in the settings later once my live url propagates to my new server?
 

Coop1979

Well-known member
#14
Bumping this thread for the afternoon crowd. Hoping to start my conversion tonight if I can get some confirmation regarding re-importing my data.
 

Jake Bunce

XenForo moderator
Staff member
#15
This probably sounds like a dumb question, but can I do this migration using my testsite url and then change the urls in the settings later once my live url propagates to my new server?
Yes, yes, and yes.
 

Valter

Active member
#16
Updated list for XF 1.2 to avoid errors.

DELETE FROM xf_user_group WHERE user_group_id >= 5;
DELETE FROM xf_user WHERE user_id != 1;
DELETE FROM xf_admin WHERE user_id != 1;
ALTER TABLE xf_user_group
AUTO_INCREMENT = 5;
ALTER TABLE xf_user
AUTO_INCREMENT = 2;
TRUNCATE TABLE xf_thread;
ALTER TABLE xf_thread
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_post;
ALTER TABLE xf_post
AUTO_INCREMENT = 1;
DELETE FROM xf_user_authenticate WHERE user_id != 1;
TRUNCATE TABLE xf_user_group_relation;
TRUNCATE TABLE xf_user_ban;
TRUNCATE TABLE xf_user_external_auth;
TRUNCATE TABLE xf_user_alert;
TRUNCATE TABLE xf_user_follow;
TRUNCATE TABLE xf_user_news_feed_cache;

DELETE FROM xf_user_option WHERE user_id > 1;
DELETE FROM xf_user_privacy WHERE user_id > 1;
DELETE FROM xf_user_profile WHERE user_id > 1;
TRUNCATE TABLE xf_user_status;
TRUNCATE TABLE xf_user_trophy;
TRUNCATE TABLE xf_admin_log;
ALTER TABLE xf_admin_log
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_conversation_master;
ALTER TABLE xf_conversation_master
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_conversation_message;
ALTER TABLE xf_conversation_message
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_conversation_recipient;
TRUNCATE TABLE xf_conversation_user;
TRUNCATE TABLE xf_error_log;
TRUNCATE TABLE xf_forum;
ALTER TABLE xf_forum
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_forum_prefix;
TRUNCATE TABLE xf_link_forum;
TRUNCATE TABLE xf_import_log;
TRUNCATE TABLE xf_ip;
ALTER TABLE xf_ip
AUTO_INCREMENT = 2;
TRUNCATE TABLE xf_liked_content;
ALTER TABLE xf_liked_content
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_moderation_queue;
TRUNCATE TABLE xf_moderator;
TRUNCATE TABLE xf_moderator_content;
ALTER TABLE xf_moderator_content
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_node;
ALTER TABLE xf_node
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_poll;
ALTER TABLE xf_poll
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_poll_response;
ALTER TABLE xf_poll_response
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_poll_vote;
TRUNCATE TABLE xf_profile_post;
ALTER TABLE xf_profile_post
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_search;
TRUNCATE TABLE xf_search_index;
TRUNCATE TABLE xf_session;
TRUNCATE TABLE xf_session_activity;
TRUNCATE TABLE xf_thread_user_post;
TRUNCATE TABLE xf_thread_watch;
TRUNCATE TABLE xf_thread_read;
TRUNCATE TABLE xf_permission_cache_content;
TRUNCATE TABLE xf_permission_group;
TRUNCATE TABLE xf_warning;
ALTER TABLE xf_warning
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_moderator_log;
ALTER TABLE xf_moderator_log
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_forum_watch;
TRUNCATE TABLE xf_forum_read;
TRUNCATE TABLE xf_report;
TRUNCATE TABLE xf_report_comment;
ALTER TABLE xf_report
AUTO_INCREMENT = 1;
ALTER TABLE xf_report_comment
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_stats_daily;
TRUNCATE TABLE xf_user_confirmation;
ALTER TABLE xf_thread_read
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_attachment;
TRUNCATE TABLE xf_attachment_data;
TRUNCATE TABLE xf_attachment_view;
ALTER TABLE xf_attachment
AUTO_INCREMENT = 1;
ALTER TABLE xf_attachment_data
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_user_field_value;
TRUNCATE TABLE xf_user_field;
TRUNCATE TABLE xf_thread_prefix;
ALTER TABLE xf_thread_prefix
AUTO_INCREMENT = 1;
TRUNCATE TABLE xf_thread_prefix_group;
ALTER TABLE xf_thread_prefix_group
AUTO_INCREMENT = 1;
 
#18
This seems to be working with XF1.5. Needed to just go through one or two tables that had some values that weren't deleted and truncate them, and change autoincrement to 1.