By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,747 Members | 2,011 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,747 IT Pros & Developers. It's quick & easy.

illegal mix of collations

P: n/a
We got a dedicated hosted server to replace a shared hosted server. I
reloaded the database tables and data and scripts. I noticed that
most of the tables had latin1_swedish_collation but some had
latin1_general_collation so I did a search and replace to make them
all the swedish collation.

The problem is one script isn't running. I'm getting a SQL query
error about an illegal mix of collations while running the script.

Here is the create table statement:

CREATE TABLE IF NOT EXISTS `INTERNAL_CLM` (
`clm_id` int(11) NOT NULL auto_increment,
`trip_id` mediumint(9) default NULL,
`car_mark` char(4) character set latin1 collate latin1_general_ci
NOT NULL,
`car_number` mediumint(9) NOT NULL,
`sighting_date` datetime NOT NULL,
`sighting_code` char(2) character set latin1 collate
latin1_general_ci default NULL,
`location_city` varchar(15) character set latin1 collate
latin1_general_ci default NULL,
`location_state` char(2) character set latin1 collate
latin1_general_ci default NULL,
`location_splc` varchar(10) character set latin1 collate
latin1_general_ci default NULL,
`l_e` char(1) character set latin1 collate latin1_general_ci NOT
NULL,
`railroad` char(4) character set latin1 collate latin1_general_ci
default NULL,
`destination_city` varchar(15) character set latin1 collate
latin1_general_ci default NULL,
`destination_state` char(2) character set latin1 collate
latin1_general_ci default NULL,
`destination_splc` varchar(10) character set latin1 collate
latin1_general_ci default NULL,
`eta` datetime default NULL,
`date_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
PRIMARY KEY (`clm_id`),
UNIQUE KEY `clm`
(`car_mark`,`car_number`,`sighting_date`,`sighting _code`,`location_city`),
KEY `location` (`location_city`,`location_state`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=65863 ;

What else do I need to change?

Thanks,
Janis

------------------script error----------------
new sighitng is not insertable. no dice
INSERT INTO LAST_SIGHTING (SELECT c.clm_id, a.remark_id FROM
all_cars_view a, clm_lastsighting_view v1, clm_crossreference_view v2,
INTERNAL_CODES e, INTERNAL_CLM c WHERE a.car_mark = c.car_mark AND
c.car_mark = v1.car_mark AND v1.car_mark = v2.car_mark AND
a.car_number = c.car_number AND c.car_number = v1.car_number AND
v1.car_number = v2.car_number AND c.sighting_date = v1.sighting_date
AND c.sighting_code = v2.code AND c.sighting_code = e.code) Illegal
mix of collations (latin1_general_ci,IMPLICIT) and
(latin1_swedish_ci,IMPLICIT) for operation '='
Aug 22 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
JRough wrote:
We got a dedicated hosted server to replace a shared hosted server. I
reloaded the database tables and data and scripts. I noticed that
most of the tables had latin1_swedish_collation but some had
latin1_general_collation so I did a search and replace to make them
all the swedish collation.

The problem is one script isn't running. I'm getting a SQL query
error about an illegal mix of collations while running the script.

Here is the create table statement:

CREATE TABLE IF NOT EXISTS `INTERNAL_CLM` (
`clm_id` int(11) NOT NULL auto_increment,
`trip_id` mediumint(9) default NULL,
`car_mark` char(4) character set latin1 collate latin1_general_ci
NOT NULL,
`car_number` mediumint(9) NOT NULL,
`sighting_date` datetime NOT NULL,
`sighting_code` char(2) character set latin1 collate
latin1_general_ci default NULL,
`location_city` varchar(15) character set latin1 collate
latin1_general_ci default NULL,
`location_state` char(2) character set latin1 collate
latin1_general_ci default NULL,
`location_splc` varchar(10) character set latin1 collate
latin1_general_ci default NULL,
`l_e` char(1) character set latin1 collate latin1_general_ci NOT
NULL,
`railroad` char(4) character set latin1 collate latin1_general_ci
default NULL,
`destination_city` varchar(15) character set latin1 collate
latin1_general_ci default NULL,
`destination_state` char(2) character set latin1 collate
latin1_general_ci default NULL,
`destination_splc` varchar(10) character set latin1 collate
latin1_general_ci default NULL,
`eta` datetime default NULL,
`date_time_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
PRIMARY KEY (`clm_id`),
UNIQUE KEY `clm`
(`car_mark`,`car_number`,`sighting_date`,`sighting _code`,`location_city`),
KEY `location` (`location_city`,`location_state`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=65863 ;

What else do I need to change?

Thanks,
Janis

------------------script error----------------
new sighitng is not insertable. no dice
INSERT INTO LAST_SIGHTING (SELECT c.clm_id, a.remark_id FROM
all_cars_view a, clm_lastsighting_view v1, clm_crossreference_view v2,
INTERNAL_CODES e, INTERNAL_CLM c WHERE a.car_mark = c.car_mark AND
c.car_mark = v1.car_mark AND v1.car_mark = v2.car_mark AND
a.car_number = c.car_number AND c.car_number = v1.car_number AND
v1.car_number = v2.car_number AND c.sighting_date = v1.sighting_date
AND c.sighting_code = v2.code AND c.sighting_code = e.code) Illegal
mix of collations (latin1_general_ci,IMPLICIT) and
(latin1_swedish_ci,IMPLICIT) for operation '='
The newsgroup you're asking your question in. This is a PHP newsgroup,
not a MySQL newsgroup.

comp.database.mysql is >>>that-a-way.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Aug 22 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.