I got this error when I try to execute a brand new installation of MaNGOS in a Debian 12 server:
The table `db_version` indicates that your [Character] database does not match the expected structure!
[A] You have database Version: 22
Structure: 2
Content: 1
Description: Add_field_comments
[B] The core needs database Version: 22
Structure: 4
Content: 1
Description: Add_Quest_Tracker_Table
You must apply all updates after [A] to [B] to use MaNGOS with this database.
These updates are included in the database/Character/Updates folder.
So I try to update the database with these files:
mangos@docker:~/mangos/zero/db/database/Character/Updates/Rel22$ ls -l
total 24
-rw-r--r-- 1 mangos mangos 384 Nov 2 17:15 readme.txt
drwxr-xr-x 2 mangos mangos 136 Nov 3 01:57 Rel21_to_BaseRel22_Updates
-rw-r--r-- 1 mangos mangos 9242 Nov 2 17:15 Rel22_03_001_Adjust_Tables.sql
-rw-r--r-- 1 mangos mangos 6624 Nov 2 17:15 Rel22_04_001_Add_Quest_Tracker_Table.sql
MariaDB [character0]> SELECT * FROM db_version ORDER BY VERSION DESC, structure DESC, content DESC LIMIT 0,1
-> ;
+---------+-----------+---------+--------------------+------------------------------------------+
| version | structure | content | description | comment |
+---------+-----------+---------+--------------------+------------------------------------------+
| 22 | 2 | 1 | Add_field_comments | Base Install of 21000_00 to Rel22_02_001 |
+---------+-----------+---------+--------------------+------------------------------------------+
1 row in set (0.001 sec)
First I try to update with Rel22_03_001_Adjust_Tables.sql but i got this error and I don't know what to do next.
Your MariaDB connection id is 509
Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [character0]> -- ----------------------------------------------------------------
-- Current Values (TODO - must be a better way to do this)
SET @cCurVersion := (SELECT `version` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
SET @cCurStructure := (SELECT `structure` FROM `db_vMariaDB [character0]> ersion` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
SET @cCurContent := (SELECT `content` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
-- Expected Values
-- This is an attempt to create a full transactional MaNGOS update
SET @cOldContent = '001';
-- New Values
SET @cNewVersion = '22';
SET @cNewStructure = '03';
SET @cNewContent = '001';
MariaDB [character0]> -- Now compatible with newer MySql Databases (v1.5)
MariaDB [character0]> -- ----------------------------------------------------------------
MariaDB [character0]> DROP PROCEDURE IF EXISTS `update_mangos`;
SET @cNewComment = 'remove_item_text';
-- Evaluate all settings
SET @cCurResult := (SELECT `description` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
Query OK, 0 rows affected, 1 warning (0.000 sec)
SET @cOldResult := (SELECT `description` FROM `db_version` WHERE `version`=@cOldVersion AND `structu
re`=@cOldStructure AND `content`=@cOldContent);
SET @cNewResult := (SELECT `description` FROM `db_version` WHERE `version`=@cNewVersion AND `structure`=@cNewStructure AND `content`=@cNewContent);
MariaDB [character0]>
MariaDB [character0]> DELIMITER $$
MariaDB [character0]>
MariaDB [character0]> CREATE DEFINER=`root`@`localhost` PROCEDURE `update_mangos`()
-> BEGIN
-> DECLARE bRollback BOOL DEFAULT FALSE ;
-> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `bRollback` = TRUE;
->
IF (@cCurResult = @cOldResult) THEN -- Does the curren -> t version match the expected version
-- APPLY UPDATE
START TRANSACTION;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- Current Values (TODO - must be a better way to do this)
`guid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The GUID of the item. This number is unique for each item instance.',
`owner_guid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The GUID of the character who has ownership of this item. (See character.g -> uid)',
`data` LONGTEXT DEFAULT NULL COMMENT 'Much like the playerbytes fields in the characters table.',
`text` LONGTEXT DEFAULT NULL COMMENT 'The Name of the Item',
PRIMARY KEY (`guid`),
SET @cCurVersion := (SELECT `version` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
-> SET @cCurStructure := (SELECT `structure` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
-> SET @cCurContent := (SELECT `content` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
->
-> -- Expected Values
-> SET @cOldVersion = '22';
@ -> SET @cOldStructure = '02';
-> SET @cOldContent = '001';
->
-> -- New Values
-> SET @cNewVersion = '22';
Id -> SET @cNewStructure = '03';
-> SET @cNewContent = '001';
-> -- DESCRIPTION IS 30 Characters MAX
-> SET @cNewDescription = 'remove_item_text';
->
-> -- COMMENT is 150 Characters MAX
-> SET @cNewComment = 'remove_item_text';
->
-> -- Evaluate all settings
-> SET @cCurResult := (SELECT `description` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
-> SET @cOldResult := (SELECT `description` FROM `db_version` WHERE `version`=@cOldVersion AND `structure`=@cOldStructure AND `content`=@cOldContent);
-> SET @cNewResult := (SELECT `description` FROM `db_version` WHERE `version`=@cNewVersion AND `structure`=@cNewStructure AND `content`=@cNewContent);
->
-> IF (@cCurResult = @cOldResult) THEN -- Does the current version match the expected version
-> -- APPLY UPDATE
-> START TRANSACTION;
-> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-> -- -- PLACE UPDATE SQL BELOW -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
->
-> RENAME TABLE item_instance TO item_instance_old;
-> RENAME TABLE item_text TO item_text_old;
-> RENAME TABLE mail TO mail_old;
->
-> DROP TABLE IF EXISTS `item_instance_new`;
-> /*!40101 SET @saved_cs_client = @@character_set_client */;
-> /*!40101 SET character_set_client = utf8 */;
-> CREATE TABLE `item_instance_new` (
-> `guid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The GUID of the item. This number is unique for each item instance.',
-> `owner_guid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The GUID of the character who has ownership of this item. (See character.guid)',
-> `data` LONGTEXT DEFAULT NULL COMMENT 'Much like the playerbytes fields in the characters table.',
-> `text` LONGTEXT DEFAULT NULL COMMENT 'The Name of the Item',
-> PRIMARY KEY (`guid`),
-> KEY `idx_owner_guid` (`owner_guid`)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Item System';
->
-> INSERT INTO `item_instance_new`
-> SELECT `guid`,`owner_guid`,`data`,`text` FROM item_instance_old LEFT JOIN item_text_old ON item_instance_old.`guid`=item_text_old.`id`;
->
->
-> DROP TABLE IF EXISTS `mail_new`;
-> /*!40101 SET @saved_cs_client = @@character_set_client */;
-> /*!40101 SET character_set_client = utf8 */;
-> CREATE TABLE `mail_new` (
-> `id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Unique message id. Every new message gets a new auto incremented id.',
-> `messageType` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'message_type.',
-> `stationery` TINYINT(3) NOT NULL DEFAULT '41' COMMENT 'The StationeryID (See Stationery.dbc).',
-> `mailTemplateId` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The Mail Template Id (See MailTemplate.dbc)',
-> `sender` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Guid of the character who sends the mail.',
-> `receiver` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Guid of the character who receives the mail.',
-> `subject` LONGTEXT DEFAULT NULL COMMENT 'The Subject of the mail.',
-> `body` LONGTEXT DEFAULT NULL COMMENT 'The Body of the mail',
-> `has_items` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Boolean value showing whether or not atems have been sent.',
-> `expire_time` BIGINT(40) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Current Unix Time + Unix Time till expiry.',
-> `deliver_time` BIGINT(40) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Current Unix Time + Unix Time till delivery.',
-> `money` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Amount of money sent.',
-> `cod` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Amount of money needed (COD).',
-> `checked` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'checked_flag.',
-> PRIMARY KEY (`id`),
-> KEY `idx_receiver` (`receiver`)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Mail System';
-> /*!40101 SET character_set_client = @saved_cs_client */;
->
-> INSERT INTO `mail_new`
-> SELECT `mail_old`.`id`,`messageType`,`stationery`,`mailTemplateId`,`sender`,`receiver`,`subject`,`text`,`has_items`,`expire_time`,`deliver_time`,`money`,`cod`,`checked` FROM mail_old LEFT JOIN item_text_old ON mail_old.`itemTextId`=item_text_old.`id`;
->
-> RENAME TABLE item_instance_new TO item_instance;
-> RENAME TABLE mail_new TO mail;
->
-> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-> -- -- PLACE UPDATE SQL ABOVE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
->
-> -- If we get here ok, commit the changes
-> IF bRollback = TRUE THEN
-> ROLLBACK;
-> SHOW ERRORS;
-> SELECT '* UPDATE FAILED *' AS `===== Status =====`,@cCurResult AS `===== DB is on Version: =====`;
-> ELSE
-> COMMIT;
-> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-> -- UPDATE THE DB VERSION
-> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-> INSERT INTO `db_version` VALUES (@cNewVersion, @cNewStructure, @cNewContent, @cNewDescription, @cNewComment);
-> SET @cNewResult := (SELECT `description` FROM `db_version` WHERE `version`=@cNewVersion AND `structure`=@cNewStructure AND `content`=@cNewContent);
->
-> SELECT '* UPDATE COMPLETE *' AS `===== Status =====`,@cNewResult AS `===== DB is now on Version =====`;
-> END IF;
-> ELSE -- Current version is not the expected version
-> IF (@cCurResult = @cNewResult) THEN -- Does the current version match the new version
-> SELECT '* UPDATE SKIPPED *' AS `===== Status =====`,@cCurResult AS `===== DB is already on Version =====`;
-> ELSE -- Current version is not one related to this update
-> IF(@cCurResult IS NULL) THEN -- Something has gone wrong
-> SELECT '* UPDATE FAILED *' AS `===== Status =====`,'Unable to locate DB Version Information' AS `============= Error Message =============`;
-> ELSE
-> IF(@cOldResult IS NULL) THEN -- Something has gone wrong
-> SET @cCurVersion := (SELECT `version` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
-> SET @cCurStructure := (SELECT `STRUCTURE` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
-> SET @cCurContent := (SELECT `Content` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
-> SET @cCurOutput = CONCAT(@cCurVersion, '_', @cCurStructure, '_', @cCurContent, ' - ',@cCurResult);
-> SET @cOldResult = CONCAT('Rel',@cOldVersion, '_', @cOldStructure, '_', @cOldContent, ' - ','IS NOT APPLIED');
-> SELECT '* UPDATE SKIPPED *' AS `===== Status =====`,@cOldResult AS `=== Expected ===`,@cCurOutput AS `===== Found Version =====`;
-> ELSE
-> SET @cCurVersion := (SELECT `version` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
-> SET @cCurStructure := (SELECT `STRUCTURE` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
-> SET @cCurContent := (SELECT `Content` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1);
-> SET @cCurOutput = CONCAT(@cCurVersion, '_', @cCurStructure, '_', @cCurContent, ' - ',@cCurResult);
-> SELECT '* UPDATE SKIPPED *' AS `===== Status =====`,@cOldResult AS `=== Expected ===`,@cCurOutput AS `===== Found Version =====`;
-> END IF;
-> END IF;
-> END IF;
-> END IF;
-> END $$
Query OK, 0 rows affected (0.008 sec)
MariaDB [character0]>
MariaDB [character0]> DELIMITER ;
MariaDB [character0]>
MariaDB [character0]> -- Execute the procedure
MariaDB [character0]> CALL update_mangos();
Empty set (0.031 sec)
+--------------------+-------------------------------+
| ===== Status ===== | ===== DB is on Version: ===== |
+--------------------+-------------------------------+
| * UPDATE FAILED * | Add_field_comments |
+--------------------+-------------------------------+
1 row in set (0.031 sec)
Query OK, 0 rows affected (0.031 sec)
MariaDB [character0]>
MariaDB [character0]> -- Drop the procedure
MariaDB [character0]> DROP PROCEDURE IF EXISTS `update_mangos`;
Problem updating Character db
in Peer to Peer Technical Support
Posted
I got this error when I try to execute a brand new installation of MaNGOS in a Debian 12 server:
The table `db_version` indicates that your [Character] database does not match the expected structure! [A] You have database Version: 22 Structure: 2 Content: 1 Description: Add_field_comments [B] The core needs database Version: 22 Structure: 4 Content: 1 Description: Add_Quest_Tracker_Table You must apply all updates after [A] to [B] to use MaNGOS with this database. These updates are included in the database/Character/Updates folder.
So I try to update the database with these files:
mangos@docker:~/mangos/zero/db/database/Character/Updates/Rel22$ ls -l total 24 -rw-r--r-- 1 mangos mangos 384 Nov 2 17:15 readme.txt drwxr-xr-x 2 mangos mangos 136 Nov 3 01:57 Rel21_to_BaseRel22_Updates -rw-r--r-- 1 mangos mangos 9242 Nov 2 17:15 Rel22_03_001_Adjust_Tables.sql -rw-r--r-- 1 mangos mangos 6624 Nov 2 17:15 Rel22_04_001_Add_Quest_Tracker_Table.sql
MariaDB [character0]> SELECT * FROM db_version ORDER BY VERSION DESC, structure DESC, content DESC LIMIT 0,1 -> ; +---------+-----------+---------+--------------------+------------------------------------------+ | version | structure | content | description | comment | +---------+-----------+---------+--------------------+------------------------------------------+ | 22 | 2 | 1 | Add_field_comments | Base Install of 21000_00 to Rel22_02_001 | +---------+-----------+---------+--------------------+------------------------------------------+ 1 row in set (0.001 sec)
First I try to update with Rel22_03_001_Adjust_Tables.sql but i got this error and I don't know what to do next.
Your MariaDB connection id is 509 Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [character0]> -- ---------------------------------------------------------------- -- Current Values (TODO - must be a better way to do this) SET @cCurVersion := (SELECT `version` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); SET @cCurStructure := (SELECT `structure` FROM `db_vMariaDB [character0]> ersion` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); SET @cCurContent := (SELECT `content` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); -- Expected Values -- This is an attempt to create a full transactional MaNGOS update SET @cOldContent = '001'; -- New Values SET @cNewVersion = '22'; SET @cNewStructure = '03'; SET @cNewContent = '001'; MariaDB [character0]> -- Now compatible with newer MySql Databases (v1.5) MariaDB [character0]> -- ---------------------------------------------------------------- MariaDB [character0]> DROP PROCEDURE IF EXISTS `update_mangos`; SET @cNewComment = 'remove_item_text'; -- Evaluate all settings SET @cCurResult := (SELECT `description` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); Query OK, 0 rows affected, 1 warning (0.000 sec) SET @cOldResult := (SELECT `description` FROM `db_version` WHERE `version`=@cOldVersion AND `structu re`=@cOldStructure AND `content`=@cOldContent); SET @cNewResult := (SELECT `description` FROM `db_version` WHERE `version`=@cNewVersion AND `structure`=@cNewStructure AND `content`=@cNewContent); MariaDB [character0]> MariaDB [character0]> DELIMITER $$ MariaDB [character0]> MariaDB [character0]> CREATE DEFINER=`root`@`localhost` PROCEDURE `update_mangos`() -> BEGIN -> DECLARE bRollback BOOL DEFAULT FALSE ; -> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `bRollback` = TRUE; -> IF (@cCurResult = @cOldResult) THEN -- Does the curren -> t version match the expected version -- APPLY UPDATE START TRANSACTION; -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- Current Values (TODO - must be a better way to do this) `guid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The GUID of the item. This number is unique for each item instance.', `owner_guid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The GUID of the character who has ownership of this item. (See character.g -> uid)', `data` LONGTEXT DEFAULT NULL COMMENT 'Much like the playerbytes fields in the characters table.', `text` LONGTEXT DEFAULT NULL COMMENT 'The Name of the Item', PRIMARY KEY (`guid`), SET @cCurVersion := (SELECT `version` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); -> SET @cCurStructure := (SELECT `structure` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); -> SET @cCurContent := (SELECT `content` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); -> -> -- Expected Values -> SET @cOldVersion = '22'; @ -> SET @cOldStructure = '02'; -> SET @cOldContent = '001'; -> -> -- New Values -> SET @cNewVersion = '22'; Id -> SET @cNewStructure = '03'; -> SET @cNewContent = '001'; -> -- DESCRIPTION IS 30 Characters MAX -> SET @cNewDescription = 'remove_item_text'; -> -> -- COMMENT is 150 Characters MAX -> SET @cNewComment = 'remove_item_text'; -> -> -- Evaluate all settings -> SET @cCurResult := (SELECT `description` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); -> SET @cOldResult := (SELECT `description` FROM `db_version` WHERE `version`=@cOldVersion AND `structure`=@cOldStructure AND `content`=@cOldContent); -> SET @cNewResult := (SELECT `description` FROM `db_version` WHERE `version`=@cNewVersion AND `structure`=@cNewStructure AND `content`=@cNewContent); -> -> IF (@cCurResult = @cOldResult) THEN -- Does the current version match the expected version -> -- APPLY UPDATE -> START TRANSACTION; -> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -> -- -- PLACE UPDATE SQL BELOW -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -> -> RENAME TABLE item_instance TO item_instance_old; -> RENAME TABLE item_text TO item_text_old; -> RENAME TABLE mail TO mail_old; -> -> DROP TABLE IF EXISTS `item_instance_new`; -> /*!40101 SET @saved_cs_client = @@character_set_client */; -> /*!40101 SET character_set_client = utf8 */; -> CREATE TABLE `item_instance_new` ( -> `guid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The GUID of the item. This number is unique for each item instance.', -> `owner_guid` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The GUID of the character who has ownership of this item. (See character.guid)', -> `data` LONGTEXT DEFAULT NULL COMMENT 'Much like the playerbytes fields in the characters table.', -> `text` LONGTEXT DEFAULT NULL COMMENT 'The Name of the Item', -> PRIMARY KEY (`guid`), -> KEY `idx_owner_guid` (`owner_guid`) -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Item System'; -> -> INSERT INTO `item_instance_new` -> SELECT `guid`,`owner_guid`,`data`,`text` FROM item_instance_old LEFT JOIN item_text_old ON item_instance_old.`guid`=item_text_old.`id`; -> -> -> DROP TABLE IF EXISTS `mail_new`; -> /*!40101 SET @saved_cs_client = @@character_set_client */; -> /*!40101 SET character_set_client = utf8 */; -> CREATE TABLE `mail_new` ( -> `id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Unique message id. Every new message gets a new auto incremented id.', -> `messageType` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'message_type.', -> `stationery` TINYINT(3) NOT NULL DEFAULT '41' COMMENT 'The StationeryID (See Stationery.dbc).', -> `mailTemplateId` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'The Mail Template Id (See MailTemplate.dbc)', -> `sender` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Guid of the character who sends the mail.', -> `receiver` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Guid of the character who receives the mail.', -> `subject` LONGTEXT DEFAULT NULL COMMENT 'The Subject of the mail.', -> `body` LONGTEXT DEFAULT NULL COMMENT 'The Body of the mail', -> `has_items` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Boolean value showing whether or not atems have been sent.', -> `expire_time` BIGINT(40) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Current Unix Time + Unix Time till expiry.', -> `deliver_time` BIGINT(40) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Current Unix Time + Unix Time till delivery.', -> `money` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Amount of money sent.', -> `cod` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Amount of money needed (COD).', -> `checked` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'checked_flag.', -> PRIMARY KEY (`id`), -> KEY `idx_receiver` (`receiver`) -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Mail System'; -> /*!40101 SET character_set_client = @saved_cs_client */; -> -> INSERT INTO `mail_new` -> SELECT `mail_old`.`id`,`messageType`,`stationery`,`mailTemplateId`,`sender`,`receiver`,`subject`,`text`,`has_items`,`expire_time`,`deliver_time`,`money`,`cod`,`checked` FROM mail_old LEFT JOIN item_text_old ON mail_old.`itemTextId`=item_text_old.`id`; -> -> RENAME TABLE item_instance_new TO item_instance; -> RENAME TABLE mail_new TO mail; -> -> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -> -- -- PLACE UPDATE SQL ABOVE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -> -> -- If we get here ok, commit the changes -> IF bRollback = TRUE THEN -> ROLLBACK; -> SHOW ERRORS; -> SELECT '* UPDATE FAILED *' AS `===== Status =====`,@cCurResult AS `===== DB is on Version: =====`; -> ELSE -> COMMIT; -> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -> -- UPDATE THE DB VERSION -> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -> INSERT INTO `db_version` VALUES (@cNewVersion, @cNewStructure, @cNewContent, @cNewDescription, @cNewComment); -> SET @cNewResult := (SELECT `description` FROM `db_version` WHERE `version`=@cNewVersion AND `structure`=@cNewStructure AND `content`=@cNewContent); -> -> SELECT '* UPDATE COMPLETE *' AS `===== Status =====`,@cNewResult AS `===== DB is now on Version =====`; -> END IF; -> ELSE -- Current version is not the expected version -> IF (@cCurResult = @cNewResult) THEN -- Does the current version match the new version -> SELECT '* UPDATE SKIPPED *' AS `===== Status =====`,@cCurResult AS `===== DB is already on Version =====`; -> ELSE -- Current version is not one related to this update -> IF(@cCurResult IS NULL) THEN -- Something has gone wrong -> SELECT '* UPDATE FAILED *' AS `===== Status =====`,'Unable to locate DB Version Information' AS `============= Error Message =============`; -> ELSE -> IF(@cOldResult IS NULL) THEN -- Something has gone wrong -> SET @cCurVersion := (SELECT `version` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); -> SET @cCurStructure := (SELECT `STRUCTURE` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); -> SET @cCurContent := (SELECT `Content` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); -> SET @cCurOutput = CONCAT(@cCurVersion, '_', @cCurStructure, '_', @cCurContent, ' - ',@cCurResult); -> SET @cOldResult = CONCAT('Rel',@cOldVersion, '_', @cOldStructure, '_', @cOldContent, ' - ','IS NOT APPLIED'); -> SELECT '* UPDATE SKIPPED *' AS `===== Status =====`,@cOldResult AS `=== Expected ===`,@cCurOutput AS `===== Found Version =====`; -> ELSE -> SET @cCurVersion := (SELECT `version` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); -> SET @cCurStructure := (SELECT `STRUCTURE` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); -> SET @cCurContent := (SELECT `Content` FROM `db_version` ORDER BY `version` DESC, `STRUCTURE` DESC, `CONTENT` DESC LIMIT 0,1); -> SET @cCurOutput = CONCAT(@cCurVersion, '_', @cCurStructure, '_', @cCurContent, ' - ',@cCurResult); -> SELECT '* UPDATE SKIPPED *' AS `===== Status =====`,@cOldResult AS `=== Expected ===`,@cCurOutput AS `===== Found Version =====`; -> END IF; -> END IF; -> END IF; -> END IF; -> END $$ Query OK, 0 rows affected (0.008 sec) MariaDB [character0]> MariaDB [character0]> DELIMITER ; MariaDB [character0]> MariaDB [character0]> -- Execute the procedure MariaDB [character0]> CALL update_mangos(); Empty set (0.031 sec) +--------------------+-------------------------------+ | ===== Status ===== | ===== DB is on Version: ===== | +--------------------+-------------------------------+ | * UPDATE FAILED * | Add_field_comments | +--------------------+-------------------------------+ 1 row in set (0.031 sec) Query OK, 0 rows affected (0.031 sec) MariaDB [character0]> MariaDB [character0]> -- Drop the procedure MariaDB [character0]> DROP PROCEDURE IF EXISTS `update_mangos`;
Can you help me please?