Error exporting to Excel / CSV in 4.1.1 [UTF-8]

[ampil]

Dear community,

I have upgraded from 3.0.0 to 4.1.1.
When I export charts without UTF-8 symbols (for example cyrillic) it exports to Excel or CSV without issues.
When I export a chart with UTF-8 symbols Superset shows 502 Bad Gateway error and server (on Linux) shows:

 bash[1841828]: 2024-12-02 18:13:43,654:INFO:werkzeug:127.0.0.1 - - [02/Dec/2024 18:13:43] "POST /api/v1/chart/data HTTP/1.0" 200 -
 bash[1841828]: 2024-12-02 18:13:43,666:ERROR:werkzeug:Error on request:
 bash[1841828]: Traceback (most recent call last):
 bash[1841828]:   File "/usr/local/lib/python3.9/site-packages/werkzeug/serving.py", line 364, in run_wsgi
 bash[1841828]:     execute(self.server.app)
 bash[1841828]:   File "/usr/local/lib/python3.9/site-packages/werkzeug/serving.py", line 328, in execute
 bash[1841828]:     write(data)
 bash[1841828]:   File "/usr/local/lib/python3.9/site-packages/werkzeug/serving.py", line 268, in write
 bash[1841828]:     self.send_header(key, value)
 bash[1841828]:   File "/usr/lib64/python3.9/http/server.py", line 523, in send_header
 bash[1841828]:     ("%s: %s\r\n" % (keyword, value)).encode('latin-1', 'strict'))
 bash[1841828]: UnicodeEncodeError: 'latin-1' codec can't encode characters in position 50-55: ordinal not in range(256)

How to fix that? It worked well before upgrading.

What I've learned is the server.py (in the error) uses 'latin-1' encoding for HTTP headers. And that's a default option.

[dosubot[bot]]

I found a similar discussion that might be relevant to your issue. In a previous case, a user encountered an error when saving a dashboard with specific charts due to encoding issues. The problem was traced back to the Superset backend database using 'latin-1' encoding. Switching the database encoding to 'utf-8' resolved the issue ^[1].

For your specific problem, it seems that the error is related to the 'latin-1' encoding being used for HTTP headers, which cannot handle UTF-8 characters. You might want to check if there is a configuration option in your server setup to change the encoding for HTTP headers to 'utf-8'. Additionally, ensure that your Superset configuration for CSV exports is set to use UTF-8 encoding:

CSV_EXPORT = {"encoding": "utf-8"}

This setting ensures that exported files use UTF-8 encoding, which should help in handling UTF-8 symbols correctly. If the issue persists, you may need to look into your server's configuration to ensure it supports UTF-8 encoding for HTTP headers.

To continue talking to Dosu, mention @dosu.

[ampil]

I have figured out that there is something wrong with the PostgreSQL connection. Oracle connection (without any additional parameters) works without issues - export to Excel and CSV works ok.

Updated the connection settings (per the StackOverflow answer):

postgresql://{username}:{password}@{host}/{db}?client_encoding=utf8

But it didn't resolve the issue.
?client_encoding=utf8mb4, ?charset=utf8 didn't work - Superset returns an error invalid dsn: invalid connection option

[ampil]

@dosu,
Please reply taking into account my previous message.

[dosubot[bot]]

The error you're encountering with the PostgreSQL connection string options like ?client_encoding=utf8mb4 or ?charset=utf8 is due to these not being valid parameters for PostgreSQL connection strings in SQLAlchemy. For PostgreSQL, the correct way to ensure UTF-8 encoding is to set the database itself to use UTF-8 encoding and not rely on these parameters in the connection string.

Here are some steps you can take:

1. Ensure Database Encoding: Verify that your PostgreSQL database is set to use UTF-8 encoding. You can check this by running the following SQL command:

   SHOW SERVER_ENCODING;

   If it's not set to UTF-8, you may need to recreate the database with UTF-8 encoding.

2. Connection String: Use a standard PostgreSQL connection string without additional encoding parameters:

   postgresql://{username}:{password}@{host}:{port}/{database}
   

   Ensure that the database itself is configured correctly for UTF-8.

3. Superset Configuration: Make sure that Superset's CSV export configuration is set to use UTF-8 encoding, as you have already done:

   CSV_EXPORT = {"encoding": "utf-8"}

If the database is correctly configured for UTF-8 and the issue persists, it might be worth checking if there are any other parts of your setup (e.g., proxy servers, application servers) that might be affecting the encoding of HTTP headers or responses.

To continue talking to Dosu, mention @dosu.

[ampil]

To fix it I had to update the file from the error message: /usr/lib64/python3.9/http/server.py
at line 514 change:

("%s: %s\r\n" % (keyword, value)).encode('latin-1', 'strict'))

to

("%s: %s\r\n" % (keyword, value)).encode('utf-8', 'strict'))

in other words, change latin-1 to utf-8.

Problem solved, export to Excel or CSV works.