duplicate id

[skyline]

hi i have an question

i have 2 servers and now i want to conflate the 2 servers to only 1 but the problem is some chars have the same id

so how is it possible to insert all the data without losses?

[jurchiks]

gotta change the ids

[skyline]

and how? because manually is very hard with so much data

[jurchiks]

well sorry, but what did you expect?
you have to generate random unused IDs and put them instead of duplicate ones, since afaik when starting GS, it deletes duplicate entrys instead of fixing them

[janiii]

well sorry, but what did you expect?
you have to generate random unused IDs and put them instead of duplicate ones, since afaik when starting GS, it deletes duplicate entrys instead of fixing them

it is not about gameserver cleaning up, but about primary key restrictions in tables. so already inserting duplicate data into tables will produce sql error, before even starting up the server. the duplicate entry error has nothing to do with gs.

[jurchiks]

well the essence of it all is - he needs to change the IDs manually if he doesn't want to disappoint his players
maybe there is some complex script/query to do this automatically, but i REALLY doubt that

[takhs7]

lol and IF u manage to get pass the char_id (primary key) thingy then what? .. maybe.. just maybe.. there will be ALOT of players with same nickname? what u will do then? rename them all ?

why dont u just forget it..

first time i saw someone trying to merge 2 servers.. rofl

[skyline]

it's always the first time...if anybody have ideas he can post it

now i make it over mysql generation

[macdonald12]

lol and IF u manage to get pass the char_id (primary key) thingy then what? .. maybe.. just maybe.. there will be ALOT of players with same nickname? what u will do then? rename them all ?

why dont u just forget it..

Think of and apply algorithm to duplicate ids and names until you've got unique ones. Not really much of an 'IF'...

first time i saw someone trying to merge 2 servers.. rofl

Probably says more about the people you 'see' then the complexity of this problem

You could write a php script and let the users with an used username choose a new one themselves, problem solved...

[UnAfraid]

Or just write a php script which will transfer all chars

I'm working on script for the same and you can try this.

http://pastebin.com/ZpGxVmJH
FOR SERVER FROM WHICH YOU WANNA TO TRANSFER
You need to configure database settings and some tables if you want them or don't.
it's coded for CLI php that means you need to run it `php merge.php` // if you named the file merge.php
It will works with apache too but you need to increase the memory_limit in php.ini and put some brs

http://pastebin.com/x591dNmc
FOR SERVER WHICH YOU WANT TO TRANSFER

http://sourceforge.net/projects/adodb/files/
The database class which script is using. You will need it to run the scripts.

MAKE BACKUP BEFORE YOU USE THEM!!


EDIT: They are for linux OS soon will post for windows..

[denser]

Or just write a php script which will transfer all chars
I'm working on script for the same and you can try this.
........................
EDIT: They are for linux OS soon will post for windows..

Man, i check the script and find it good enough for my purpose
could you confirm my guess?
my aim is: i want join characters from one server to another, but there different account names.
as i suppose - your script ideal for shared login server db?
and what is file adodb/adodb.inc.php ?

[UnAfraid]

AAA download the adodb class http://sourceforge.net/projects/adodb/files/

Mmm.. try this i think it will help you

First copy from secondary server tables:
characters as characters2
accounts as accounts2

then run the script

Code: Select all

 <?phprequire_once('adodb/adodb.inc.php');set_time_limit(0);if (!ini_get('display_errors')){	ini_set('display_errors', 1);}error_reporting(E_ALL);// Primary Serverdefine('MySQL_USER', 'root'); // Default: root but is not recommended!!define('MySQL_PASS', 'pass'); // Default: define('MySQL_DB', 'l2jdb'); // Default: l2jdbdefine('MySQL_HOST', '127.0.0.1'); // Default: 127.0.0.1 $DB = NewADOConnection('mysql://' . MySQL_USER . ':' . MySQL_PASS . '@' . MySQL_HOST . '/' . MySQL_DB. '?persist'); // Connecting to MySQL serverif (!$DB) {	die('Wrong mysql connect configuration please try again: ' . $DB->ErrorMsg());} define('PATTERN', '1'); // If there is a duplicate what we will append to account ?$acc2 = $DB->GetAll('SELECT login FROM accounts2 ORDER BY login'); // Copy your second server's account table into main database server foreach($acc2 as $row) { 	if (!empty($row[0]))	{		$count = $DB->Execute('SELECT COUNT(*) FROM accounts WHERE login = ?', array($row[0])); 		if ($count > 1) // so this is duplicate what we gona do with it?		{			echo 'Duplicate found for ' , $row[0] , PHP_EOL;			$update = $DB->Execute('UPDATE accounts2 SET login = ? WHERE login = ?', array($row[0] . PATTERN, $row[0]));			$update2 = $DB->Execute('UPDATE characters2 SET account_name = ? WHERE account_name = ?', array($row[0] . PATTERN, $row[0]));			if ($update && $update2)				echo 'Duplicate fixed.' , PHP_EOL;			else				echo 'Failed updating duplicate entry please use another pattern..' , PHP_EOL;		}	} }echo 'Duplicates are fixed ' , PHP_EOL;?> 

after that just dump those tables and import them in original tables

you can use notepad++ for massive replace via ctrl + f
characters2 => characters
and
accounts2 => accounts

btw i didn't tested it yet try it and if you had some problems write here and will try to solve them and please do backups

[Naminator_X_]

Approach 1

1st You extract the data from server 2 to an SQL file.
2nd You start writing algorithm with PHP to extract this data
3rd You go to MySQL.com and read some documentation about INSERT INTO ..... ON DUPLICATE
4th You finish your script.
5th...Don't forget to use set_time_limit(0) in the php script...or you could end up with half merged database

Tip: Close the server while you merge the data and do it table by table don't automate the whole work in 1 loop

Approach 2

1st You extract the data from server 2 to an sql file
2nd You check which IDs are reserved and store them in temporary database/tables/whatever
3rd Make a script in which the affected users can enter and register again with their current character data

Tip: Don't forget to delete the records after user pre-registers or he can double/triple..etc his fortune


Good luck !

[UnAfraid]

5: If you look on my script you will see set_time_limit on every file

Tip: you are right in one loop will not happen i'm starting to write it on C# it will be more fast and i think will be better

when i did it i will share the final script

[UnAfraid]

Finally i did it only with mysql

need only one php script for name duplicates i will post it soon.

So how it works i just add + 1000000000 to all charId's to avoid possible duplicates with them.

After update the database dump those tables and just insert them on primary server.

will be better if delete all chars which are not logged since 60 (I choses 60 ) days and < 76 lvl this mysql query:

Code: Select all

DELETE FROM characters WHERE characters.level < 76 AND DATEDIFF(CURRENT_DATE( ) , FROM_UNIXTIME(`lastaccess`/1000)) > 60;

Code: Select all

-- items TableUPDATE `items` SET `object_id` = `object_id` + 1000000000;UPDATE `items` SET `owner_id` = `owner_id` + 1000000000; -- item_attributes TableUPDATE `item_attributes` SET `itemId` = `itemId` + 1000000000;  -- characters TableUPDATE `characters` SET `charId` = `charId` + 1000000000;UPDATE `characters` SET `clanid` = `clanid` + 1000000000; -- clan_data TableUPDATE `clan_data` SET `clan_id` = `clan_id` + 1000000000;UPDATE `clan_data` SET `ally_id` = `ally_id` + 1000000000;UPDATE `clan_data` SET `leader_id` = `leader_id` + 1000000000;UPDATE `clan_data` SET `crest_id ` = `crest_id ` + 1000000000;UPDATE `clan_data` SET `crest_large_id ` = `crest_large_id ` + 1000000000;UPDATE `clan_data` SET `ally_crest_id ` = `ally_crest_id ` + 1000000000; -- clan_notices TableUPDATE `clan_notices` SET `clan_id` = `clan_id` + 1000000000; -- clan_privs TableUPDATE `clan_privs` SET `clan_id` = `clan_id` + 1000000000; -- clan_skills TableUPDATE `clan_skills` SET `clan_id` = `clan_id` + 1000000000; -- clan_subpledges TableUPDATE `clan_subpledges` SET `clan_id` = `clan_id` + 1000000000;UPDATE `clan_subpledges` SET `leader_id` = `leader_id` + 1000000000; -- clan_wars TableUPDATE `clan_wars` SET `clan1` = `clan1` + 1000000000;UPDATE `clan_wars` SET `clan2` = `clan2` + 1000000000; -- character_friends tableUPDATE `character_friends` SET `charId` = `charId` + 1000000000; -- character_hennas tableUPDATE `character_hennas` SET `charId` = `charId` + 1000000000; -- character_instance_time tableUPDATE `character_instance_time` SET `charId` = `charId` + 1000000000; -- character_macroses tableUPDATE `character_macroses` SET `charId` = `charId` + 1000000000; -- character_premium_items tableUPDATE `character_premium_items` SET `charId` = `charId` + 1000000000; -- character_quest_global_data tableUPDATE `character_quest_global_data` SET `charId` = `charId` + 1000000000; -- character_quests tableUPDATE `character_quests` SET `charId` = `charId` + 1000000000; -- character_raid_points tableUPDATE `character_raid_points` SET `charId` = `charId` + 1000000000; -- character_recipebook tableUPDATE `character_recipebook` SET `charId` = `charId` + 1000000000; -- character_recipeshoplist tableUPDATE `character_recipeshoplist` SET `charId` = `charId` + 1000000000; -- character_recommends tableUPDATE `character_recommends` SET `charId` = `charId` + 1000000000; -- character_shortcuts tableUPDATE `character_shortcuts` SET `charId` = `charId` + 1000000000; -- character_skills tableUPDATE `character_skills` SET `charId` = `charId` + 1000000000; -- character_skills_save tableUPDATE `character_skills_save` SET `charId` = `charId` + 1000000000; -- character_subclasses tableUPDATE `character_subclasses` SET `charId` = `charId` + 1000000000; -- character_tpbookmark tableUPDATE `character_tpbookmark` SET `charId` = `charId` + 1000000000; -- olympiad_nobles tableUPDATE `olympiad_nobles` SET `charId` = `charId` + 1000000000;