MySQL Error 1267: Illegal mix of collations for operation '%s'.
MySQL Error 1267: Illegal mix of collations for operation '%s'.
Questions tagged [mysql-error-1267]
24 questions
147
votes
10 answers
Illegal mix of collations MySQL Error
I'm getting this strange error while processing a large number of data...
Error Number: 1267
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
SELECT COUNT(*) as num from keywords WHERE…
Ali
- 261,656
- 265
- 575
- 769
57
votes
20 answers
Illegal mix of collations error in MySql
Just got this answer from a previous question and it works a treat!
SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount
FROM ratings WHERE month='Aug' GROUP BY username HAVING TheCount > 4
ORDER BY TheAverage DESC, TheCount…
Oliver
- 809
- 1
- 11
- 18
21
votes
3 answers
django character set with MySQL weirdness
I'm seeing
OperationalError (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='")
It looks like some of my variables are UTF8 strings
'name': 'p\xc7\x9d\xca\x87\xc9\x9f\xc4\xb1\xc9\xa5s…
Paul Tarjan
- 48,968
- 59
- 172
- 213
19
votes
4 answers
MYSQL case sensitive search for utf8_bin field
I created a table and set the collation to utf8 in order to be able to add a unique index to a field. Now I need to do case insensitive searches, but when I performed some queries with the collate keyword and I got:
mysql> select * from page where…
Charlie
7
votes
2 answers
Internationalize content best practices for using utf8_encode() (php function)
In order for website to accept user submitted content which may not be in English (e.g. Japanese) and save it to the database, is it in my best interest to utf8_encode all new content, and user utf8_decode when retrieving it later?
Further info:
I…
whamsicore
- 8,320
- 9
- 40
- 50
5
votes
2 answers
django python collation error
What is the reason for the following error? when i try to filter with:
if MyObject.objects.filter(location = aDictionary['address']):
where location is defined as:
location = models.CharField(max_length=100, blank=True, default='')
I get the…
prostock
- 9,327
- 19
- 70
- 118
4
votes
1 answer
MySQL query utf-8 characters (e.g. Chinese)(Also, I am using Doctrine)
$q = $this->createQuery('q')
->where('q.group_id=?', $group_id)
->andWhere('q.content=?', $content)
->execute();
If my $content contains any unicode characters (e.g. Chinese/japanese) this causes the following message:
SQLSTATE[HY000]: General…
whamsicore
- 8,320
- 9
- 40
- 50
4
votes
1 answer
Inconsistent MySQL COLLATE errors across databases
I have two physically-separate MySQL databases on which I have to run a single query.
The query has a section of SQL that looks like this:
and foo_table.bar_column like concat('%', rules.pattern, '%') COLLATE utf8_general_ci
It runs fine on…
Teflon Ted
- 8,696
- 19
- 64
- 78
4
votes
4 answers
mysql: error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
I want to make the password column of my User table to be case sensitive in mysql.
Following is the description of the table:
/*Table: mst_user*/
FIELD TYPE COLLATION
------------- ------------ …
DarkKnightFan
- 1,913
- 14
- 42
- 61
3
votes
4 answers
Query with column named "name"
I am trying to join a few tables but it is failing on my join statement. I believe it is because the column name in the second join is "name" and perhaps MySQL thinks i am trying to access an attribute? how can i get around this?
SELECT…
Silvertiger
- 1,680
- 2
- 19
- 32
3
votes
2 answers
Join query in MySQL
I have two tables: members and renewals
The two tables have a field called memberid which links the two data tables together. I am trying to construct a query which will extract the firstname and surname from the members table for the data that is…
thisisready
- 623
- 2
- 10
- 22
3
votes
3 answers
mysql select concat(charfield, format(doublefield,8)) gives error 1267
which is ironic on two counts, 1) because concat(charfield, doublefield) works (it doesn't mind when one of the fields to be concatenated is numeric) and 2) because the mysql reference shows this: CONCAT(str1,str2,...) as the prototype for CONCAT…
user119282
- 337
- 4
- 12
2
votes
1 answer
Efficient way to index MySQL table column with utf8 charset
CREATE TABLE profile_category (
id mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
pc_name char(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
This is one of the tables in database that is…
Ernest
- 8,701
- 5
- 40
- 51
2
votes
1 answer
mysql Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
When I execute this query
SELECT * FROM login_table
WHERE username = 'sam'
AND pass = AES_ENCRYPT('passabc', 'mystring')
I keep on getting this error.
#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and…
Parag
- 4,754
- 9
- 33
- 50
1
vote
2 answers
MYSQL querying two tables with different fields
Here is my search query, which is putting out an argument error.
What do I need to do to correct it. Basically I need to put both of these tables together and pull out the next 5 events within the two tables by the start date. You will also notice…
Juan Gonzales
- 1,967
- 2
- 22
- 36