DB: Made timestamp columns, those can have no date, nullable instead of saving invalid date '0000-00-00 00:00:00'

* This makes our tables mysql strict mode conform

Ref #21113

2 files changed, 25 insertions, 0 deletions

diff --git a/sql/updates/auth/master/2018_01_02_00_auth.sql b/sql/updates/auth/master/2018_01_02_00_auth.sql
new file mode 100644
index 00000000000..891380c76be
--- /dev/null
+++ b/sql/updates/auth/master/2018_01_02_00_auth.sql
@@ -0,0 +1,14 @@
+SET @sql_mode = @@session.sql_mode;
+SET SESSION sql_mode = '';
+
+ALTER TABLE `account`
+  CHANGE `last_login` `last_login` TIMESTAMP NULL;
+
+UPDATE `account` SET `last_login`=NULL WHERE `last_login`='0000-00-00 00:00:00';
+
+ALTER TABLE `battlenet_accounts`
+  CHANGE `last_login` `last_login` TIMESTAMP NULL;
+
+UPDATE `battlenet_accounts` SET `last_login`=NULL WHERE `last_login`='0000-00-00 00:00:00';
+
+SET SESSION sql_mode = @@sql_mode;
diff --git a/sql/updates/world/master/2018_01_02_00_world.sql b/sql/updates/world/master/2018_01_02_00_world.sql
new file mode 100644
index 00000000000..d15d303ca78
--- /dev/null
+++ b/sql/updates/world/master/2018_01_02_00_world.sql
@@ -0,0 +1,11 @@
+SET @sql_mode = @@session.sql_mode;
+SET SESSION sql_mode = '';
+
+ALTER TABLE `game_event`
+  CHANGE `start_time` `start_time` TIMESTAMP NULL COMMENT 'Absolute start date, the event will never start before',
+  CHANGE `end_time` `end_time` TIMESTAMP 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';
+
+SET SESSION sql_mode = @@sql_mode;