fix(setup): MySQL 5.7 (#1445)

5 files changed, 30 insertions, 34 deletions

diff --git a/data/sql/base/db_auth/account.sql b/data/sql/base/db_auth/account.sql
index d2ad2b7904..9125c61e79 100644
--- a/data/sql/base/db_auth/account.sql
+++ b/data/sql/base/db_auth/account.sql
@@ -20,7 +20,7 @@ CREATE TABLE `account`
   `last_ip` varchar(15) NOT NULL DEFAULT '127.0.0.1',
   `failed_logins` int(10) unsigned NOT NULL DEFAULT '0',
   `locked` tinyint(3) unsigned NOT NULL DEFAULT '0',
-  `last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `last_login` TIMESTAMP NULL DEFAULT NULL,
   `online` tinyint(3) unsigned NOT NULL DEFAULT '0',
   `expansion` tinyint(3) unsigned NOT NULL DEFAULT '0',
   `mutetime` bigint(20) NOT NULL DEFAULT '0',
@@ -37,16 +37,16 @@ CREATE TABLE `account`
 LOCK TABLES `account` WRITE;
 /*!40000 ALTER TABLE `account` DISABLE KEYS */;
 INSERT INTO `account` VALUES 
-(1,'test1','047ce22643f9b0bd6baeb18d51bf1075a4d43fc6','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0),
-(2,'test2','10eb1ff16cf5380147e8281cd8080a210ecb3c53','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0),
-(3,'test3','e546bbf9ca93ae5291f0b441bb9ea2fa0c466176','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0),
-(4,'test4','61015d83b456a9c6a7defdff07f55265f24097af','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0),
-(5,'test5','dddeac4ffe5f286ec57b7a1ed63bf3a859debe1e','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0),
-(6,'test6','f1f94cdffd83c8c4182d66689077f92c807ab579','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0),
-(7,'test7','6fcd35c35b127be1d9ca040b2b478eb366506ce2','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0),
-(8,'test8','484332ccb02e284e4e0a04573c3fa417f4745fdf','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0),
-(9,'test9','4fce15ed251721f02754d5381ae9d0137b6a6a30','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0),
-(10,'test10','b22d249228e84ab493b39a2bd765bee9b7c0b350','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,'0000-00-00 00:00:00',0,2,0,'','',0,'',0);
+(1,'test1','047ce22643f9b0bd6baeb18d51bf1075a4d43fc6','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,NULL,0,2,0,'','',0,'',0),
+(2,'test2','10eb1ff16cf5380147e8281cd8080a210ecb3c53','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,NULL,0,2,0,'','',0,'',0),
+(3,'test3','e546bbf9ca93ae5291f0b441bb9ea2fa0c466176','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,NULL,0,2,0,'','',0,'',0),
+(4,'test4','61015d83b456a9c6a7defdff07f55265f24097af','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,NULL,0,2,0,'','',0,'',0),
+(5,'test5','dddeac4ffe5f286ec57b7a1ed63bf3a859debe1e','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,NULL,0,2,0,'','',0,'',0),
+(6,'test6','f1f94cdffd83c8c4182d66689077f92c807ab579','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,NULL,0,2,0,'','',0,'',0),
+(7,'test7','6fcd35c35b127be1d9ca040b2b478eb366506ce2','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,NULL,0,2,0,'','',0,'',0),
+(8,'test8','484332ccb02e284e4e0a04573c3fa417f4745fdf','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,NULL,0,2,0,'','',0,'',0),
+(9,'test9','4fce15ed251721f02754d5381ae9d0137b6a6a30','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,NULL,0,2,0,'','',0,'',0),
+(10,'test10','b22d249228e84ab493b39a2bd765bee9b7c0b350','','','','','2016-01-30 21:09:43','127.0.0.1',0,0,NULL,0,2,0,'','',0,'',0);
 /*!40000 ALTER TABLE `account` ENABLE KEYS */;
 UNLOCK TABLES;
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
diff --git a/data/sql/base/db_world/game_event.sql b/data/sql/base/db_world/game_event.sql
index f507b42368..3ade9d3a6d 100644
--- a/data/sql/base/db_world/game_event.sql
+++ b/data/sql/base/db_world/game_event.sql
@@ -10,8 +10,8 @@ DROP TABLE IF EXISTS `game_event`;
 CREATE TABLE `game_event` 
 (
   `eventEntry` tinyint(3) unsigned NOT NULL COMMENT 'Entry of the game event',
-  `start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Absolute start date, the event will never start before',
-  `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Absolute end date, the event will never start afler',
+  `start_time` TIMESTAMP NULL DEFAULT NULL COMMENT 'Absolute start date, the event will never start before',
+  `end_time` TIMESTAMP NULL DEFAULT NULL COMMENT 'Absolute end date, the event will never start after',
   `occurence` bigint(20) unsigned NOT NULL DEFAULT '5184000' COMMENT 'Delay in minutes between occurences of the event',
   `length` bigint(20) unsigned NOT NULL DEFAULT '2592000' COMMENT 'Length in minutes of the event',
   `holiday` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'Client side holiday id',
@@ -36,16 +36,16 @@ INSERT INTO `game_event` VALUES
 (10,'2014-04-27 22:01:00','2020-12-31 05:00:00',525600,10080,201,'Children\'s Week',0),
 (11,'2013-09-12 23:01:00','2020-12-31 05:00:00',525600,10080,321,'Harvest Festival',0),
 (12,'2014-10-18 22:00:00','2020-12-31 05:00:00',525600,20160,324,'Hallow\'s End',0),
-(13,'0000-00-00 00:00:00','0000-00-00 00:00:00',525600,1,0,'Elemental Invasions',0),
+(13,NULL,NULL,525600,1,0,'Elemental Invasions',0),
 (14,'2014-03-23 00:00:00','2020-12-31 05:00:00',10080,1440,0,'Fishing Extravaganza Announce',0),
 (15,'2014-03-23 13:00:00','2020-12-31 05:00:00',10080,120,0,'Fishing Extravaganza Fishing Pools',0),
 (16,'2007-08-05 03:00:00','2020-12-31 05:00:00',180,120,0,'Gurubashi Arena Booty Run',0),
-(17,'0000-00-00 00:00:00','0000-00-00 00:00:00',525600,1,0,'Scourge Invasion',0),
+(17,NULL,NULL,525600,1,0,'Scourge Invasion',0),
 (18,'2010-05-07 06:00:00','2020-12-31 05:00:00',60480,6240,283,'Call to Arms: Alterac Valley!',0),
 (19,'2010-04-02 06:00:00','2020-12-31 05:00:00',60480,6240,284,'Call to Arms: Warsong Gulch!',0),
 (20,'2010-04-23 06:00:00','2020-12-31 05:00:00',60480,6240,285,'Call to Arms: Arathi Basin!',0),
 (21,'2010-04-30 06:00:00','2020-12-31 05:00:00',60480,6240,353,'Call to Arms: Eye of the Storm!',0),
-(22,'0000-00-00 00:00:00','0000-00-00 00:00:00',525600,1,0,'AQ War Effort',0),
+(22,NULL,NULL,525600,1,0,'AQ War Effort',0),
 (23,'2014-09-03 23:01:00','2020-12-31 05:00:00',131040,4320,0,'Darkmoon Faire Building (Elwynn Forest)',0),
 (24,'2014-09-20 22:01:00','2020-12-31 05:00:00',525600,21600,372,'Brewfest',0),
 (25,'2015-07-29 19:00:00','2020-12-31 05:00:00',1440,480,0,'Pyrewood Village',0),
@@ -54,7 +54,7 @@ INSERT INTO `game_event` VALUES
 (28,'2008-04-07 05:00:00','2020-12-31 05:00:00',86400,21600,0,'Edge of Madness, Hazza\'rah',0),
 (29,'2008-04-21 05:00:00','2020-12-31 05:00:00',86400,21600,0,'Edge of Madness, Renataki',0),
 (30,'2008-05-05 05:00:00','2020-12-31 05:00:00',86400,21600,0,'Edge of Madness, Wushoolay',0),
-(31,'0000-00-00 00:00:00','0000-00-00 00:00:00',5184000,2592000,0,'Arena Tournament',0),
+(31,NULL,NULL,5184000,2592000,0,'Arena Tournament',0),
 (32,'2008-05-15 19:00:00','2020-01-01 07:00:00',10080,5,0,'L70ETC Concert',0),
 (33,'2011-03-21 23:10:00','2020-03-21 23:00:00',30,5,0,'Dalaran: Minigob',0),
 (34,'2012-09-30 22:01:00','2020-12-31 05:00:00',525600,44640,0,'Brew of the Month October',0),
@@ -69,25 +69,25 @@ INSERT INTO `game_event` VALUES
 (43,'2012-06-30 22:01:00','2020-12-31 05:00:00',525600,44640,0,'Brew of the Month July',0),
 (44,'2012-07-31 22:01:00','2020-12-31 05:00:00',525600,44640,0,'Brew of the Month August',0),
 (45,'2012-08-31 22:01:00','2020-12-31 05:00:00',525600,44640,0,'Brew of the Month September',0),
-(48,'0000-00-00 00:00:00','0000-00-00 00:00:00',5184000,2592000,0,'Wintergrasp Alliance Defence',5),
-(49,'0000-00-00 00:00:00','0000-00-00 00:00:00',5184000,2592000,0,'Wintergrasp Horde Defence',5),
+(48,NULL,NULL,5184000,2592000,0,'Wintergrasp Alliance Defence',5),
+(49,NULL,NULL,5184000,2592000,0,'Wintergrasp Horde Defence',5),
 (50,'2013-09-18 23:01:00','2020-12-31 04:00:00',525600,1440,398,'Pirates\' Day',0),
 (51,'2013-11-01 01:00:00','2020-12-31 05:00:00',525600,2820,409,'Day of the Dead',0),
 (52,'2015-12-25 05:00:00','2020-12-31 05:00:00',525600,11700,0,'Winter Veil: Gifts',0),
 (53,'2010-04-09 06:00:00','2020-12-31 09:00:00',60480,6240,400,'Call to Arms: Strand of the Ancients!',0),
 (54,'2010-04-16 06:00:00','2020-12-31 09:00:00',60480,6240,420,'Call to Arms: Isle of Conquest!',0),
-(55,'0000-00-00 00:00:00','0000-00-00 00:00:00',5184000,2592000,0,'Arena Season 3',0),
-(56,'0000-00-00 00:00:00','0000-00-00 00:00:00',5184000,2592000,0,'Arena Season 4',0),
-(57,'0000-00-00 00:00:00','0000-00-00 00:00:00',5184000,2592000,0,'Arena Season 5',0),
-(58,'0000-00-00 00:00:00','0000-00-00 00:00:00',5184000,2592000,0,'Arena Season 6',0),
-(59,'0000-00-00 00:00:00','0000-00-00 00:00:00',5184000,2592000,0,'Arena Season 7',0),
-(60,'0000-00-00 00:00:00','0000-00-00 00:00:00',5184000,2592000,0,'Arena Season 8',0),
+(55,NULL,NULL,5184000,2592000,0,'Arena Season 3',0),
+(56,NULL,NULL,5184000,2592000,0,'Arena Season 4',0),
+(57,NULL,NULL,5184000,2592000,0,'Arena Season 5',0),
+(58,NULL,NULL,5184000,2592000,0,'Arena Season 6',0),
+(59,NULL,NULL,5184000,2592000,0,'Arena Season 7',0),
+(60,NULL,NULL,5184000,2592000,0,'Arena Season 8',0),
 (61,'2010-09-06 23:00:00','2010-10-09 23:00:00',9999999,47520,0,'Zalazane\'s Fall',0),
 (62,'2014-03-23 13:00:00','2020-12-31 05:00:00',10080,180,301,'Fishing Extravaganza Turn-ins',0),
 (63,'2014-03-22 12:00:00','2020-12-31 05:00:00',10080,180,424,'Kalu\'ak Fishing Derby Turn-ins',0),
 (64,'2014-03-22 13:00:00','2020-12-31 05:00:00',10080,60,0,'Kalu\'ak Fishing Derby Fishing Pools',0),
-(65,'0000-00-00 00:00:00','0000-00-00 00:00:00',5184000,2592000,0,'Venture Bay Alliance Defence',5),
-(66,'0000-00-00 00:00:00','0000-00-00 00:00:00',5184000,2592000,0,'Venture Bay Horde Defence',5),
+(65,NULL,NULL,5184000,2592000,0,'Venture Bay Alliance Defence',5),
+(66,NULL,NULL,5184000,2592000,0,'Venture Bay Horde Defence',5),
 (67,'2010-01-01 23:40:00','2020-12-31 05:00:00',60,5,0,'AT Event Trigger (Tirion Speech)',0),
 (68,'2010-01-01 23:55:00','2020-12-31 05:00:00',60,5,0,'AT Event Trigger (Horde Event)',0),
 (69,'2010-01-01 23:10:00','2020-12-31 05:00:00',60,5,0,'AT Event Trigger (Alliance Event)',0);
diff --git a/data/sql/updates/db_auth/2019_01_05_00.sql b/data/sql/updates/db_auth/2019_01_05_00.sql
index 0cf6e9dc90..aab66251b5 100644
--- a/data/sql/updates/db_auth/2019_01_05_00.sql
+++ b/data/sql/updates/db_auth/2019_01_05_00.sql
@@ -19,7 +19,7 @@ INSERT INTO version_db_auth (`sql_rev`) VALUES ('1546540718864817294');
 ALTER TABLE `account` CHANGE `last_login` `last_login` TIMESTAMP NULL DEFAULT NULL;
 ALTER TABLE `account` CHANGE `email` `email` VARCHAR(255) NOT NULL DEFAULT '';
 
-UPDATE `account` SET `last_login`=NULL WHERE `last_login`='0000-00-00 00:00:00';
+UPDATE IGNORE `account` SET `last_login`=NULL WHERE `last_login`='0000-00-00 00:00:00';
 
 --
 -- END UPDATING QUERIES
diff --git a/data/sql/updates/db_world/2017_10_15_00.sql b/data/sql/updates/db_world/2017_10_15_00.sql
index b0f9d5f6e3..8213242a9e 100644
--- a/data/sql/updates/db_world/2017_10_15_00.sql
+++ b/data/sql/updates/db_world/2017_10_15_00.sql
@@ -20,10 +20,6 @@ SELECT sql_rev INTO OK FROM version_db_world WHERE sql_rev = '150404422256069670
 --
 INSERT INTO version_db_world (`sql_rev`) VALUES ('1504044222560696700');
 
--- fix NO_ZERO_DATE error on mysql 5.7 (for latest linux platforms)
--- please use 5.6 on windows
-SET SESSION sql_mode = "ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES";
-
 DROP TABLE IF EXISTS holiday_dates;
 CREATE TABLE holiday_dates (
   id INT UNSIGNED NOT NULL,
diff --git a/data/sql/updates/db_world/2019_01_08_01.sql b/data/sql/updates/db_world/2019_01_08_01.sql
index 9b34e2e2f9..900726eeac 100644
--- a/data/sql/updates/db_world/2019_01_08_01.sql
+++ b/data/sql/updates/db_world/2019_01_08_01.sql
@@ -23,8 +23,8 @@ ALTER TABLE `game_event`
   CHANGE `start_time` `start_time` TIMESTAMP NULL DEFAULT NULL COMMENT 'Absolute start date, the event will never start before',
   CHANGE `end_time` `end_time` TIMESTAMP NULL DEFAULT NULL COMMENT 'Absolute end date, the event will never start after';
 
-UPDATE `game_event` SET `start_time`=NULL WHERE `start_time`='0000-00-00 00:00:00';
-UPDATE `game_event` SET `end_time`=NULL WHERE `end_time`='0000-00-00 00:00:00';
+UPDATE IGNORE `game_event` SET `start_time`=NULL WHERE `start_time`='0000-00-00 00:00:00';
+UPDATE IGNORE `game_event` SET `end_time`=NULL WHERE `end_time`='0000-00-00 00:00:00';
 
 --
 -- END UPDATING QUERIES