[Msg] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation‏

[Friendly]

Trying to backup my server and It says this, I messaged my host to see if they could fix it but is there anyway I can do this because they haven't replied in 24 hours! Really need to get my server back online.

[Friendly]

Knowone knows?

[Defent]

Who is your host and how are you trying to back it up?

[Friendly]

Who is your host and how are you trying to back it up?

I use HFB and I use Navicat to do my backups once every 30 minutes but I cant import the backup until  they give me give me SUPER Privileges and they haven't replied to my ticked for 48 hours!

[Defent]

There is no backup function on your control panel? I'm not familiar wit HFB, sorry.

[Friendly]

No HFB does not give a backup function, that is why I use navicat.

[GeneralTragedy]

You're not going to get "super" rights from HFB... the response is going to be it's a security issue.  You might try to create a new table and copy the data from your backup into it (or stop your server, delete everything from the existing table, and use it...), rather than trying to use the restore process.  Moving forward, you'd be better off using the mechanism I do for managing this.

 

What I do is create events that run every hour to copy the table data to backup tables.  Then when you need to do a rollback, you just delete the current table and rename the backup table you want to use to the proper name.  I also have an event that runs and cleans up the old backup tables after a period of time (I have it set for 2 days...).  Here's the SQL I use for the key tables...

 

Character Data:

DROP EVENT IF EXISTS character_data_backup;

DELIMITER $$
CREATE EVENT character_data_backup
ON SCHEDULE EVERY 1 HOUR
STARTS Now()
COMMENT 'Backs up character data'
DO
BEGIN
SET @vCurrentTime := (DATE_FORMAT(now(),'%Y_%m_%d_%H_%i_%s'));
SET @vMyTablename := CONCAT("`user-XXXX`.`character_data_backup_",@vCurrentTime,"`");
SET @vMyCreateTable := CONCAT("CREATE TABLE ", @vMyTablename, " ( `CharacterID` int( 11 ) unsigned NOT NULL AUTO_INCREMENT ,",
" `PlayerUID` varchar( 20 ) CHARACTER SET latin1 NOT NULL DEFAULT '0',",
" `InstanceID` int( 11 ) NOT NULL DEFAULT '0',",
" `Datestamp` datetime DEFAULT NULL ,",
" `LastLogin` datetime NOT NULL ,",
" `Inventory` longtext CHARACTER SET latin1,",
" `Backpack` longtext CHARACTER SET latin1,",
" `Worldspace` varchar( 128 ) CHARACTER SET latin1 NOT NULL DEFAULT '[]',",
" `Medical` varchar( 300 ) CHARACTER SET latin1 NOT NULL DEFAULT '[]',",
" `Alive` tinyint( 3 ) unsigned NOT NULL DEFAULT '1',",
" `Generation` int( 11 ) unsigned NOT NULL DEFAULT '1',",
" `LastAte` datetime NOT NULL ,",
" `LastDrank` datetime NOT NULL ,",
" `KillsZ` int( 11 ) unsigned NOT NULL DEFAULT '0',",
" `HeadshotsZ` int( 11 ) unsigned NOT NULL DEFAULT '0',",
" `DistanceFoot` int( 11 ) NOT NULL DEFAULT '0',",
" `Duration` int( 11 ) NOT NULL DEFAULT '0',",
" `CurrentState` varchar( 200 ) CHARACTER SET latin1 NOT NULL DEFAULT '[]',",
" `KillsH` int( 11 ) unsigned NOT NULL DEFAULT '0',",
" `Model` varchar( 50 ) CHARACTER SET latin1 NOT NULL DEFAULT '",'"Survivor2_DZ"',"',",
" `KillsB` int( 11 ) unsigned NOT NULL DEFAULT '0',",
" `Humanity` int( 11 ) NOT NULL DEFAULT '2500',",
" `Infected` tinyint( 3 ) DEFAULT '0',",
" `last_updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,",
" PRIMARY KEY ( `CharacterID` ) ,",
" KEY `PlayerUID` ( `PlayerUID` ) USING BTREE,",
" KEY `Alive` ( `Alive` ) USING BTREE ) ENGINE = InnoDB DEFAULT CHARSET = utf8;");
PREPARE stmt1 FROM @vMyCreateTable;
EXECUTE stmt1;

SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

SET @vMyInsertData := CONCAT("INSERT INTO ",@vMyTablename," SELECT * FROM `user-XXXX`.`character_data`;");
PREPARE stmt2 FROM @vMyInsertData;
EXECUTE stmt2;

DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;
END $$

 

Object Data:

DROP EVENT IF EXISTS object_data_backup;

DELIMITER $$
CREATE EVENT object_data_backup
ON SCHEDULE EVERY 1 HOUR
STARTS Now()
COMMENT 'Backs up object data'
DO
BEGIN
SET @vCurrentTime := (DATE_FORMAT(now(),'%Y_%m_%d_%H_%i_%s'));
SET @vMyTablename := CONCAT("`user-XXXX`.`object_data_backup_",@vCurrentTime,"`");
SET @vMyCreateTable := CONCAT("CREATE TABLE ", @vMyTablename, " ( `ObjectID` int( 11 ) unsigned NOT NULL AUTO_INCREMENT ,",
" `ObjectUID` bigint( 24 ) NOT NULL DEFAULT '0',",
" `Instance` int( 11 ) unsigned NOT NULL ,",
" `Classname` varchar( 50 ) CHARACTER SET latin1 DEFAULT NULL ,",
" `Datestamp` datetime NOT NULL ,",
" `LastUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,",
" `CharacterID` int( 11 ) unsigned NOT NULL DEFAULT '0',",
" `Worldspace` varchar( 128 ) CHARACTER SET latin1 NOT NULL DEFAULT '[]',",
" `Inventory` longtext CHARACTER SET latin1,",
" `Hitpoints` varchar( 512 ) CHARACTER SET latin1 NOT NULL DEFAULT '[]',",
" `Fuel` double( 13, 5 ) NOT NULL DEFAULT '1.00000',",
" `Damage` double( 13, 5 ) NOT NULL DEFAULT '0.00000',",
" PRIMARY KEY ( `ObjectID` ) ,",
" KEY `ObjectUID` ( `ObjectUID` ) USING BTREE,",
" KEY `Instance` ( `Instance` ) USING BTREE ) ENGINE = InnoDB DEFAULT CHARSET = utf8;");
PREPARE stmt1 FROM @vMyCreateTable;
EXECUTE stmt1;

SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

SET @vMyInsertData := CONCAT("INSERT INTO ",@vMyTablename," SELECT * FROM `user-XXXX`.`object_data`;");
PREPARE stmt2 FROM @vMyInsertData;
EXECUTE stmt2;

DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;
END $$

 

Trader Data:

DROP EVENT IF EXISTS traders_data_backup;

DELIMITER $$
CREATE EVENT traders_data_backup
ON SCHEDULE EVERY 1 DAY
STARTS Now()
COMMENT 'Backs up traders data'
DO
BEGIN
SET @vCurrentTime := (DATE_FORMAT(now(),'%Y_%m_%d_%H_%i_%s'));
SET @vMyTablename := CONCAT("`user-XXXX`.`traders_data_backup_",@vCurrentTime,"`");

SET @vMyCreateTable := CONCAT("CREATE TABLE ",@vMyTablename," ( `id` int( 8 ) NOT NULL AUTO_INCREMENT ,",
" `item` varchar( 255 ) CHARACTER SET latin1 NOT NULL COMMENT '[Class Name,1 = CfgMagazines | 2 = Vehicle | 3 = Weapon]',",
" `qty` int( 8 ) NOT NULL COMMENT 'amount in stock available to buy',",
" `buy` varchar( 255 ) CHARACTER SET latin1 NOT NULL COMMENT '[[Qty,Class,Type],]',",
" `sell` varchar( 255 ) CHARACTER SET latin1 NOT NULL COMMENT '[[Qty,Class,Type],]',",
" `order` int( 2 ) NOT NULL DEFAULT '0' COMMENT '# sort order for addAction menu',",
" `tid` int( 8 ) NOT NULL COMMENT 'Trader Menu ID',",
" `afile` varchar( 64 ) CHARACTER SET latin1 NOT NULL DEFAULT 'trade_items',",
" PRIMARY KEY ( `id` ) ,",
" UNIQUE KEY `item` ( `item` , `tid` ) ) ENGINE = InnoDB DEFAULT CHARSET = utf8;");
PREPARE stmt1 FROM @vMyCreateTable;
EXECUTE stmt1;

SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

SET @vMyInsertData := CONCAT("INSERT INTO ",@vMyTablename," SELECT * FROM `user-XXXX`.`traders_data`;");
PREPARE stmt2 FROM @vMyInsertData;
EXECUTE stmt2;

DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;
END $$

 

Player Data:

DROP EVENT IF EXISTS player_data_backup;

DELIMITER $$
CREATE EVENT player_data_backup
ON SCHEDULE EVERY 1 HOUR
STARTS Now()
COMMENT 'Backs up player data'
DO
BEGIN
SET @vCurrentTime := (DATE_FORMAT(now(),'%Y_%m_%d_%H_%i_%s'));
SET @vMyTablename := CONCAT("`user-XXXX`.`player_data_backup_",@vCurrentTime,"`");
SET @vMyCreateTable := CONCAT("CREATE TABLE ",@vMyTablename," (`PlayerUID` varchar(20) CHARACTER SET latin1 NOT NULL DEFAULT '0',"," `PlayerName` varchar(128) NOT NULL DEFAULT 'Null',"," `PlayerMorality` int(11) NOT NULL DEFAULT '0',"," `PlayerSex` tinyint(3) unsigned NOT NULL DEFAULT '0',"," PRIMARY KEY (`PlayerUID`)) ENGINE = InnoDB DEFAULT CHARSET = utf8;");
PREPARE stmt1 FROM @vMyCreateTable;
EXECUTE stmt1;

SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

SET @vMyInsertData := CONCAT("INSERT INTO ",@vMyTablename,"SELECT * FROM `user-XXXX`.`player_data`;");
PREPARE stmt2 FROM @vMyInsertData;
EXECUTE stmt2;

DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt2;
END $$

 

Old Table Cleanup:

DROP EVENT IF EXISTS table_backup_cleanup;

DELIMITER $$
CREATE EVENT table_backup_cleanup
ON SCHEDULE EVERY 3 HOUR
STARTS Now()
COMMENT 'Cleans up old table backups'
DO
BEGIN
SET SESSION group_concat_max_len = 500000;
SELECT GROUP_CONCAT(table_name) FROM INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE '%backup%'
AND DATE_ADD(CREATE_TIME, INTERVAL 2 DAY) < now() INTO @myVar;

SET @vDropQuery := CONCAT("DROP TABLE ", @myVar,";");
PREPARE stmt FROM @vDropQuery;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END $$

 

A few things to note... you will need to change the value user-XXXX to whatever your DB username is.  Also, you can tweak the frequency of the jobs by adjusting the schedule parameter set in the event (i.e. "ON SCHEDULE EVERY 1 HOUR" can be "ON SCHEDULE EVERY 30 MINUTE" or whatever).  And, if your table names are different case (i.e. some databases use "Character_DATA" instead of "character_data", as the mixed case is the SQL the devs provide), you will need to adjust the scripts because case does matter. Finally, you'll probably notice some tables are missing.  That's because they don't store any data that actually changes, just reference data.  So, you can just reload these using the default Epoch scripts if you need to.

 

Hope that helps,

GT

[Friendly]

So there is no way for me to import the backup I have, because when I tried to roll the server back it reset all the tables to default so my server is as if knowone has played on it before...

[GeneralTragedy]

So there is no way for me to import the backup I have, because when I tried to roll the server back it reset all the tables to default so my server is as if knowone has played on it before...

 

You could try this...

 

- Install mySql on your local computer (if it's not already)

- Restore your databases locally

- EXPORT the data to a SQL file

- Import the data using the import function of the phmyadmin control panel on your HFB server

 

or

 

- EXPORT the data and structure to a SQL file

- Run the SQL statement which will create the tables and data

 

These are just a couple of options for getting data into your SQL database, besides a "restore".

 

GT

[Friendly]

Thanks got it working, I just went into the sql and imported each table on at a time.

[GeneralTragedy]

Good deal, glad you found a solution that worked for you.  Really like HFB as a hosting provider, but they have some limitations that can be a pain.  Not as many as some other providers, however.

[Friendly]

Good deal, glad you found a solution that worked for you.  Really like HFB as a hosting provider, but they have some limitations that can be a pain.  Not as many as some other providers, however.

Ya they ignored my ticket and just changed the status to answered without answering anything lol

[BetterDeadThanZed]

Ya they ignored my ticket and just changed the status to answered without answering anything lol

 

That's typical of them. You might want to think about switching providers when you can. I'm happy with Vilayer.