Jump to content
TrinityCore
mpk

[SOLVED]MySql update causes world update failure because of wrong default values

Recommended Posts

I've updated to the new version of MySql (5.7.20) on ubuntu and it causes the following error:

>> Applying update "2017_10_27_01_world.sql" 'BDDAB16'...
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1067 (42000) at line 181: Invalid default value for 'start_time'

Line 181 of the mentioned update is

ALTER TABLE `game_event` ADD COLUMN `holidayStage` TINYINT UNSIGNED NOT NULL DEFAULT '0' AFTER `holiday`;

however it only indirectly causes the world server startup error. The problem mysql is talking about is in the table game_event the two columns start_time and end_time have a default value of '0000-00-00 00:00:00'

vdg8pvn.png

 

however in the mysql documentation it is clearly states that the range of Type timestamp is between 1970-01-01 and 2038-01-19


USsyo52.png

https://dev.mysql.com/doc/refman/5.7/en/datetime.html

therefore the default value is out of range, which causes the error. I'm not sure why mysql allowed setting this default value in the first place or why it is only noticed now by it but it has to do with the new version of mysql 5.7.20 since it went unnoticed by previous versions.
Fixing the problem:
Changing the default value of the two defective columns to the minimum allowed value sadly only results in another error since multiple rows in game_event already have faulty start_time and end_time  values. Only changing both fixes the error and the world server start normally.
The attached sql file fixes the problem and also corrects a minor typo of the comment of end_time "[..]aflter." -> "[..]after."

fix_world_game_event_default_values.sql

Share this post


Link to post
Share on other sites

Nevermind, this was already discussed in https://github.com/TrinityCore/TrinityCore/issues/20739

\ Thread can be closed

THOUGH technically the wiki says

MySQL ≥ 5.1.0 

https://trinitycore.atlassian.net/wiki/spaces/tc/pages/10977288/Linux+Requirements

and the copypasta package installer commands use mysql-server which will always install the latest version.
So it is technically a bug after all:D

Edited by mpk

Share this post


Link to post
Share on other sites

--- Canned message start --- It appears the issue in the original post was solved, so this thread shall be closed. Should you encounter any other difficulties, please open a new thread. --- Canned message end ---

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...