Requirement: Creating New Tables

[John Adams]

Guys,

When we make any new tables for Emu, please remember to force collation to latin_general_ci, and not this hokey ass Swedish crap. I think that's what MySQL defaults to if the server isn't set to a specific collation. Either way, I have spent literally hours debugging a problem that was caused by mis-matched collations.

And now that I have spent the afternoon "FIXING" both World and Parser databases collations, I do not want to see anymore f**king Swedes!!!

Make sure your table creates end with:

Code: Select all

) ENGINE=INNODB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

eg.,

Code: Select all

DROP TABLE IF EXISTS `rulesets`;
CREATE TABLE `rulesets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ruleset_id` int(10) unsigned NOT NULL,
  `ruleset_name` varchar(64) NOT NULL DEFAULT 'default_ruleset',
  `ruleset_active` tinyint(1) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `RuleNameIDX` (`ruleset_name`),
  KEY `RulesIDX` (`ruleset_id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

You should not need to set collation on every text-base field, because if the table collation is set, the columns will default.

Also, you can force your MySQL to the new standard collation with these entries in my.cnf/my.ini files:

Code: Select all

character-set-server = latin1
collation-server = latin1_general_ci

You must comply.



(sorry, been watching a lot of 7 of 9 Voyager episodes...)

[Zcoretri]

You must comply.

(sorry, been watching a lot of 7 of 9 Voyager episodes...)

Loved Voyager and 7 of 9 of course was hot

Duly noted John...modifying my config file.

[John Adams]

After wasting hours trying to figure out what was wrong with my DB Editor scripts (again), I discovered that almost all DB patches applied recently neglected to define the 'latin1_general_ci' collation, which I can only blame myself for overlooking since I am the one who committed them

Moving forward though, I'd like a little more attention paid to DB tables and fields we create or modify, to include the proper collation. MySQL has become a total bitch to work with as it is (5.5+) with all the restrictions etc... so this one thing really is an inconvenience for me to fix.

Brand new DB's coming from PatchServer are once again fixed as of tonight. To update your own (if you encounter collation errors) I have attached some queries to update your local DBs. I won't be fixing this in PatchServer, since it's just too big when only a few of our tables were bad.


Let me know if you have any questions. And as always, back up your DB -=BEFORE=- you run any queries that modify it

[Gangrenous]

I will reiterate this is very important to default in your my.cnf, it is very irritating to run into this issue.