Python ASCII codec error for queries with characters not in ASCII

[AntoineAugusti]

Issue Summary

I get an encoding error when writing a query with characters that are not encoded in ascii. I think I first installed Redash in its v3 and upgraded to v4. Because I did start at v4, I don't really know how to fix the issue

It looks similar to #2228

Steps to Reproduce

1. Go to /queries/new
2. Write query select 'à'
3. Execute query
4. Error is Error running query: 'ascii' codec can't encode character u'\xe0' in position 196: ordinal not in range(128)

Technical details:

• Redash Version: currently 4.0.1+b4038
• Browser/OS: MacOS / Chrome
• How did you install Redash: Open source version with provisioning script

[AntoineAugusti]

@RichardLitt Why do you think it's related to the frontend?

Can anyone tell me how I can find the file / line number responsible for this error?

[RichardLitt]

@AntoineAugusti I mess up sometimes. Renamed.

[AntoineAugusti]

@arikfr Would you be able to help on this? I did not find what is causing this.

[tatsushid]

I had the same issue with

• Redash version: 5.0.0.b4755
• Env: Open source version installed in Ubuntu 16.04

but it was resolved by adding a following line to my .env file

export REDASH_DATABASE_URL='postgresql:///postgres?client_encoding=utf-8'

and restarting services by

supervisorctl restart all

The detail is, in my case, when I ran a query

SELECT 'テスト'

it returned the same error with the stack trace below

[2018-09-28 18:49:00,253][PID:27607][ERROR][MainProcess] Task redash.tasks.execute_query[53838eed-0808-4f56-8155-1434b9f8aa26] raised unexpected: UnicodeEncodeError('ascii', u"select '\u30c6\u30b9\u30c8'", 8, 11, 'ordinal n
ot in range(128)')
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/celery/app/trace.py", line 240, in trace_task
    R = retval = fun(*args, **kwargs)
  File "/opt/redash/redash.5.0.0.b4755/redash/worker.py", line 71, in __call__
    return TaskBase.__call__(self, *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/celery/app/trace.py", line 438, in __protected_call__
    return self.run(*args, **kwargs)
  File "/opt/redash/redash.5.0.0.b4755/redash/tasks/queries.py", line 528, in execute_query
    scheduled_query).run()
  File "/opt/redash/redash.5.0.0.b4755/redash/tasks/queries.py", line 479, in run
    run_time, utils.utcnow())
  File "/opt/redash/redash.5.0.0.b4755/redash/models.py", line 787, in store_result
    for q in queries:
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2924, in __iter__
    self.session._autoflush()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 1434, in _autoflush
    self.flush()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2254, in flush
    self._flush(objects)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2380, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2344, in _flush
    flush_context.execute()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
    uow
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 866, in _emit_insert_statements
    execute(statement, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1416, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
UnicodeEncodeError: 'ascii' codec can't encode characters in position 8-10: ordinal not in range(128)

As this trace, I thought it didn't come from the original query to DB but from saving the result into the internal PostgreSQL temporally. If Redash is installed on clean Ubuntu env, internal Postgres should be

# sudo -u postgres psql -l
                               List of databases
    Name     |  Owner   | Encoding  | Collate |  Ctype  |   Access privileges
-------------+----------+-----------+---------+---------+-----------------------
 postgres    | postgres | SQL_ASCII | C       | C       |
 redash      | redash   | SQL_ASCII | C       | C       |
 template0   | postgres | SQL_ASCII | C       | C       | =c/postgres          +
             |          |           |         |         | postgres=CTc/postgres
 template1   | postgres | SQL_ASCII | C       | C       | =c/postgres          +
             |          |           |         |         | postgres=CTc/postgres
(4 rows)

It looks if there is no encoding configuration, Redash should use ascii encoding to connect to redash DB but it seems to cause the ascii translation and raise the exception.

In redash/settings/__init__.py, the internal PostgreSQL connection default value is

SQLALCHEMY_DATABASE_URI = os.environ.get("REDASH_DATABASE_URL", os.environ.get('DATABASE_URL', "postgresql:///postgres"))

so I thought overwriting it with an explicit encoding would work and actually, that works.

I hope it helps solving the issue.

[AntoineAugusti]

I tried to do what you suggested @tatsushid but it didn't work for me.

The stack trace was a bit different:

[2018-09-30 16:01:45,600][PID:7446][WARNING][Worker-1] Unexpected error while running query:
Traceback (most recent call last):
  File "/opt/redash/redash.4.0.1.b4038/redash/tasks/queries.py", line 449, in run
    data, error = query_runner.run_query(annotated_query, self.user)
  File "/opt/redash/redash.4.0.1.b4038/redash/query_runner/pg.py", line 152, in run_query
    cursor.execute(query)
UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 196: ordinal not in range(128)

I solved this issue by adding an encoding cast at the beginning of this method:

redash/redash/query_runner/pg.py

Line 153 in 4685887

def run_query(self, query, user):

query = query.encode('utf-8')

but it's definitely not pretty.

Any better way to solve this?

[fahrettinf89]

I know this is an old post but changing the code did not help me. I removed pg.py and pg.pyc and it didnt show me any error. What do I have to do to apply this changes?
Do you guys came up with a solution for this problem?