LookupError: unknown encoding: latin1_swedish

[Raoul Snyman]

Using Python 2.5, MySQL-python 1.2.2, Gentoo 2.6.11-gentoo-r8DevEnvP3, SQLAlchemy 0.5.0beta2, Pylons 0.9.6.2

When querying the database, my application dies with this error:

LookupError: unknown encoding: latin1_swedish

Any ideas why this error might be occurring? It doesn't happen when connecting to the db from my Mac, only from the Gentoo server. One note: I think the MySQL client is not installed in the "standard" place on the server. Could this be the problem? How can I tell MySQL-python where the MySQL client is?

Stack trace:

Exception happened during processing of request from ('172.20.128.234', 52462)
Traceback (most recent call last):
File "/usr/lib/python2.5/site-packages/Paste-1.4.2-py2.5.egg/paste/httpserver.py", line 1046, in process_request_in_thread
self.finish_request(request, client_address)
File "/usr/lib/python2.5/SocketServer.py", line 254, in finish_request
self.RequestHandlerClass(request, client_address, self)
File "/usr/lib/python2.5/SocketServer.py", line 522, in init
self.handle()
File "/usr/lib/python2.5/site-packages/Paste-1.4.2-py2.5.egg/paste/httpserver.py", line 425, in handle
BaseHTTPRequestHandler.handle(self)
File "/usr/lib/python2.5/BaseHTTPServer.py", line 316, in handle
self.handle_one_request()
File "/usr/lib/python2.5/site-packages/Paste-1.4.2-py2.5.egg/paste/httpserver.py", line 420, in handle_one_request
self.wsgi_execute()
File "/usr/lib/python2.5/site-packages/Paste-1.4.2-py2.5.egg/paste/httpserver.py", line 287, in wsgi_execute
self.wsgi_start_response)
File "/usr/lib/python2.5/site-packages/Paste-1.4.2-py2.5.egg/paste/cascade.py", line 92, in call
return self.apps-1
File "/usr/lib/python2.5/site-packages/Paste-1.4.2-py2.5.egg/paste/registry.py", line 340, in call
app_iter = self.application(environ, start_response)
File "/usr/lib/python2.5/site-packages/Pylons-0.9.6.2-py2.5.egg/pylons/wsgiapp.py", line 314, in call
return self.app(environ, start_response)
File "/usr/lib/python2.5/site-packages/Beaker-0.9.5-py2.5.egg/beaker/middleware.py", line 75, in call
return self.app(environ, start_response)
File "/usr/lib/python2.5/site-packages/Beaker-0.9.5-py2.5.egg/beaker/middleware.py", line 148, in call
return self.wrap_app(environ, session_start_response)
File "/usr/lib/python2.5/site-packages/Routes-1.7.3-py2.5.egg/routes/middleware.py", line 99, in call
response = self.app(environ, start_response)
File "/usr/lib/python2.5/site-packages/Pylons-0.9.6.2-py2.5.egg/pylons/wsgiapp.py", line 95, in call
response = self.dispatch(controller, environ, start_response)
File "/usr/lib/python2.5/site-packages/Pylons-0.9.6.2-py2.5.egg/pylons/wsgiapp.py", line 236, in dispatch
return controller(environ, start_response)
File "/data/apache/iol/IOL_featurama/featurama/lib/base.py", line 25, in call
c.default_section = Session.query(Section).get(self.default_section)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/orm/query.py", line 416, in get
return self._get(key, ident)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/orm/query.py", line 1176, in _get
return q.all()[0]
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/orm/query.py", line 977, in all
return list(self)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/orm/query.py", line 1042, in iter
return self._execute_and_instances(context)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/orm/query.py", line 1045, in _execute_and_instances
result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none(), _state=self._refresh_state)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/orm/session.py", line 731, in execute
clause, params or {})
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/engine/base.py", line 847, in execute
return Connection.executorsc
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/engine/base.py", line 898, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) > 1), distilled_params=params)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/engine/base.py", line 910, in _execute_compiled
self.execute_raw(context)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/engine/base.py", line 919, in execute_raw
self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/engine/base.py", line 961, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context=context)
File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0beta2-py2.5.egg/sqlalchemy/databases/mysql.py", line 1477, in do_execute
cursor.execute(statement, parameters)
File "build/bdist.linux-i686/egg/MySQLdb/cursors.py", line 149, in execute
query = query.encode(charset)
LookupError: unknown encoding: latin1_swedish_ci

[Andy Dustman]

You should probably change the default connection character set for your server if you can. Otherwise set it explicitly when you connect with the charset parameter.

[Raoul Snyman]

I'm using MySQLdb through SQLAlchemy, so I'm not too sure how to do that.

Also, the identical code works from my Mac, but not on the Gentoo server.

On my Mac:

raoul@umbreon:raoul $ python
Python 2.5.2 (r252:60911, Feb 22 2008, 07:57:53)
[GCC 4.0.1 (Apple Computer, Inc. build 5363)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> connection = MySQLdb.connect(host='devdb', user='*', passwd='*', db='news')
>>> cursor = connection.cursor()
>>> cursor.execute("SELECT VERSION()")
1L
>>> cursor.execute(u"SELECT VERSION()")
1L
>>>

On the server:

raoul@dev ~ $ python
Python 2.5.2 (r252:60911, Jul 29 2008, 09:55:12)
[GCC 3.3.5-20050130 (Gentoo 3.3.5.20050130-r1, ssp-3.3.5.20050130-1, pie-8.7.7. on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> connection = MySQLdb.connect(host='devdb', user='*', passwd='*', db='news')
>>> cursor = connection.cursor()
>>> cursor.execute("SELECT VERSION()")
1L
>>> cursor.execute(u"SELECT VERSION()")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "build/bdist.linux-i686/egg/MySQLdb/cursors.py", line 149, in execute
LookupError: unknown encoding: latin1_swedish_ci
>>>

[Andy Dustman]

All that is happening here:

>>> cursor.execute("SELECT VERSION()")
1L

is you are getting back the number of rows in the result set. (You can't depend on this value though; use cursor.rowcount instead.) To actually see the version number, you need to do cursor.fetchone(). Maybe you know this, but it's a bad example for new users.

You can try using connection.set_character_set("utf8") after you connect and see if that helps. Or, add charset="utf8" to your connect() parameters.

[Raoul Snyman]

I'm just illustrating that Python dies when I try to query with a unicode string. These are the important lines:

>>> cursor.execute(u"SELECT VERSION()")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "build/bdist.linux-i686/egg/MySQLdb/cursors.py", line 149, in execute
LookupError: unknown encoding: latin1_swedish_ci

If I query MySQL with a normal string, I don't have a problem (hence "1L" = 1 line returned, use fetchone to get the actual row). But if I query MySQL with a Unicode string (which is what SQLAlchemy is using), then it dies.

What I'm asking is what could cause my Mac to work perfectly, and yet my Gentoo server to die.

Once again, the example from my Mac:

>>> cursor.execute(u"SELECT VERSION()")
1L

(please note the "u" for Unicode and the "1L" which indicates that the query was successful, and the abscence of the stack trace which happens on my Gentoo server)