TDB 1105.24111 - 2024/11/16TDB1105.24111

10 files changed, 170 insertions, 0 deletions

diff --git a/sql/old/11.x/auth/24092_2024_11_16/2024_09_23_00_auth.sql b/sql/old/11.x/auth/24092_2024_11_16/2024_09_23_00_auth.sql
new file mode 100644
index 00000000000..c4d4e867cdd
--- /dev/null
+++ b/sql/old/11.x/auth/24092_2024_11_16/2024_09_23_00_auth.sql
@@ -0,0 +1,3 @@
+-- TDB 1102.24092 auth
+UPDATE `updates` SET `state`='ARCHIVED',`speed`=0;
+REPLACE INTO `updates_include` (`path`, `state`) VALUES ('$/sql/old/11.x/auth', 'ARCHIVED');
diff --git a/sql/old/11.x/auth/24092_2024_11_16/2024_09_26_00_auth.sql b/sql/old/11.x/auth/24092_2024_11_16/2024_09_26_00_auth.sql
new file mode 100644
index 00000000000..a5c75e51c38
--- /dev/null
+++ b/sql/old/11.x/auth/24092_2024_11_16/2024_09_26_00_auth.sql
@@ -0,0 +1,20 @@
+
+ALTER TABLE `realmlist` MODIFY `population` float NOT NULL DEFAULT '0';
+
+SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'; -- views do not have explicit charset/collation options but record whatever the connection settings were as their own (visible in mysqldump)
+
+DROP VIEW IF EXISTS `vw_log_history`;
+DROP VIEW IF EXISTS `vw_rbac`;
+
+CREATE SQL SECURITY INVOKER VIEW `vw_log_history` AS (SELECT FROM_UNIXTIME(MIN(`logs`.`time`)) AS `First Logged` ,FROM_UNIXTIME(MAX(`logs`.`time`)) AS `Last Logged` ,COUNT(*) AS `Occurrences` ,`realmlist`.`name` AS `Realm` ,`logs`.`type` ,`logs`.`level` ,`logs`.`string` FROM `logs` LEFT JOIN realmlist ON `logs`.`realm` = `realmlist`.`id` GROUP BY `logs`.`string`, `logs`.`type`, `logs`.`realm`);
+
+CREATE SQL SECURITY INVOKER VIEW `vw_rbac` AS
+(
+    SELECT `t1`.`linkedId` AS `Permission ID`,
+           `t1`.`id` AS `Permission Group`,
+           IFNULL(`t2`.`secId`, 'linked') AS `Security Level`,
+       `t3`.`name` AS `Permission`
+    FROM `rbac_linked_permissions` `t1`
+    LEFT JOIN `rbac_default_permissions` `t2` ON `t1`.`id` = `t2`.`permissionId`
+    LEFT JOIN `rbac_permissions` `t3` ON `t1`.`linkedId` = `t3`.`id`
+);
diff --git a/sql/old/11.x/auth/24092_2024_11_16/2024_10_01_00_auth.sql b/sql/old/11.x/auth/24092_2024_11_16/2024_10_01_00_auth.sql
new file mode 100644
index 00000000000..5853468e2c7
--- /dev/null
+++ b/sql/old/11.x/auth/24092_2024_11_16/2024_10_01_00_auth.sql
@@ -0,0 +1,23 @@
+DELETE FROM `build_info` WHERE `build` IN (56819);
+INSERT INTO `build_info` (`build`,`majorVersion`,`minorVersion`,`bugfixVersion`,`hotfixVersion`) VALUES
+(56819,11,0,2,NULL);
+
+DELETE FROM `build_auth_key` WHERE `build`=56819 AND `platform`='Mac' AND `arch`='A64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=56819 AND `platform`='Mac' AND `arch`='A64' AND `type`='WoWC';
+DELETE FROM `build_auth_key` WHERE `build`=56819 AND `platform`='Mac' AND `arch`='x64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=56819 AND `platform`='Mac' AND `arch`='x64' AND `type`='WoWC';
+DELETE FROM `build_auth_key` WHERE `build`=56819 AND `platform`='Win' AND `arch`='A64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=56819 AND `platform`='Win' AND `arch`='x64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=56819 AND `platform`='Win' AND `arch`='x64' AND `type`='WoWC';
+INSERT INTO `build_auth_key` (`build`,`platform`,`arch`,`type`,`key`) VALUES
+(56819,'Mac','A64','WoW',0x1553CD215A62E2DC92CD61C4FB5DF9C3),
+(56819,'Mac','A64','WoWC',0x1B7EF05D342064F931E53264031E3497),
+(56819,'Mac','x64','WoW',0x9D5B7E1B5E59E54A3B96E5CCBA6EF465),
+(56819,'Mac','x64','WoWC',0x2BDCD952BAA47E6CBD71C780BC8CCD47),
+(56819,'Win','A64','WoW',0x7DC752479B560884B8289BC5B5C85F3F),
+(56819,'Win','x64','WoW',0x4373F45E9D617636332569671ED5D5E9),
+(56819,'Win','x64','WoWC',0xB9E422DF76DA635E193B75BD54ADD8DE);
+
+UPDATE `realmlist` SET `gamebuild`=56819 WHERE `gamebuild`=56647;
+
+ALTER TABLE `realmlist` CHANGE `gamebuild` `gamebuild` int unsigned NOT NULL DEFAULT '56819';
diff --git a/sql/old/11.x/auth/24092_2024_11_16/2024_10_23_00_auth.sql b/sql/old/11.x/auth/24092_2024_11_16/2024_10_23_00_auth.sql
new file mode 100644
index 00000000000..c7b7fde410c
--- /dev/null
+++ b/sql/old/11.x/auth/24092_2024_11_16/2024_10_23_00_auth.sql
@@ -0,0 +1,19 @@
+DELETE FROM `build_info` WHERE `build` IN (57171);
+INSERT INTO `build_info` (`build`,`majorVersion`,`minorVersion`,`bugfixVersion`,`hotfixVersion`) VALUES
+(57171,11,0,5,NULL);
+
+DELETE FROM `build_auth_key` WHERE `build`=57171 AND `platform`='Mac' AND `arch`='A64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57171 AND `platform`='Mac' AND `arch`='A64' AND `type`='WoWC';
+DELETE FROM `build_auth_key` WHERE `build`=57171 AND `platform`='Mac' AND `arch`='x64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57171 AND `platform`='Mac' AND `arch`='x64' AND `type`='WoWC';
+DELETE FROM `build_auth_key` WHERE `build`=57171 AND `platform`='Win' AND `arch`='A64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57171 AND `platform`='Win' AND `arch`='x64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57171 AND `platform`='Win' AND `arch`='x64' AND `type`='WoWC';
+INSERT INTO `build_auth_key` (`build`,`platform`,`arch`,`type`,`key`) VALUES
+(57171,'Mac','A64','WoW',0x4327288A88EBDC1EBE58F27BA30F69E6),
+(57171,'Mac','A64','WoWC',0x2279265138F9516F382CA160CABDB128),
+(57171,'Mac','x64','WoW',0x28D502237C9E250B31CE081A6C540D7F),
+(57171,'Mac','x64','WoWC',0x36B3B18FA95E1F153289340FA437C311),
+(57171,'Win','A64','WoW',0x9F1E52B1F144DA385F8C6F1E9167D057),
+(57171,'Win','x64','WoW',0x967B99503C5D7A0B8B47941E16F60948),
+(57171,'Win','x64','WoWC',0x3E17E628F236DA642C9EB0B8624C8499);
diff --git a/sql/old/11.x/auth/24092_2024_11_16/2024_10_23_01_auth.sql b/sql/old/11.x/auth/24092_2024_11_16/2024_10_23_01_auth.sql
new file mode 100644
index 00000000000..ac2f4583146
--- /dev/null
+++ b/sql/old/11.x/auth/24092_2024_11_16/2024_10_23_01_auth.sql
@@ -0,0 +1,19 @@
+DELETE FROM `build_info` WHERE `build` IN (57212);
+INSERT INTO `build_info` (`build`,`majorVersion`,`minorVersion`,`bugfixVersion`,`hotfixVersion`) VALUES
+(57212,11,0,5,NULL);
+
+DELETE FROM `build_auth_key` WHERE `build`=57212 AND `platform`='Mac' AND `arch`='A64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57212 AND `platform`='Mac' AND `arch`='A64' AND `type`='WoWC';
+DELETE FROM `build_auth_key` WHERE `build`=57212 AND `platform`='Mac' AND `arch`='x64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57212 AND `platform`='Mac' AND `arch`='x64' AND `type`='WoWC';
+DELETE FROM `build_auth_key` WHERE `build`=57212 AND `platform`='Win' AND `arch`='A64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57212 AND `platform`='Win' AND `arch`='x64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57212 AND `platform`='Win' AND `arch`='x64' AND `type`='WoWC';
+INSERT INTO `build_auth_key` (`build`,`platform`,`arch`,`type`,`key`) VALUES
+(57212,'Mac','A64','WoW',0x5AF00EAFB241FAA2AF7485FA62AA094E),
+(57212,'Mac','A64','WoWC',0xA4B115B6BA907AEC5AEF622F813BD81C),
+(57212,'Mac','x64','WoW',0xFB59E41DAC6FF691A87A20696B3AC44E),
+(57212,'Mac','x64','WoWC',0x32EB0F3BB5EA60EEC4945F71DBD2B4F3),
+(57212,'Win','A64','WoW',0xA1D949D062EC49E5A2978BD1932C8D0E),
+(57212,'Win','x64','WoW',0xB8ADB57D27EF98C8DDEF9943BE07EE94),
+(57212,'Win','x64','WoWC',0xD18B79CEF34B5E10BA8C779EFE6D9EA0);
diff --git a/sql/old/11.x/auth/24092_2024_11_16/2024_10_30_00_auth.sql b/sql/old/11.x/auth/24092_2024_11_16/2024_10_30_00_auth.sql
new file mode 100644
index 00000000000..41431443a1b
--- /dev/null
+++ b/sql/old/11.x/auth/24092_2024_11_16/2024_10_30_00_auth.sql
@@ -0,0 +1,23 @@
+DELETE FROM `build_info` WHERE `build` IN (57292);
+INSERT INTO `build_info` (`build`,`majorVersion`,`minorVersion`,`bugfixVersion`,`hotfixVersion`) VALUES
+(57292,11,0,5,NULL);
+
+DELETE FROM `build_auth_key` WHERE `build`=57292 AND `platform`='Mac' AND `arch`='A64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57292 AND `platform`='Mac' AND `arch`='A64' AND `type`='WoWC';
+DELETE FROM `build_auth_key` WHERE `build`=57292 AND `platform`='Mac' AND `arch`='x64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57292 AND `platform`='Mac' AND `arch`='x64' AND `type`='WoWC';
+DELETE FROM `build_auth_key` WHERE `build`=57292 AND `platform`='Win' AND `arch`='A64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57292 AND `platform`='Win' AND `arch`='x64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57292 AND `platform`='Win' AND `arch`='x64' AND `type`='WoWC';
+INSERT INTO `build_auth_key` (`build`,`platform`,`arch`,`type`,`key`) VALUES
+(57292,'Mac','A64','WoW',0xAB48CDD3449440EAA6E72D43914A3824),
+(57292,'Mac','A64','WoWC',0x46E6FC16392B1B0B686BDC12C92A6D18),
+(57292,'Mac','x64','WoW',0x375B0734567192503CC9F9C8028DC6F3),
+(57292,'Mac','x64','WoWC',0xE59F7530C7BFF77CB62E62D4E6D3EEC8),
+(57292,'Win','A64','WoW',0xF42B862D0559CCD3204B2DF58E995761),
+(57292,'Win','x64','WoW',0xED8353327D5538F75FAF2E0C584D2EF3),
+(57292,'Win','x64','WoWC',0x67FC9D56CC96C6F4ACE29151375962F4);
+
+UPDATE `realmlist` SET `gamebuild`=57292 WHERE `gamebuild` IN (56819,57171,57212);
+
+ALTER TABLE `realmlist` CHANGE `gamebuild` `gamebuild` int unsigned NOT NULL DEFAULT '57292';
diff --git a/sql/old/11.x/auth/24092_2024_11_16/2024_11_01_00_auth.sql b/sql/old/11.x/auth/24092_2024_11_16/2024_11_01_00_auth.sql
new file mode 100644
index 00000000000..8785a0705d3
--- /dev/null
+++ b/sql/old/11.x/auth/24092_2024_11_16/2024_11_01_00_auth.sql
@@ -0,0 +1,23 @@
+DELETE FROM `build_info` WHERE `build` IN (57388);
+INSERT INTO `build_info` (`build`,`majorVersion`,`minorVersion`,`bugfixVersion`,`hotfixVersion`) VALUES
+(57388,11,0,5,NULL);
+
+DELETE FROM `build_auth_key` WHERE `build`=57388 AND `platform`='Mac' AND `arch`='A64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57388 AND `platform`='Mac' AND `arch`='A64' AND `type`='WoWC';
+DELETE FROM `build_auth_key` WHERE `build`=57388 AND `platform`='Mac' AND `arch`='x64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57388 AND `platform`='Mac' AND `arch`='x64' AND `type`='WoWC';
+DELETE FROM `build_auth_key` WHERE `build`=57388 AND `platform`='Win' AND `arch`='A64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57388 AND `platform`='Win' AND `arch`='x64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57388 AND `platform`='Win' AND `arch`='x64' AND `type`='WoWC';
+INSERT INTO `build_auth_key` (`build`,`platform`,`arch`,`type`,`key`) VALUES
+(57388,'Mac','A64','WoW',0xFA9D6F044063948AB74FB3FDE335C0D5),
+(57388,'Mac','A64','WoWC',0x3962974506F57EF33BBBD4C904A82157),
+(57388,'Mac','x64','WoW',0xE7E3432357BC634EBD7B76BA40D51FD2),
+(57388,'Mac','x64','WoWC',0xBFFDD44D892A2143B61BB59E448E348C),
+(57388,'Win','A64','WoW',0x92129DE92D2BA5F67581C5DE642B1289),
+(57388,'Win','x64','WoW',0x16F9CB724C662E3E8029A442443C6305),
+(57388,'Win','x64','WoWC',0xDFD9BFFD00FBEFECBF0AEF0FFC9E8F2D);
+
+UPDATE `realmlist` SET `gamebuild`=57388 WHERE `gamebuild`=57292;
+
+ALTER TABLE `realmlist` CHANGE `gamebuild` `gamebuild` int unsigned NOT NULL DEFAULT '57388';
diff --git a/sql/old/11.x/auth/24092_2024_11_16/2024_11_11_00_auth.sql b/sql/old/11.x/auth/24092_2024_11_16/2024_11_11_00_auth.sql
new file mode 100644
index 00000000000..58b45dc37cd
--- /dev/null
+++ b/sql/old/11.x/auth/24092_2024_11_16/2024_11_11_00_auth.sql
@@ -0,0 +1,12 @@
+ALTER TABLE `account`
+  MODIFY `last_ip` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '127.0.0.1' AFTER `joindate`,
+  MODIFY `last_attempt_ip` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '127.0.0.1' AFTER `last_ip`;
+
+ALTER TABLE `battlenet_accounts` 
+  MODIFY `last_ip` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '127.0.0.1' AFTER `joindate`;
+
+ALTER TABLE `ip_banned` 
+  MODIFY `ip` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '127.0.0.1' FIRST;
+
+ALTER TABLE `logs_ip_actions` 
+  MODIFY `ip` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '127.0.0.1' AFTER `type`;
diff --git a/sql/old/11.x/auth/24092_2024_11_16/2024_11_12_00_auth.sql b/sql/old/11.x/auth/24092_2024_11_16/2024_11_12_00_auth.sql
new file mode 100644
index 00000000000..3eb0af6c898
--- /dev/null
+++ b/sql/old/11.x/auth/24092_2024_11_16/2024_11_12_00_auth.sql
@@ -0,0 +1,5 @@
+ALTER TABLE `realmlist` 
+  MODIFY `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '127.0.0.1' AFTER `name`,
+  MODIFY `localAddress` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '127.0.0.1' AFTER `address`,
+  ADD `address3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL AFTER `localAddress`,
+  ADD `address4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL AFTER `address3`;
diff --git a/sql/old/11.x/auth/24092_2024_11_16/2024_11_14_00_auth.sql b/sql/old/11.x/auth/24092_2024_11_16/2024_11_14_00_auth.sql
new file mode 100644
index 00000000000..a32fafed936
--- /dev/null
+++ b/sql/old/11.x/auth/24092_2024_11_16/2024_11_14_00_auth.sql
@@ -0,0 +1,23 @@
+DELETE FROM `build_info` WHERE `build` IN (57534);
+INSERT INTO `build_info` (`build`,`majorVersion`,`minorVersion`,`bugfixVersion`,`hotfixVersion`) VALUES
+(57534,11,0,5,NULL);
+
+DELETE FROM `build_auth_key` WHERE `build`=57534 AND `platform`='Mac' AND `arch`='A64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57534 AND `platform`='Mac' AND `arch`='A64' AND `type`='WoWC';
+DELETE FROM `build_auth_key` WHERE `build`=57534 AND `platform`='Mac' AND `arch`='x64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57534 AND `platform`='Mac' AND `arch`='x64' AND `type`='WoWC';
+DELETE FROM `build_auth_key` WHERE `build`=57534 AND `platform`='Win' AND `arch`='A64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57534 AND `platform`='Win' AND `arch`='x64' AND `type`='WoW';
+DELETE FROM `build_auth_key` WHERE `build`=57534 AND `platform`='Win' AND `arch`='x64' AND `type`='WoWC';
+INSERT INTO `build_auth_key` (`build`,`platform`,`arch`,`type`,`key`) VALUES
+(57534,'Mac','A64','WoW',0x073B823E8991A0DF012AE33D661A6060),
+(57534,'Mac','A64','WoWC',0xC8824559DC24858EA7E3BD0971F98A15),
+(57534,'Mac','x64','WoW',0x0C7C67AAAD4011AA9D81AB838B255CDF),
+(57534,'Mac','x64','WoWC',0xCE9CD9A02B04F0C3B126F3C7E1BF7228),
+(57534,'Win','A64','WoW',0xCC612F1485F158C8B2730B7DFD926563),
+(57534,'Win','x64','WoW',0x948857BF76A28FD24C5A06D18B4F2FCF),
+(57534,'Win','x64','WoWC',0x62B0812A4518C21360685D4422C2A312);
+
+UPDATE `realmlist` SET `gamebuild`=57534 WHERE `gamebuild`=57388;
+
+ALTER TABLE `realmlist` CHANGE `gamebuild` `gamebuild` int unsigned NOT NULL DEFAULT '57534';