Re: mysqlIndexerTest.test_unicode fails for me

On Mon, May 04, 2020 at 11:41:59AM -0400, John P. Rouillard wrote:
> Earlier you said:
> 
>    I've now changed the String entry in sql_to_hyperdb_value in
>    backends/rdbms_common.py from 'str' to 'us2s'.
> 
> is this what caused the double encoding? I assume not since it's
> converting 's' to 's' for python2 right?

No we've always had the problem that we're writing strings to the DB
that are using hard-coded utf-8 from roundups db interface. That means
we're happily writing utf-8 to a latin1 backend.

Now with the configurable charset option for mysql, mysql returns
*unicode* for python2. Thats fine as long as the DB is utf-8 in the
backend. If we would explicitly *force* latin1 in the config *and* write
utf-8 strings (not unicode) to the db (we do) and read it back as
unicode (happens when an explicit charset option is passed to mysql) we
get unicode back that the db has converted *from latin1* using *utf-8*
decoding rules. Thats a classic double-encoding case.

Roundup GUI  UTF-8 written to DB
äöüß         b'\xc3\xa4\xc3\xb6\xc3\xbc\xc3\x9f'

latin1 read by db                        latin1 decoded to unicode
b'\xc3\xa4\xc3\xb6\xc3\xbc\xc3\x9f'      u'äöüÃ\x9f'

> It would be better to allow an upgrade to 2.0.0 using python2 to not
> require a dump/restore. We really need an 2.0.0 upgrade mechanism that
> doesn't require somebody taking the tracker offline possibly for hours
> to do a dump/restore. When converting to python 3.x it's not great to
> need this change, but justifyable.

We currently seem to have some installations out there that
- have a default latin1 (or other non-utf-8) backend
- we wrote utf-8 to that database ignoring the db encoding

So there is no other way back to a sane state than dump/restore.
At least I don't see any.

> >Now that I understand the whole thing a little better: let me note that
> >legacy databases can probably converted by dumping them to sql text
> >format, editing the dump to change the charset to utf-8 and
> >re-importing.
> 
> Does that work only because utf-8 is a superset of latin1? I assume
> this will only work for latin1 encodings.

No this is because latin1 is a charset that can store almost any other
encoding transparently :-) It won't choke on wrong characters because
*any* 8-bit character is valid latin1. (Except for some low 7-bit
reserved control characters but thos are also reserved in utf-8)

> How does dump/restore differ from using:
> 
>   ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
>   ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This would read the db as latin1 and write it back as utf-8.
The problem is: What we wrote to the db is *not* latin1. It's utf-8.
But I don't know enough about mysql to know if it's possible to
interpret the latin1 data in the database *as utf-8*.

> Also any idea what this means if somebody is supporting asian
> languages in mysql? I know we have had a few bugs reported handling
> Chinese for example.

I think they would have an utf-8 backend already, otherwise that
wouldn't work. So they should be fine with py3.

> If we can't use the database schema mutability/upgrade code to do this
> conversion (grep for _version_ in roundup/backends/back_mysql.py), can
> you write up the sequence required to do the conversion. We can review
> it here or in upgrading.txt.

I don't think this
- is possible to auto-detect
- is possible to auto-convert if we could detect it because I see no
  other way than dumping the db and changing the encoding in the dump
  (the encoding name at the top of the dump that is, so that when
  reading the db it's interpreted as utf-8).

> Maybe we should post a notice of what is hapening on the roundup-users
> list and see what effect this will have on uptake of 2.0.0.

I think its hard to get sensitive feedback on the topic because of the
complexity of the material. One diagnosis would be to dump the db, look
what encoding the db *thinks* this is in by looking at the dump and then
looking for strings in the dump. But the latest point is already hard
because an intelligent tool will auto-detect the encoding and you
wouldn't see what really is in the dump :-)

Ralf
-- 
Dr. Ralf Schlatterbeck                  Tel:   +43/2243/26465-16
Open Source Consulting                  www:   http://www.runtux.com
Reichergasse 131, A-3411 Weidling       email: [email protected]


_______________________________________________
Roundup-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/roundup-devel