Fixed Duplicate entry 'Alex' for key 2

Claudio

Well-known member
When I´m importing users from IPB 3.3 to the latest Xenforo version, I get this error at the middle of the process:

Server Error

Mysqli statement execute error : Duplicate entry 'Alex' for key 2
  1. Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
  2. Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
  3. Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 574
  4. Zend_Db_Adapter_Abstract->insert() in XenForo/DataWriter.php at line 1591
  5. XenForo_DataWriter->_insert() in XenForo/DataWriter.php at line 1580
  6. XenForo_DataWriter->_save() in XenForo/DataWriter.php at line 1381
  7. XenForo_DataWriter->save() in XenForo/Model/Import.php at line 782
  8. XenForo_Model_Import->importUser() in XenForo/Importer/IPBoard.php at line 1169
  9. XenForo_Importer_IPBoard->_importUser() in XenForo/Importer/IPBoard.php at line 935
  10. XenForo_Importer_IPBoard->_importOrMergeUser() in XenForo/Importer/IPBoard.php at line 726
  11. XenForo_Importer_IPBoard->stepUsers() in XenForo/Importer/Abstract.php at line 77
  12. XenForo_Importer_Abstract->runStep() in XenForo/ControllerAdmin/Import.php at line 180
  13. XenForo_ControllerAdmin_Import->_runStep() in XenForo/ControllerAdmin/Import.php at line 132
  14. XenForo_ControllerAdmin_Import->actionImport() in XenForo/FrontController.php at line 310
  15. XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 132
  16. XenForo_FrontController->run() in /home/testforum/admin.php at line 13
What does this mean?
 
This error seems to appear when there are 2 usernames with the same name (strange, not?, how can it be?), so I´ve changed the name of the user Alex to Alex2 and I´m going to try if this fix the issue..
 
That's a username collision. Two users are not allowed to have the same username. The importer should detect this and give you the option to merge the users. This check is apparently failing for some reason.

This might be a character encoding problem. I found this post which may be relevant here:

http://xenforo.com/community/threads/vbulletin-4-importer.5726/page-31#post-263445

I just queried in phpmyadmin for the key username küppi and there is only one entry.
There is also a entry "kuppi" - a different user which differs in the char ü/u
Maybe the german umlauts makes the trouble?

Update: I just renamed the user kuppi and whohooo the import goes ahead. Seams the importer have trouble with the umlauts. My db-charset is latin1

In his case he had two different but similar usernames in the source database which apparently became the same username during the conversion. Check your IPB user records for names that are similar to Alex.
 
well, I can´t understand why for Xenforo there are 2 usernames with the same name and for IPB there aren´t....

Suppose that I have 2 usernames: "Alex" and "Alex Rb".
This might produce a username collision? I don´t understand the concept...
 
That's a username collision. Two users are not allowed to have the same username. The importer should detect this and give you the option to merge the users. This check is apparently failing for some reason.

This might be a character encoding problem. I found this post which may be relevant here:

http://xenforo.com/community/threads/vbulletin-4-importer.5726/page-31#post-263445



In his case he had two different but similar usernames in the source database which apparently became the same username during the conversion. Check your IPB user records for names that are similar to Alex.
well, I can´t understand why for Xenforo there are 2 usernames with the same name and for IPB there aren´t....

Suppose that I have 2 usernames: "Alex" and "Alex Rb".
This might produce a username collision? I don´t understand the concept...
 
That's a username collision. Two users are not allowed to have the same username. The importer should detect this and give you the option to merge the users. This check is apparently failing for some reason.

This might be a character encoding problem. I found this post which may be relevant here:

http://xenforo.com/community/threads/vbulletin-4-importer.5726/page-31#post-263445



In his case he had two different but similar usernames in the source database which apparently became the same username during the conversion. Check your IPB user records for names that are similar to Alex.

which of those user names do you think are causing this conflict?

list.webp
 
Jake is right. Do you have phpmyadmin at hand? Check the user table directly. I had the same problem with importing a large DB, had 2 users, one correct and one "corupt" user with strange characters.
 
Jake is right. Do you have phpmyadmin at hand? Check the user table directly. I had the same problem with importing a large DB, had 2 users, one correct and one "corupt" user with strange characters.
Well, the table has 50.000 users, so this is going to be a hard work.
How can I search what I don´t know what I´m looking for?

I´ve already tried to search through IPB the user álex, aléx but I don´t find anyone.
How should I search this username that is conflicting?
 
My duplicate user was something like "Alex" and "$$((AlexS**$$##"

When you search the term Alex, how may result does it produce? Look for weird characters and try renaming them.
 
If the import is currently stuck on that error then I can debug it if you give me FTP and admin access. I can isolate the source user.
 
FYI for others. To debug this I edited this file:

library/XenForo/Importer/IPBoard.php

I added the red code:

Rich (BB code):
	protected function _importOrMergeUser(array $user, array $options = array())
	{
		$im = $this->_importModel;

		if ($user['email'] && $emailMatch = $im->getUserIdByEmail($this->_convertToUtf8($user['email'])))
		{
			if (!empty($options['mergeEmail']))
			{
				return $this->_mergeUser($user, $emailMatch);
			}
			else
			{
				if ($im->getUserIdByUserName($this->_convertToUtf8($user['name'], true)))
				{
					$this->_session->setExtraData('userMerge', $user['member_id'], 'both');
				}
				else
				{
					$this->_session->setExtraData('userMerge', $user['member_id'], 'email');
				}
				return false;
			}
		}

		if ($nameMatch = $im->getUserIdByUserName($this->_convertToUtf8($user['name'], true)))
		{
			if (!empty($options['mergeName']))
			{
				return $this->_mergeUser($user, $nameMatch);
			}
			else
			{
				$this->_session->setExtraData('userMerge', $user['member_id'], 'name');
				return false;
			}
		}
echo $user['name'] . ' - ' . $user['member_id'] . '<br />';
		return $this->_importUser($user, $options);
	}

Then I refreshed the error page during the import. It printed each source username and source member_id (what IPB calls the user_id). The last username / member_id that is printed before the error is the source user that is causing the error. In this case the problem user was:

�� Alex ��

Apparently those weird characters were being stripped during the import which resulted in a duplicate username with another "Alex" on the forum. To fix the error you can manually change that username in the source database.
 
I never actually tried to fix this bug on your forum. Rather I just had you change the conflicting name in the source database, a conflict which the importer was failing to detect.

I was finally able to reproduce this error using my own test case with the vBulletin importer. The nature of the problem I found is that the importer only does a rtrim when checking for duplicate usernames (XenForo_Importer_vBulletin::_importOrMergeUser), but the datawriter does a full trim as part of the type casting for TYPE_STRING (XenForo_DataWriter::_castValueToType). So you can get a situation like this:

Target user:
"username"

Source user:
" username "

The importer trims the right space, checks for duplicates, finds none. Then it passes the name to the datawriter to be written out to the database. The datawriter does a full trim before writing the user record thereby resulting in a username collision.

The fix is to do a full trim in the importer when checking for a duplicate username:

XenForo_Importer_vBulletin::_importOrMergeUser

XenForo_Importer_PhpBb3::_importOrMergeUser

XenForo_Importer_IPBoard::_importOrMergeUser

This is for my specific test case using the vBulletin importer. I still have not been able to create a scenario like yours with the IPB importer where "�� Alex ��" is imported as 'Alex' and fails the dupe check.
 
I never actually tried to fix this bug on your forum. Rather I just had you change the conflicting name in the source database, a conflict which the importer was failing to detect.

I was finally able to reproduce this error using my own test case with the vBulletin importer. The nature of the problem I found is that the importer only does a rtrim when checking for duplicate usernames (XenForo_Importer_vBulletin::_importOrMergeUser), but the datawriter does a full trim as part of the type casting for TYPE_STRING (XenForo_DataWriter::_castValueToType). So you can get a situation like this:

Target user:
"username"

Source user:
" username "

The importer trims the right space, checks for duplicates, finds none. Then it passes the name to the datawriter to be written out to the database. The datawriter does a full trim before writing the user record thereby resulting in a username collision.

The fix is to do a full trim in the importer when checking for a duplicate username:

XenForo_Importer_vBulletin::_importOrMergeUser

XenForo_Importer_PhpBb3::_importOrMergeUser

XenForo_Importer_IPBoard::_importOrMergeUser

This is for my specific test case using the vBulletin importer. I still have not been able to create a scenario like yours with the IPB importer where "�� Alex ��" is imported as 'Alex' and fails the dupe check.
This is like chinese for me but if this will help to improve Xenforo I´d like to give you thanks :D
 
Thanks Mike. I just ran a test import vB3->XF, and stumbled over various duplicate user entries, such as tram / träm and Rüdiger / rudiger. What I don't understand is that even when I try to edit the database directly (have one Rüdiger and one rudiger user), I would also get a duplicate entry error. If I search with `username` = 'rudiger', I would get Rüdiger as the result. My xf_user table is utf8_general_ci as it is supposed to be. o_O

For exact string matching in a query use this clause:

Code:
...
WHERE BINARY username = 'rudiger'

That should restrict the matching rows like you want.
 
Top Bottom