1. 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

Discussion in 'Installation, Upgrade, and Import Support' started by bubbl3, Jun 4, 2011.

  1. bubbl3

    bubbl3 Active Member

    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 :)
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
     
    bubbl3 likes this.
  3. SchmitzIT

    SchmitzIT Well-Known Member

    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.
     
    bubbl3 likes this.
  4. Luke F

    Luke F Well-Known Member

    Same here, just truncated a whole bunch of tables. Far easier than reinstalling from scratch and merging.
     
    bubbl3 likes this.
  5. Brogan

    Brogan XenForo Moderator Staff Member

    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.
     
    bubbl3 likes this.
  6. bubbl3

    bubbl3 Active Member

    I was of the idea to clean all the tables like users, threads, posts, attachments, etc. Anyone of you has list of the ones he cleaned?
     
  7. SchmitzIT

    SchmitzIT Well-Known Member

    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;
    
    
     
    Jake Bunce and bubbl3 like this.
  8. bubbl3

    bubbl3 Active Member

    Thanks SchmitzIT, that was more than i hoped for, much appreciated :)
     
  9. SchmitzIT

    SchmitzIT Well-Known Member

    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 :)
     
    bubbl3 likes this.
  10. high1976

    high1976 Active Member

    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;
    
     
  11. bubbl3

    bubbl3 Active Member

    Thanks for the update, i will need it very soon :)
     
  12. SchmitzIT

    SchmitzIT Well-Known Member

    If you run into any trouble, please don't hesitate to get in touch :)
     
    bubbl3 likes this.
  13. Coop1979

    Coop1979 Well-Known Member

    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?
     
  14. Coop1979

    Coop1979 Well-Known Member

    Bumping this thread for the afternoon crowd. Hoping to start my conversion tonight if I can get some confirmation regarding re-importing my data.
     
  15. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Yes, yes, and yes.
     
  16. Valter

    Valter Active Member

    Updated list for XF 1.2 to avoid errors.

     
    visulet and Jake Bunce like this.
  17. Jim McClain

    Jim McClain Member

    Are there any changes to this list for XF 1.4.5? Is each line of this list a separate query, or are there multiple-line queries.

    Thank you,

    Jim
     
  18. Ivo Pereira

    Ivo Pereira New Member

    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.
     
  19. mmg

    mmg New Member

    The above script from @Valter worked with 1.5.11, just used it today.
     
    Valter likes this.

Share This Page