Trinitycore: Check if mysql strict mode is enabled before apply updates

[Aokromes]

Description:

Since people seem to be unable to read the wiki, where it states _MySQL strict mode must be disabled_, maybe we can add a check for it.

Branch(es):

ALL

TC rev. hash/commit:

N/A fe97d96

TDB version: N/A

Operating system: ALL

[joschiwald]

That is a bad solution, sql files should be applyable even strict mode is enabled

[ghost]

Maybe its better to extend the wiki article and show how to disable strict mode instead of saying that people do not read the wiki. I think most people don't know how to handle or disable strict mode as mysql is something they do not use that often (if at all). Just my two pennies...

[Aokromes]

Wiki states also don't use mysql 5.7 unless you known very well what to do. If you cannot use google, well, maybe building a server is out of your hands.

[ghost]

That's true lol

[DDuarte]

What's required for our SQL files to be compatible with strict mode?

[Shauren]

date column values cannot be set to 0

[joschiwald]

known problematic columns:
account.last_login
battlenet_account.last_login
game_event.start_time
game_event.end_time

[tje3d]

i do this ( for now 😅 ):

SET @sql_mode = @@SESSION.sql_mode;
SET session sql_mode = '';

-- queries ...

SET session sql_mode = @@sql_mode;

[joschiwald]

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;

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;

seems that the core already handle null values correctly
testing ...

[berkbuzcu]

@Aokromes UBUNTU 17+ doesn't support MySQL 5.6 even with hacks and tricks, any newbie to linux dude probably can't figure it out. So most users are forced to use MySQL 5.7 now that Ubuntu 16.04 is deprecated.

[Aokromes]

That's why it's writen "unless you known very well what are you doing"

[berkbuzcu]

I don't think you understand, or are taking the context off what's written on purpose. The wiki quote is "DON'T use the 5.7.xx versions unless you know VERY WELL what are you doing. You will have issues with 5.7+ !!!" It means that 5.7 is what you shouldn't use unless you know what you are doing. However, with Ubuntu 17.10 (and possibly 17.04) you can not install MySQL server 5.6 unless you are very well versed in linux, even then, I don't think it should be done. A person that can do this can easily maintain 5.7 anyway. The main argument is 16.04 is deprecated and you can't use 5.6 on 17+ versions. Thus I believe 5.7 support should be provided.

[Aokromes]

That's why you must known how to google how to disable mysql strict mode, anyway, when new TDB is released that "limitation" will be lifted if someone probes you can compile on windows without having to download external files.
Also, it's a good way to learn how to google or disable strict mode
Also 2: that warning you posted is from windows only because lack of certain files, information maybe is outdated.

[tje3d]

Just use MySQL 5.7 and you will not have any problem ( I have no problem for more than 5 months. )

[berkbuzcu]

As a disclaimer I think I should state that I don't have any problems but you are right, it's just a google away. Just stating what might be off your sights, sorry for the bother.

[jackpoz]

what's missing to mark this issues as implemented ?