I have issues with UTF8 conflicts in my current database because it defaults to 'latin1_swedish_ci' for the collation_connection variable as originally setup in the master database. I cannot globally alter the database as this would break several other databases running that require this setting to be latin1_swedish_ci.

I've gone through the code and tweaked several places so that it first sets UTF8 before making another connection,
e.g.:

Code: Select all

diff -U2 menu.php menu.php.orig
--- menu.php    2007-10-05 22:13:05.000000000 -0400
+++ menu.php.orig       2007-10-05 11:43:40.000000000 -0400
@@ -161,13 +161,8 @@
                {
                        // Get the scope select data
-                       $query = "SET NAMES utf8;";
-                        $result = $roster->db->query($query);
-                       $query = "SET character set utf8;";
-                        $result = $roster->db->query($query);
-
-                        $query = "SELECT `guild_name`, CONCAT(`region`,'-',`server`), `guild_id` FROM `" . $roster->db->table('guild') . "`"
+                       $query = "SELECT `guild_name`, CONCAT(`region`,'-',`server`), `guild_id` FROM `" . $roster->db->table('guild') . "`"
                                   . " ORDER BY `region` ASC, `server` ASC, `guild_name` ASC;";
 
-                       $result = $roster->db->query($query);
+                       $result = $roster->db->query($query);
 
                        if( !$result )
@@ -211,9 +206,4 @@
                {
                        // Get the scope select data
-                       $query = "SET NAMES utf8;";
-                        $result = $roster->db->query($query);
-                       $query = "SET character set utf8;";
-                        $result = $roster->db->query($query);
-
                        $query = "SELECT `name`, `member_id` FROM `" . $roster->db->table('players') . "`"
                                   . " WHERE `guild_id` = '" . $roster->data['guild_id'] . "'"
@@ -609,9 +599,4 @@
 
                // --[ Fetch button list from DB ]--
-               $query = "SET NAMES utf8;";
-                $result = $roster->db->query($query);
-               $query = "SET character set utf8;";
-                $result = $roster->db->query($query);
-
                $query = "SELECT `mb`.*, `a`.`basename` "
                           . "FROM `" . $roster->db->table('menu_button') . "` AS mb "


but every time I patch one area another one pops up:
e.g.

Code: Select all

SQL:
SELECT `members`.`member_id`, `members`.`name`, `members`.`class`, `members`.`level`, `members`.`zone`, `members`.`online`, `members`.`last_online`, UNIX_TIMESTAMP(`members`.`last_online`) AS 'last_online_stamp', DATE_FORMAT( DATE_ADD(`members`.`last_online`, INTERVAL 0 HOUR ), '%a %b %D, %l:%i %p' ) AS 'last_online_format', `members`.`note`, `members`.`guild_title`, `alts`.`main_id`, `guild`.`update_time`, IF( `members`.`note` IS NULL OR `members`.`note` = '', 1, 0 ) AS 'nisnull', `members`.`officer_note`, IF( `members`.`officer_note` IS NULL OR `members`.`officer_note` = '', 1, 0 ) AS 'onisnull', `members`.`guild_rank`, `players`.`server`, `players`.`race`, `players`.`sex`, `players`.`exp`, `players`.`clientLocale`, `players`.`lifetimeRankName`, `players`.`lifetimeHighestRank`, IF( `players`.`lifetimeHighestRank` IS NULL OR `players`.`lifetimeHighestRank` = '0', 1, 0 ) AS 'risnull', `players`.`hearth`, IF( `players`.`hearth` IS NULL OR `players`.`hearth` = '', 1, 0 ) AS 'hisnull', UNIX_TIMESTAMP( `players`.`dateupdatedutc`) AS 'last_update_stamp', DATE_FORMAT( DATE_ADD(`players`.`dateupdatedutc`, INTERVAL 0 HOUR ), '%a %b %D, %l:%i %p' ) AS 'last_update_format', IF( `players`.`dateupdatedutc` IS NULL OR `players`.`dateupdatedutc` = '', 1, 0 ) AS 'luisnull', `proftable`.`professions`, `talenttable`.`talents` FROM `roster_members` AS members LEFT JOIN `roster_players` AS players ON `members`.`member_id` = `players`.`member_id` LEFT JOIN (SELECT `member_id` , GROUP_CONCAT( CONCAT( `skill_name` , '|', `skill_level` ) ORDER BY `skill_order`) AS 'professions' FROM `roster_skills` GROUP BY `member_id`) AS proftable ON `members`.`member_id` = `proftable`.`member_id` LEFT JOIN (SELECT `member_id` , GROUP_CONCAT( CONCAT( `tree` , '|', `pointsspent` , '|', `background` ) ORDER BY `order`) AS 'talents' FROM `roster_talenttree` GROUP BY `member_id`) AS talenttable ON `members`.`member_id` = `talenttable`.`member_id` LEFT JOIN `roster_addons_memberslist_alts` AS alts ON `members`.`member_id` = `alts`.`member_id` LEFT JOIN `roster_guild` AS guild ON `members`.`guild_id` = `guild`.`guild_id` WHERE `members`.`guild_id` = "2" ORDER BY IF(`members`.`member_id` = `alts`.`member_id`,1,0), `members`.`level` DESC, `members`.`name` ASC;
File: lib/dbal/mysql.php
Line: 203
Backtrace (most recent call last):

    * lib/functions.lib.php
          o Line: 236
          o Function Called: backtrace
    * lib/dbal/mysql.php
          o Line: 203
          o Function Called: die_quietly
          o Arguments:
                + 1270: Illegal mix of collations (utf8_general_ci,IMPLICIT), (latin1_swedish_ci,COERCIBLE), (utf8_general_ci,IMPLICIT) for operation 'concat'
                + Database Error
                + /array1/webfiles/wowroster/roster-2.0beta-1377/lib/dbal/mysql.php
                + 203
                + SELECT `members`.`member_id`, `members`.`name`, `members`.`class`, `members`.`level`, `members`.`zone`, `members`.`online`, `members`.`last_online`, UNIX_TIMESTAMP(`members`.`last_online`) AS 'last_online_stamp', DATE_FORMAT( DATE_ADD(`members`.`last_online`, INTERVAL 0 HOUR ), '%a %b %D, %l:%i %p' ) AS 'last_online_format', `members`.`note`, `members`.`guild_title`, `alts`.`main_id`, `guild`.`update_time`, IF( `members`.`note` IS NULL OR `members`.`note` = '', 1, 0 ) AS 'nisnull', `members`.`officer_note`, IF( `members`.`officer_note` IS NULL OR `members`.`officer_note` = '', 1, 0 ) AS 'onisnull', `members`.`guild_rank`, `players`.`server`, `players`.`race`, `players`.`sex`, `players`.`exp`, `players`.`clientLocale`, `players`.`lifetimeRankName`, `players`.`lifetimeHighestRank`, IF( `players`.`lifetimeHighestRank` IS NULL OR `players`.`lifetimeHighestRank` = '0', 1, 0 ) AS 'risnull', `players`.`hearth`, IF( `players`.`hearth` IS NULL OR `players`.`hearth` = '', 1, 0 ) AS 'hisnull', UNIX_TIMESTAMP( `players`.`dateupdatedutc`) AS 'last_update_stamp', DATE_FORMAT( DATE_ADD(`players`.`dateupdatedutc`, INTERVAL 0 HOUR ), '%a %b %D, %l:%i %p' ) AS 'last_update_format', IF( `players`.`dateupdatedutc` IS NULL OR `players`.`dateupdatedutc` = '', 1, 0 ) AS 'luisnull', `proftable`.`professions`, `talenttable`.`talents` FROM `roster_members` AS members LEFT JOIN `roster_players` AS players ON `members`.`member_id` = `players`.`member_id` LEFT JOIN (SELECT `member_id` , GROUP_CONCAT( CONCAT( `skill_name` , '|', `skill_level` ) ORDER BY `skill_order`) AS 'professions' FROM `roster_skills` GROUP BY `member_id`) AS proftable ON `members`.`member_id` = `proftable`.`member_id` LEFT JOIN (SELECT `member_id` , GROUP_CONCAT( CONCAT( `tree` , '|', `pointsspent` , '|', `background` ) ORDER BY `order`) AS 'talents' FROM `roster_talenttree` GROUP BY `member_id`) AS talenttable ON `members`.`member_id` = `talenttable`.`member_id` LEFT JOIN `roster_addons_memberslist_alts` AS alts ON `members`.`member_id` = `alts`.`member_id` LEFT JOIN `roster_guild` AS guild ON `members`.`guild_id` = `guild`.`guild_id` WHERE `members`.`guild_id` = "2" ORDER BY IF(`members`.`member_id` = `alts`.`member_id`,1,0), `members`.`level` DESC, `members`.`name` ASC;
    * addons/memberslist/inc/memberslist.php
          o Line: 413
          o Function Called: query
          o Arguments:
                + SELECT `members`.`member_id`, `members`.`name`, `members`.`class`, `members`.`level`, `members`.`zone`, `members`.`online`, `members`.`last_online`, UNIX_TIMESTAMP(`members`.`last_online`) AS 'last_online_stamp', DATE_FORMAT( DATE_ADD(`members`.`last_online`, INTERVAL 0 HOUR ), '%a %b %D, %l:%i %p' ) AS 'last_online_format', `members`.`note`, `members`.`guild_title`, `alts`.`main_id`, `guild`.`update_time`, IF( `members`.`note` IS NULL OR `members`.`note` = '', 1, 0 ) AS 'nisnull', `members`.`officer_note`, IF( `members`.`officer_note` IS NULL OR `members`.`officer_note` = '', 1, 0 ) AS 'onisnull', `members`.`guild_rank`, `players`.`server`, `players`.`race`, `players`.`sex`, `players`.`exp`, `players`.`clientLocale`, `players`.`lifetimeRankName`, `players`.`lifetimeHighestRank`, IF( `players`.`lifetimeHighestRank` IS NULL OR `players`.`lifetimeHighestRank` = '0', 1, 0 ) AS 'risnull', `players`.`hearth`, IF( `players`.`hearth` IS NULL OR `players`.`hearth` = '', 1, 0 ) AS 'hisnull', UNIX_TIMESTAMP( `players`.`dateupdatedutc`) AS 'last_update_stamp', DATE_FORMAT( DATE_ADD(`players`.`dateupdatedutc`, INTERVAL 0 HOUR ), '%a %b %D, %l:%i %p' ) AS 'last_update_format', IF( `players`.`dateupdatedutc` IS NULL OR `players`.`dateupdatedutc` = '', 1, 0 ) AS 'luisnull', `proftable`.`professions`, `talenttable`.`talents` FROM `roster_members` AS members LEFT JOIN `roster_players` AS players ON `members`.`member_id` = `players`.`member_id` LEFT JOIN (SELECT `member_id` , GROUP_CONCAT( CONCAT( `skill_name` , '|', `skill_level` ) ORDER BY `skill_order`) AS 'professions' FROM `roster_skills` GROUP BY `member_id`) AS proftable ON `members`.`member_id` = `proftable`.`member_id` LEFT JOIN (SELECT `member_id` , GROUP_CONCAT( CONCAT( `tree` , '|', `pointsspent` , '|', `background` ) ORDER BY `order`) AS 'talents' FROM `roster_talenttree` GROUP BY `member_id`) AS talenttable ON `members`.`member_id` = `talenttable`.`member_id` LEFT JOIN `roster_addons_memberslist_alts` AS alts ON `members`.`member_id` = `alts`.`member_id` LEFT JOIN `roster_guild` AS guild ON `members`.`guild_id` = `guild`.`guild_id` WHERE `members`.`guild_id` = "2" ORDER BY IF(`members`.`member_id` = `alts`.`member_id`,1,0), `members`.`level` DESC, `members`.`name` ASC;
    * addons/memberslist/guild/index.php
          o Line: 229
          o Function Called: makememberslist
    * index.php
          o Line: 112
          o Function Called: require
          o Arguments:
                + /array1/webfiles/wowroster/roster-2.0beta-1377/addons/memberslist/guild/index.php


So, is there a better place I'm missing for using "SET NAMES UTF8" and "SET CHARACTER SET UTF8" so that it persists throughout each DB connection?

Otherwise I have a llloooootttt of tweaking to do

http://wowroster.sniikt.com
MYSQL 4.7.1

lib/db/mysql.php already sets these when it connects to the database

If you're referring to lines 102 and 103 of lib/dbal/mysql.php:

Code: Select all

                else
                {   
                        $this->link_id = @mysql_connect($dbhost, $dbuser, $dbpass);
                }

                mysql_query("SET NAMES 'utf8'");
                mysql_query("SET CHARACTER SET 'utf8'");

                if( (is_resource($this->link_id)) && (!is_null($this->link_id)) && ($dbname != '') )
                {
                        if( !@mysql_select_db($dbname, $this->link_id) )
                        {
                                @mysql_close($this->link_id);
                                $this->link_id = false;


this setting either is not working, or is not remaining constant across all connections to the database.

Try setting up a mysql database with latin1_swedish_ci setup as the default collation_connection setting and installing wowroster. You'll get the same errors.

Working fine on mine

Code: Select all

mysql>SHOW VARIABLES LIKE 'collation\_%';
Variable_name   Value
collation_connection    latin1_swedish_ci
collation_database      latin1_swedish_ci
collation_server        latin1_swedish_ci

Hmm, well anyway I must have some other setting that is causing that variable to be reset and not persist through connections.

Regardless, I've finally gotten all the problem areas tweaked on my site and things are running smoothly.

If anyone is interested in which files I changed and what changes I made, let me know and I'll be happy to post up the diffs.

Thanks again for all the feedback and the great product guys!

Cheers!

Roster sets it for the current connection on each page load. The only way it could change back is some other app on the same connection changing it, or your connection dropping and automatically reestablishing.