Illegal Mix Of Collations When Using The Concat Function

Illegal Mix Of Collations?
I am trying to execute in MySQL Query Browser:

SELECT * FROM name
WHERE (lastname,firstname) > ('BAR','FOO')
ORDER BY lastname, firstname ASC LIMIT 1;

And I keep getting the result:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '>'; 1267

How do I change my character sets or whatever to avoid this problem?

---

Illegal Mix Of Collations
i've created a view from a select statement which executes fine, but when i try to select from the view, i get the error:

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

i have tried to change all the charset variables to 'utf8' and all the collation variables to 'utf8_unicode_ci' and alter all the tables collation (ive checked using "show table status" but i still get the error, all data is ASCII in all tables !

---

Illegal Mix Of Collations
How do I address the illegal mix of collations problem?

SELECT * FROM student
WHERE stud_name LIKE "n%"

#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'

SHOW VARIABLES LIKE "col%"
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

version()
4.1.7

---

Illegal Mix Of Collations
I have a table in my MySQL database that has a field called location. The default value for this field national. Other than that value the field will hold a the name(s) of a state such as california, arizona, new mexico, idaho. In trying to build my recordset I keep getting an error when I try something like this:

select * from affiliates where location = 'national' order by name

I have tried numerous variations with the apostrophes but no luck. What am I missing here?

---

Illegal Mix Of Collations
i have php and MySQL 4.1.11 installed on my linux box,i am getting error for mix of collation when i use find_in_set php function also some invalid characters are coming on site from db.

error is

#1267 - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'find_in_set'

---

#1267 - Illegal Mix Of Collations
I get this error when using a select query

#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

---

"Illegal Mix Of Collations" MySQL Error
What I am attempting to do with an SQL query is grab data from multiple tables for outputting to an Excel spreadsheet file using PHP. Each row should show details for each member, as queried from multiple tables (using the field 'PersonalID' as the primary/foreign key). The query I have is as follows:

SELECT tblcoreuser.PersonalID, tblcoreuser.usrForename AS Forename, tblcoreuser.usrSurname AS Surname, tblcoreuser.voucher AS Free_Status, tblcoreuser.Joined AS Date_Joined, tblcoreuser.Package AS Membership_Package, tbladdress.Address1, tbladdress.Address2, tbladdress.County, tbladdress.Postcode, tblcredits.numCredits AS Remaining_Credits

FROM tblcoreuser, tbladdress, tblcredits

WHERE tblcoreuser.PersonalID = tbladdress.PersonalID AND tblcoreuser.PersonalID = tblcredits.PersonalID

ORDER BY tblcoreuser.PersonalID DESC;
The error being returned is:

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

I've tried removing certain parts of the query, but cannot seem to pinpoint the exact cause of the problem. I'm sure I'm missing something simple -

---

CONCAT Function (bug?)
I'm needed to insert large BLOBs into a database. With the 1MB packet
limit, sending larger amounts of data would be difficult, so I had a neat
idea. I would do an initial insert of an empty record and get the
auto_insert ID from the response, and then loop through, appending data to
the record.

My table is simple. One unsigned int auto_increment field (DataID), and
one long blob field (BinaryData).

When I loop through the data to send, I run:

UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data
here') WHERE DataID = 35


The binary data I insert I escape null characters, backslashes, single and
double quotes. The data seems to insert fine.

The problem is that as I increase the amount of data in the field, CONCAT
seems to drop all but the last 416k of the data. Thus if I loop through
adding 400k blocks at a time (Which I do) I am left with at most 800k of
data in the blob field.

Using 4k blocks I end up with 419k of data in the field when all is said
and done.

---

CONCAT Function
I'm needed to insert large BLOBs into a database. With the 1MB packet
limit, sending larger amounts of data would be difficult, so I had a neat
idea. I would do an initial insert of an empty record and get the
auto_insert ID from the response, and then loop through, appending data to
the record.

My table is simple. One unsigned int auto_increment field (DataID), and
one long blob field (BinaryData).

When I loop through the data to send, I run:

UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data
here') WHERE DataID = 35


The binary data I insert I escape null characters, backslashes, single and
double quotes. The data seems to insert fine.

The problem is that as I increase the amount of data in the field, CONCAT
seems to drop all but the last 416k of the data. Thus if I loop through
adding 400k blocks at a time (Which I do) I am left with at most 800k of
data in the blob field.

Using 4k blocks I end up with 419k of data in the field when all is said
and done.

Please let me know if/when this will be fixed, and if there is a
work around that might be used, r a better way to insert BLOB data is
known.

---

Myisamchk Illegal Instruction
I recently managed to corrupt a table pretty badly. I read the sections in the documentation about recovering after a crash. None of the methods worked. All give output similar to the following:

% bin/myisamchk -t ~/tmp -f -o BROKE/EventsBROKE
- recovering (with keycache) MyISAM-table 'BROKE/EventsBROKE.MYI'
Data records: 101333504
bin/myisamchk: error: 127 for record at pos 0
MyISAM-table 'BROKE/EventsBROKE' is not fixed because of errors
Try fixing it by using the --safe-recover (-o) or the --force (-f) option

% bin/myisamchk -t ~/tmp -e BROKE/EventsBROKE
Checking MyISAM file: BROKE/EventsBROKE
Data records: 101333504 Deleted blocks: 86920613
bin/myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check records and index references
Illegal instruction

The mysql server version is fairly old, 3.23.54 but unfortunatly it can't be
upgraded. The table that is corrupted is large... in the range of 14GB.
I couldn't find a reference to this error in my searches, so I apologise if
this has been covered before, but has anyone encountered these problems
before? And more importantly, were you able to fix it?

---

Collations?
Can anybody tell me what they are for and what i should choose? Is the default one that phpmyadmin uses ok? And why are there so many version of UTF8? I thought UTF 8 supported every character

---

Collations & Charsets
I had this issue a while back, and honestly, I never solved it. For some data I was storing for personal use, I actually gave in to the computer, and settled with UTF-8... the charset my computer/MySQL decided I was going to save in whether they liked it or not. This project was actually storing data through a webpage (PHP), so was quite easy to convert it from it's EUC source to UTF-8.

My new project is not personal, and I would really like to know why my collation is not working.

I've got a simple table. Looking at it through phpmyadmin, I see that the collation is set to ujis_japanese_ci. If I'm not mistaken, that's EUC_JP. There is a field which will have Japanese characters. I see that it's collation is alo set to ujis_japanese_ci. When I insert data through my perlscript, it gets mangled. When I try to put in data manually via phpmyadmin (with encoding set to EUC_JP, of course) I get the following complaints. The data is inserted, but in a mangled fashion.

Warning: mb_strpos() [function.mb-strpos]: Unknown encoding or conversion error. in /var/www/localhost/htdocs/phpmyadmin/libraries/string.lib.php on line 112

Warning: mb_strpos() [function.mb-strpos]: Unknown encoding or conversion error. in /var/www/localhost/htdocs/phpmyadmin/libraries/string.lib.php on line 112

Warning: mb_strpos() [function.mb-strpos]: Unknown encoding or conversion error. in /var/www/localhost/htdocs/phpmyadmin/libraries/string.lib.php on line 112

If I change the browser's encoding to UTF-8 and manually insert Japanese text, it is displayed just fine (in UTF-8). Seems that regardless of what I set the collation to, it wants to store in UTF-8.

My final complaint is that on a remote server, I've got a much older version of MySQL, from before the times of collations. The Japanese text works fine and dandy there... no ifs, ands or buts. Can someone enlighten me as to why collations were implimented in the first place?

---

Collations Mix Match
I've setup my own deadicated server and everythings well apart from mySQL.If defaults every database and table to latin1_swedish_ci
I have no idea if it needs to use this collation but its giving some php apps a problem.
I'm running 4.1.9 nt with php 5 on 2K3.
I think I'm going to have to manually ajust the collation on all my existing databases, but how do I stop this happening?

---

Documentation For Collations
I've read the MySql docs many times, but I can't seem to find documentation as to the sort orders and accent insensitivity per collation in MySQL 4.1. (when does ñ = n?)

Can someone point me in the right direction to find such documentation as to the difference between these many collations? It must be hidden somewhere at mysql.com

---

Default Character Set And Collations
I was just asked to look at a server with MySQL DB on it and upgrade it to the latest. I've done this before, but never in another language.

The current server is using 4.0 and they wish to upgrade to latest. The problem is that they use Japanese throughout the DB data and I don't know what characterset/collation the DBs are in. I know that 4.0 doesn't have UTF-8, so what would they be?

I check the system variables for default charset and it is Latin1. The people who were "taking care" of the server think the db's are ja-utf-8 but that option is not in the list of available charsets.

---

Character Sets And Collations
I've just installed MySQL 4.1.7 on my personal computer which i do all my web work on. I've put content into my database through phpMyAdmin.

Now when I import my sql file to my server Mysql 4.0.22 i get an error message 1064: Character Sets is wrong. & the website using the database to get content from, doesn't display it anymore.

Does anyone who how I can export my MySQL4.1.7 to a format which MySQL4.0.22 can read?

or can sometell me how to change the Character Sets and Collations to a set which works fine in both MySQL versions?

---

Illegel Mix Of Collations Error
I have a table (depth as int, name as varchar).

If I run the following query I get NO ERROR
SELECT CONCAT(REPEAT(' ', 2) , display_name) as name
FROM test

If I run this:
SELECT CONCAT(REPEAT(' ', depth) , display_name) as name
FROM test

I get the following error:

Illegal mix of collations (utf8_general_ci, COERCIBLE) and
(latin1_swedish_ci, IMPLICIT) for operation concat.

Can anyone explain this to me. I can't find an explanation anywhere.

---

Lost With Charsets And Collations
I've always stored English, French and Japanese text strings in my databases without paying much attention to mysql charsets and collations.Since I've never noticed any problem, I'm wondering: what are they for? ( I've read the documentation but it's obviously targeted to experienced admins.so I'm still clueless )

This week, I've installed the new version of mysql on my windows box (AppServ install) and I accidentally set default-character-set to UTF-8. Now,when I use SQLyog, all my fields have the collation field set to UTF-8 ( all my web pages are encoded in ISO or JIS).

Will this change have any bad repercussion?
What should I do to have my database safely store any language from a non-UTF-8 website?

---

IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere!

The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over £300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....

---

Concat Maybe?
Theres a way in mysql to get a comma separated list of fields in a one to many relationship - rather than multiple rows for each record in the 'one' table. But I can't remember what it is, I thought it was concat but that didn't work... I've done it before but I can't remember what it was or why.

---

Using CONCAT
Is it possible to make query which would select some value even column would be NULL/EMPTY ?

Example:

Table
name

John
NULL
Mike

SELECT CONCAT_WS('something',name) FROM table;

results:
Johh
Something
Mike

---

WHERE With CONCAT
I have a table A with a (varchar 20) A.field and a table B with two (varchar 10) fields: B.field1 and B.field2.

How can I make a SELECT with a WHERE of this type:

WHERE A.field = CONCAT (B.field1 , B.field2)

I get an error in CONCAT. How is the string concatenation in mySQL?

---

Concat
I'm trying to combine 2 fields into 1 new field using concat. This is what I using

UPDATE 'idxactrs' SET 'idxactrs.desc' = concat(remarks1,remarks2)

It just says "You have an error in your SQL syntax near ''idxactrs' ...

Any Ideas?

---

Using Concat()
I'm trying to combine 2 fields into 1 new field using concat. This is what I using

UPDATE 'idxactrs' SET 'idxactrs.desc' = concat(remarks1,remarks2)

It just says "You have an error in your SQL syntax near ''idxactrs' ...

---

Use CONCAT
I'm having lots of problems using the CONCAT instruction. I'm trying to store in a variable (@f) a COUNT, but the system always crashes and I don't know why. Here you have the lines:


set f=0;
set @f:=f;
set @stmt3:=CONCAT("select count(*) into `",@f,"` from `",@n,"` where sourceurl=`",@b,"` and link=`",@c,"`");
prepare query from @stmt3;
execute query;

---

Concat
I'm having problem using concat

select CONCAT(firstname,' ', lastname) as fullname from tbltable where fullname = 'name';

It say Unknown column 'fullname' in 'where clause'. It is my sql wrong or what?

---

How To Use CONCAT In The 'FROM' Section
For example I'd like to use the following query:
SELECT * FROM CONCAT('table_', 'name');

Is there a way to do this? or something similar?

---

How Does Mysql_field_len Act On CONCAT?
I am quite puzzled by this. I have two fields which if selected by themselves will return a field length of 2 using mysql_field_len. However if I select them as CONCAT(field1,' ',field2), the field length is much greater than the 5 I would expect. It looks to be about 20. Is there a way to control this length? Where is it getting the field length from?

EDIT: Nevermind, this can be done using CAST(CONCAT(field1,' ',field2) AS CHAR(5))

---

Concat Two Tables
I have two tables, one containing payments to a supplier and one containing
invoices issued by a supplier. I'd like to get a total from each table with
one query.

I need somehow to list all payments and all transactions together in one
table result set.

i.e.....

---

Using CONCAT In Subqueries
SELECT tblmonths.fldMonth, tblyears.fldYear, tblmonths.fldID, CONCAT(tblmonths.fldID," ", tblyears.fldYear)
FROM tblmonths, tblyears
where CONCAT(tblmonths.fldID," ", tblyears.fldYear)
NOT EXISTS
(SELECT CONCAT(fldMonth, " ", fldYear)
from tblexpensesclaims)
GROUP BY tblyears.fldYear, tblmonths.fldID

---

CONCAT And Variables
I have the following statement:

------------------
SELECT CONCAT('hxxp://www.whatever.com/all.html?t=4&p=1&u=',@c) into @c;

set @stmt3:=CONCAT("INSERT INTO webs values ('",@c,"')");
prepare query from @stmt3;
execute query;
--------------

I think that I'm using correctly the CONCAT function but it doesn't work.

---

Update Using Concat
I have a form that I call for the data from a database table. Once the table is filled with data, the viewer has the option to add data to one field in the form. I want to be able to add the new comments without losing the old comments in the db column.

Field A has the existing data ($Remarks)
Field B has the new comments ($NRemarks)

I have tried various CONCAT statements but none of them work, and I am thinking that this may be because Field B is not a column in the DB, as it is new information being added.

Is there a way to keep the data in the DB column called Remarks and add the information in Field B to it on a seperate line.

I don't know if there is a command like append or edit or add to, that will add information to the existing information. I have tried the following:

$sql="UPDATE 'workorder' SET Remarks CONCAT (Remarks, NRemarks)";
?>

$sql="UPDATE 'workorder' SET Remarks CONCAT ($Remarks, $NRemarks)FROM workorder WHERE Work = '$Contact'";
?>

$sql="UPDATE `workorder` SET Remarks = CONCAT(SELECT Remarks FROM workorder
WHERE Work = '$Contact', '', '$NRemarks') WHERE Work = '$Contact'";
?>

---

Using CONCAT For 2 Tables
Currently I'm using this SQL query to connect 2 tables but now, I need to add a 3rd table in the same manner as the "links" table in the following query.

SELECT * FROM tracker LEFT JOIN links ON tracker.location = CONCAT('link[', links.linkid, ']') ORDER BY tracker.occurred DESC LIMIT 0, 15

---

Problem With Concat
I have the following select:

SELECT distinct name, main.ACCOUNT,
concat(FlatNo,' , ',FlatName,' , ',number,' , ',`St Name`,' , ',SuburbName,' , ',`Town Name`,' , ',Stand) AS Address
FROM ...

I need to return all the available address information that we have but the select returns only about 15 addresses in a DB of about 100000. Some of the address fields do contain null values and the SELECT CONCAT is returning null for the whole row if any field contains a null value.

Is there anyway round this? I have tried IFNULL(Field,"") inside the concat but this does not seem to work either.

---

CONCAT All Columns
I want to produce a query that does something like this:

SELECT MD5(CONCAT(*)) FROM table WHERE 1

* is all the columns.
I don't want to have to type in every column name, but have MySQL do it.

---

Problems With Concat
I'm having problems with Concat. I have this piece of code:

set @m:=a;
SET @stmt1:=CONCAT("INSERT INTO ",@m," VALUES (b,c)");
PREPARE query from @stmt1;
execute query;

b and c are cursors and when it tries to execute the query and I get the following error:

"Unknown column 'b' in field list"


It seems that is not doing correctly the CONCAT?

---

Concat For Numbers
I am interested in performing a concat for numbers with a space
e.g.
select concat (num1, ' ', num2) from tablex as numconc

such that if num1 is 123 and num2 is 456 the result would be '123 456'
I believe concat should work for a string rather than number format, however having created the fields num1 and num2 in string format it still does not work.
Any advice on why this isn't working, or an alternative method to achieve the result would be most welcome.

---

Help With Subquery / Group Concat
I've attached some data from which I want to extract individual itinerary ids and show the ships which go to those destinations. The problem is that the destination ids are stored as comma separated values in a single field and there are rows which duplicate the same associations between the ship and destination ids. I can do:

SELECT ship_id, GROUP_CONCAT(DISTINCT destination_ids) FROM itineries GROUP BY ship_id;

and get a list of destination ids for each ship but what I actually want is a list of ships for each destination Id.

Can somebody give me some pointers on how to achieve this? I think it might be possible with a subquery but am not sure how to go about this. MYSQL version is 4.1.20.

---

SELECT CONCAT() Subquery
SELECT * FROM
(SELECT CONCAT(comments.category,'s') FROM comments WHERE comments.author_user_id = '1')

The subquery alone yields "post" (`comments.category` is an ENUM() field); and I want to select all the rows from the "posts" database table. Ideally, the query would be processed like:

SELECT * FROM posts

How do I perform a string concatenation during a SELECT query?

---

SELECT Subquery CONCAT
SELECT * FROM
(SELECT CONCAT(comments.category,'s') FROM comments WHERE comments.author_user_id = '1')

The subquery alone yields "post" (`comments.category` is an ENUM() field); and I want to select all the rows from the "posts" database table. Ideally, the query would be processed like:

SELECT * FROM posts

How do I perform a string concatenation during a SELECT query with MySQL?

---

CONCAT () Returns BLOB
MySQL ver. 3.23.49

SELECT tblHrsClass.classid , CONCAT( tblHrsClass.classTtl ,' ', tblHrsClass.classDate ) class_ttl FROM tblHrsStudent LEFT JOIN tblHrsClass ON tblHrsStudent . classid =tblHrsClass.classid GROUP BY tblHrsClass . classid HAVING COUNT(tblHrsStudent . classid )<19

The query above is broke. I'm using it to build a list box.

I can run the query without the CONCAT function and pulls the expected data.

When I add CONCAT( tblHrsClass.classTtl ,' ', tblHrsClass.classDate ) class_ttl,

class_ttl is returned as a BLOB of a certain size.

---

Need To Concat Column And DATE_FORMAT
I'd like to combine the title and starttime so I can distinguish amongst several requests with the same title. ie:

ID=7116
name=Testing11-26-2007

Here's my query:
select ID, title+DATE_FORMAT(starttime,"%m-%d-%Y") as name from requests where ID=7116

Which gives me:
ID=7116
name=11

What do I need to do to get the result of DATE_FORMAT into a form that will concat with the text from another column?

---

Concat 2nd Table Results Into First?
Given three tables used to track multiple email addresses and phone numbers for a person:

Table 1: id, name, address
Table 2: table1_id, email
Table 3: table1_id, phone

How can I do a select to end up with all emails and phones concated together into a single row per individual:

id, name, address, "email,email,email", "phone,phone"

The solution needs to work with MySQL 3.23 (or possible MySQL 4.0). 4.1 is NOT an option, so group_concat is out.

---

CONCAT Length Limitation
I have encountered a problem while using the CONCAT command to update a column with a size over 1mb, the existing information is lost. I can't seem to find a suitable alternative to update a field in a row with several appends. the data length in total will be exceeding over 16mb at times.

---

Concat A Field In Up To 4 Rows?
A customer can have between 1 and 4 rows, is it possible to make a query that will merge field "A" of all rows and then discard all other rows leaving only 1 row left with the merged data in field "A".

---

GROUP CONCAT Issue
Iam using Mysql Version 5.0.

the query,

SELECT GROUP_CONCAT(field_name)FROM TABLE1;

This returns BLOG as result set in the editor in my Toad.

What is the issue, i have just 300 records in my table.

Why this happens, please help

---

Nesting A 'concat' Within A 'replace'
I have the_table:
col_1 , col_2
-------------------------
aaa , aaa_do aaa_dat

the query that I am using:"update the_table set col_2=replace(col_2,col_1,concat('hoorah_' , col_1) ) "

I want
col_1 , col_2
-------------------------
aaa , hoorah_aaa_do hoorah_aaa_dat

however the query does not successfully stick the 'hoorah_' in front of the aaa_dat; instead I get this:
col_1 , col_2
-------------------------
aaa , hoorah_aaa_do aaa_dat

---

Problem W/concat To Date
I am trying to run a report that displays data that falls between a date range. The dates are entered in mm/dd/yyyy format. I convert the date using ColdFusion code for MySQL to use:

cfset Start1c = DateFormat(#Start1#, "yyyy/mm/dd")
cfset End1c = DateFormat(#End1#, "yyyy/mm/dd")

The query that uses this variable will run as long as I,
1. use slashes in the date variable above
2. query for data that falls in the same year

Here is the part of my query that has a problem w/the formatting of this date:
date_format(date_sub(concat_ws('-',dataYear,dataMonth,'02'), interval 1 day), '%Y-%m-%d')
BETWEEN '#Start1c#' AND '#End1c#'
AND InputMethod = 1

dataYear and dataMonth are integer fields combined to create a date.

---

INSERT With Concat Auto_increment
I have a table that stores online orders. Each row in the table uses auto_increment to create a unique id for each order, in a field "id".

Each order (row in the table) also has a serial_number field that has a string representing the product and a unique number representing the order. I would like to append the id field value for the newly created order to the product string, to create the serial number.

Can this be done within a single SQL statement or do I need to insert the order, find out the id, then update the order and appending the id on the serial number string.

Is it possible to append the id of the newly inserted row to another field in the row in a insert statement?

I tried INSERT INTO orders SET serial_number = CONCAT('product text',id); but MySQL did not like the id column in the concat function.
Do I need to use LAST_INSERT_ID to find the id?

---