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

XF 1.1 Help importing data from VB using raw sql query

Discussion in 'Installation, Upgrade, and Import Support' started by puppi, May 26, 2013.

  1. puppi

    puppi New Member

    I think it would be brilliant to be able to import data to XenForo using raw sql instead of the migration tool.
    My particular concern here to migrate from clunky VB 4 to slick XF 1.1 :)

    The reasons being that:
    1) Migration tool is inevitably much slower than raw sql query
    2) By using raw sql, one may avoids many php and webserver-specific pitfalls that might cause migration tool to fail (something that actually bugging me).

    So I looked at the schemas and quickly put together these queries to migrate posts and threads:

    Migrate posts:

    migrate threads:

    However, as for forums migration, I'm having difficulty because the table structure of the VB and XF diverge here and I'm by no means an expert in data migration.

    Here are my (failed) attempted queries:

    migrate forums:
    I just need basic migration of forum->thread->post . Not fancy about thanks, profile etc. So If you have and share the queries or know how to fixed mine, I'd be greatful.
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

  3. puppi

    puppi New Member

    Thanks for the reference. However I think, a basic CLI importer is not far reaching and should be available as a free community solution. I continue trying to make correct queries while the migration tools is striving :)
  4. dbembibre

    dbembibre Active Member

    I take your queries to migrate my 12 million of posts, the thread query is incorrect because you miss the node_id->forumid relation. This is the correct query.

    INSERT IGNORE INTO xf_thread (nodeid, thread_id, title, reply_count, view_count, user_id, username, post_date, sticky,
    first_post_id, last_post_date, last_post_id, last_post_user_id, last_post_username) SELECT
    forumid, threadid, title, replycount, views, postuserid, postusername, dateline, sticky, firstpostid, lastpost,
    lastpostid,postuserid, lastposter FROM forum3_6.thread;

    As i run the incorrect query execute the update query to relationate threads and nodes.

    mysql> select count(node_id) from xf_thread where node_id not in (select forumid from forum3_6.thread);
    | count(node_id) |
    | 524452 |
    1 row in set (6.48 sec)

    mysql> UPDATE xf_thread SET xf_thread.node_id = (SELECT forum3_6.thread.forumid FROM forum3_6.thread
    -> WHERE forum3_6.thread.threadid = xf_thread.thread_id);

Share This Page