Location Mod

[1948Pal]

Wait so there is a table mm_pins? So if you go "SELECT * FROM mm_pins LIMIT 1" you get results?

No I had the db prefix all mixed up,  a "SELECT * FROM test_mm_pins LIMIT 1" will show results. Sorry

[Gobalopper]

Ok so the problem arises from those two tables having different character sets:

CHARSET=latin1 COLLATE=latin1_general_ci for mm_pins
and
CHARSET=latin1 for location_country

Can you run the show create table on one of the regular smf tables so we can find the default character set the other tables use? Try show create table test_messages;.

[1948Pal]

Show create table test_messages:

Code Select Expand

CREATE TABLE `test_messages` (\n  `ID_MSG` int(10) unsigned NOT NULL auto_increment,\n  `ID_TOPIC` mediumint(8) unsigned NOT NULL default '0',\n  `ID_MEMBER` mediumint(8) unsigned NOT NULL default '0',\n  `ID_MSG_MODIFIED` mediumint(8) unsigned NOT NULL default '0',\n  `subject` tinytext collate latin1_general_ci NOT NULL,\n  `posterName` tinytext collate latin1_general_ci NOT NULL,\n  `posterEmail` tinytext collate latin1_general_ci NOT NULL,\n  `posterTime` int(10) unsigned NOT NULL default '0',\n  `posterIP` tinytext collate latin1_general_ci NOT NULL,\n  `smileysEnabled` tinyint(4) NOT NULL default '1',\n  `modifiedTime` int(10) unsigned NOT NULL default '0',\n  `modifiedName` tinytext collate latin1_general_ci NOT NULL,\n  `body` text collate latin1_general_ci NOT NULL,\n  `icon` varchar(16) collate latin1_general_ci NOT NULL default 'xx',\n  `ID_BOARD` smallint(5) unsigned NOT NULL default '0',\n  PRIMARY KEY  (`ID_MSG`),\n  UNIQUE KEY `topic` (`ID_TOPIC`,`ID_MSG`),\n  UNIQUE KEY `ID_BOARD` (`ID_BOARD`,`ID_MSG`),\n  KEY `participation` (`ID_MEMBER`,`ID_TOPIC`),\n  KEY `ID_TOPIC` (`ID_TOPIC`),\n  KEY `ipIndex` (`posterIP`(15),`ID_TOPIC`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

[Gobalopper]

Try this query:
ALTER TABLE test_location_country CHARACTER SET latin1 COLLATE latin1_general_ci;

[1948Pal]

OK, doing now. Be right back

[1948Pal]

OK, ran the query, successful. Should I be doing something else? Cause I'm still getting same errors.

[Gobalopper]

What does this query return:

SHOW CREATE TABLE test_members;

Because that is the only other table that is used in the query that doesn't work.

[1948Pal]

CREATE TABLE `test_members` (\n  `ID_MEMBER` mediumint( unsigned NOT NULL auto_increment,\n  `memberName` varchar(80) collate latin1_general_ci NOT NULL default '',\n  `realName` tinytext collate latin1_general_ci NOT NULL,\n  `passwd` varchar(64) collate latin1_general_ci NOT NULL default '',\n  `emailAddress` tinytext collate latin1_general_ci NOT NULL,\n  `dateRegistered` int(10) unsigned NOT NULL default '0',\n  `personalText` tinytext collate latin1_general_ci NOT NULL,\n  `gender` tinyint(4) unsigned NOT NULL default '0',\n  `birthdate` date NOT NULL default '0001-01-01',\n  `websiteTitle` tinytext collate latin1_general_ci NOT NULL,\n  `websiteUrl` tinytext collate latin1_general_ci NOT NULL,\n  `location` tinytext collate latin1_general_ci NOT NULL,\n  `ICQ` tinytext collate latin1_general_ci NOT NULL,\n  `AIM` varchar(16) collate latin1_general_ci NOT NULL default '',\n  `YIM` varchar(32) collate latin1_general_ci NOT NULL default '',\n  `hideEmail` tinyint(4) NOT NULL default '0',\n  `timeFormat` varchar(80) collate latin1_general_ci NOT NULL default '',\n  `signature` text collate latin1_general_ci NOT NULL,\n  `posts` mediumint( unsigned NOT NULL default '0',\n  `timeOffset` float NOT NULL default '0',\n  `avatar` tinytext collate latin1_general_ci NOT NULL,\n  `pm_ignore_list` text collate latin1_general_ci NOT NULL,\n  `pm_email_notify` tinyint(4) NOT NULL default '0',\n  `lastLogin` int(10) unsigned NOT NULL default '0',\n  `karmaBad` smallint(5) unsigned NOT NULL default '0',\n  `karmaGood` smallint(5) unsigned NOT NULL default '0',\n  `usertitle` tinytext collate latin1_general_ci NOT NULL,\n  `lngfile` tinytext collate latin1_general_ci NOT NULL,\n  `notifyAnnouncements` tinyint(4) NOT NULL default '1',\n  `notifyOnce` tinyint(4) NOT NULL default '1',\n  `MSN` tinytext collate latin1_general_ci NOT NULL,\n  `memberIP` tinytext collate latin1_general_ci NOT NULL,\n  `secretQuestion` tinytext collate latin1_general_ci NOT NULL,\n  `secretAnswer` varchar(64) collate latin1_general_ci NOT NULL default '',\n  `disableCensoring` tinyint(4) NOT NULL default '0',\n  `city` tinytext collate latin1_general_ci NOT NULL,\n  `country` tinytext collate latin1_general_ci,\n  `showOnline` tinyint(4) default '1',\n  `WARNING` int(11) default NULL,\n  `instantMessages` smallint(5) NOT NULL default '0',\n  `unreadMessages` smallint(5) NOT NULL default '0',\n  `ID_THEME` tinyint(4) unsigned NOT NULL default '0',\n  `ID_GROUP` smallint(5) unsigned NOT NULL default '0',\n  `is_activated` tinyint(3) unsigned NOT NULL default '1',\n  `validation_code` varchar(10) collate latin1_general_ci NOT NULL default '',\n  `ID_MSG_LAST_VISIT` int(10) unsigned NOT NULL default '0',\n  `additionalGroups` tinytext collate latin1_general_ci NOT NULL,\n  `smileySet` varchar(48) collate latin1_general_ci NOT NULL default '',\n  `totalTimeLoggedIn` int(10) unsigned NOT NULL default '0',\n  `ID_POST_GROUP` smallint(5) unsigned NOT NULL default '0',\n  `passwordSalt` varchar(5) collate latin1_general_ci NOT NULL default '',\n  `isPostModerated` tinyint(4) NOT NULL default '0',\n  `moderatedPosts` int( NOT NULL default '0',\n  `messageLabels` text collate latin1_general_ci NOT NULL,\n  `buddy_list` tinytext collate latin1_general_ci NOT NULL,\n  `notifySendBody` tinyint(4) NOT NULL default '0',\n  `notifyTypes` tinyint(4) NOT NULL default '2',\n  PRIMARY KEY  (`ID_MEMBER`),\n  KEY `posts` (`posts`),\n  KEY `dateRegistered` (`dateRegistered`),\n  KEY `lastLogin` (`lastLogin`),\n  KEY `ID_MEMBER` (`ID_MEMBER`),\n  KEY `posts_2` (`posts`),\n  KEY `dateRegistered_2` (`dateRegistered`),\n  KEY `lastLogin_2` (`lastLogin`),\n  KEY `ID_GROUP` (`ID_GROUP`),\n  KEY `birthdate` (`birthdate`),\n  KEY `ID_POST_GROUP` (`ID_POST_GROUP`),\n  KEY `lngfile` (`lngfile`(24))\n) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Sorry about the smilies, didn't know how to make them not show up.

[Gobalopper]

Ok and the show create table for the location_country table now shows the collation as being COLLATE=latin1_general_ci?

[1948Pal]

Yes it does
CREATE TABLE `test_location_country` (\n  `c2code` char(2) character set latin1 NOT NULL default '',\n  `name` varchar(42) character set latin1 NOT NULL default '',\n  `ID_MAP` smallint(5) unsigned NOT NULL default '0',\n  PRIMARY KEY  (`c2code`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

[Gobalopper]

Ok try finding the collation on your database then:

SHOW CREATE DATABASE yourdatabasename;

[Gobalopper]

Actually try doing this:

ALTER TABLE test_mm_pins MODIFY COLUMN `country` CHAR(2)  CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL;

ALTER TABLE test_location_country MODIFY COLUMN `c2code` CHAR(2)  CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL;

[1948Pal]

Great, that worked for the member map button (?action=mm), The who is still getting the collation error

[Gobalopper]

Can you post the error message for that one?

[Gobalopper]

Or try this query:
ALTER TABLE test_location_ip2country MODIFY COLUMN `c2code` CHAR(2)  CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL;

[1948Pal]

Is this situation unique to my server/host?
[error]
Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
File: /home/allabout/public_html/test/Sources/Who.php
Line: 103
[error]

[1948Pal]

Or try this query:
ALTER TABLE test_location_ip2country MODIFY COLUMN `c2code` CHAR(2)  CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL;

YESss, that worked. Excellent and outstanding work. Thank you so much.

[Gobalopper]

Well somehow your table collations got changed from what I think the default is for the overall database.

Basically what happens is your database has a set charset/collation, which can then be overridden at the table level or at the column level. So somewhere along there was a difference. Glad to see it is working again.

[1948Pal]

One last question, and thank you again, if I am to reinstall or install this on a different forum, do you think I will have to run these queries again to alter the tables?

[Gobalopper]

Hmm... I'm not sure. You might have to, it depends on the setup really. It is possible it could happen again.