test_sqlite3 fails on non-UTF-8 locale

[serhiy-storchaka]

$ LC_ALL=en_US.iso88591 ./python -m test -vuall test_sqlite3
...
======================================================================
ERROR: test_ctx_mgr_rollback_if_commit_failed (test.test_sqlite3.test_dbapi.MultiprocessTests.test_ctx_mgr_rollback_if_commit_failed)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/serhiy/py/cpython/Lib/test/test_sqlite3/test_dbapi.py", line 1767, in test_ctx_mgr_rollback_if_commit_failed
    cx = sqlite.connect(TESTFN, timeout=self.CONNECTION_TIMEOUT)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe6 in position 16: unexpected end of data

======================================================================
ERROR: test_open_uri (test.test_sqlite3.test_dbapi.OpenTests.test_open_uri)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/serhiy/py/cpython/Lib/test/test_sqlite3/test_dbapi.py", line 674, in test_open_uri
    with managed_connect(TESTFN) as cx:
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/serhiy/py/cpython/Lib/contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "/home/serhiy/py/cpython/Lib/test/test_sqlite3/test_dbapi.py", line 44, in managed_connect
    cx = sqlite.connect(*args, **kwargs)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe6 in position 16: unexpected end of data

======================================================================
ERROR: test_open_with_path_like_object (test.test_sqlite3.test_dbapi.OpenTests.test_open_with_path_like_object)
Checks that we can successfully connect to a database using an object that
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/serhiy/py/cpython/Lib/test/test_sqlite3/test_dbapi.py", line 670, in test_open_with_path_like_object
    with managed_connect(path) as cx:
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/serhiy/py/cpython/Lib/contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "/home/serhiy/py/cpython/Lib/test/test_sqlite3/test_dbapi.py", line 44, in managed_connect
    cx = sqlite.connect(*args, **kwargs)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe6 in position 16: unexpected end of data

======================================================================
ERROR: test_trace_callback_content (test.test_sqlite3.test_hooks.TraceCallbackTests.test_trace_callback_content)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/serhiy/py/cpython/Lib/test/test_sqlite3/test_hooks.py", line 279, in test_trace_callback_content
    con1 = sqlite.connect(TESTFN, isolation_level=None)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe6 in position 16: unexpected end of data

----------------------------------------------------------------------

[serhiy-storchaka]

It is not just tests. There is a bug in the code.

It is due to this line:

cpython/Modules/_sqlite/connection.c

Line 198 in 4153f2c

if (PySys_Audit("sqlite3.connect", "s", database) < 0) {

database is a file path which can be not UTF-8. And "s" tries to decode it as UTF-8. There may be other similar bugs related to audition in other code. @tiran

I afraid also that sqlite3.connect() does not work correctly with non-ASCII path on Windows. It could be more correct to use sqlite3_open16() on Windows, but it lacks the flags parameter.

@erlend-aasland

[erlend-aasland]

I'd rather fix this by documenting that the database path must be UTF-8. I'm afraid that using both sqlite3_open16 and sqlite3_open_v2 will create too much complexity in the code; my initial reaction is that it is not worth the added complexity, but I will absolutely consider it.

[erlend-aasland]

Quoting the SQLite docs:

Note to Windows users: The encoding used for the filename argument of sqlite3_open() and sqlite3_open_v2() must be UTF-8, not whatever codepage is currently defined. Filenames containing international characters must be converted to UTF-8 prior to passing them into sqlite3_open() or sqlite3_open_v2().

We should add this information to the docs.

[erlend-aasland]

I'm troubled by this sentence in the SQLite docs:

The default encoding will be UTF-8 for databases created using sqlite3_open() or sqlite3_open_v2(). The default encoding for databases created using sqlite3_open16() will be UTF-16 in the native byte order.

[erlend-aasland]

I propose to resolve this by improving the docs. Let me know if you agree.

[serhiy-storchaka]

#92926 resolves this issue. It also fixes support of non-decodable bytes paths. I am not sure about URIs, perhaps they need additional work.