On Sat, 04 Jun 2022 14:49:27 -0000, Malcolm Lewis wrote:

Hi As long as the history can be maintained whether that be in a searchable read-only archive or similar, would it not make more sense just to start fresh and move forward?

That makes sense to me as well - I don't know that we need to go back through history "forever" (if we decided we do, maybe we can do an export to a mailing list format and just wrap it into the ML archives? The NNTP gateway is also a ML gateway IIRC).

Setup an instance that works with the current infrastructure login system etc?

Training for the Admins and moderators in the new setup as well.

I think there's a Docker image we could use to just get familiar with the system itself; the bigger deal is working out structure and how what we have translates to Discourse, and all the different config options. I looked at it a few years back, but it's been a while. -- Jim Henderson Please keep on-topic replies on the list so everyone benefits

On So, Jun 5 2022 at 01:21:37 +0200, Sasi Olin wrote:

What I'm thinking of doing would be to do two database imports, with different encodings set, one for data with the modify date before the breakage with the old encoding and then the other for data with the modify date after the breakage with the new encoding. The only real issue is finding the exact moment the database broke, which will be easier having the post above.

Update on this: picked the date based on that post, and it seems to work to an extent: all the posts after that date seem to be broken (because the db still uses latin1 encoding while vb keeps pushing utf8 content into it) Using ``` mysqldump --host=192.168.47.4 --port=3307 --user=vbulletin \ --password=$password --where "dateline > '1304923020'" webforums vb_post ``` I was able to see the breakage quite easily, and I am able to force mysql to export this as if it was a utf-8 database, so that we get both both parts of the database to be the correct encoding inside the dump Could we maybe create a copy of vb server (and its database), so that we could test what changes we actually need to make for this to work? And on that note, does anyone have a good idea how to find broken characters inside of the dump, so that we can find the earliest date with breakage (in other tables too, I was just using vb_post as an example, because it's the most obvious of the broken tables)? It shouldn't be too difficult to do in an automated way I would assume, but my process for this feels more manual than it probably should be. LCP [Sasi] https://lcp.world/

On Mi, Jun 15 2022 at 22:19:50 +0200, Sasi Olin wrote:

A quick note on that, it's not entirely true, Russian part of the forums was using utf8 since it started, so we will have to exclude all of the posts that belong to that part of the forums and export them together with the rest of utf8 posts after the date. This far I found 1304945809 to be the most recent broken post, but I will have to check it again after I come up with a query that excludes russian forum posts.

After some time, I came up with the following queries: ``` # Posts excluding the russian forum (909) select * from vb_post where dateline < '1304944363' and threadid in (select threadid from vb_thread where forumid not in (with recursive cte as (select forumid from vb_forum where forumid='909' union all select c.forumid from vb_forum c join cte ON cte.forumid=c.parentid) select forumid from cte)); # Threads excluding the russian forum (909) select * from vb_thread where dateline < '1304944363' and threadid in (select threadid from vb_thread where forumid not in (with recursive cte as (select forumid from vb_forum where forumid='909' union all select c.forumid from vb_forum c join cte ON cte.forumid=c.parentid) select forumid from cte)); # All of pms select * from vb_pmtext; ``` I am not touching the rest of the forums, since we will not need it for discourse migration anyway. Should we schedule maintenance for the forums and fix them using those? What I would essentially do would be: 1. Dump the entire database into utf8 charset (essentially intentionally breaking it with mysqldump --set-charset=utf8 > ...) 2. Dump the bits of the database with latin1 encoding using the queries above (with mysqldump --set-charset=latin1 --single-transaction=TRUE \ --where="$query_after_where" $database $table > ...) 3. Import the big dump into a new database 4. Override the broken database bits with the small dumps It seems like the least potential for breakage this way, since all of those seem properly indexed but I am open to suggestions. There are some older and newer posts with broken encoding, because a lot of people were using utf8 with nntp before the switch and some people kept using signatures with broken encoding past the utf8 switch. We can keep around the old database and fix the posts one by one, but people did reference broken posts in the forums themselves as well, so it would be breaking the actual flow of the conversations at that point. LCP [Sasi] https://lcp.world/