Can't decode french characters after switching to PostgrSQL from MySQL

[Radomir Wojcik]

my code worked fine up until I switched to PostgreSQL from MySQL. Now I am bombarded with these errors whenever I have french accents in my strings. My database is correctly configured with LATIN1 (as UTF8 didn't like the accents when trying to import them from MySQL, the default was UTF8).

My postgresql.conf has the right settings by the look of it:

client_encoding = latin1                # encoding

# These settings are initialized by initdb, but they can be changed.

lc_messages = 'en_CA.iso88591'                  # locale for system error message

                                        # strings

lc_monetary = 'en_CA.iso88591' #en_US.UTF-8'    # locale for monetary formatting

lc_numeric = 'en_CA.iso88591'                   # locale for number formatting

lc_time = 'en_CA.iso88591' 

How do I switch this ascii decoder in django to a decoder that can handle french accents?

UnicodeDecodeError at /alarms/edit/100/

'ascii' codec can't decode byte 0xc3 in position 10: ordinal not in range(128)

Request Method:	POST
Request URL:	https://mandala.cldssinc.com/alarms/edit/100/
Django Version:	1.5.2
Exception Type:	UnicodeDecodeError
Exception Value:	'ascii' codec can't decode byte 0xc3 in position 10: ordinal not in range(128)
Exception Location:	/var/web/mandala_env/mandala/alarms/models.py in __unicode__, line 21
Python Executable:	/var/web/mandala_env/bin/python
Python Version:	2.6.6
Python Path:	['/var/web/mandala_env/mandala/mandala', '/var/web/mandala_env/mandala', '/var/web/mandala_env/bin', '/var/web/mandala_env/lib64/python26.zip', '/var/web/mandala_env/lib64/python2.6', '/var/web/mandala_env/lib64/python2.6/plat-linux2', '/var/web/mandala_env/lib64/python2.6/lib-tk', '/var/web/mandala_env/lib64/python2.6/lib-old', '/var/web/mandala_env/lib64/python2.6/lib-dynload', '/usr/lib64/python2.6', '/usr/lib/python2.6', '/var/web/mandala_env/lib/python2.6/site-packages']
Server time:	Thu, 22 Aug 2013 01:16:16 -0400 Thanks

[Radomir Wojcik]

And I tried with both UTF8 and LATIN1 database encodings, both produce the same error:

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileg

es   

-----------+----------+----------+-------------+-------------+------------------

-----

 mandala   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 mandala3  | postgres | LATIN1   | C           | C           | 

I have a feeling that this is related to django + postgres as I noticed too that the login username used to be case insensitive but after the conversion is case sensitive.. strange. Is Postgres really that good? If I can't fix I will have to switch back to MySQL and wait for MariaDB to be officially supported...

[Russell Keith-Magee]

Yes, PostgreSQL is that good. Without wanting to be too blunt, PostgreSQL is a database; MySQL is a popular tool that can be used to store data. 

It's difficult to say for certain what has gone wrong without knowing exactly how you've exported your data. However, all indications suggest that something in your conversion chain has gone wrong, and as a result, you've got data that has been double converted, or is being interpreted as ASCII when it's actually unicode content. If you put unicode content into a PostgreSQL database that has been configured to use UTF-8, it handles it fine. However, if you put in garbage, or you haven't configured PostgreSQL to handle UTF-8 content, you can't expect it to work without problems.

The best way to track this down is to follow a single string from one database to another. First, put a string that is known to be UTF-8 into your PostgreSQL database, and make sure it comes out OK. Then, pick a row in your MySQL database that has unicode content in it, and export it from MySQL. Make sure the exported format has the content correctly encoded. Then import into PostgreSQL; check that was has been imported correctly. Once you've moved a single string from one database to another, you can reproduce that process en masse for the rest of your database.

Yours,

Russ Magee %-)

[Radomir Wojcik]

Its not a problem with the conversion process. I re-created the schema using django syncdb and then I went table by table putting the data back in using csv files I exported from MySQL. I took the long way to get this all in and PostgreSQL had no problems taking my csv data as input.  Any advice on how to get PostgreSQL running properly as MySQL did would be appreciated. It must be able to handle french accents which are not ascii.

[Radomir Wojcik]

Also why did from django.contrib.auth.views.login become case sensitive? I have users that login with User and user, in PostgreSQL this is treated as 2 different things. Using MySQL it always saw it as one.. all very frustrating. 

I will try SQLite tomorrow maybe instead, been up 2 days straight trying to get this to work and I really don't want to use MySQL anymore since its now owned by Oracle and slowly going to become closed source while MariaDB is not fully supported by Django just yet.

[Radomir Wojcik]

Adding the unicode u to the 'string' as such u'string'  fixes the issue but now I have to add this u to every string that will potentially have a character with a french accent in it? How come MySQL didn't need this? There must be a straight forward answer to this, something that will make Postgres + django handle every string as unicode.

[Xavier Ordoquy]

Hi,

Le 22 août 2013 à 08:25, Radomir Wojcik <rad...@cldssinc.com> a écrit :

> Adding the unicode u to the 'string' as such u'string' fixes the issue but now I have to add this u to every string that will potentially have a character with a french accent in it? How come MySQL didn't need this? There must be a straight forward answer to this, something that will make Postgres + django handle every string as unicode.

As a french user, I haven't faced encoding issues with Django and Postgres.
As Russell pointed out, you need to make sure your postgres database is utf8 and the data you imported also were.
Your concerns shows that something's wrong with one of those two things - or maybe both.

By the way, if you're using python 2, you shouldn't be using 'string' notation for character strings and should be using the u'string' one.
'string' is a binary string while u'string' is a text string.
This is misleading with python 2 as there's implicit conversion between the two and utf8 will be used as the default charset which may lead to the kind of errors you've faced.

Mysql is more flexible about that but you may have missed that it has something like 4 different encoding configuration parameters and your client side configuration may have been utf8.
In this regard, postgresql might look too restrictive but it's safer and better.

Best regards,
Xavier.

[Radomir Wojcik]

Also getting a bunch of these too:

Non-ASCII character '\xc3' in file /var/web/mandala_env/mandala/alarms/views.py on line 329, but no encoding declared; see http://www.python.org/peps/pep-0263.html for details (views.py, line 329)

Is this normal? PostgreSQL is more strict with character types? So now I have to add # coding=<encoding name> to every py file in my django project?

[Russell Keith-Magee]

On Thu, Aug 22, 2013 at 2:11 PM, Radomir Wojcik <rad...@cldssinc.com> wrote:

Also why did from django.contrib.auth.views.login become case sensitive? I have users that login with User and user, in PostgreSQL this is treated as 2 different things. Using MySQL it always saw it as one.. all very frustrating. 

Django has always been case sensitive. MySQL is the problem here, because it's default string collation isn't case sensitive. However, MySQL will still allow you to store "User" and "user" in the same table without hitting uniqueness constraints. 

 

I will try SQLite tomorrow maybe instead, been up 2 days straight trying to get this to work and I really don't want to use MySQL anymore since its now owned by Oracle and slowly going to become closed source while MariaDB is not fully supported by Django just yet.

SQLite is a reasonable database for testing, but don't expect it to stand up to any sort of real load in practice. 

Yours,

Russ Magee %-)

[Radomir Wojcik]

Thanks Xavier,

I had this at the top of my settings.py file, tried changing it to latin-1 and it didn't make a difference.

# -*- coding: utf-8 -*-

So right now I am using the UTF-8 database as shown above: mandala   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

The data contains special characters, french accents mainly and they seem to reside in the database and display just fine. By importing the csv files and letting the new django postgres schema accept it, I have had a very clean migration process, you can say I did it by hand because postgres COPY alerted me when the data was bad and I had to change it.

Lets put the database aside for a second, I also have some error messages with accents in them, and they give me the error below as before it worked just fine.

messages.error(request, "Sé")  

And now:

Non-ASCII character '\xc3' in file /var/web/mandala_env/mandala/alarms/views.py on line 329, but no encoding declared; see http://www.python.org/peps/pep-0263.html for details (views.py, line 329

But this makes no sense because I do have encoding declared in settings.py right near the top, and this must have worked before when using MySQL -> # -*- coding: utf-8 -*-

[Russell Keith-Magee]

On Thu, Aug 22, 2013 at 2:06 PM, Radomir Wojcik <rad...@cldssinc.com> wrote:

Its not a problem with the conversion process.

Well, with all due respect -- yes, it is. Allow me to assure you that PostgreSQL handles UTF-8 and unicode just fine. If you're getting errors, it's either a problem with your original source of data, or in your conversion process getting the data into Django. This means the data you're putting in either isn't unicode to start with, or isn't being imported in a format that is being identified as unicode, and is being double converted.

 

I re-created the schema using django syncdb and then I went table by table putting the data back in using csv files I exported from MySQL. I took the long way to get this all in and PostgreSQL had no problems taking my csv data as input.  Any advice on how to get PostgreSQL running properly as MySQL did would be appreciated. It must be able to handle french accents which are not ascii.

So lets get this straight -- you're exporting from MySQL -- a database with (ahem) esoteric encoding behavior for UTF data -- into CSV -- a data format with no concept of character encoding -- and then you're importing into PostgreSQL. I almost don't have enough fingers to count the places where you could be introducing encoding problems. 

As for how to get PostgreSQL running properly? Make sure that you've created your database cluster with UTF-8. That's it. 

If you're having problems loading and retrieving UTF-8 data, it's because your original data source isn't encoded as UTF-8, or you're loading it in a way that is double encoding. However, since you haven't provided any details or samples, it's impossible to say what you're doing wrong.

Yours,

Russ Magee %-)

[Radomir Wojcik]

From what I read, SQLite is production grade and its the most used database in the world.. besides the point.

So other than Postgres not picking up my encoding (UTF-8 or Latin-1) , both did not work. How can I get the login to be case insensitive again? This will really be a bummer if I have to re-write my own login instead of using the built in stuff. I think it all goes back to the encoding issue though... 

[Radomir Wojcik]

Russ like I said before, I used COPY to get the data back in and it was accepted by postgres with the schema it crated using django (See step 5 below)

1) Export each table individually from MySQL into csv format.

2) Have Django re-create the models schema from django on its own.

3) Export the django schema using pg_dump for comparison

Repeat 5, 6 for each table: 4) Compare each csv file to the postgre schema for each table and align the data accordingly using spreadsheet software.

5) Use COPY (postgre utiity) to insert data from the csv fie to the database.

COPY locations_location FROM '/tmp/locations_location.csv' DELIMITER ',' CSV;

6) pg_dump dbname > outfile the data in order for migration. Restore on system using psql dbname < infile




When I was using COPY, it would complain about any issues and I fixed the csv's as I went along, so everything inserted was validated with PostgreSQL on the way in.




One very important question which I still don't understand. Are french characters such as "é" UTF-8 compatible or not? Because they seem to be sitting in the database just fine and they display just fine most of the time, but not when passing strings from my views, such as:

messages.success(request, u'{0} edited & saved Alarm Definition successfully.'.format(cur_alarm_rec))

I had to add the unicode u' around it because cur_alarm_rec contained an "é"

I also ended up adding the u to most of my models unicode output

    def __unicode__(self):

        return u'%s:%s' % (self.id, self.city)

As some cities contained special french accent characters as well.

[Radomir Wojcik]

This must be my problem all along:

By the way, if you're using python 2, you shouldn't be using 'string' notation for character strings and should be using the u'string' one. 
'string' is a binary string while u'string' is a text string. 
This is misleading with python 2 as there's implicit conversion between the two and utf8 will be used as the default charset which may lead to the kind of errors you've faced. 

I was not using u' at all I was always using the binary strings and had no problems. I guess MySQL would convert them automagically for me while Postgres must deal with the data raw waiting for me to add the u' to it and then it works.  So what I was really looking for is an option that would convert all binary strings to unicode (text) strings for me without me having to go back and test and add it manually all over the place...

[Radomir Wojcik]

MySQL would also somehow make the built in django contrib views login case insensitive, and now its case sensitive. Since it takes the request as a parameter and request.POST is immutable I will have to write my own login without using the batteries built in solution since I cannot do a .lower() on POST.get['username'] and save it back to the request.

[Radomir Wojcik]

So is it normal that without adding the u' to a string that contains french accents will give the error:

ascii' codec can't decode byte 0xc3 in position 10: ordinal not in range(128)  ?

Are french characters UTF-8 Compliant?

Would this insertion process not fail if the data contained within the postgres.sql file contain non UTF-8 characters when mandala4 in this case is UTF-8 encoded?

psql mandala4 -U postgres < mandala.postgres.sql 

Password for user postgres: 

SET

SET

SET

SET

SET

CREATE EXTENSION

COMMENT

SET

SET

SET

CREATE TABLE

ALTER TABLE

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

CREATE TABLE

ALTER TABLE

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

CREATE TABLE

ALTER TABLE

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

CREATE TABLE

....

Seeing as it had no problems inserting the french accents into a UTF-8 encoded database, this means that french characters are UTF-8 complaint?

This shows you the database is utf8:

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileg

es   

-----------+----------+----------+-------------+-------------+------------------

-----

 mandala   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 mandala3  | postgres | LATIN1   | C           | C           | 

 mandala4  | mandala  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      

    +

           |          |          |             |             | postgres=CTc/post

gres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      

    +

           |          |          |             |             | postgres=CTc/post

gres

(6 rows)

Here are some snippets from the sql dump file , client encoding is LATIN1 but going into a UTF-8 database, and it did so without issue:

SET statement_timeout = 0;

SET client_encoding = 'LATIN1';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

--

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 

--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

--

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 

--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

The auth_user creation as created by django using postgres, but now the login is case sensitive:

CREATE TABLE auth_user (

    id integer NOT NULL,

    password character varying(128) NOT NULL,

    last_login timestamp with time zone NOT NULL,

    is_superuser boolean NOT NULL,

    username character varying(30) NOT NULL,

    first_name character varying(30) NOT NULL,

    last_name character varying(30) NOT NULL,

    email character varying(75) NOT NULL,

    is_staff boolean NOT NULL,

    is_active boolean NOT NULL,

    date_joined timestamp with time zone NOT NULL

);

example data going in:

COPY auth_user (id, password, last_login, is_superuser, username, first_name, last_name, email, is_staff, is_active, date_joined) FROM stdin;

1       bcrypt$$2a$12$YW6Gxsl0z4/PgQPrbwZgm.f5tB2Bnu.wShpNg2rmsr4H3W8knbZsq     2013-08-21 22:07:57.359359-04   t       admin                   a...@a.com t       t       2013-08-21 18:16:30-04

Here is the other model with issues when the city has an accent:

CREATE TABLE locations_location (

    id character varying(20) NOT NULL,

    created timestamp with time zone,

    modified timestamp with time zone,

    address character varying(255),

    address2 character varying(255),

    city character varying(255),

    province character varying(255),

    postal_code character varying(255),

    county character varying(255),

    country character varying(255),

    phone character varying(255),

    latitude numeric(11,7),

    longitude numeric(11,7),

    open_time time without time zone,

    close_time time without time zone,

    is_24_hours boolean NOT NULL,

    notes text,

);

And here is the insert of data and the city that has the problem:

2012-08-29 08:36:00-04  2013-07-16 14:23:00-04  t       t       t       74 Principale St       \N      Néguac  New Brunswick 

Is this enough info to come up with a good conclusion of what this could be?

[Xavier Ordoquy]

Hi

Le 22 août 2013 à 09:52, Radomir Wojcik <rad...@cldssinc.com> a écrit :

Are french characters UTF-8 Compliant?

As I said, yes. Been using Django + Postgres with french texts and faced no issue so far.

You also have a decent Django french user base which would have noticed that sort of issues.

Back on our topic, if you set client_encoding to 'LATIN1'; you need to make sure your file is latin1 encoded (try the file command, it should give you the encoding).

Most of the time, it'll be utf8, so you'll probably need to edit your dump, update the client_encoding to utf8, import it again.

Regards,

Xavier.

[Radomir Wojcik]

So when I changed the encoding in the sql file from LATIN1 to UTF-8 I get import errors:

(1 row)

ERROR:  invalid byte sequence for encoding "UTF8": 0xe9 0x67 0x75

CONTEXT:  COPY locations_location, line 31

 setval 

So what encoding can I use that is safe with french accents but at the same time can be used in django no problem? LATIN1 was ok in MYSQL

[Radomir Wojcik]

Xavier, what do you mean file is latin1 encoded? You lost me.

By the way, one of the Non UTF-8 data that it complains about is the accent over the e in Neguac:

 Néguac  New Brunswick

See my error above given by psql db < file.sql

[Radomir Wojcik]

Xavier , which encoding do you use for french characters with django?

UTF-8 does not like é, 

But it says it exists in utf-8:

U+00E9

é

c3 a9

LATIN SMALL LETTER E WITH ACUTE

http://www.utf8-chartable.de

[Radomir Wojcik]

This fixed it

iconv -f ISO_8859-1 -t UTF-8 mandala.postgres.sql > mandala.postgres.utf8.sql

Now it takes the accents in as utf-8. I guess it didnt' like the LATIN1 encoding mixed with UTF-8

[Xavier Ordoquy]

I'm using full utf8.

Whenever I need to import data, I make sure they are utf8 encoded.

an extract from a database dump:

SET statement_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = off;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET escape_string_warning = off;

[...]

COPY xxx (id, description) FROM stdin;

1               [...]passionnée[...]vêtements[...]bébé très

\.

As you can see, there are all sort of accents which are correctly processed and displayed.

Regards,

Xavier.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.

[Radomir Wojcik]

Thanks a bunch. Yeah when I did a full convert to UTF-8 I no longer have the issue. I still have the case sensitive login issue which came with the PostgreSQL upgrade. Do you have the same issue? Is this normal? 

Logging in as Rad no longer works, I have to use exact case as it is in the database (lower case), rad

[Jonathan Blumtritt]

Hello everyone,

I'm running Django 1.5.1 with Apache2/mod_wsgi/Daemon Mode in production.
Idle or stale Daemon processes seem to use up a lot (!) of memory after
a day or so. The ultimate cause for this might be in the web
applications code, but I would like to make sure, there is nothing wrong
with my apache configuration. Some of you may be using Apache/daemon
mode and my be able to help.

I followed instructions/recommendations given here:
http://code.google.com/p/modwsgi/wiki/IntegrationWithDjango

An also read the following:
http://code.google.com/p/modwsgi/wiki/ConfigurationDirectives#WSGIDaemonProcess
http://code.google.com/p/modwsgi/wiki/ProcessesAndThreading

My actual configuration in httpd.conf has these arguments:

WSGIDaemonProcess processgroup user=abc group=abc processes=5 threads=1 python-path=/my/path/to/webapp:/my/path/to/lib/python2.7/site-packages

My Sysadmin suggested I should also set:

maximum-requests, inactivity-timeout, deadlock-timeout

My Apache build is:

Server version: Apache/2.2.3
...
Server MPM: Prefork
threaded: no
forked: yes (variable process count)


Anyone can tell, if there is something wrong with the DaemonProcess
settings?

Best,
Jonathan

[Michael Radziej]

Hi Radomir,

> So when I changed the encoding in the sql file from LATIN1 to UTF-8 I get
> import errors:
>
> (1 row)
>
> ERROR: invalid byte sequence for encoding "UTF8": 0xe9 0x67 0x75
> CONTEXT: COPY locations_location, line 31
> setval

Well, this is not a UTF8 sequence, but probably iso-8859-1. Why do you
want to import it again, at all, after it was correct in the database,
according to your mail from Thu, 22 Aug 2013 00:02:50 -0700?

>
>
> So what encoding can I use that is safe with french accents but at the same
> time can be used in django no problem? LATIN1 was ok in MYSQL

UTF8 is fine for French with all characters, even Œ, œ and Ÿ.


You wrote earlier:

> messages.error(request, "Sé")
>
> And now:
>
> Non-ASCII character '\xc3' in file
> /var/web/mandala_env/mandala/alarms/views.py on line 329, but no encoding
> declared; see http://www.python.org/peps/pep-0263.html for details
> (views.py, line 329

This means that you need to declare the encoding of your source code
like this on the top of each file when you use non-ASCII characters in
your source.

# -*- coding: <encoding name> -*-

I don't see the realtion of this message to changing the database
engine.


Kind regards

Michael

[Radomir Wojcik]

Michael I already have this on top of my settings.py file all along, but djanog does not pick it up..

#django project

# encoding line  is 2nd in settings.py

[Radomir Wojcik]

# Django settings for mandala project.

# -*- coding: utf-8 -*-

But django says its not declared. Where should I put this? 

[Michael Radziej]

Hi,

this has nothing to do with Django at all, it's a python thing. The
encoding declaration needs to go on top of EVERY source code file if
you the source code contains non-ASCII encoding. This has NOTHING to do
with Django encoding or the encoding your database uses!

Now, slowly: When you exchanged MySQL with postgresql, there should have
been no need to change much in your sources. Why are there suddenly
non-ASCII characters in your sources? Or are you just trying out
something?


Kind regards

Michael

[Radomir Wojcik]

Yeah I figured that its python as per the message. I didn't know I had to add it to every source that has the chars but thanks for the tip.

I was trying it out to see if the  issue was related to the database encoding or not, a UTF-8 conversion on the data completely fixed my issue. This is the command for those with the same problem:

Convert sql file from latin to utf-8: