bigdump error during upgrade, not sure what it means

[fraherg]

hi all
i wasin the process of running bigdump as my database backup was 140meg, so pretty big, but bigdump failed on line 403000 with an error regarding a duplicate entry, so i googled the error and didnt return any results, so as the bckup is being restored to a test forum (as its my first time doing this) i simply deleted the offending line, i re-ran bigdump and got exactly the same error for the next line down, the error is as follows, but i am not sure what it means or more to the point what to do lol, i am new to SQL

Error:
Error at the line 405500: INSERT INTO phpbb_search_wordlist (word_text, word_id, word_common) VALUES('pat '51416', '0');

Query: INSERT INTO phpbb_search_wordlist (word_text, word_id, word_common) VALUES('patê', '51416', '0');


MySQL: Duplicate entry 'patfor key 1

I have checked the backup and in the back up and it is as follows
INSERT INTO phpbb_search_wordlist (word_text, word_id, word_common) VALUES('patê', '51416', '0');

does it look like something is wrong on the line?

[D¡cky]

There is nothing wrong with that line. The problem is the non-latin character. Setting BigDump to use utf8 may fix the problem.
OPEN bigdump.php and FIND

Code: Select all

$db_connection_charset = '';

REPLACE WITH

Code: Select all

$db_connection_charset = 'utf8';

Moving to 3.0.x Support as this appears not to be related to a conversion.

[fraherg]

hi, i tried that and changed the correct line in the bigdump file then retried bigdump but it failed on the same line with the same error

[stevemaury]

What upgrade are you doing that requires a database backup? Are you converting from version 2 to version 3?

But in any event, make another backup and do not include in it any of the three search_* tables. If you are converting from version 2 to version 3, those tables are not converted anyway, and if you are doing something else, those tables can be rebuilt.

To rebuild them after restoring a backup without those tables, first run this SQL:

Code: Select all

# Table: 'phpbb_search_results'
CREATE TABLE phpbb_search_results (
   search_key varchar(32) DEFAULT '' NOT NULL,
   search_time int(11) UNSIGNED DEFAULT '0' NOT NULL,
   search_keywords mediumtext NOT NULL,
   search_authors mediumtext NOT NULL,
   PRIMARY KEY (search_key)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;


# Table: 'phpbb_search_wordlist'
CREATE TABLE phpbb_search_wordlist (
   word_id mediumint(8) UNSIGNED NOT NULL auto_increment,
   word_text varchar(255) DEFAULT '' NOT NULL,
   word_common tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
   word_count mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
   PRIMARY KEY (word_id),
   UNIQUE wrd_txt (word_text),
   KEY wrd_cnt (word_count)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;


# Table: 'phpbb_search_wordmatch'
CREATE TABLE phpbb_search_wordmatch (
   post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
   word_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
   title_match tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
   UNIQUE unq_mtch (word_id, post_id, title_match),
   KEY word_id (word_id),
   KEY post_id (post_id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

And then recreate the search index under the Maintenance tab in the ACP.

[fraherg]

hi
cheers for the reply, what i want to do is upgrade my board to phpbb3, so have set up a test forum but thought i had to convert the phpbb2 board, so i took a backup of the live board, then set up a test phpbb2 install and am trying to restore the back up to this, then will run the phpbb3 convertor tool, or is there a way i can upgrade to phpbb3 using the backup that i have?

[D¡cky]

If you want to use the backup you have and don't want to create another backup as stevemaury suggested, you will need to remove the search_wordlist and search_wordmatch from the backup file so it can be inserted. Or, you could keep removing the duplicate lines when BigDump errors out. The conversion to phpBB3 does not convert the search tables. They need to be rebuilt after the conversion.

[Brf]

There is no need to create a copy of the phpBB2 board like that, unless you plan on converting it to a different server.
The upgrade to phpBB2 will only copy the data out of the phpBB2 into the phpBB3. It leaves the old phpBB2 intact.

[fraherg]

hi
my phpbb2 forum ison a different url & hosting package so was not sure how to do it, do i just bigdump it on the new phpbb3 server or just use the onvert tool and point somehow to the phpbb2 forum ? sorry if thatsa stupid question i am new to this lol

[Mick]

Information on how to convert from phpBB2 to phpBB3 is here.

Basically, you must have both phpBB2 and phpBB3 installed on the same server. After you have finished installing phpBB3 you will have the option to convert your phpBB2 board using the built in converter. The information it asks you for is related to the phpBB2 installation and can be found in its config.php file on the server. All of your users, posts, forums, PMs etc are converted. However, styles and MODs are not (with the exception of the birthday and attachment MODs which are supported by the converter). The phpBB2 database is not converted per se, it is merely read and converted/moved to the new phpBB3 database, so if it goes wrong your old board will not be affected at all. At the end of the conversion, once you're happy it's all working, your phpBB2 database and files can be backed up and removed if you so wish. They are not connected to the new installation in any way.

Then, when you have your new phpBB3 board working OK follow this to move to a new server.

Knowledge Base - Moving Your phpBB3 Forum To A New Host Using phpMyAdmin.

[D¡cky]

If you already have phpBB2 and phpBB3 installed on the new server, you can continue in the direction you are going by importing the phpBB2 database backup in the database on the new server. You will also want a copy of the phpBB2 files from the old server on the new server. This is so your avatars, smilies and attachments will be converted to phpBB3. Make a backup of the phpBB2 files from the old server and upload all the files except for config.php.

So.... you have two choices. Install phpBB3 on the old server, do the conversion and transfer the phpBB3 database and files (except for config.php) to the new server, or transfer the phpBB2 database and files (except for config.php) to the new server and run the conversion. If you do the conversion on the old server, do not build the search index until after you have transferred phpBB3 the new server.