Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL Collation issues

Posted on 2009-07-08
14
Medium Priority
?
571 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

722 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