Import Error - UnicodeEncodeError

[razorness]

Just installing Nominatim v3.7.1 on a fresh Ubuntu 20.04 and importing europe-latest.osm.pbf from Geofrabrik. After almost 2h of import, I ran into this issue:

2021-05-22 15:39:25: Using project directory: /opt/europe
2021-05-22 15:39:25: Creating database
2021-05-22 15:39:25: Setting up database
2021-05-22 15:39:25: Loading basic data
2021-05-22 15:39:26: Installing database module
2021-05-22 15:39:26: Importing OSM data file
2021-05-22 15:39:26  osm2pgsql version 1.4.1
2021-05-22 15:39:26  Database version: 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)
2021-05-22 15:39:26  PostGIS version: 3.0
2021-05-22 15:39:26  Node-cache: cache=32000MB, maxblocks=512000*65536, allocation method=11
2021-05-22 15:39:26  Parsing gazetteer style file '/usr/local/etc/nominatim/import-extratags.style'.
NOTICE:  table "place" does not exist, skipping
2021-05-22 17:15:15  Reading input files done in 5749s (1h 35m 49s).          224.6/s)
2021-05-22 17:15:15    Processed 2777175918 nodes in 2902s (48m 22s) - 957k/s
2021-05-22 17:15:15    Processed 333896140 ways in 2295s (38m 15s) - 145k/s
2021-05-22 17:15:15    Processed 5645839 relations in 552s (9m 12s) - 10k/s
2021-05-22 17:15:15  node cache: stored: 2461186840(88.62%), storage efficiency: 58.68% (dense blocks: 231720, sparse nodes: 1148026881), hit rate: 90.46%
2021-05-22 17:15:16  osm2pgsql took 5750s (1h 35m 50s) overall.
2021-05-22 17:15:17: Create functions (1st pass)
Traceback (most recent call last):
  File "/usr/local/bin/nominatim", line 11, in <module>
    exit(cli.nominatim(module_dir='/usr/local/lib/nominatim/module',
  File "/usr/local/lib/nominatim/lib-python/nominatim/cli.py", line 288, in nominatim
    return parser.run(**kwargs)
  File "/usr/local/lib/nominatim/lib-python/nominatim/cli.py", line 92, in run
    return args.command.run(args)
  File "/usr/local/lib/nominatim/lib-python/nominatim/clicmd/setup.py", line 84, in run
    refresh.create_functions(conn, args.config, args.sqllib_dir,
  File "/usr/local/lib/nominatim/lib-python/nominatim/tools/refresh.py", line 86, in create_functions
    sql.run_sql_file(conn, 'functions.sql',
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/sql_preprocessor.py", line 103, in run_sql_file
    cur.execute(sql)
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/connection.py", line 24, in execute
    LOG.debug(self.mogrify(query, args).decode('utf-8'))
UnicodeEncodeError: 'ascii' codec can't encode character '\u5e02' in position 25068: ordinal not in range(128)

Any ideas?

[lonvia]

Do you use any custom settings, i.e. what is the content of .env in your project directory if you have any?

[gvoider]

Fresh install of Nominatim 3.7.2 on clean Ubuntu 20.04 in an LXC container on Proxmox 6.4.

python3-jinja2/focal,now 2.10.1-2 all [installed]
python3-psycopg2/focal,now 2.8.4-2 amd64 [installed]

Everything installed by the guide:
https://nominatim.org/release-docs/latest/appendix/Install-on-Ubuntu-20/

[lonvia]

It works fine on a vanilla Ubunutu 20.04 vagrant libvirt image, so something must be off with this Proxmox setup. The odd thing is that the position is completely out of range for the query string (should be around 16k characters), so it really looks like something is corrupting your data. You could add a print(sql) around here. This might shed some light on where the string is wrong.

[gvoider]

Well, that was strange.... The error was upon executing functions.sql file, that has no unicode symbols at all:
Anyway, using Proxmox VM instead of an LXC works fine. Thanks!
import.zip

[lonvia]

Thanks for the log. So the problem is the one Chinese character here which indicates that unicode handling is somehow different in that LXC container. If you still have the LXC container around, can you replace the print(sql) with a print(type(sql)) and tell me what it reports?

[gvoider]

Not much, I'm afraid:
<class 'str'>

[lonvia]

I've just run into the same issue trying to execute our vagrant scripts on a vanilla centos8 docker image. This is indeed an issue with UTF-8 and starting the docker container with -e 'LANG=en_US.UTF-8' fixed it for me. I suspect that the underlying problem here is the default database encoding used for the Postgresql database cluster. Because of the missing locale setting, it would initialise the cluster with encoding SQL_ASCII. We do ask for UTF8 encoding when creating the database but it looks like the default setting throws off psycopg2.

For anybody else seeing this error: to check if the encoding is the issue, check the encoding of the template database:

psql -d template1 -c 'SHOW SERVER_ENCODING'

(don't forget to add connection parameters for host/port etc. if the database is on a different host). If that prints anything other than UTF8, your cluster is likely set up wrong.

[VARGA-Peter]

I don't understand why this is not caught by the program and - at least - a solution is provided. The replies here are like "Do you know what time it is" and the answer is "Yes"!

I applies the following steps from https://www.shubhamdipt.com/blog/how-to-change-postgresql-database-encoding-to-utf8/

nominatim@yourserver:~$ psql
....
Type "help" for help.

nominatim=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
nominatim=# DROP DATABASE template1;
nominatim=# CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8';
nominatim=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

Run this when the postgress database has been initialised. This fixed the issue for me.

PS:

1. I followed exactly the instructions from https://nominatim.org/release-docs/latest/appendix/Install-on-Centos-8/

2. Also, the link for editing on GitHub in the top right corner is apparently broken.

[lonvia]

The answer was provided above and it was: make sure that your entire system is set up to run with UTF-8. Fiddling with the templates might help a bit for now but if you don't have your entire system set up with UTF-8, then it will come back to bite you later.

[VARGA-Peter]

The answer was provided above and it was: make sure that your entire system is set up to run with UTF-8. Fiddling with the templates might help a bit for now but if you don't have your entire system set up with UTF-8, then it will come back to bite you later.

I am using a pre-built system prepared by the hoster. I mentioned that the import routine could catch it if the coding is not correct and then change at least the coding with a separate parameter. I may now forwards to the hoster the information that his image uses the old SQL_ASCII instead of UTF8. Even when I change the system coding, the database remains wrong as it was already initialised.

However, now, when somebody has the same issue he has in this thread the exactly steps how to fix it at least in the database.

Definitely, with your last post I now finally understood what is meant and I changed the system locale. It could be mentioned in the installation instructions as a remark...

Thank you!

[danieldegroot2]

Related #2671