conversion from 2.0, duplicate usernames

[Sulla]

Hi,

I am restoring a Vbulletin board. I had several backups and have got VB up and running. It was quit a large board with over 100,000 posts and 8,000 ish users.

To make a long story short, I was/am a game developer, 43 yrs old, but about 5 years ago got very ill. This meant my company, employees, everything was lost. It has also left me with concentration issues etc. Where I used to be able to do 18+ hour days, now a thing of the past.

I have an idea for game and to start with I would like to get my old forums and websites back online. I am switching from VBulletin, as it is so bloody awful now. Seems almost every board I see is hacked or was recently hacked. So I wanted a decent, popular, secure, board that would allow me to import my old VBulletin users and posts. I have a VPN server and have installed both VB and phpbb.

Old VB Board:
http://www.closecombat.org/forums/index.php

New PHPBB board:
http://www.closecombat.org/csoforums/index.php?

The problems start when I try an run the converter/importer. It is set-up fine and says ready, but once I start importing, I get the following error msg.

-----------------------------------------------
"Fatal conversion error
Fatal conversion error

functions_vb30.php [ 1828 ]

Colliding usernames were found on your old board. In order to complete the conversion please delete or rename these users so that there is only one user on your old board for each clean username."
-----------------------------------------------

Now VB itself only allowed one copy of a user name, the board I am importing to is clean install, so no names.

What is the converter picking up as duplicate names and how do I fix this? I have 8000 names.
Is it picking up and expecting a unique email? How can I find out exactly what it considers colliding usernames?

Any help would be so greatly appreciated! Kind Regards - Shaun

[prototech]

phpBB doesn't allow usernames with characters that look alike, whereas vB does. For example: Testing and Test¡ng are both allowed in vB, but not in phpBB as they both produce the same "clean" string when run through utf8_clean_string(). There should be a list of colliding usernames below the "Colliding usernames were found..." message. It should be along the lines of "some_user is the clean username for:".

Try commenting out $db->sql_query($drop_sql); on line 1831 in install/convertors/functions_vb30.php then run the convertor again. If it's still not listing the usernames as indicated above (which it definitely should), then run this query:

Code: Select all

SELECT username_clean
		FROM phpbb_userconv
		GROUP BY username_clean
		HAVING COUNT(user_id) > 1;

Does that return any results?

[Sulla]

Hia Protech,

As I said, I am much slower now. That VB DB has been such a pain to even get running. But now its working fine want to get over to a more secure board ASAP.

Code: Select all

Ok, I tried //$db->sql_query($drop_sql);
 and 
<!-- $db->sql_query($drop_sql);  -->

Neither made any difference? Is that the correct way to comment the line out? Either of them?

How do I run the query that you suggested?

Code: Select all

CODE: SELECT ALL
SELECT username_clean
      FROM phpbb_userconv
      GROUP BY username_clean
      HAVING COUNT(user_id) > 1;

So want to get the users imported and actually start doing some useful stuff, the forums look like they have s many customisation and integration options!

Sorry, if am missing something obvious! Oh when I put in the settings, localhost will not work, I have to use 127.0.0.1, is that normal?

Regards - Shaun

[Sulla]

Oh,

On Usernames, would it pick up CSO_Fubar and CSO_Aussie as being the same as there are an awful lot with the CSO_ in front of the name.

Rgds - Shaun

[prototech]

Neither made any difference? Is that the correct way to comment the line out? Either of them?

The first one is the proper method. Commenting it out just ensures that database table where the info about colliding usernames is stored isn't dropped so that you can run the query on it afterwards.

How do I run the query that you suggested?

This is usually done in whichever database administration tool you have installed. This is usually phpMyAdmin. See: Executing SQL Queries in phpMyAdmin. Or simply save this as a file called collisions.php and put it in the phpBB board root directory. Run it and post the output.

Code: Select all

<?php

define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);

	$sql = 'SELECT username_clean
		FROM ' . $table_prefix . 'userconv
		GROUP BY username_clean
		HAVING COUNT(user_id) > 1';
	$result = $db->sql_query($sql);

	$colliding_names = array();
	while ($row = $db->sql_fetchrow($result))
	{
		$colliding_names[] = $row['username_clean'];
	}
	$db->sql_freeresult($result);

	// there was at least one collision, the admin will have to solve it before conversion can continue
	if (sizeof($colliding_names))
	{
		$sql = 'SELECT user_id, username_clean
			FROM ' . $table_prefix . 'userconv
			WHERE ' . $db->sql_in_set('username_clean', $colliding_names);
		$result = $db->sql_query($sql);
		unset($colliding_names);

		$colliding_user_ids = array();
		while ($row = $db->sql_fetchrow($result))
		{
			$colliding_user_ids[(int) $row['user_id']] = $row['username_clean'];
		}
		$db->sql_freeresult($result);

		$sql = 'SELECT username, userid, posts
			FROM ' . $convert->src_table_prefix . 'user
			WHERE ' . $src_db->sql_in_set('userid', array_keys($colliding_user_ids));
		$result = $src_db->sql_query($sql);

		$colliding_users = array();
		while ($row = $db->sql_fetchrow($result))
		{
			$row['userid'] = (int) $row['userid'];
			if (isset($colliding_user_ids[$row['userid']]))
			{
				$colliding_users[$colliding_user_ids[$row['userid']]][] = $row;
			}
		}
		$db->sql_freeresult($result);
		unset($colliding_user_ids);
	
		print_r($colliding_users);
	}
	exit_handler();
?>

On Usernames, would it pick up CSO_Fubar and CSO_Aussie as being the same as there are an awful lot with the CSO_ in front of the name.

No, those should be fine.

[Sulla]

Code: Select all

General Error
SQL ERROR [ mysqli ]

Table 'stratek5_php3forum.USERCONV_TABLE' doesn't exist [1146]

An sql error occurred while fetching this page. Please contact an administrator if this problem persists.

Hi, This is what I got after running the collisions.php in the forum root!

http://www.closecombat.org/csoforums/collisions.php

Any ideas, very welcome !

Cheers - Shaun

[Sulla]

Code: Select all

user_id Ascending
username_clean
1
sulla
2
sk_vonsoden
4
ice
5
cso_gunner
6
cso_soolla
7
cso_greypanther
8
cso_pixter
9
cso_redimpz
10
cso_axrael
11
cso prima donna
12
cso_toml
13
beeblebrox
14
vonb
15
melsang
16
cso_2milesniper
17
cso_fergusfrog
19
cso_iceman
20
bin-45
21
cso_jim_mcneil
22
cso_sbufkle
23
cso_vb
24
cso_jericho
25
cso_fubar
26
cso_aussie
27
cso_tokarev
28
jjvan
29
cso_geyer
30
cso_jacripper
31
cso_darter2
32
cso_janneb

The Users are all there under `userconv`

In the screenshot you can see it says user_id username_clean Ascending

So they are all there in that table, the ones as in the screen need cleaning? Or is that just a marker? In the table I can see the 2 fields user_id and username_clean.

What do I do from this point, oh the sql query ran but gave a syntax error.

Bit lost - Shaun

[prototech]

Sorry about that. I fixed the code in my previous post. Try running it again.

The table does indeed contain all usernames, although that doesn't mean that they're all colliding. The converter just inserts them there as it can't insert them into the users table until it checks for collisions because the username_clean field in the users table does not allow duplicate values.

[Sulla]

Hia Mat,

Ok, I replaced both sets of code. The one for collision.php and the query.

Collision gives me:

General Error
SQL ERROR [ mysqli ]

Table 'stratek5_php3forum.phpbb_userconv' doesn't exist [1146]

The SQL Query gives the following:
An sql error occurred while fetching this page. Please contact an administrator if this problem persists.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CODE: SELECT ALL SELECT username_clean FROM phpbb_userconv GROUP BY ' at line 1

It makes no sense, must be something really dopey I am doing! Hope importing the posts is easier than this lol

Am sorry mate, have attached screenshot of phpmyadmin, if that helps!

Cheers despondently Shaun

[prototech]

CODE: SELECT ALL isn't a part of the query, so remove that.

[Sulla]

Hia Mate,

CODE: SELECT ALL isn't a part of the query, so remove that.

I had removed it prior to running the query. Any ideas at all users would be so welcome mate.

Cheers - Shaun

[prototech]

Did you drop the table or remove the comment from functions_vb30.php? The table isn't listed in that last screenshot that you posted.

[Sulla]

I have not dropped or removed any comments etc mate.

Attached screen:

[prototech]

Hm I see.. the table name is missing the table prefix (phpbb_). Seems like a bug in the converter, although that should have no effect in this particular case.

The query should be the following then:

Code: Select all

SELECT username_clean
      FROM userconv
      GROUP BY username_clean
      HAVING COUNT(user_id) > 1;

[Sulla]

Hia Mate,

Code: Select all

SELECT username_clean
      FROM userconv
      GROUP BY username_clean
      HAVING COUNT(user_id) > 1;

I ran the query without the prefix on the userconv and got the following:

Code: Select all

SELECT username_clean
FROM userconv
GROUP BY username_clean
HAVING COUNT( user_id ) >1
LIMIT 0 , 30

			Start row:  Number of rows:  Headers every  rows

- Options
 Partial texts
 Full texts
Show binary contents
Show BLOB contents
Show binary contents as HEXHide browser transformation Geometry
 Well Known Text
 Well Known Binary

username_clean
aiden
david
hoth

I just wan to get the users and posts imported, so can get to work on the site.

There has to be more than 3 of either names? Or is it just counting to 30?

Sorry, need so much help with this mate, am kind of lost!

Cheers - Shaun