Web Help Desk Upgrading MySQL to Postgres

Warning- this post has a lot of words, and some of them are very sweary indeed….This job took me 10 months. Seriously. None of the solutions are particularly difficult or hard to understand, but I’d like you to know that while a lot of it seems easy now, I was constantly tied in knots because I didn’t know what the hell I was doing. It’s easy now to say ‘oh just cast tinyint to boolean and ignore that the data type is tinyint(4)’ but first you have to understand a tinyint. And then the difference between that and tinyint(4). And why that might make your translator program barf and cause the database at the other end lose it’s shit completely. Let me be clear- if you want to do this, you really shouldn’t. It would be much easier to simply donate $50k of your time to someone who will torture you for days without end. And if you decide to go ahead anyway, please be forewarned that you’ll-

1. Be up until 2am weekends and weekdays more times than you can count2. Cry when small errors occur3. Eventually decide that the only way out is to cleanse the entire planet with fire4. Want to hunt down and murder anyone who has ever touched a database. And their families5. Never see your family6. Wonder what it’s like to NOT be completely obsessed with something that you have no hope of understanding in your lifetime or several more…

Now, if you still want to go ahead, I’ve now done most of the work. So maybe you’ll be luckier, but don’t count on it. Also I’m putting the solution at the bottom so you at least have to scan past my cries for help, drugs, medical assistance and an easeful death.YOU HAVE BEEN WARNED- this is only a small sample of the hell I went through.

HistoryI first purchased Web Help Desk after testing the free version for ages. This was shortly after it came down from $5000 per seat to $500 and shortly before Solarwinds purchased the product. So I’m a long time user, and never, ever wanted to see inside the guts of the product. Oh, it’s ugly.When I first bought it, Web Help Desk (WHD for short) used a ‘Frontbase’ database. I decided in my youthful wisdom (not), that Frontbase sucked and that I would use one of the other database backends that were supported, and I chose MySQL. Because I’d heard if it. That turns out to be a spectacularly bad reason to choose a db engine.What I had actually done was paint myself into a corner, and thus the 10 month effort to unpaint. The reason for this effort was because I wanted to move my install back into compliance with what Solarwinds implement for a new install, so that I could leverage things like upgrades without pain and perhaps even move to an appliance if they ever decide to support one again. I was also moving away from using Macs as my VMs, I wanted to be able to deploy my servers as Linux VMs, as I was pretty sure they would announce end of support for Mac servers at some point (as I was doing this for my Screenconnect install, Connectwise announced end of support for Mac servers). Oh, and I wanted the install protected with an SSL certificate. Not asking much hey?

So here’s a copy/pasta of some of my efforts- I’ll spare you the entire litany of things that didn’t work and just give some realtime notes of what happened… but go to the end if you want the tl;dr

According to pgloader docs herehttps://pgloader.readthedocs.io/en/latest/ref/mysql.html#default-mysql-casting-rulesThese are automatically translatedBLOB to byteaLONGBLOB to byteaVARCHAR to varcharTEXT to textDATETIME to timestampz – there are all sorts of modifiers here, no idea what they mean INT should be translated to int, this is apparently an alias for int4 according to IBM, as it is 4 bytes long. So that should be ok DECIMAL to decimal which is incorrect- needs to be ‘numeric’so we need to add to our load file this line‘CAST type decimal to numeric’

Sooooo, first steps- boot a new VM and install PostgresQL 9.2Well, this is pretty fucking annoying because it’s an old version, so you have to install the repo first etc.Then install pgsqlThen test itThen modify the conf files to allow external accessBut first you have to set a postgres password using

I actually think this is one of the major problems with my setup, but they didn’t tell me about it when it came up-https://support.solarwinds.com/Success_Center/Web_Help_Desk_(WHD)/Knowledgebase_Articles/Upgrading_to_Web_Help_Desk_12_6_for_MySQL_usersSo back to mysql, next steps are to connect with mysql workbench and check a few things1. that lower case tables are on2. can be accessed over the network3. other errors from previous attemptsgot a warning when trying to restart mysqld, I wonder if this might affect the translationwarning timestamp with implicit default value is deprecated. please use —explicit_defaults_for_timestamp server option- Thats a bullshit message and can be ignored, thanks for wasting another 30 minutes. Fuckcan’t create test file /var/lib/mysql/webheldesk.lower-testdatadir is meant to be /var/lib/mysqlseems to launch on boot? Fuck it, got other things to fixlogin to mysql

mysql -u root -p

This should dump out a backup of your mysql db- you can use these passwords as much as you like- any passwords that remain in this doc are no longer in use.mysqldump -u root -p -h localhost –opt whd | gzip > /home/Downloads/mysqldump-${db}-$HN-`date +%Y-%m-%d`.gz

Next thing is to fix the db as it was originally set up on a Mac without any timezone data and using the Swedish Latin collation for character set. I’m not 100% sure these things will fix it as there were still some Latin entries in the db, but I’m including it because I did it…

The next issue was that there was an error in pgsql where some shit about a foreign constraint in a table called ‘orion_alert_sources’ wasn’t going to work. This managed to shit me up the wall for fucking ages, and all for nought because it refers to a service that I don’t use.

table is called ‘Orion Alert Filters’ColumnsIDDATADEFAULTACCEPTORIONALERTSOURCE_ID

This table has the foreign key ORION_ALERT_FILTERS_ORION_ALERT_SOURCES_FK

So how did I work that one out? Well it turns out that the casting rules were trying to be too tricky, and were changing a data type into something incompatible in the new db.

After that I spent a horrific week or two trying to get Postgres 9.2 and pgloader installed on to the same machine. Let’s just say that I ended up using pgloader in Docker and the psychiatrists invoices are going to be enormous.

The Docker pgloader is amazing- and I was able to speed up my testing by having the .load file on a server and keep it open in text wrangler, so I could make a change, save it and run it immediately. Boom.

Fixing the time zone tables in MySQLSo my old MySQL db didn’t contain any tz_info and getting it into the db wasn’t easy- because my install was on a Mac, none of the instructions from around the internet worked, and I finally found out why. Every bit of instruction you see on the internet has the .conf files in a particular place, and on a Mac they’re in a different place. Fuck.AND- when you execute the commands in MySQL Workbench, it looks like they are working, but they don’t. This is because Workbench IS actually modifying the file, but MySQL on a Mac isn’t reading that cunting file.

in the end I kind of sorted this by moving the MySQL install to CentOS and upgrading the db from version 5.5.x to 5.7.24, which is still hella old, but I didn’t want to update to a version that was too modern in case I introduced other errors. That or manually updating the tz_info tables actually worked, not sure which.

Fixing character case in MySQLThen just when you thought it couldn’t possibly get more complicated the migration barfed it’s guts up because of upper/ lower case characters in the fucking column names-

ok, we’re going to start by saving a backup, then dropping the database, checking the .conf in /usr/share/mysql/charsets/ ?then importing back in with the charset set to utf8changed the character set without dropping and reimporting and still have a problem with the * in my regex for the .load file- that was fixed by changing * to .*doesn’t work as ‘illegal utf8 characters- looks like we have to massage the original database to find the issues then use the ‘alter table’ statement in mydsql workbench on the following tables-database WHDclient_noteNOTE_TEXTemail data objectsubjectemail data objectmessage bodyclientfirst namefaq answerinvalid email recipient contentinvalid email recipient subjectsomething like-

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

still doesn’t fucking work, I’m going to look at the .load file AGAIN.

A few more tries and we made some progress, but when starting WHD and choosing the newly migrated Postgres db, we get the following error-

Because apparently we can’t be trusted with the big boy toys. Remind why I don’t go postal again?

And then the upgrade went perfectly. Why is that note not in the upgrade docs, or do they consider it redundant?Next step is to try the Postgres migration again, but first I have to dump the pgdb and create a new one that will match the credentials of the built in one. It’s not very clear, but the built in pgdb seems to use admin/ admin and port 20293. Looks like the database must be called whd and the owner is named whd- actually nope here is the correct article-https://support.solarwinds.com/Success_Center/Web_Help_Desk_(WHD)/Knowledgebase_Articles/Restore_PostgreSQL_on_a_new_server_after_a_Web_Help_Desk_WHD_upgradeok built a new VM with no postgres and installed whd. Initially set up the built in pgsql db, then logged in and changed the database settings. This required a restart, and that failed. The error message is 2019-02-24 15:22:58.360 [ApplicationManager-1] ERROR c.s.w.s.a.i.WhdApplicationManagerImpl – Failed to start WHD applicationorg.springframework.scheduling.SchedulingException: Could not start Quartz Scheduler; nested exception is org.quartz.SchedulerConfigException: Failure occured during job recovery. [See nested exception: org.quartz.JobPersistenceException: Couldn’t recover jobs: ERROR: operator does not exist: character varying = boolean Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 115 [See nested exception: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = boolean Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 115]]So it looks like we need to fine tune the casting rules in pgloaderNeed to drop the db, adjust the casting rules and try againso the cast rule that is wrong is this default rule

Quartz Scheduler is an open source java based app that apparently needs to store or read some data from our database, and it’s barfing because of one of our casting rules. I found an old list of the data types- here are the differences between the doc here-https://gist.github.com/ajbrown/7923127and my casted pgsql db-CREATE TABLE whd.qrtz_fired_triggers is_nonconcurrent character varying(1) COLLATE pg_catalog.”default”, requests_recovery character varying(1) COLLATE pg_catalog.”default”,

Looks like all of the above data types should be Boolean.Back to MySQL, if we check all of these table types they are all varchar(1), which in MySQL is EXACTLY the same as BOOLEAN, which is why they work in MySQL but not in pgsqlSo we can either change them in MySQL before import or fix them in the casting rules, not sure which is better- I guess that kind of depends on how fussy WHD is and how many times this is in the MySQL db.On further investigation, these 6 instances are the only places where varchar(1) appears, so it is probably safe to do either way.And having a closer look we see that there is NOTHING in any of these tables, so we can simply stop whd and change them- in pgloader there doesn’t seem to be a clean way to do this, so modifying the MySQL is chosenUnfortunately I can’t change the data types with mysql workbench, keeps defaulting to tinyint, so I’ll do the import then make the changesThis resulted in me watching youtube videos about mysql in Kurdish, I hope you’re fucking happy Solarwinds.ok so after watching more videos it turns out that tinyint is correct as it actually is the same as boolean in mysql. Fuck.So I went back and used MySQL Workbench to change them all and they became tinyint(4)– which doesn’t work, so we have to modify the .load file to be more specific. I removed the display length requirement in the casting rule and it did finally work. AND it also seemed to fix the Orion error as well.

Next step is to import this back to the correct vmtriedpgsql9/bin/pg_restore -Fc -v -h 127.0.0.1 -p 20293 -U postgres -d whd path_to_whd_pgdump.backupbut that didn’t work, says can’t connect to db called ‘whd’, connection refused. This means either the server is not listening on port 20293 or I don’t have the correct privs- or a firewall issue, or a name/ connection profile issue?Ah ok the failure was because pgsql was not running, need to sudo ./whd start firstimport seemed to go ok, next step is cleaning up some of the old vms and rebooting the fresh one – set ip address, set up license, SSL Cert

After setting this all up there was a few more housekeeping things like Firewall rules, setting backups, checking port availability and testing but it’s all good now.

So if you want the full instructions-1. massage the data in your source first- time zone data, character set, lower case table names, data types etc2. migrate the database3. export and re-import into built in pgsql4. drink heavily

Many thanks to Dimitri Fontaine (who actually helped me and didn’t ignore my noob questions) Ben Rollins – mate I wrote about 20x more emails than I actually sentClayton Crabtree who got me to keep going rather than start again on a more promising route.

If you’re a java, database or linux guru, count yourself lucky that I didn’t contact you…