A phpBB2 to vBulletin conversion problem [Archive]

View Full Version : A phpBB2 to vBulletin conversion problem

Tanapangarap

Thu 20th Dec '07, 10:08pm

Hi--

Type of import (clean or merge). A merge, no previous import attempted.
Source board system and version. phpBB 2.0.12
Target version. vBulletin 3.6.8
Module issue occurred on. didn't even get to start

Any SQL error reported. See below
Me and my fellow admin, Saint, are trying to upgrade the forum to vBulletin, and we have hit our first major setback. Initially, we had the "connection to the source server failed" error, but Saint had the mind to realize that we should import the database for the phpBB forum to a database on the same server where the vBulletin installation is. I gave him the backup I downloaded by uploading it a temp directory in FTP for him to use as the source, In the mean time, I uploaded the backup of the database for the phpBB forum for Saint to use, and he tried to import it to the new database set alongside the same server that vBulletin is installed on, using bigdump since phpMyadmin has 2MB limit on import.

Further IM conversation about the matter:

[18:31] Saint: what the deuce?
[18:32] Hyperion: Me neither, but I'm looking at the MySQL documentation linked to in the database for phpBB. Maybe I'll find something.
[18:32] Hyperion: What happend now?
[18:32] Saint: well, i decided to try using the original database again.
[18:32] Saint: you know, in impex.
[18:33] Saint: and it says
[18:33] Saint: "There seems to have been a problem with the database.

ImpEx Database errormysql error: Invalid SQL: CREATE TABLE `vbfields` (
`fieldname` varchar(50) NOT NULL default '',
`tablename` varchar(20) NOT NULL default '',
`vbmandatory` enum('Y','N','A') NOT NULL default 'N',
`defaultvalue` varchar(200) default '!##NULL##!',

`dictionary` mediumtext NOT NULL,

`product` varchar(25) defa
[18:33] Saint: ult ''

)

mysql error: Table 'vbfields' already exists

mysql error number: 1050

Date: Thursday 20th 2007f December 2007 07:39:37 PM
Database: [ insert database name here. I removed it for security reason ]
MySQL error: Table 'vbfields' already exists
"
[18:34] Saint: and now when i try it again, it gives me the connection to source served failed error again.

Well, this time attempting to upload the backup worked for a few seconds, but now another error, as quoted from my fellow admin via IM:

Error at the line 193225: INSERT INTO `haruko_search_wordlist` VALUES ('nausica�', 114769, 0);

Query: INSERT INTO `haruko_search_wordlist` VALUES ('nausicaä', 114769, 0);

MySQL: Duplicate entry 'nausicaä' for key 1I'm beginning to think I should send a ticket so that I can provide the personal info.

Edit--

Attempted to upload a copy of a backup made with the option for dropping duplicates CHECKED. Yet got the same error.

Edit--

The following 27 files, which make up an estimated 2/3 of the total files from the database for the phpBB forum, show up in the database we're attempting to import to before the error quoted above appears. Those files are:

haruko_auth_access
haruko_banlist
haruko_categories
haruko_config
haruko_confirm
haruko_disallow
haruko_forums
haruko_forum_prune
haruko_groups
haruko_logbook
haruko_logins
haruko_modules
haruko_module_admin_panel
haruko_module_cache
haruko_module_group_auth
haruko_module_info
haruko_optimize_db
haruko_posts
haruko_posts_text
haruko_privmsgs
haruko_privmsgs_archive
haruko_privmsgs_text
haruko_proxies
haruko_ranks
haruko_search_phrases
haruko_search_results
haruko_search_wordlist

Saintv1

Fri 21st Dec '07, 1:44am

I'll try and make our problem a little more concise:

We're trying to convert our board from phpBB2 to VB, the latter being on a new server. When I tried using Impex, it told me that it could not connect to the source DB, so I guess our original DB isn't allowing the remote connection. Accordingly, we backed up the database and we're now trying to put it on a DB on the new server. However, we don't have SSH access, so we've been trying to use Bigdump (http://www.ozerov.de/bigdump.php). When I try to import the backup, it works normally for a while, but then produces the following error:

Error at the line 193254: INSERT INTO `haruko_search_wordlist` VALUES ('nausica�', 114769, 0);
Query: INSERT INTO `haruko_search_wordlist` VALUES ('nausicaä', 114769, 0);

MySQL: Duplicate entry 'nausicaä' for key 1

Can anybody lend a hand?

Steve Machol

Fri 21st Dec '07, 1:48am

See this page for a solution to the 'duplicate key' error:

http://www.vbulletin.com/docs/html/mysql_duplicatekey

Saintv1

Fri 21st Dec '07, 1:57am

Sorry, but I'm not sure how that relates to our problem.

Steve Machol

Fri 21st Dec '07, 2:02am

Sorry, but I'm not sure how that relates to our problem.
This:

It can also be caused by backing up a newer MySQL database with the "Compatibility Option" if this is the case, the MYSQL backup does not contain auto-increment information and the proper indexes are not created when the backup is restored.

Saintv1

Fri 21st Dec '07, 2:09am

What is the "Compatibility Option?" Sorry, I'm not exactly well-versed in this stuff. I don't see any such option when I export. The DB I'm backing up is using MySQL 4.0, if that makes a difference.

Steve Machol

Fri 21st Dec '07, 12:30pm

Which method did you use to make the backup? Which versions of MySQL are unning on the old and new servers?

Saintv1

Fri 21st Dec '07, 2:01pm

The old database is using MySQL 4.0, and the new database is using 4.1. I've been making the backup using the export function in phpmyadmin.

Someone has suggested to me that the reason this may be happening is because the old database and the new database are using different encoding schemes. While converting from one to the other, an accent sign is being lost, so the word "nausicaä" is now clashing with the same word accented in a different way.

To fix that, I tried changing the line "INSERT INTO `haruko_search_wordlist` VALUES ('nausicaä', 114769, 0);" to "REPLACE INTO `haruko_search_wordlist` VALUES ('nausicaä', 114769, 0);". This fixed the problem, but then I got another similar error for a different word (the word was "uber"). So I suppose I could perform a Find/Replace of "INSERT INTO `haruko_search_wordlist`" with "REPLACE INTO `haruko_search_wordlist`" and see if that works.

Jerry

Fri 21st Dec '07, 2:21pm

Looks like your restoring to a database where the tables already are and they aren't being dropped first.

Saintv1

Fri 21st Dec '07, 3:15pm

No; I'm dropping the tables before each attempt.

Jerry

Fri 21st Dec '07, 4:42pm

The vBfields error in the first post is usually from that, curious.

How big is the back up from the source and do you have SSH to the source server ?

Saintv1

Fri 21st Dec '07, 5:11pm

The vBfields error in the first post is usually from that, curious.

How big is the back up from the source and do you have SSH to the source server ?

140 megs, and no.

Saintv1

Fri 21st Dec '07, 5:15pm

Someone has suggested to me that the reason this may be happening is because the old database and the new database are using different encoding schemes. While converting from one to the other, an accent sign is being lost, so the word "nausicaä" is now clashing with the same word accented in a different way.

To fix that, I tried changing the line "INSERT INTO `haruko_search_wordlist` VALUES ('nausicaä', 114769, 0);" to "REPLACE INTO `haruko_search_wordlist` VALUES ('nausicaä', 114769, 0);". This fixed the problem, but then I got another similar error for a different word (the word was "uber"). So I suppose I could perform a Find/Replace of "INSERT INTO `haruko_search_wordlist`" with "REPLACE INTO `haruko_search_wordlist`" and see if that works.

I tried this, and it seemed to work, but towards the end of the restore, I got another similar error:

Error at the line 1594700: INSERT INTO `haruko_spelling_words` VALUES (55033, 0x656e74726563f4746573, 'ENTRKTS');
Query: INSERT INTO `haruko_spelling_words` VALUES (55033, 0x656e74726563f4746573, 'ENTRKTS');

MySQL: Duplicate entry 'entrec' for key 2

Jerry

Fri 21st Dec '07, 6:08pm

Then set the compatibility mode when using phpMyAdmin when grabbing the source initially for the versioin, and set the charset in bigdump.php when using it to restore.

bigdump.php :

// Connection character set should be the same as the dump file character set (utf8, latin1, cp1251, koi8r etc.)
// See http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html for the full list

$db_connection_charset = '';

Saintv1

Fri 21st Dec '07, 6:20pm

Jerry

Fri 21st Dec '07, 6:23pm

If you have no ssh access did you originally get the source database dump using phpMyAdmin or some other way ?

Saintv1

Fri 21st Dec '07, 7:06pm

I used phpmyadmin, but there's no compatibility mode option. The database I'm trying to backup is MySQL 4.0, and the database I'm trying to restore it to is 4.1, if that makes a difference.

Saintv1

Fri 21st Dec '07, 11:48pm

Okay, I got everything to work (I think). I've imported the board with Impex, with only 2 failed members, 1 failed thread, and 5 failed posts.

I've run into a couple of issues, though. At least one account could not be logged into, as if the user had entered an incorrect password. The password had to be set in the AdminCP before the user could login successfully. Only a few users have been testing the board, so I can't be sure if this was an isolated incident or a widespread problem at the moment.

The second issue is an error found at the top of member.php, which curiously only appears in the profiles of members who were mods or admins on the phpBB. The error is as follows:

Warning: mktime() expects parameter 4 to be long, string given in /member.php on line 551 Curiously, if I were to open such a user's profile in the admincp and then save it (even without making any changes) the error would vanish. I guess it's not an issue, since it's solved.

Jerry

Mon 24th Dec '07, 2:12pm

Typically a date issue with a user, though defaults are used it a valid one isn't present so I'm curious as to the nature and version of the source.

Is it all users or just a few ?