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 count
2. Cry when small errors occur
3. Eventually decide that the only way out is to cleanse the entire planet with fire
4. Want to hunt down and murder anyone who has ever touched a database. And their families
5. Never see your family
6. 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.
History
I 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
Tools
Many CentOS 7 virtual machines
MySQL 5.7.24
Postgres 9.2
PGLoader (an open source migration tool from Dimitri Fontaine)
whd can only use postgres 9.2 or 9.3 as external dbs, let’s go to 9.2 as this matches the requirements for internal
Data types- what needs to be changed?
https://support.solarwinds.com/Success_Center/Reusable_content_-_InfoDev/WHD/Database_datatypes
According to the table I need to change
WHD Native MySQL From to this in PGSQL
PK INT int4
Currency DECIMAL numeric
datetime DATETIME timestampz
integer INT int4
pk BLOB bytea
data LONGBLOB bytea
text TEXT text
varchar VARCHAR varchar
According to pgloader docs here
https://pgloader.readthedocs.io/en/latest/ref/mysql.html#default-mysql-casting-rules
These are automatically translated
BLOB to bytea
LONGBLOB to bytea
VARCHAR to varchar
TEXT to text
DATETIME 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’
Should I use pgadmin or pgloader for migration? Decided on pgloader as there didn’t seem to be a lot of info about doing this process with pgadmin, despite it being a more popular tool.
After migrating to postgres, this might help to restore into the built in Postgres server
https://support.solarwinds.com/Success_Center/Web_Help_Desk_(WHD)/Knowledgebase_Articles/Restore_PostgreSQL_on_a_new_server_after_a_Web_Help_Desk_WHD_upgrade
Web Help Desk run commands
Stop
cd /usr/local/webhelpdesk/sudo ./whd stop
Start
cd /usr/local/webhelpdesk/sudo ./whd start
Start the embedded PostgreSQL 9.2 database
Run the following database command:
sudo.bin/pgsql/etc/rc.d/init.d/postgresql-9.2 start
Sooooo, first steps- boot a new VM and install PostgresQL 9.2
Well, this is pretty fucking annoying because it’s an old version, so you have to install the repo first etc.
Then install pgsql
Then test it
Then modify the conf files to allow external access
But first you have to set a postgres password using
sudo su postgres –
bash-4.2$ psql
psql (9.4.4)
Type “help” for help.
postgres=# \password
Enter new password:
Enter it again:
postgres=# \q
exit
conf mods from here-
https://devops.profitbricks.com/tutorials/install-postgresql-on-centos-7/
installed pgloader with yum
yum install –enablerepo=*postgresql* pgloader
by using these instructions
https://stackoverflow.com/questions/37836796/install-pgloader-in-cent-os-6-7
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_users
So back to mysql, next steps are to connect with mysql workbench and check a few things
1. that lower case tables are on
2. can be accessed over the network
3. other errors from previous attempts
got a warning when trying to restart mysqld, I wonder if this might affect the translation
warning 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. Fuck
can’t create test file /var/lib/mysql/webheldesk.lower-test
datadir is meant to be /var/lib/mysql
seems to launch on boot? Fuck it, got other things to fix
login to mysql
mysql -u root -pThis 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…
SELECT CONCAT(“ALTER TABLE “, TABLE_SCHEMA, ‘.’, TABLE_NAME,’ COLLATE utf8_general_ci;’) FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA=‘whd’ AND TABLE_TYPE = ‘BASE TABLE’;
mysqldump –add-drop-table whd | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql whd
Here’s the new .load file-
LOAD DATABASE
FROM mysql://root:pass@172.16.1.199:3306/whd
INTO postgresql://whd:pass@172.16.1.208:5432/whd
WITH include drop, create tables, no truncate, create indexes, reset no sequences, foreign keys, downcase identifiers
SET maintenance_work_mem to '256MB', work_mem to '128MB', search_path to 'whd'
CAST type decimal to numeric keep default,
type int to int,
type datetime to timestamptz,
type blob to bytea,
type longblob to bytea,
type text to text using remove-null-characters,
type varchar to varchar keep typemod using remove-null-characters
;
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’
Columns
ID
DATA
DEFAULTACCEPT
ORIONALERTSOURCE_ID
This table has the foreign key
ORION_ALERT_FILTERS_ORION_ALERT_SOURCES_FK
Definition:
Target
orion_alert_sources (ORIONALERTSOURCE_ID → ID)
On Update
RESTRICT
On Delete
RESTRICT
Now,
ORIONALERTSOURCE_ID
bigint(20)
and
ID
bigint(20) AI PK
ERROR PostgreSQL Database error 42804:
foreign key constraint "orion_alert_filters_orion_alert_sources_fk" cannot be implemented
DETAIL: Key columns "orionalertsource_id" and "id" are of incompatible types: numeric and bigint.
QUERY: ALTER TABLE whd.orion_alert_filters ADD CONSTRAINT
orion_alert_filters_orion_alert_sources_fk
FOREIGN KEY(orionalertsource_id) REFERENCES whd.orion_alert_sources(id)
ON UPDATE RESTRICT
ON DELETE RESTRICT
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 MySQL
So 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 MySQL
Then 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-
MySQL my table names are in lower case, column names are in upper case, indexes are in upper case, foreign keys are in mixed case
https://support.solarwinds.com/Success_Center/Web_Help_Desk_(WHD)/Knowledgebase_Articles/Upgrading_to_Web_Help_Desk_12_6_for_MySQL_users
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 utf8
changed 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 WHD
client_note
NOTE_TEXT
email data object
subject
email data object
message body
client
first name
faq answer
invalid email recipient content
invalid email recipient subject
something 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-
c.s.w.s.a.i.whdapplicationmanagerimpl = failed to start WHD applicationorg.springframework.scheduling.schedulingException: Could not start Quartz Scheduler; nested exception isorg.quartz.schedulerConfigException: Failure occured during job recovery. [See nested exception: org.quartz.JobPersistenceException:Couldn’t recover jobs: ERROR ‘the operator does not exist: character verying = booleanhint: no operator matches the given name and argument type(s). you might need to add explicit type castsposition: 115 [See nested exception: org.postgres.util.PGSQLException: ERROR: operator does not exist: character varying = booleanhint: no operator matches the given name and argument type(s). you might need to add explicit type castsPosition: 115]]
Well Gunga Din, instead of committing mass murder, we finally get Solarwinds tech support on the case who advise us that we’re using the wrong JDBC connector. We’ve used the latest available one off the website (as specified in their own documentation) but nooo, we have to use this version-
https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.47.zip
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_upgrade
ok 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 again
so the cast rule that is wrong is this default rule
type tinyint to boolean when (= 1 precision) using tinyint-to-boolean
because it is migrating tinyint to boolean. As an aside, this article
https://support.solarwinds.com/Success_Center/Web_Help_Desk_(WHD)/WHD_Documentation/WHD_Admin_Guide/020_Installation_requirements/0K0_Database_datatypes
does not mention that WHD uses tinyint at all in MySQL. Fuck.
So I need to go back to MySQL, find all the tinyint values and figure out if they can be safely cast to something else. The error is about an operator with varchar, so I guess I need to understand that a bit more
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/7923127
and 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”,
whd.qrtz_job_details
is_durable character varying(1) COLLATE pg_catalog.”default” NOT NULL, is_nonconcurrent character varying(1) COLLATE pg_catalog.”default” NOT NULL,
is_update_data character varying(1) COLLATE pg_catalog.”default” NOT NULL,
requests_recovery character varying(1) COLLATE pg_catalog.”default” NOT NULL,
whd.qrtz_simprop_triggers
bool_prop_1 character varying(1) COLLATE pg_catalog.”default”, bool_prop_2 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 pgsql
So 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 chosen
Unfortunately I can’t change the data types with mysql workbench, keeps defaulting to tinyint, so I’ll do the import then make the changes
This 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.
So the final .load file was-
LOAD DATABASE
FROM mysql://root:pass@172.16.1.199:3306/whd INTO postgresql://whd:whd@172.16.1.208:5432/WHDDB WITH include drop, create tables, no truncate, create indexes, reset no sequences, foreign keys, downcase identifiers SET maintenance_work_mem to ‘256MB’, work_mem to ‘128MB’, search_path to ‘whd’ CAST type decimal to numeric keep default, type bigint with extra auto_increment to bigserial drop typemod, type bigint when (= 20 precision) to bigint drop typemod, type tinyint to boolean drop typemod
;
AND IT WORKED!
Next steps are all about getting it working on the proper machine- as right now the whd is on a different machine to the db.
Was able to get a dump by executing from
https://support.solarwinds.com/Success_Center/Web_Help_Desk_(WHD)/Knowledgebase_Articles/Back_up_and_restore_the_PostgreSQL_database_using_the_command_line
/usr/local/webhelpdesk/pgsql9/bin/pg_dump -Fc -v -h 172.16.1.208 -p 5432 -U whd WHDDB > /path/to/whd_pgdump.backup
Next step is to import this back to the correct vmtried
pgsql9/bin/pg_restore -Fc -v -h 127.0.0.1 -p 20293 -U postgres -d whd path_to_whd_pgdump.backup
but 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 first
import 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
Also I installed an SSL certificate. There seems to be various problems with the docs.
for instance, this document seems to have complete instructions for adding a cert-
https://support.solarwinds.com/Success_Center/Web_Help_Desk_(WHD)/WHD_Documentation/WHD_Admin_Guide/070_Set_up_the_application/Set_up_the_certificates
BUT-
– it does not mention the requirement to delete the old tomcat self signed cert
– does reference the need to change the alias of the cert but is not specific enough
– does not mention the required format of cert- does not mention the requirement of having passwords match cert to keystore
– does not mention that the startup log will still register an error even when the cert is properly installed
This article-https://support.solarwinds.com/Success_Center/Web_Help_Desk_(WHD)/WHD_Documentation/WHD_Admin_Guide/080_Configure_and_manage_authentication/0H0_Import_a_PKCS12_File_into_the_Keystore
does mention a couple of these things but it needs to be all in one place or referenced from a single place. Hint- use Portecle with
sudo java -jar Portecle.jar
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 etc
2. migrate the database
3. export and re-import into built in pgsql
4. 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 sent
Clayton 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…