[Err] 1607 - Cannot create stored routine `p_dbg_checkIdRangeConsistant_movable_objects`. Check warnings
[Err] CREATE PROCEDURE `p_dbg_checkIdRangeConsistant_movable_objects`()
COMMENT 'Check consistant of content of movable_objects_server_id_ranges table. OK when no rows returned, else shows range intersections'
BEGIN
select * from `movable_objects_server_id_ranges` r1
where exists (
select * from `movable_objects_server_id_ranges` r2 where r2.ID != r1.ID
and (
(r1.RangeEndID between r2.RangeStartID and r2.RangeEndID) or
(r1.RangeStartID between r2.RangeStartID and r2.RangeEndID) or
(r1.RangeStartID <= r2.RangeStartID and r1.RangeEndID >= r2.RangeEndID)
)
);
END;
[Err] 1607 - Cannot create stored routine `p_dbg_checkIdRangeConsistant_unmovable_objects`. Check warnings
[Err] CREATE PROCEDURE `p_dbg_checkIdRangeConsistant_unmovable_objects`()
COMMENT 'Check consistant of content of unmovable_objects_server_id_ranges table. OK when no rows returned, else shows range intersections'
BEGIN
select * from `unmovable_objects_server_id_ranges` r1
where exists (
select * from `unmovable_objects_server_id_ranges` r2 where r2.ID != r1.ID
and (
(r1.RangeEndID between r2.RangeStartID and r2.RangeEndID) or
(r1.RangeStartID between r2.RangeStartID and r2.RangeEndID) or
(r1.RangeStartID <= r2.RangeStartID and r1.RangeEndID >= r2.RangeEndID)
)
);
END;
[Err] 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 ';
end;
select EquipmentContainerID, RootContainerID from `character` whe' at line 12
[Err] CREATE PROCEDURE `p_deleteCharacter`(
IN `inCharID` INT UNSIGNED,
IN `inAccID` INT UNSIGNED
)
BEGIN
declare eqContID, invContID int unsigned default null;
declare isCharFound tinyint unsigned default 0; -- not found by default
declare exit handler for sqlexception
begin
rollback;
resignal;
end;
select EquipmentContainerID, RootContainerID from `character` where id = inCharID and AccountID = inAccID
into eqContID, invContID;
if(eqContID is not null and invContID is not null) then
set isCharFound = 1; -- found
START TRANSACTION;
DELETE FROM skills WHERE CharacterID = inCharID;
DELETE FROM equipment_slots WHERE CharacterID = inCharID;
DELETE FROM character_wounds WHERE CharacterID = inCharID;
DELETE FROM character_titles WHERE CharacterID = inCharID;
-- DELETE FROM guest_links WHERE characterId = inCharID;
-- personal_lands -> claims -> (claim_rules, unmovable_objects_claims)
-- DELETE FROM claim_rules WHERE ClaimID IN (SELECT ID FROM claims WHERE PersonalLandID IN (SELECT ID FROM personal_lands WHERE CharID = inCharID));
-- DELETE FROM unmovable_objects_claims WHERE ClaimID IN (SELECT ID FROM claims WHERE PersonalLandID IN (SELECT ID FROM personal_lands WHERE CharID = inCharID));
-- DELETE FROM claims WHERE PersonalLandID IN (SELECT ID FROM personal_lands WHERE CharID = inCharID);
-- DELETE FROM personal_lands WHERE CharID = inCharID;
-- claim_subjects -> (claim_rules, claim_rules_unmovable)
-- DELETE FROM claim_rules WHERE ClaimSubjectID IN (SELECT ID FROM claim_subjects WHERE CharID = inCharID);
-- DELETE FROM claim_rules_unmovable WHERE ClaimSubjectID IN (SELECT ID FROM claim_subjects WHERE CharID = inCharID);
-- DELETE FROM claim_subjects WHERE CharID = inCharID;
-- DELETE FROM minigame_results WHERE characterID = inCharID;
DELETE FROM food_eaten WHERE CharID = inCharID;
-- DELETE FROM guild_actions WHERE CharID = inCharID;
-- DELETE FROM guild_actions WHERE ProducerCharID = inCharID;
DELETE FROM chars_deathlog WHERE CharID = inCharID;
DELETE FROM chars_deathlog WHERE KillerID = inCharID;
DELETE FROM skill_raise_logs WHERE PlayerID = inCharID;
UPDATE movable_objects SET CarrierCharacterID = NULL WHERE CarrierCharacterID = inCharID;
UPDATE movable_objects SET OwnerID = NULL, DroppedTime = 0 WHERE OwnerID = inCharID;
UPDATE unmovable_objects SET OwnerID = NULL, DroppedTime = 0 WHERE OwnerID = inCharID;
-- _cm_old_cmLocks
-- UPDATE geo_data SET PrivatePropertyPlayerID=0 WHERE PrivatePropertyPlayerID = inCharID;
-- /_cm_old_cmLocks
DELETE FROM `character` WHERE ID = inCharID;
CALL f_deleteContainer(eqContID);
CALL f_deleteContainer(invContID);
COMMIT;
end if;
select isCharFound as `found`;
END;
[Err] 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 ';
END;
START TRANSACTION;
SELECT RootContainerID FROM movable_obj' at line 11
[Err] CREATE PROCEDURE `p_deleteMovableObject`(
IN `inID` INT UNSIGNED
)
BEGIN
declare cID INT UNSIGNED DEFAULT NULL;
-- SELECT RootContainerID, GeoDataID FROM movable_objects WHERE ID = inID INTO cID, geoID FOR UPDATE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
SELECT RootContainerID FROM movable_objects WHERE ID = inID INTO cID FOR UPDATE;
DELETE FROM movable_objects WHERE ID = inID;
if(cID is not null) then
CALL f_deleteContainer( cID);
end if;
/*
if(geoID is not null) then
UPDATE geo_data SET MovableObjectsCount = MovableObjectsCount -1
WHERE ID = geoID AND MovableObjectsCount >0;
end if;
*/
COMMIT;
END;
[Err] 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 ';
END;
START TRANSACTION;
select RootContainerID
from unmovabl' at line 11
[Err] CREATE PROCEDURE `p_deleteUnmovableObject`(
in_ID INT UNSIGNED
-- in_sideObjectMask TINYINT UNSIGNED
)
BEGIN
declare cID int unsigned default null;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
select RootContainerID
from unmovable_objects
where ID = in_ID
into cID
for update;
/*
if(in_sideObjectMask = 0) then
-- truly unmovable object
-- side object have no records in geo_data.HaveUnmovableObject, so update below is redundant. Rewrite it if you really need it
-- reset HaveUnmovableObject where our object was single one
update geo_data geo
join
(
select t.GeoDataID
from
(
select ug.GeoDataID, ug.UnmovableObjectID
from unmovable_geodata ug
left join geo_data geo on geo.ID = ug.GeoDataID
where ug.UnmovableObjectID = in_ID
)
as t
where not exists(select * from unmovable_geodata where GeoDataID = t.GeoDataID and UnmovableObjectID != t.UnmovableObjectID)
) as t
on t.GeoDataID = geo.ID
set HaveUnmovableObject = 0;
else
-- remove side object mask from SubCellsObjectMask
update geo_data
set SubCellsObjectMask = (SubCellsObjectMask & ~in_sideObjectMask)
where ID = geoID;
end if;
*/
-- delete object
-- DELETE FROM unmovable_geodata WHERE UnmovableObjectID = in_ID;
DELETE FROM stables_logs WHERE UnmovableObjectID = in_ID;
DELETE FROM stables_pens WHERE UnmovableObjectID = in_ID;
UPDATE `character` SET BindedObjectID=null WHERE BindedObjectID = in_ID;
DELETE FROM unmovable_objects WHERE ID = in_ID;
-- delete container
if(cID is not null) then
CALL f_deleteContainer( cID);
end if;
/* if(geoID is not null) then
UPDATE geo_data
SET HaveUnmovableObject = (CASE WHEN EXISTS(SELECT * FROM unmovable_objects WHERE GeoDataID = geoID) THEN 1 ELSE 0 END)
WHERE ID = geoID;
end if;
*/
COMMIT;
END;
[Err] 1607 - Cannot create stored routine `p_issueIdRange_movable_objects`. Check warnings
[Err] CREATE PROCEDURE `p_issueIdRange_movable_objects`(
in_serverID INT UNSIGNED,
in_idCount INT UNSIGNED
)
MODIFIES SQL DATA
COMMENT 'Return movable_objects IDs which available for concrete server to insert'
BEGIN
declare rangeID, startID, endID, currFreeIdCount, currMaxUsedId int unsigned default 0;
declare errorFlag, cursorDone tinyint unsigned default FALSE;
declare rangeCursor cursor for
(
select ID, RangeStartID, RangeEndID
from `movable_objects_server_id_ranges`
where ServerID = in_serverID
order by RangeStartID -- ordering for consecutive ID using
for update
);
declare continue handler for not found set cursorDone = TRUE;
-- mysql does not stop procedure execution on errors, so do it manually
declare continue handler for sqlexception
begin
-- "leave this_sp;" can't be called here, so use flag
set errorFlag = TRUE;
end;
start transaction;
-- We using the movable_objects_server_id_ranges_lock table like a mutex - lock it at transaction start,
-- and release it on commit/rollback. This ugly solution provides us a 100% deadlock protection when
-- this procedure runs simultaneously from several sessions.
-- If you don't care about deadlocks, you can simply skip this insert - all locking logic below
-- still provide data consistant and prevent any range intersections (but don't save you from deadlocks)
insert into `movable_objects_server_id_ranges_lock` (ID, IsLocked) values (1, 1)
on duplicate key update IsLocked=1;
open rangeCursor;
-- use cursor for iterate thru all exists server ranges
range_loop: loop
fetch rangeCursor into rangeID, startID, endID;
if(cursorDone) then
leave range_loop;
end if;
-- get max used id from this range and block this rage from inserts
select max(ID)
into currMaxUsedId
from `movable_objects`
where ID >= startID and ID <= endID
for update;
-- TODO: how about id reusing here?
if(currMaxUsedId is null) then
-- range have no any used ids
set currFreeIdCount = (currFreeIdCount + (endID - startID) + 1);
elseif(currMaxUsedId >= startID and currMaxUsedId < endID) then
-- range have some used ids
set currFreeIdCount = (currFreeIdCount + (endID - currMaxUsedId));
update `movable_objects_server_id_ranges`
set RangeStartID = (currMaxUsedId + 1)
-- , RangeEndID = endID
where ID = rangeID;
else -- if(currMaxUsedId >= endID) then
-- range is full or currMaxUsedId is out of range bounds
delete from `movable_objects_server_id_ranges`
where ID = rangeID;
end if;
end loop;
close rangeCursor;
if(currFreeIdCount < in_idCount) then
-- reserve new ID range
-- This operation give us currently maximum claimed ID with blocking from same queries and
-- from inserting into the gap just before max(RangeEndID) and after max(RangeEndID)
select max(RangeEndID)
into startID
from `movable_objects_server_id_ranges`
for update;
-- compatibility with old auto_increment IDs
if(startID is null) then
select max(ID)
into startID
from `movable_objects`
for update;
end if;
-- We reserve new range with full size of in_idCount. This save us from inserting small ranges
-- each time this procedure called
set startID = (ifnull(startID, 0) + 1);
set endID = (startID + in_idCount - 1);
-- remember new range
insert into `movable_objects_server_id_ranges`
(ServerID, RangeStartID, RangeEndID)
values (in_serverID, startID, endID);
end if;
-- return result
if(!errorFlag) then
select RangeStartID, RangeEndID
from `movable_objects_server_id_ranges`
where ServerID = in_serverID
order by RangeStartID;
commit;
else -- in case of error
rollback;
end if;
END;
[Err] 1607 - Cannot create stored routine `p_issueIdRange_unmovable_objects`. Check warnings
[Err] CREATE PROCEDURE `p_issueIdRange_unmovable_objects`(
in_serverID INT UNSIGNED,
in_idCount INT UNSIGNED
)
MODIFIES SQL DATA
COMMENT 'Return unmovable_objects IDs which available for concrete server to insert'
BEGIN
declare rangeID, startID, endID, currFreeIdCount, currMaxUsedId int unsigned default 0;
declare errorFlag, cursorDone tinyint unsigned default FALSE;
declare rangeCursor cursor for
(
select ID, RangeStartID, RangeEndID
from `unmovable_objects_server_id_ranges`
where ServerID = in_serverID
order by RangeStartID -- ordering for consecutive ID using
for update
);
declare continue handler for not found set cursorDone = TRUE;
-- mysql does not stop procedure execution on errors, so do it manually
declare continue handler for sqlexception
begin
-- "leave this_sp;" can't be called here, so use flag
set errorFlag = TRUE;
end;
start transaction;
-- We using the unmovable_objects_server_id_ranges_lock table like a mutex - lock it at transaction start,
-- and release it on commit/rollback. This ugly solution provides us a 100% deadlock protection when
-- this procedure runs simultaneously from several sessions.
-- If you don't care about deadlocks, you can simply skip this insert - all locking logic below
-- still provide data consistant and prevent any range intersections (but don't save you from deadlocks)
insert into `unmovable_objects_server_id_ranges_lock` (ID, IsLocked) values (1, 1)
on duplicate key update IsLocked=1;
open rangeCursor;
-- use cursor for iterate thru all exists server ranges
range_loop: loop
fetch rangeCursor into rangeID, startID, endID;
if(cursorDone) then
leave range_loop;
end if;
-- get max used id from this range and block this rage from inserts
select max(ID)
into currMaxUsedId
from `unmovable_objects`
where ID >= startID and ID <= endID
for update;
-- TODO: how about id reusing here?
if(currMaxUsedId is null) then
-- range have no any used ids
set currFreeIdCount = (currFreeIdCount + (endID - startID) + 1);
elseif(currMaxUsedId >= startID and currMaxUsedId < endID) then
-- range have some used ids
set currFreeIdCount = (currFreeIdCount + (endID - currMaxUsedId));
update `unmovable_objects_server_id_ranges`
set RangeStartID = (currMaxUsedId + 1)
-- , RangeEndID = endID
where ID = rangeID;
else -- if(currMaxUsedId >= endID) then
-- range is full or currMaxUsedId is out of range bounds
delete from `unmovable_objects_server_id_ranges`
where ID = rangeID;
end if;
end loop;
close rangeCursor;
if(currFreeIdCount < in_idCount) then
-- reserve new ID range
-- This operation give us currently maximum claimed ID with blocking from same queries and
-- from inserting into the gap just before max(RangeEndID) and after max(RangeEndID)
select max(RangeEndID)
into startID
from `unmovable_objects_server_id_ranges`
for update;
-- compatibility with old auto_increment IDs
if(startID is null) then
select max(ID)
into startID
from `unmovable_objects`
for update;
end if;
-- We reserve new range with full size of in_idCount. This save us from inserting small ranges
-- each time this procedure called
set startID = (ifnull(startID, 0) + 1);
set endID = (startID + in_idCount - 1);
-- remember new range
insert into `unmovable_objects_server_id_ranges`
(ServerID, RangeStartID, RangeEndID)
values (in_serverID, startID, endID);
end if;
-- return result
if(!errorFlag) then
select RangeStartID, RangeEndID
from `unmovable_objects_server_id_ranges`
where ServerID = in_serverID
order by RangeStartID;
commit;
else -- in case of error
rollback;
end if;
END;
[Msg] Finished - Unsuccessfully
--------------------------------------------------