Solved

MySQL Collation issues

Posted on 2009-07-08
14
481 Views
Last Modified: 2012-06-27
For some reason I'm getting a collation error with the attached code, and I just can't figure it out.
It's giving: "illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='".
I don't understand how this is possible since everything is encoded to utf-8.
Any suggestions?
#!C:\Python26\python.exe
print "Content-Type: text/html"
print
import MySQLdb
import cgi, cgitb
 
cgitb.enable()
 
DB_HOST = 'localhost'
DB_USER = 'root'
DB_PASSWORD = 'rootpass'
DB_DATABASE = 'ccs'
DB_PORT = 3307
db = MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE, DB_PORT)
artist = unicode("artist").encode('utf-8')
album = unicode("album").encode('utf-8')
 
c=db.cursor()
c.execute("CREATE TABLE musicfiles ( artist TEXT NOT NULL, album TEXT, albumid INT(25) NOT NULL ) CHARSET 'utf8' COLLATE 'utf8_general_ci'")
query = 'SELECT albumid FROM musicfiles WHERE (artist, album) = (\'' + artist + '\',\'' + album + '\')'
print query
c.execute(query)

Open in new window

0
Comment
Question by:khsater
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24808681
try this:
query = 'SELECT albumid FROM musicfiles WHERE (artist COLLATE 'utf8_general_ci', album COLLATE \'utf8_general_ci\') = (\'' + artist + '\' COLLATE \'utf8_general_ci\',\'' + album + '\' COLLATE \'utf8_general_ci\')'

Open in new window

0
 
LVL 4

Author Comment

by:khsater
ID: 24808729
Well, the error changed:
"COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'"
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24808791
so, the fields are defined in latin1 ...
query = 'SELECT albumid FROM musicfiles WHERE (artist , album) = (\'' + artist + ' COLLATE \'latin1_swedish_ci\' ,\'' + album + '\' COLLATE \'latin1_swedish_ci\')'

Open in new window

0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 4

Author Comment

by:khsater
ID: 24808829
No, that's why I'm confused.  According to Navicat, all of the columns' charsets are set to UTF-8.  That's what made me think this may be an issue on the Python side of things. Doing what you posted results in the original error message.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24808875
then, your server default is the latin one:
query = 'SELECT albumid FROM musicfiles WHERE (artist , album) = (\'' + artist + ' COLLATE \'utf8_general_ci\' ,\'' + album + '\' COLLATE \'utf8_general_ci\')'

Open in new window

0
 
LVL 4

Author Comment

by:khsater
ID: 24808896
Nope, it's set to utf-8, according to phpMyAdmin
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24808947
then indeed, I don't see it neither ...
0
 
LVL 4

Author Comment

by:khsater
ID: 24820084
I've thought about it and I think the issue may be with the module I'm using.  I'm going to see if I can find a way to use a different MySQL module for Python.
0
 
LVL 4

Author Comment

by:khsater
ID: 24823983
I can't believe I forgot to mention this, but this only happens when I try to match multiple columns.  If I take out either artist or album, the query executes.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24824566
so:
query = 'SELECT albumid FROM musicfiles 
WHERE artist = \'' + artist + ' COLLATE \'utf8_general_ci\'
  AND album = \'' + album + '\' COLLATE \'utf8_general_ci\' ';

Open in new window

0
 
LVL 4

Author Comment

by:khsater
ID: 24824619
Thank you.  It works without the collate statements but I still have absolutely no clue why.
Could you please explain the difference between the comma separated query and the "AND" query?
0
 
LVL 4

Author Comment

by:khsater
ID: 24828547
I'll just figure out the underlying issue another time, as it is likely related to the Python module.
0
 
LVL 4

Author Closing Comment

by:khsater
ID: 31601338
Thanks!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24829900
can you help me?
I could not find your initial syntax in the docs:
WHERE (col1, col2) = (value1, value2)

I presume the explanation is that with that syntax, mysql implicitly changes the data type/collation of the tuplet ...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question