asked on

MySQL Collation issues

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

Guy Hengel [angelIII / a3]

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

khsater

ASKER

Well, the error changed:
"COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'"

Guy Hengel [angelIII / a3]

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

khsater

ASKER

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]

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

khsater

ASKER

Nope, it's set to utf-8, according to phpMyAdmin

Guy Hengel [angelIII / a3]

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

khsater

ASKER

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.

khsater

ASKER

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

khsater

ASKER

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?

khsater

ASKER

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

khsater

ASKER

Thanks!

Guy Hengel [angelIII / a3]

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