MySQL Forums
Forum List  »  General

Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
Posted by: Sisir Ghosh
Date: June 05, 2007 07:24AM

My department is developing a mission critical software for our callcenter and as we are in the POWER UTILITY service the callcenter has become an integram part of our service.

Now we are writinng/upgrading the existing software.
Since this is a mission critical application we are writing triggers so that the new database as well the old database gets updated simultaneously and in case of any unpredictable accidents we may revart back to the old system.

Both the databases are on MySql-5.

The trigger I wrote is

DELIMITER $$

DROP TRIGGER `newcallcenter`.`tgr_complaint_status_update`$$

CREATE TRIGGER `newcallcenter`.`tgr_complaint_status_update` BEFORE UPDATE on `newcallcenter`.`complaint_status`
FOR EACH ROW BEGIN

DECLARE this_deponame VARCHAR(50) DEFAULT NULL;

SELECT deponame INTO this_deponame FROM newcallcenter.rrc_depo_list
WHERE rrc=SUBSTRING(NEW.docket_no,5,2) AND depo=NEW.depot LIMIT 0,1;

UPDATE callcenter.depo_fb SET depo=this_deponame WHERE docket_no=NEW.docket_no;

END$$

DELIMITER ;

which simultaneously pdates the complaint_status table in the new database and depo_fb table in the old database.

Now I am getting the error

Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

I have searched google and came to know that the collation_connection has to be same with collation_database and collation_server.

Also there is a sql for changing the collation_connection i.e.

mysql> SET collation_connection='latin1_general_ci';

but this query is not doing any change.

the
mysql> SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+

please help in this regard

the depo_fb table in callcenter database is

CREATE TABLE `callcenter`.`depo_fb` (
`docket_no` varchar(20) character set latin1 collate latin1_general_ci default NULL,
`RRC` varchar(20) character set latin1 collate latin1_general_ci default NULL,
`fb_code` int(11) default NULL,
`depo` varchar(50) character set latin1 collate latin1_general_ci default NULL,
`team` varchar(50) character set latin1 collate latin1_general_ci default NULL,
`comp_end_time` datetime default NULL,
`punch_time` datetime default NULL,
`remarks` varchar(255) character set latin1 collate latin1_general_ci default NULL,
`piller_box` varchar(25) character set latin1 collate latin1_general_ci default NULL,
`unit_no` varchar(15) character set latin1 collate latin1_general_ci default NULL,
`phase` varchar(6) character set latin1 collate latin1_general_ci default NULL,
`last_feed` int(11) default NULL,
KEY `docket` (`docket_no`),
KEY `ptime` (`punch_time`),
KEY `fbcode` (`fb_code`),
KEY `RRC` (`RRC`),
KEY `comp_end_time` (`comp_end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

the complaint_status table in the newcallcenter database is


CREATE TABLE `newcallcenter`.`complaint_status` (
`docket_no` varchar(11) NOT NULL COMMENT 'Complaint Docket_no',
`ref_rrc` char(2) NOT NULL COMMENT 'RRC Code',
`depot` tinyint(1) default NULL COMMENT 'Depot Code',
`mistry_other` varchar(50) default NULL,
`mistry` char(6) default NULL COMMENT 'Mistry Code',
`depot_rem` varchar(255) default NULL COMMENT 'Mistry Remarks',
`pillar_box` varchar(50) default NULL COMMENT 'Pillar Bos Data',
`unit_no` varchar(10) default NULL COMMENT 'Unit No Data',
`phase` varchar(50) default NULL COMMENT 'Pillar Box Marker',
`pole_no` varchar(15) default NULL,
`g_mother` varchar(11) default NULL COMMENT 'Group Mother Docket No',
`g_flag` char(2) default NULL COMMENT 'Group Mother / Child Flag (gm, gc)',
`s_mother` varchar(11) default NULL COMMENT 'Suspend Mother Docket No',
`s_flag` char(2) default NULL COMMENT 'Suspend Mother / Child Flag (sm, sc)',
`feedback` smallint(3) default NULL COMMENT 'Feedback Code By Depot / Agent / Consultant',
`printno` smallint(3) default '0' COMMENT 'Total printed counter',
`org_comp_time` timestamp NULL default NULL COMMENT 'Replace complaind date from complain table in case redirect to another depo',
`updated_by` varchar(30) NOT NULL COMMENT 'Agent / RRC Code',
`update_time` timestamp NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`docket_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='To store latest status of the complaint';

the rrc_depo_list table in the newcallcenter is


CREATE TABLE `newcallcenter`.`rrc_depo_list` (
`rrc` char(2) NOT NULL COMMENT 'RRC Code',
`rrc_name` varchar(15) NOT NULL COMMENT 'RRC Name',
`depo` tinyint(1) NOT NULL COMMENT 'Depot Code',
`deponame` varchar(15) NOT NULL COMMENT 'Depot Name',
`dist_name` varchar(10) default NULL COMMENT 'District Short Name',
`morning` tinyint(2) default NULL COMMENT 'Morning-Vehicle',
`evening` tinyint(2) default NULL COMMENT 'Evening-Vehicle',
`night` tinyint(2) default NULL COMMENT 'Night-Vehicle',
PRIMARY KEY (`rrc`,`depo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='Depot list under different RRC';

thanks in advance

SISIR

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.