Re: How does somebody upgrade to roundup 2.0 if using mysql?

Hi Ralf:

In message <[email protected]>,
Ralf Schlatterbeck writes:
>On Sun, May 24, 2020 at 09:14:13PM -0400, John P. Rouillard wrote:
>> 
>> A patch needed to make tests work with mysql has the side effect of
>> making a mysql database created with roundup 1.x unusable with roundup
>> 2.0 even if you continue to use python 2.
>
>Does this DB contain data that could be published or can you reproduce
>this with an almost-empty DB? I'd like to experiment with this.

I don't have an example database. I am stating my understanding of the
issue you reported with the patch. If I were to try to create one,
what would be needed? My guess:

  1) mysql database with data entered by roundup 1.6.1
  2) I need german or other characters that are not part of the
     7 bit ascii/latin1/utf8 common character set

But I am not sure what is needed to exhibit a problem.

>> Ralf, Joseph you probably understand this the best. Can you guys write
>> up an entry in the upgrading docs for this. I will plan on marking
>> this with a big warning in the release announcement.
>
>I'm myself not using mysql, so I have a hard time describing in detail
>what the problem is. As far as I understand the problem (only for
>python2, for python3 the interface is unicode aka python3 str):

To upgrade to roundup 2.0 with python3 requires a roundup-admin export
with the existing 1.x tracker using python2. Then import the data
using roundup 2.0 with python3. So the problem: the existing mysql
database can't be used, still exists.

Joseph did I explain the sequence right?  IIRC we need both 1.x and
2.0 roundup versions on the system to make the upgrade work.

Note that exports are not designed to be used between different
versions of roundup, but (if I have it right) it seems to work for
this case.

Joseph should it be possible to export just the db using
'roundup-admin exporttable' under roundup 1.6 to not export the
files. Then I assume roundup-admin 2.0 will import just the table data.

>- When no 'charset' parameter is specified when opening the database,
>  the encoding defaults to the encoding of the database. The default is
>  latin1 in mysql
>- This means we were happily writing UTF-8 encoded data to a latin1
>  backend
>- When specifying a charset parameter (which we do by default now),
>  mysql returns the data in *decoded* form (as a python2 unicode object)
>  This is decoded into utf-8 by the backend code. When the DB is latin1
>  in the backend we're probably ending up with latin1 decoded as utf-8.
>  But I'm currently unable to verify this guess.

Is it possible to add a seting (maybe charset = None) to go back to
the original way of not using a charset. This is obviously not a great
alternative as we have encodning on encoding, but it provides an
upgrade path to 2.0 that is compatible with roundup 1.6.

That at least gives the user the option of retaining the 1.X behavior.

>Since the backend is still latin1 the DB returns decoded data. I'm
>unsure how it decodes the data when there is a database which is
>labelled latin1 but written in utf-8.
>
>My guess is that you can fix the issue by:
>- Dumping the database to text (not with roundup export but with mysql
>  dump). This is considerably faster than the roundup export mechanism
>- Edit the dumpfile to fix the charset (we can probably provide a script
>  that does that)
>- Re-import (into a new db, preferrably)
>
>Dumps that I've inspected (not created with roundup) have a header
>something like the following:
>
>/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
>/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
>/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
>/*!40101 SET NAMES utf8mb4 */;
>...
>
>And then for each table another comment like the following:
>/*!40101 SET @saved_cs_client     = @@character_set_client */;
>/*!40101 SET character_set_client = utf8 */;
>
>Can you check what is reported when you dump the non-working database
>you mention?

So you have the same question I do: what does this look like if it's a
roundup-1.6 database created without a charset? Would utf8 be replaced
by latin1?

>> IIUC performing an export using roundup-admin under the old tracker
>> and then creating a new tracker using 2.0 and performing an import
>> should work. However if you have a tracker of any size, this is a
>> significant investment in time and disk space that requires the
>> tracker be placed offline.

Also I forgot to mention the possible incompatibility of export/import
between different versions of roundup.

>Yes, I think there is no way around this but we can probably speed
>things up by dumping with mysql, modifying the dump, and then
>re-importing, this should reduce the downtime considerably.
>We probably *do* have databases out there that are labelled latin1 but
>written to as utf-8. So I don't think there is any way around a
>dump/restore cycle for these cases.

If I understand you correctly, if we have a double encoded database
utf8 encoded data (generated by roundup) re-encoded as latin1 (by
mysql), a dump/restore is required.

I expect this is the state for all roundup 1.x mysql databases. Do you
expect to have 1.x databases with utf8 encoded data stored as utf8 by
mysql?

I will take another shot at getting my development system running
cleanly again. When I tried to test running mysql on a pi, it was
problematic. The import of my test db crashed the system 8-).

Also I need to get my head around all these encodings etc. so I have
some clue about the validity of my test.

Have a great day.

--
				-- rouilj
John Rouillard
===========================================================================
My employers don't acknowledge my existence much less my opinions.