5 Replies Latest reply on Jun 21, 2018 8:15 PM by adamc

    Migrating from MySQL to Postgres- error

    adamc

      I've been migrating from MySQL 5.6.40 to Postgres 9.2 via PGLoader. There were some initial migration issues that I thought I had overcome, but I can't get WHD to use the new database, login is fine but when it loads it gets to the finish and fails with-

       

      WARNING: New column type ColumnType: 'int4' p31 s0 w0 is different type than existing column type ColumnType: 'int8' p19 s0 w0; update not supported by MDSPostgreSQLPlugIn.

       

      Now please excuse me - I'm not a database specialist- but I have no idea what this means.

       

      If anyone recognises this error can you please tell me what it means and if i can fix it?

       

      cheers

       

      Adam

        • Re: Migrating from MySQL to Postgres- error
          fluffy midnight

          Hi adamc,

           

          I would start by making sure the data types are the same, if I recall correctly PostgreSQL 9.2 doesn't support integer-8 values and yet your error refers to the PSQL data being 'int4' meanwhile it's trying to go to MySQL as 'int8' which it can't do...

           

          You should be able to change the column type properties so it's int-4 on the MySQL side, then try again.

           

          -Midnight

            • Re: Migrating from MySQL to Postgres- error
              adamc

              sorry for the late reply, I've been fighting with this for weeks and because it's a database issue Solarwinds aren't interested.

               

              Here's what I found out-

              Postgres DOES support Bigint values but the error turned out to be related to the setup of the MySQL database. Something about 2 data fields with incompatible data types, that can't be munged together. I think the solution to this would be to set the data types correctly, but I still don't know how to do that.

               

              I decided to skip the database transformation and use MySQL on the target machine- and it still won't work!

              -the new error seems to be connected to the time zone setup on the updated jdbc connector

               

              I'm disappointed that it would have been easier to change help desk products than figure this out myself. But I guess I've learned something.

               

              Thanks for your help fluffy midnight

            • Re: Migrating from MySQL to Postgres- error
              adamc

              I just found the exact errors so I will include this for completeness-

               

              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.

                • Re: Migrating from MySQL to Postgres- error
                  fluffy midnight

                  Hi adamc,

                   

                  You need to modify the key constraint so it can take the appropriate value, take a look at the one in PostgreSQL and then set SQL's to the same one and try again.

                   

                  Each error you've provided so far are related to the configuration of the database you are migrating to rather than from PostgreSQL.

                   

                  Take a slightly older JDBC driver for MySQL, there's little difference and it doesn't affect the way Web Help Desk communicates to the database. You can get the driver at either of these two links: MySQL J Connector, MySQL J Connector (old)

                  Once you have it you need to place it into the help desk installation folder located here:

                  Windows: \Program Files\WebHelpDesk\bin\jre\lib\ext

                  Linux: /usr/local/webhelpdesk/bin/jre/lib/ext

                   

                  You also want to confirm that when you set up the new database, that you set the schema's collation type to utf8 (utf8_general_ci) as this can change and control some of the key constraints.

                   

                  -Midnight

                    • Re: Migrating from MySQL to Postgres- error
                      adamc

                      ok perhaps my understanding of this is incorrect?

                      When migrating from MySQL to Postgres,  the translation program 'PGLoader' exports ok but when WHD tries to use the database I get

                       

                      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.

                       

                      To me, this indicates that there is a foreign key constraint made up of 2 columns, "orionalertsource_id" and "id". Those columns are 2 different data types, and Postgres can't deal with this until one of the data types matches or is compatible with the other.

                      This means that I can solve this issue by changing either "orionalertsource_id" or "id" data type so that they are both EITHER numeric or both bigint -probably on the MySQL database before migration

                       

                      I have asked Solarwinds for hints on this and they aren't interested. The reason I asked them is because they created the columns in my database and they should know what is the best way around this issue.

                       

                      According to this

                      Database datatypes - SolarWinds Worldwide, LLC. Help and Support

                       

                      WHD won't use 'Bigint' so I would have to change that. It would be nice to get confirmation from Solarwinds.

                       

                      If I have misunderstood here please let me know.