2020-03-08T00:32:56  -heroes-bot- PROBLEM: PSQL locks on mirrordb1.infra.opensuse.org - POSTGRES_LOCKS CRITICAL: DB postgres total locks: 51 ; See https://monitor.opensuse.org/icinga/cgi-bin/extinfo.cgi?type=2&host=mirrordb1.infra.opensuse.org&service=PSQL%20locks
2020-03-08T01:22:55  -heroes-bot- RECOVERY: PSQL locks on mirrordb1.infra.opensuse.org - POSTGRES_LOCKS OK: DB postgres total=45 ; See https://monitor.opensuse.org/icinga/cgi-bin/extinfo.cgi?type=2&host=mirrordb1.infra.opensuse.org&service=PSQL%20locks
2020-03-08T01:36:55  -heroes-bot- PROBLEM: PSQL locks on mirrordb1.infra.opensuse.org - POSTGRES_LOCKS CRITICAL: DB postgres total locks: 55 ; See https://monitor.opensuse.org/icinga/cgi-bin/extinfo.cgi?type=2&host=mirrordb1.infra.opensuse.org&service=PSQL%20locks
2020-03-08T03:07:37  *** okurz_ is now known as okurz
2020-03-08T09:05:12  <pjessen> cboltz: the exported data is in the standard mysqldump format, CREATE TABLE with CHARSET=latin1
2020-03-08T09:17:25  <pjessen> it is possible I did create the problem myself by using default charset utf8mb4 on the CREATE DATABASE.
2020-03-08T09:18:20  <pjessen> I think I'll try another import+upgrade, but leave everything as latin1.
2020-03-08T13:41:28  <lcp> it will be worth doing someday, although it will probably be easier if the data matches the overall encoding of the database
2020-03-08T13:41:56  <lcp> worth doing re-encoding to utf8 that is
2020-03-08T14:02:59  -heroes-bot- PROBLEM: MySQL WSREP recv on galera3.infra.opensuse.org - CRIT wsrep_local_recv_queue_avg = 277.806645 ; See https://monitor.opensuse.org/icinga/cgi-bin/extinfo.cgi?type=2&host=galera3.infra.opensuse.org&service=MySQL%20WSREP%20recv
2020-03-08T14:03:22  <pjessen> lcp: I have tried converting all tables to utf8mb4, but some complained about duplicate keys.
2020-03-08T14:04:03  <lcp> ugh, that sucks
2020-03-08T14:04:32  <pjessen> right now, I've completed a 2nd import+upgrade, much faster on stand-alone mysql.
2020-03-08T14:04:56  <pjessen> if I use client charset=latin1, the pages display okay, but the links are wrong.
2020-03-08T14:06:31  <pjessen> if I use client charset=utf8mb4, the pages display wrong, but the links work
2020-03-08T14:12:12  <lcp> did you try re-encode latin1 imported database to utf-8?
2020-03-08T14:13:33  <lcp> because if the encoding matches the tables now, it should be easier to re-encode in different encoding
2020-03-08T14:13:50  <lcp> (and maybe won't cause overlap in keys)
2020-03-08T14:16:29  <cboltz> pjessen: to start with, I'd like to look at the database dump (to replace guesses with knownledge ;-) - can you point me to it?
2020-03-08T14:17:31  <cboltz> also, when you converted to utf8mb4, was the complaint really about "duplicate keys"? My guess would be a complaint about the "index lengtht"
2020-03-08T14:21:22  <lcp> it seems that the wildcard dns entry for opensuse.org was removed
2020-03-08T14:30:49  <malcolmlewis> pjessen, could this conversion of the charset be done by the MF guy before exporting?
2020-03-08T14:32:16  <malcolmlewis> pjessen, AFAIK, since we have a vB license you could ask their (as in vB) support for help
2020-03-08T14:33:12  <malcolmlewis> cboltz, lcp ^^
2020-03-08T14:34:41  <lcp> it could be done by them, but I think they don't owe us much of anything in their view ;)
2020-03-08T14:35:41  <malcolmlewis> lcp, a little bit harsh.... ;) I'm sure they would help
2020-03-08T14:35:53  <cboltz> lcp: IMHO they owe us a *big* pain compensation (for various reasons) ;-)
2020-03-08T14:36:17  <cboltz> but nevertheless, I'd avoid everything that could delay the migration
2020-03-08T14:37:04  <pjessen> cboltz: on forum.o.o, in /home/pjessen
2020-03-08T14:37:29  <pjessen> cboltz: no, it was duplicate keys - due to the collation changing.
2020-03-08T14:38:12  <cboltz> that's... surprising
2020-03-08T14:39:31  <pjessen> malcolmlewis: I'm sure we could ask vB for support, but I'm not yet sure what to ask :-)
2020-03-08T14:41:23  <cboltz> "can someone help with this migration and upgrade?"
2020-03-08T14:41:24  <cboltz> ;-)
2020-03-08T14:42:12  <malcolmlewis> pjessen, what cboltz said, worth a shot?
2020-03-08T14:42:19  <lcp> "my house is on fire, please call 112"
2020-03-08T14:43:00  <pjessen> malcolmlewis: nah, I prefer asking intelligent questions. better chance of also receiving an intelligent reply :-)
2020-03-08T14:43:55  <pjessen> cboltz: the file is called webforums_2002something.xz
2020-03-08T14:44:04  <cboltz> yeah, I've already found it
2020-03-08T14:44:27  <lcp> the question should be "how do you expect the users of 4.x to reencode their database to utf8mb4 before migration to 5.x"
2020-03-08T14:44:40  <lcp> because clearly this worked for 4.x in some way
2020-03-08T14:45:33  <malcolmlewis> pjessen, there are some scripts here: https://forum.vbulletin.com/forum/vbulletin-5-connect/vbulletin-5-connect-questions-problems-troubleshooting/vbulletin-5-support-issues-questions/4403164-database-question-regarding-collation-latin1-vs-utf8-vs-utf8mb4
2020-03-08T14:45:55  <cboltz> maybe it "accidently" worked, but this accident depends on the server settings (like connection charset etc.)
2020-03-08T14:46:12  <cboltz> sometimes even "crap in, crap out" works if both ways are equally crappy ;-)
2020-03-08T14:46:18  <pjessen> yup.  looking at forums.o.o, they serve everything in utf8.
2020-03-08T14:48:12  <lcp> interesting
2020-03-08T14:48:43  <lcp> those scripts also look mighty interesting ;)
2020-03-08T14:49:25  <malcolmlewis> pjessen, I would login and check we have support, then ask the same sort of question?
2020-03-08T14:49:52  <pjessen> I had better take a look then.
2020-03-08T14:50:13  <malcolmlewis> pjessen, might be easier i the long run?
2020-03-08T14:50:19  <malcolmlewis> i/in
2020-03-08T14:50:19  <lcp> those scripts actually look like something that could fix the issue
2020-03-08T14:51:27  <lcp> there are two scripts for utf-8 there, utf8table fix and update vb5 tables to innodb and utf8
2020-03-08T14:51:38  <cboltz> looks like my crap in, crap out theory wasn't too wrong
2020-03-08T14:51:49  <cboltz> all CREATE TABLE statements use latin1
2020-03-08T14:52:01  <cboltz> but it seems the database content is utf8 encoded
2020-03-08T14:52:13  <lcp> wew
2020-03-08T14:52:18  <cboltz> (for example, search for "N�rnberg" and pipe that line into "file"
2020-03-08T14:52:41  <pjessen> cboltz: interesting - the forums link malcolm sent says otherwise.
2020-03-08T14:52:45  <cboltz> (checked in the vb_event table, but I'd be surprised if other tables differ)
2020-03-08T14:53:26  <cboltz> my guess is that the connection charset (between PHP and mysql) was set incorrectly
2020-03-08T14:53:36  <pjessen> I can rerun the import+upgrade in about 3 hours now, maybe it's worth a try creating the tables as utf8.
2020-03-08T14:54:05  <pjessen> cboltz: the client charset you mean?
2020-03-08T14:54:18  <cboltz> indeed, replacing   DEFAULT CHARSET=latin1   with   DEFAULT CHARSET=utf8   in (a copy of) the dump is worth a try
2020-03-08T14:54:26  <cboltz> yes, client charset
2020-03-08T14:55:14  <pjessen> that one is easily done, the import only takes about 30-40min
2020-03-08T14:56:51  <cboltz> be careful when doing the replacement, it seems like some table content also contains DEFAULT CHARSET
2020-03-08T14:57:26  <cboltz> sed '/^)/ s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/'   should work (note the  /^)/  part)
2020-03-08T14:58:55  <pjessen> already running
2020-03-08T15:24:25  <pjessen> cboltz: nice trick with /^)/
2020-03-08T15:25:49  <lcp> I'm still trying to figure out how that works
2020-03-08T15:27:22  <pjessen> lcp: I read it to mean "for lines matching ^), do the next cmd"
2020-03-08T15:27:44  <cboltz> lcp: the  /^)/  sets the range sed works on (in this case: does the s/../../ replacement)
2020-03-08T15:28:10  <cboltz>  /^)/  is basically the regex   ^)   - lines starting with   )
2020-03-08T15:28:53  <cboltz> regexes are not the only thing you can use
2020-03-08T15:29:04  <lcp> oh, interesting
2020-03-08T15:29:07  <cboltz> for example    1,5 s/x/y/   will replace x with y in lines 1..5
2020-03-08T15:31:16  <cboltz> hmm, looks like we still have charset problems, for example   Ank�?ndigungen und Neues   directly on the front page and several similar issues in the topic titles if you enter this subforum, and also in the content there
2020-03-08T15:31:33  <cboltz> at least it seems to be consistently broken ;-)
2020-03-08T15:31:53  <pjessen> I haven't changed anything yet
2020-03-08T15:32:01  <cboltz> ah, ok
2020-03-08T15:32:50  <cboltz> tell me when you have ;-)
2020-03-08T15:32:55  <pjessen> the database import is running, after that I'll do another upgrade, which will take 2h20m
2020-03-08T15:32:58  <pjessen> cboltz: willdo
2020-03-08T16:27:57  <lcp> cboltz: you might recognize this https://planet-test.kde.org/ >:D
2020-03-08T16:28:21  <lcp> Carl from KDE got inspired by our planet-test and reused it 😁
2020-03-08T16:29:04  <cboltz> :-)
2020-03-08T16:44:51  <cboltz> speaking about planet-test.o.o
2020-03-08T16:45:06  <cboltz> - can we have rss20.xml (which the current planet.o.o uses) instead of atom.xml?
2020-03-08T16:45:19  <cboltz> - besides that, is there anything that stops us from switching over?
2020-03-08T16:49:35  <lcp> cboltz: there is rss20.xml
2020-03-08T16:49:40  <lcp> it's just not visible
2020-03-08T16:49:55  <cboltz> :-)
2020-03-08T16:50:24  <lcp> so nothing is stopping me tbh
2020-03-08T16:51:08  <cboltz> so switching is just a haproxy config change away
2020-03-08T16:51:33  <cboltz> just tell me when you want it switched ;-)
2020-03-08T16:51:43  <lcp> yup
2020-03-08T16:54:55  -heroes-bot- PROBLEM: PSQL locks on mirrordb1.infra.opensuse.org - POSTGRES_LOCKS CRITICAL: DB postgres total locks: 63 ; See https://monitor.opensuse.org/icinga/cgi-bin/extinfo.cgi?type=2&host=mirrordb1.infra.opensuse.org&service=PSQL%20locks
2020-03-08T17:04:56  -heroes-bot- RECOVERY: PSQL locks on mirrordb1.infra.opensuse.org - POSTGRES_LOCKS OK: DB postgres total=48 ; See https://monitor.opensuse.org/icinga/cgi-bin/extinfo.cgi?type=2&host=mirrordb1.infra.opensuse.org&service=PSQL%20locks
2020-03-08T17:11:26  <pjessen> i think the database import is working this time, I see Nürnberg in vb_event.
2020-03-08T17:16:04  <lcp> nice
2020-03-08T17:17:58  <cboltz> I still see some broken umlauts - is the import already complete, or still running?
2020-03-08T17:18:55  -heroes-bot- PROBLEM: PSQL locks on mirrordb1.infra.opensuse.org - POSTGRES_LOCKS CRITICAL: DB postgres total locks: 50 ; See https://monitor.opensuse.org/icinga/cgi-bin/extinfo.cgi?type=2&host=mirrordb1.infra.opensuse.org&service=PSQL%20locks
2020-03-08T17:28:24  <pjessen> import is still running, I had to restart due to table full. F* - just happened again. after 56mins.
2020-03-08T17:28:50  <cboltz> nice :-/
2020-03-08T17:29:11  <pjessen> there is plenty of space, I dont understand
2020-03-08T17:29:29  <cboltz> what's the exact error message?
2020-03-08T17:30:11  <pjessen> ERROR 1114 (HY000) at line 8222: the table 'vb_session' is fulll. (not copy&paste)
2020-03-08T17:30:21  <pjessen> same place is stopped on the 1st attempt.
2020-03-08T17:30:30  <pjessen> exact same place
2020-03-08T17:33:12  <cboltz> https://bobcares.com/blog/error-1114-hy000-the-table-is-full/
2020-03-08T17:33:43  <cboltz> try adjusting/increasing innodb_data_file_path
2020-03-08T17:34:08  <cboltz> (and use innodb_file_per_table if it isn't set already)
2020-03-08T17:35:21  <pjessen> cboltz: already set, its the default.
2020-03-08T17:36:44  <pjessen> cboltz: innodb_data_file_path isn't set at all.
2020-03-08T17:37:54  <pjessen> so - the question is, why does the change to using default charset utf8mb4 make such a difference???
2020-03-08T17:38:24  <cboltz> check   show variables like 'innodb_data_%';   for the current value
2020-03-08T17:39:07  <cboltz> utf8mb4 needs (AFAIK) 4 bytes per char, which means varchar(255) needs 4*255 = 1020 bytes
2020-03-08T17:39:29  <cboltz> utf8 "only" needs 2 or 3 bytes (not sure) per char
2020-03-08T17:39:45  <lcp> 4mb, as the name implies ;)
2020-03-08T17:40:33  <pjessen> innodb_data_file_path = 12M autoextend
2020-03-08T17:41:55  <pjessen> i think utf8mb4 means it'll support up to 4 bytes per char, just like utf8 will do 2 or 3.
2020-03-08T17:42:53  <pjessen> utf8 ~= utf8mb3
2020-03-08T17:43:16  <cboltz> right, but utf8mb4 also uses more space (4*255, even if you only store ascii in such a field) on disk and (worse) in the index length
2020-03-08T17:44:05  <pjessen> I guess the disk could have run full  whilst I wasn't watching.
2020-03-08T17:45:10  <pjessen> I think I'll shut down thr webserver, drop the last webforums database, and try importing again.
2020-03-08T17:47:11  <cboltz> good idea, and maybe only use utf8 as encoding, not utf8mb4 (at least for now)
2020-03-08T17:49:40  <pjessen> cboltz: hmm.  vb does expect/recommend mb4, but I should be able to force it to utf8.,
2020-03-08T17:50:41  <cboltz> well, for the import, use utf8
2020-03-08T17:50:49  <cboltz> you can convert it later
2020-03-08T17:51:08  <pjessen> true
2020-03-08T17:52:15  <pjessen> right, have restarted the import.
2020-03-08T18:54:54  <pjessen> aarg. after 57min, all space cleared, using utf8 instead of utf8mb4, I still got a "table is full". the filesystem is 29% full.
2020-03-08T18:55:13  <pjessen> getting a little frustrated now.
2020-03-08T18:56:22  <cboltz> nice :-/
2020-03-08T18:57:06  <cboltz> same line again?
2020-03-08T18:58:13  <cboltz> just as a wild idea - could this be caused by LOCK TABLES?
2020-03-08T18:58:56  <cboltz> grep -v "^LOCK TABLES" $dump   might be worth a try (note the   ^   to avoid false matches)
2020-03-08T18:59:51  <pjessen> cboltz: line 8223 this time
2020-03-08T19:00:14  <cboltz> oh, you are making progress ;-)
2020-03-08T19:00:22  <pjessen> cboltz: slap!
2020-03-08T19:00:47  <malcolmlewis> pjessen, raise a request with vB....
2020-03-08T19:00:50  <cboltz> both lines are   INSERT INTO `vb_session` ...   so the progress is a bit limited
2020-03-08T19:01:38  <pjessen> malcolmlewis: yes, maybe. I just have to keep going - they're not fast to respond.
2020-03-08T19:02:32  <cboltz> I'd tend to ask mariadb upstream - importing a sql dump is not really vB specific
2020-03-08T19:02:51  <cboltz> as a sidenote: lines 8222 and 8223 are both ~1 MB long
2020-03-08T19:02:58  <pjessen> it is difficult to imagine a 2.5Gb database growing to 30Gb just because of a charset change
2020-03-08T19:03:44  <cboltz> the temporary mysql server has more than enough disk space IMHO - no need to worry about that
2020-03-08T19:05:24  <lcp> oh lol, it took me way too long to notice it was the next line
2020-03-08T19:40:44  <pjessen> I wonder if may I should just leave vb_session as Latin1 ......
2020-03-08T19:51:53  <pjessen> wait - vb_session - sounds like temporary data anyway?
2020-03-08T20:04:07  <lcp> pjessen: it is apparently
2020-03-08T20:35:56  <cboltz> even if it's temporary data - if restoring a database from a sqldump fails, then something is completely wrong :-(
2020-03-08T20:49:18  <lcp> true
2020-03-08T21:53:23  -heroes-bot- PROBLEM: MySQL WSREP recv on galera2.infra.opensuse.org - CRIT wsrep_local_recv_queue_avg = 108.990422 ; See https://monitor.opensuse.org/icinga/cgi-bin/extinfo.cgi?type=2&host=galera2.infra.opensuse.org&service=MySQL%20WSREP%20recv
2020-03-08T22:30:55  <cboltz> pjessen: just noticed an interesting detail:   CREATE TABLE vb_session [...] ENGINE=MEMORY
2020-03-08T22:31:09  <cboltz> with this engine, you probably really can drop the INSERTs ;-)
2020-03-08T22:38:46  <cboltz> the documentation says that increasing max_heap_table_size might help
2020-03-08T22:40:13  <cboltz> as a sidenote - there are 3 MEMORY tables
2020-03-08T23:15:58  <cboltz> pjessen: seems the documentation is right - after increasing max_heap_table_size on my laptop, the import seems to work (still running)
2020-03-08T23:16:19  <cboltz> I adjusted the config on forumsoo_temp.i.o.o, see /etc/my.cnf.d/memory_table_size.cnf
2020-03-08T23:56:59  <cboltz> a test import on my laptop just aborted after an hour with
2020-03-08T23:57:04  <cboltz> ERROR 1062 (23000) at line 9085: Duplicate entry 'men�' for key 'tagtext'
2020-03-08T23:57:15  <cboltz> command used:
2020-03-08T23:57:33  <cboltz> sed '/^)/ s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/' < webforums_200224_updated.dmp.sql | sed '/^)/ s/ ENGINE=MyISAM/ ENGINE=InnoDB/' | mysql forum
2020-03-08T23:58:34  <cboltz> on the positive side, I didn't hit the "table is full" error anymore (after increasing max_heap_table_size)