XF 1.4 Error whilst importing DB.

[TDUBS]

Hello,

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?

Thanks!

 

[Paul B]

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

 

[TDUBS]

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.

 

[Mike]

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)

 

[TDUBS]

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`)
) ENGINE=InnoDB AUTO_INCREMENT=117 DEFAULT CHARSET=utf8;

 

[Mike]

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

 

[TDUBS]

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

Do you mind if I PM you to review the script?

 

[Mike]

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

 

[TDUBS]

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

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.

 

[imthebest]

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

 

[TDUBS]

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

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:

<?php
    $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");


?>