We help IT Professionals succeed at work.

MySQL Collation issues

khsater
khsater asked
on
Medium Priority
837 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

Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
Well, the error changed:
"COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'"
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
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.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
Nope, it's set to utf-8, according to phpMyAdmin
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
then indeed, I don't see it neither ...

Author

Commented:
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.

Author

Commented:
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.
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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?

Author

Commented:
I'll just figure out the underlying issue another time, as it is likely related to the Python module.

Author

Commented:
Thanks!
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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 ...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.