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

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.

> 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):

- 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.

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?

> 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.

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.

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]