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

XF 1.4 Error whilst importing DB.

Discussion in 'Troubleshooting and Problems' started by TDUBS, Apr 6, 2015.

  1. TDUBS

    TDUBS Active Member


    I am in the process of moving my forums to a new server. I use Navicat for MySQL amd I'm trying to import my database on the new server and I'm receiving 60+ errors and they all are in relation to the following:

    [Err] 1062 - Duplicate entry 'Forum' for key 'node_name_unique'
    Causing these:
    [Err] INSERT INTO xf_node VALUES("9","General Discussion","Discuss anything related to Gaming!","","Forum","8","2","1","42","43","1","0","0","a:1:{i:8;a:7:{s:7:\"node_id\";i:8;s:9:\"node_name\";N;s:12:\"node_type_id\";s:8:\"Category\";s:5:\"title\";s:7:\"Gaming\";s:5:\"depth\";i:0;s:3:\"lft\";i:25;s:3:\"rgt\";i:58;}}");
    Any ideas?

  2. Brogan

    Brogan XenForo Moderator Staff Member

    How did you make the dump and how are you doing the restore?
  3. TDUBS

    TDUBS Active Member

    As I don't have root access to the previous server (friend who's now inactive hosts it), I have written a script which backs up my entire database and I can just get it via FTP. As far as the restore, I am trying to import the SQL file via Navicat. I can share the script with you if you'd like to review it.
  4. Mike

    Mike XenForo Developer Staff Member

    Based on the specific text of the error, that sounds like it dumped the index incorrectly. The referenced index name has 2 columns.

    You'll need to open up the dump and fine the line containing "node_name_unique" and paste it here. Note if your dump is large, you may need to be careful about which program/text editor you open it with. Some (I'd probably say most) don't handle large files. (There are some recommendations of programs here: http://stackoverflow.com/questions/159521/text-editor-to-open-big-giant-huge-large-text-files)
  5. TDUBS

    TDUBS Active Member

    CREATE TABLE `xf_node` (
      `node_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `title` varchar(50) NOT NULL,
      `description` text NOT NULL,
      `node_name` varchar(50) DEFAULT NULL COMMENT 'Unique column used as string ID by some node types',
      `node_type_id` varbinary(25) NOT NULL,
      `parent_node_id` int(10) unsigned NOT NULL DEFAULT '0',
      `display_order` int(10) unsigned NOT NULL DEFAULT '1',
      `display_in_list` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT 'If 0, hidden from node list. Still counts for lft/rgt.',
      `lft` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Nested set info ''left'' value',
      `rgt` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Nested set info ''right'' value',
      `depth` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Depth = 0: no parent',
      `style_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Style override for specific node',
      `effective_style_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Style override; pushed down tree',
      `breadcrumb_data` blob,
      PRIMARY KEY (`node_id`),
      UNIQUE KEY `node_name_unique` (`node_name`,`node_type_id`),
      KEY `parent_node_id` (`parent_node_id`),
      KEY `display_order` (`display_order`),
      KEY `display_in_list` (`display_in_list`,`lft`),
      KEY `lft` (`lft`)
  6. Mike

    Mike XenForo Developer Staff Member

    Hmm actually looking more closely, it might be a bad dump. The query in question has a node_name value of "" when that should be null. You mentioned writing a script to do the backup so you're going to need to change that to handle data types more gracefully (nulls in particular).
  7. TDUBS

    TDUBS Active Member

    Do you mind if I PM you to review the script?
  8. Mike

    Mike XenForo Developer Staff Member

    It's not really something I have scope to take on. I'd really have to recommend using an off the shelf solution (ideally mysqldump).
  9. TDUBS

    TDUBS Active Member

    Don't you need root access to the server in order to do this? I'd gladly do it if root access wasn't needed.
  10. imthebest

    imthebest Formerly Super120

    Ask your host to run a mysqldump on your database and provide you the output file.
  11. TDUBS

    TDUBS Active Member

    I managed to find a script on StackOverflow someone gave when they were looking for a script which will execute mysqldump. I had to modify my MySQL ini file and made the net_buffer_length to 100M and max_allowed_packet to 100M in order to import it on the new server. The below script works like a charm if anyone would like to use it:

        $toDay = date('d-m-Y');
        $dbhost = 'localhost';
        $dbuser = 'username';
        $dbpass = 'password';
        $dbname = 'database';
        exec("mysqldump --user=$dbuser --password='$dbpass' --host=$dbhost $dbname > /full path to where you want your db to save to/".$toDay."_DB.sql");

Share This Page