• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

MySQL Database downgrade from Crying Damson (0.3.2) to Mystic Spirit (0.2pl23)

trou

trou

ot revolution is comming
Senator
Joined
Aug 12, 2008
Messages
1,351
Reaction score
36
Downgrading database from 0.3.2 to 0.2 TEST VERSION

So Let's start.
1. Make backup of your exisitng database!:
Code: 
mysqldump -u<user> -p<password> <databasename> > backup.sql
For safety of course. To restore database use:
mysql -u<user> -p<password> <database> < backup.sql
2. Create file x.sql. Paste this code there:
Code: 
DROP TRIGGER IF EXISTS `oncreate_players`;
DROP TRIGGER IF EXISTS `oncreate_guilds`;
DROP TRIGGER IF EXISTS `ondelete_players`;
DROP TRIGGER IF EXISTS `ondelete_guilds`;
DROP TRIGGER IF EXISTS `ondelete_accounts`;

ALTER TABLE `groups` DROP `customflags`;
ALTER TABLE `groups` DROP `violationaccess`;
ALTER TABLE `players` DROP `balance`;
ALTER TABLE `players` DROP `stamina`;
ALTER TABLE `accounts` ADD `type` INT NOT NULL DEFAULT 1;
DROP TABLE `player_deaths`;
CREATE TABLE `player_deaths`
(
	`player_id` INT NOT NULL,
	`time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
	`level` INT NOT NULL DEFAULT 1,
	`killed_by` VARCHAR(255) NOT NULL,
	`altkilled_by` VARCHAR(255) NOT NULL,
        `is_player` INT NOT NULL,
	KEY (`player_id`),
	FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

DROP TABLE IF EXISTS `bans`;
CREATE TABLE `bans`
(
	`type` INT NOT NULL COMMENT 'this field defines if its ip, accountban or namelock',
	`ip` INT UNSIGNED NOT NULL DEFAULT 0,
	`mask` INT UNSIGNED NOT NULL DEFAULT 4294967295,
	`player` INT UNSIGNED NOT NULL DEFAULT 0,
	`account` INT UNSIGNED NOT NULL DEFAULT 0,
	`time` INT UNSIGNED NOT NULL DEFAULT 0,
	`reason_id` INT NOT NULL DEFAULT 0,
	`action_id` INT NOT NULL DEFAULT 0,
	`comment` VARCHAR(60) NOT NULL DEFAULT '',
	`banned_by` INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE = InnoDB;

ALTER TABLE `tiles` DROP KEY (`id`);
ALTER TABLE `tiles` DROP COLUMN `world_id`;
ALTER TABLE `tile_items` DROP KEY `tile_id`;
ALTER TABLE `tile_items` DROP KEY `sid`;
ALTER TABLE `tiles` ADD UNIQUE INDEX (`id`);
ALTER TABLE `tiles` ADD FOREIGN KEY (`tile_id`) REFERENCES `tiles`(`id`) ON DELETE CASCADE;


DELIMITER |

CREATE TRIGGER `ondelete_accounts`
BEFORE DELETE
ON `accounts`
FOR EACH ROW
BEGIN
    DELETE FROM `bans` WHERE `account` = OLD.`id`;
END|

CREATE TRIGGER `ondelete_guilds`
BEFORE DELETE
ON `guilds`
FOR EACH ROW
BEGIN
    UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`);
END|

CREATE TRIGGER `ondelete_players`
BEFORE DELETE
ON `players`
FOR EACH ROW
BEGIN
    DELETE FROM `bans` WHERE `type` = 2 AND `player` = OLD.`id`;
    UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
END|

CREATE TRIGGER `oncreate_guilds`
AFTER INSERT
ON `guilds`
FOR EACH ROW
BEGIN
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('the Leader', 3, NEW.`id`);
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('a Vice-Leader', 2, NEW.`id`);
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('a Member', 1, NEW.`id`);
END|

CREATE TRIGGER `oncreate_players`
AFTER INSERT
ON `players`
FOR EACH ROW
BEGIN
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10);
END|

DELIMITER ;

3.Now run:
Code: 
mysql -u<user> -p<password> <database> < x.sql

4.If your players were using account names you can provide them website interfrace to retrieve their account id.
Create subpage with following code:
Code: 
<?PHP
if($action == "")
{
	$main_content .= 'To play on '.$config['server']['serverName'].' you need an account. 
						All you have to do to create your new account is to enter your email address, password to new account, verification code from picture and to agree to the terms presented below. 
						If you have done so, your account number, password and e-mail address will be shown on the following page and your account and password will be sent 
						to your email address along with further instructions.<BR><BR>
						<FORM ACTION="index.php?subtopic=name&action=shownumber" onsubmit="return validate_form(this)" METHOD=post>
						<TABLE WIDTH=100% BORDER=0 CELLSPACING=1 CELLPADDING=4>
						<TR><TD BGCOLOR="'.$config['site']['vdarkborder'].'" CLASS=white><B>Create a '.$config['server']['serverName'].' Account</B></TD></TR>
						<TR><TD BGCOLOR="'.$config['site']['darkborder'].'"><TABLE BORDER=0 CELLSPACING=8 CELLPADDING=0>
						  <TR><TD>
						    <TABLE BORDER=0 CELLSPACING=5 CELLPADDING=0>';

		$main_content .= '<script type="text/javascript">var accountcustom=1;</script>
						  <TR><TD width="150" valign="top"><B>Account name: </B></TD><TD colspan="2"><INPUT id="account_number" NAME="reg_account"" VALUE="" SIZE=30 MAXLENGTH=50><BR><font size="1" face="verdana,arial,helvetica">(Wpisz account name)</font></TD></TR>
						';
	
		$main_content .= '<script type="text/javascript">var accountcustom=0;</script>';
	$main_content .= '<TR><TD width="150" valign="top"><B>Password: </B></TD><TD colspan="2"><INPUT TYPE="password" id="passor" NAME="reg_password" VALUE="" SIZE=30 MAXLENGTH=50><BR><font size="1" face="verdana,arial,helvetica">(Here write your password to new account on '.$config['server']['serverName'].')</font></TD></TR>';
					  
	if($config['site']['verify_code'] == 'yes')
		$main_content .= '<script type="text/javascript">var verifya=1;</script><TR><TD width="150"><B>Code: </B></TD><TD colspan="2"><img src="imgverification/imagebuilder.php" border="0" alt="Image Verification is missing, please contact the administrator"></TD></TR>
						  <TR><TD width="150" valign="top"><B>Verification Code: </B></TD><TD colspan="2"><INPUT id="verify" NAME="reg_code" VALUE="" SIZE=30 MAXLENGTH=50><BR><font size="1" face="verdana,arial,helvetica">(Here write verification code from picture)</font></TD></TR>';
	else
		$main_content .= '<script type="text/javascript">var verifya=0;</script>';
	$main_content .= '</TABLE>
					  </TD></TR>
					  <TR><TD>
					    <TABLE BORDER=0 CELLSPACING=5 CELLPADDING=0><TR><TD>
					       Please review the following terms and state your agreement below.
					    </TD></TR>
					    <TR><TD>
					      <B>'.$config['server']['serverName'].' Rules</B><BR>
					     <h1>Za chwilę zostanie wyświetlony Twój Account Number. Kliknij <b>I Agree</b> aby kontynuowac</h1>
					    </TD></TR></TABLE>
					  </TD></TR>
					  <TR><TD>
					    <TABLE BORDER=0 CELLSPACING=5 CELLPADDING=0>

					    <TR><TD>
					     
					    </TD></TR></TABLE>
					  </TD></TR>
					</TABLE></TD></TR>
					</TABLE>
					<BR>
					<TABLE BORDER=0 WIDTH=100%>
					  <TR><TD ALIGN=center>
					    <IMG SRC="'.$layout_name.'/images/general/blank.gif" WIDTH=120 HEIGHT=1 BORDER=0><BR>
					  </TD><TD ALIGN=center VALIGN=top>
					    <INPUT TYPE=image NAME="I Agree" SRC="'.$layout_name.'/images/buttons/sbutton_iagree.gif" BORDER=0 WIDTH=120 HEIGHT=18>
					    </FORM>
					  </TD><TD ALIGN=center>
					    <FORM  ACTION="index.php?subtopic=latestnews" METHOD=post>
					    <INPUT TYPE=image NAME="Cancel" SRC="'.$layout_name.'/images/buttons/sbutton_cancel.gif" BORDER=0 WIDTH=120 HEIGHT=18>
					    </FORM>
					  </TD><TD ALIGN=center>
					    <IMG SRC="/images/general/blank.gif" WIDTH=120 HEIGHT=1 BORDER=0><BR>
					  </TD></TR>
					</TABLE>
					</TD>
					<TD><IMG SRC="'.$layout_name.'/images/general/blank.gif" WIDTH=10 HEIGHT=1 BORDER=0></TD>
					</TR>
					</TABLE>';
}
if($action == "shownumber") {
$posted_reg_account = trim($_POST['reg_account']);

	$reg_password = trim($_POST['reg_password']);
	$reg_code = trim($_POST['reg_code']);


	if($config['site']['verify_code'] == 'yes')
	{
		//check verification code
		$string = strtoupper($_SESSION['string']);
		$userstring = strtoupper($reg_code);
		session_destroy();
		if(empty($string))
			$reg_form_errors[] = "Information about verification code in session is empty.";
		else
		{
			if(empty($userstring))
				$reg_form_errors[] = "Please enter verification code.";
			else
			{
				if($string != $userstring)
					$reg_form_errors[] = "Verification code is incorrect.";
			}
		}
	}
	//check password
	if(empty($reg_password))
		$reg_form_errors[] = "Please enter password to your account.";

	if(empty($reg_form_errors))
	{

		//create object 'account' and generate new acc. number
		$ac_id = $SQL->query('SELECT id FROM `accounts` WHERE `name`='.$SQL->quote($posted_reg_account).' AND`password` = '.$SQL->quote($reg_password).'   LIMIT 1;');//$_REQUEST['reg_account']
		while($data = $ac_id->fetch()) {
		print_r($data);
		if(!empty($data['id'])) {
			
			$main_content .= '<BR><BR>';
		$main_content .= '<TABLE WIDTH=100% BORDER=0 CELLSPACING=1 CELLPADDING=4>
		<TR><TD BGCOLOR="'.$config['site']['vdarkborder'].'" CLASS=white><B>Account ID</B></TD></TR>
		<TR><TD BGCOLOR="'.$config['site']['darkborder'].'">
		  <TABLE BORDER=0 CELLPADDING=1><TR><TD>
		    <BR><FONT SIZE=5>Twój account number to: <B>'.$data['id'].'</B></FONT><BR><BR>';
		
			$main_content .= '</TD></TR></TABLE>
		</TD></TR>
		</TABLE><BR><BR>';
		}
		else {
			$main_content .='<h1>Prawdopodobnie podałeś błędne dane</h1>';
			}
			}
		// saves account information in database

		//show information about registration
		
	}
	else
	{
		//SHOW ERRORs if data from form is wrong
		$main_content .= '<div class="SmallBox" >  <div class="MessageContainer" >    <div class="BoxFrameHorizontal" style="background-image:url('.$layout_name.'/images/content/box-frame-horizontal.gif);" /></div>    <div class="BoxFrameEdgeLeftTop" style="background-image:url('.$layout_name.'/images/content/box-frame-edge.gif);" /></div>    <div class="BoxFrameEdgeRightTop" style="background-image:url('.$layout_name.'/images/content/box-frame-edge.gif);" /></div>    <div class="ErrorMessage" >      <div class="BoxFrameVerticalLeft" style="background-image:url('.$layout_name.'/images/content/box-frame-vertical.gif);" /></div>      <div class="BoxFrameVerticalRight" style="background-image:url('.$layout_name.'/images/content/box-frame-vertical.gif);" /></div>      <div class="AttentionSign" style="background-image:url('.$layout_name.'/images/content/attentionsign.gif);" /></div><b>The Following Errors Have Occurred:</b><br/>';
		foreach($reg_form_errors as $show_msg)
		{
					$main_content .= '<li>'.$show_msg;
		}
		$main_content .= '</div>    <div class="BoxFrameHorizontal" style="background-image:url('.$layout_name.'/images/content/box-frame-horizontal.gif);" /></div>    <div class="BoxFrameEdgeRightBottom" style="background-image:url('.$layout_name.'/images/content/box-frame-edge.gif);" /></div>    <div class="BoxFrameEdgeLeftBottom" style="background-image:url('.$layout_name.'/images/content/box-frame-edge.gif);" /></div>  </div></div><br/>
		<BR>
		<CENTER>
		<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0><FORM ACTION=index.php?subtopic=name METHOD=post><TR><TD>
		<INPUT TYPE=hidden NAME=email VALUE="">

		<INPUT TYPE=image NAME="Back" ALT="Back" SRC="'.$layout_name.'/images/buttons/sbutton_back.gif" BORDER=0 WIDTH=120 HEIGHT=18>
		</TD></TR></FORM></TABLE>
		</CENTER>';
	}
	}
	

?>



If there will be any errors - paste in this topic or via PM.
 
Start 0.3.2 normally, then change in config.lua to save in normal mode, than use command /save or /closesever and houses should be saved in tile_items table.
 
mickula said:
Start 0.3.2 normally, then change in config.lua to save in normal mode, than use command /save or /closesever and houses should be saved in tile_items table.
Click to expand...

ok i will but tried u that too or u are using on ur server relational house storage?
 
Yes, i've tried it and it worked.
 
Yes it worked. But now there's no point to change.
 
With Plain password type your php code works but when you use SHA1 Password type it do not.
 
ALTER TABLE `tiles` DROP KEY ( `id` ) ;

MySQL zwrócił komunikat: Dokumentacja
#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 '(`id`)' at line 1
 
what does Za chwile zostanie wyswietlony Twój Account Number. Kliknij I Agree aby kontynuowac means? xD
and Wpisz account name

I get this error after I write the account name and the new password
Invalid subtopic. Can't load page.
 
Last edited:
You should give a tutorial for no linux users, also what about spells, scripts, quests, acions, movements changes?
 
i tried 0.3.4 pl2 to 0.2.5 and
SQL query:

ALTER TABLE `groups` DROP `customflags` ;

MySQL said: Documentation
#1146 - Table 'database.groups' doesn't exist

also


SQL query:

ALTER TABLE `tiles` DROP KEY ( `id` ) ;

MySQL said: Documentation
#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 '(`id`)' at line 1
 

Similar threads

Czorcik
  • Question
Problem with Gesior Acc HIGHSCORE (offline player)
Replies
2
Views
481
Czorcik
Czorcik
V
  • Question
AAC reset system geisor
Replies
0
Views
290
vitorelias1
V
ralke
  • Question
AAC ZnoteAAC show killing assists at characterprofile deathlist
Replies
0
Views
178
ralke
ralke
blokerzinha
  • Question
Problem with the wrong player appearing as top even though he is not. Znote
Replies
1
Views
336
blokerzinha
blokerzinha
Back
Top