• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 714
  • Last Modified:

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

0
khsater
Asked:
khsater
  • 8
  • 6
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
khsaterAuthor Commented:
Well, the error changed:
"COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'"
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
khsaterAuthor 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
khsaterAuthor Commented:
Nope, it's set to utf-8, according to phpMyAdmin
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then indeed, I don't see it neither ...
0
 
khsaterAuthor 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.
0
 
khsaterAuthor 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
khsaterAuthor 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?
0
 
khsaterAuthor Commented:
I'll just figure out the underlying issue another time, as it is likely related to the Python module.
0
 
khsaterAuthor Commented:
Thanks!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now