Solved

MySQL Collation issues

Posted on 2009-07-08
14
472 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
  • 8
  • 6
14 Comments
 
LVL 142

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 142

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 142

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 142

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 142

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 142

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL ERROR 1045 (28000) 2 78
count download link and run update query 9 70
insert row field data graphically 4 24
MySQL Warning Statements when you have a LIMIT clause. 6 29
Strings in Python are the set of characters that, once defined, cannot be changed by any other method like replace. Even if we use the replace method it still does not modify the original string that we use, but just copies the string and then modif…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
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 …

772 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